select from insert into not working with sqlalchemy

Question

I want to insert a record in mytable (in DB2 database) and get the id generated in that insert. I'm trying to do that with python 2.7. Here is what I did:

import sqlalchemy
from sqlalchemy import *
import ibm_db_sa

db2 = sqlalchemy.create_engine('ibm_db_sa://user:pswd@localhost:50001/mydatabase')
sql = "select REPORT_ID from FINAL TABLE(insert into MY_TABLE values(DEFAULT,CURRENT TIMESTAMP,EMPTY_BLOB(),10,'success'));"
result = db2.execute(sql)
for item in result:
    id = item[0]
print id

When I execute the code above it gives me this output:

10 //or a increasing number

Now when I check in the database nothing has been inserted ! I tried to run the same SQL request on the command line and it worked just fine. Any clue why I can't insert it with python using sqlalchemy ?

@Lennart I don't think that commit is the problem because when I run a simple INSERT INTO with the same code it insert the record correctly. — deltascience
– deltascience, Commented Mar 11, 2015 at 15:14

Renier · Accepted Answer · 2015-03-12 07:04:58Z

3

Did you try a commit? @Lennart is right. It might solve your problem.

Your code does not commit the changes you have made and thus are rolled back. If your Database is InnoDB, it is transactional and thus needs a commit.

according to this, you also have to connect to your engine. so in your instance it would look like:

db2 = sqlalchemy.create_engine('ibm_db_sa://user:pswd@localhost:50001/mydatabase')
conn = db2.connect()
trans = conn.begin()
try:
    sql = "select REPORT_ID from FINAL TABLE(insert into MY_TABLE values(DEFAULT,CURRENT TIMESTAMP,EMPTY_BLOB(),10,'success'));"
    result = conn.execute(sql)
    for item in result:
        id = item[0]
    print id
    trans.commit()
except:
    trans.rollback()
    raise

I do hope this helps.

answered Mar 12, 2015 at 7:04

Renier

1,5454 gold badges34 silver badges63 bronze badges

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

1 Comment

dwanderson Over a year ago

Out of curiosity, is trans.rollback() necessary? I would think that if you never commit(), it should rollback(), and I would think if your trans encountered an exception, you'd want to conn.end() or trans.end() or whatever the case might be? I'm not sure about any of the above though; trying to check my assumptions. Otherwise, though, this solved my issue. Not sure why other INSERT statements succeed without requiring a commit(), but that's for a different post, I guess. Anyways, thanks!

Collectives™ on Stack Overflow

select from insert into not working with sqlalchemy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related