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
– Boaz, Commented Jul 21, 2015 at 7: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
– DarioBB, Commented Jul 21, 2015 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
– Boaz, Commented Jul 21, 2015 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
– DarioBB, Commented Jul 21, 2015 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
– DarioBB, Commented Jul 21, 2015 at 16:17

Boaz · Accepted Answer · 2015-07-22 08:59:59Z

3

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...

answered Jul 22, 2015 at 8:59

Boaz

5,08413 gold badges55 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DarioBB Over a year ago

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)) ?

Boaz Over a year ago

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 stackoverflow.com/questions/1769403/…

Muayyad Alsadi · Accepted Answer · 2019-08-18 09:15:23Z

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, 2019 at 9:15

Muayyad Alsadi

1,60316 silver badges25 bronze badges

Comments

Alex-Bogdanov · Accepted Answer · 2017-06-02 11:36:35Z

0

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', 
                }
            )
        )

answered Jun 2, 2017 at 11:36

Alex-Bogdanov

2,4561 gold badge22 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to insert data with SQlAlchemy declarative way

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related