How to insert data with SQlAlchemy declarative way

Question

I can add data to my db this way:

a = Model_tbl_name("3", "da", "3", "eheeee", "", "", "", "", "", "", "", "", func.now(), func.now()) 

db_session.add(a) 
db_session.commit()

But i can't do it this way:

data = Model_tbl_name.insert().values({"title_hr":request.form['title_hr'],"text_hr":request.form['text_hr']})

I tried similar, but no help:

data = db_session.Model_tbl_name.insert().execute({"title_hr":request.form['title_hr'],"text_hr":request.form['text_hr']})

My initial motivation is to pass all form data like JSON, i would like to have it like this to work:

data = db_session.Model_tbl_name.insert().execute(json.loads(new_request_form))

In documentation, it is stated it can be done: http://docs.sqlalchemy.org/en/rel_0_9/core/dml.html?highlight=insert%20values#sqlalchemy.sql.expression.Insert.values

like this:

users.insert().values({"name": "some name"})

But no help, I just can't get it. How can i make it to work, must i provide all JSON data in values() method? How should I write that command to make it work?

Second, how can I grab that error, because, I get no error in Flask, only stops working. I can figure out how to display errors when working with SQLAlchemy declarative way.

P.S. I am using Flask framework, SQLAlchemy, and Python version is 3.4

What is the error you are getting? How do you know it is not succeeding? — Boaz, Jul 21 '15 at 07:41
Boaz, i know because there is no insert into db. I'm not getting any error, and I think it is because SQLAlchemy handles and states that error somewhere in it's scope I would say it like that. I just need proper command for storing insert this way, nothing else! — DarioBB, Jul 21 '15 at 13:49
I understand but since it looks correct, I'm trying to get some more info from you in order to help. What are you getting in data. + a stupid question just to make sure- did you do db_session.commit() in the new case? — Boaz, Jul 21 '15 at 13:58
Boaz, i just do not get any error. I did add db_session.commit(). I won't even wirte a log file after this: data = Model_tbl_name.insert().values({"title_hr":request.form['title_hr'],"text_hr":request.form['text_hr']}) My conclusion is that SQLAlchemy obviously handles that error inside it's scope so that is why i can't print it. Is there any suitable way to display that error? I'm trying with: import logging logging.basicConfig(filename='db.log') logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG) but no luck with it — DarioBB, Jul 21 '15 at 14:29
Boaz, I managed to debug it at some level. I am getting this error: "AttributeError: 'scoped_session' object has no attribute 'Model_tbl_name'" If I write it like this: data = db_session.Model_tbl_name.insert().values({"orderby":"3", "front_page":"da", "categories_id":"3", "title_hr":"eheeee", "title_en":"", "sifra":"", "price":"", "text_hr":"", "text_en":"", "video_title_hr":"", "video_title_en":"", "video_url":"", "created":func.now(), "modified":func.now()}) db_session.add(data) db_session.commit() How sould i write it to save it to db? — DarioBB, Jul 21 '15 at 16:17

score 3 · Answer 1 · answered Jul 22 '15 at 08:59

Well, I don't think you can do db_session.Class_name.
This is what you can do with it: http://docs.sqlalchemy.org/en/rel_1_0/orm/session_basics.html

If your problem is the default values, you can define relevant default values in the Model, and then you can construct an object with less fields.

a = Model_tbl_name(title_hr=request.form['title_hr'],
                   text_hr=request.form['text_hr'])

If you still want to pass the dictionary, you can do the following:

dataDict = json.loads(new_request_form)
a = Model_tbl_name(**dataDict)
db_session.add(a) 
db_session.commit()

IMHO - the first method is more readable and reliable (you can check for validity of the params - that they match the object) -> at the end of the day, more secure...

Thank you very much Boaz, I actually achieved that just before reading your message! Can you tell me just what ** signs before dataDict in () do? Couldn't you just wrote: Model_tbl_name(json.loads(new_request_form)) ? — DarioBB, Jul 22 '15 at 13:32
kwargs in meant for a function to take unknown number of variables. The "side effect" of it is that it enables you to pass a dict. Explained really nicely here http://stackoverflow.com/questions/1769403/understanding-kwargs-in-python — Boaz, Jul 22 '15 at 13:40

score 2 · Answer 2 · answered Aug 18 '19 at 09:15

2

if you need the insert way (let's say because you want to pass .prefix_with('IGNORE'))

you can do it like this

session.execute(Model_tbl_name.__table__.insert().values(
  col1=val1, col2=val2).prefix_with('IGNORE'))

answered Aug 18 '19 at 09:15

Muayyad Alsadi

1,506
15
23

score 1 · Answer 3 · answered Jun 02 '17 at 11:36

it works good with Connection class:

        import sqlalchemy as sa

        conn.execute(
            sa.insert(Model_tbl_name).values(
                {
                    Model_tbl_name.field1: 'value1',
                    Model_tbl_name.field2: 'value2',
                    Model_tbl_name.field3: 'value3', 
                }
            )
        )

How to insert data with SQlAlchemy declarative way

3 Answers3