Python 3 pyodbc SQL query

I am using Python 3.3 with MS SQL server. I want to execute an insert in a table with identity column and at the same time to get back the identity value vor the new entry with either @@IDENTITY global variable or scope_identity() function:

sql = "INSERT INTO table (col) VALUES (somevalue)\n"
sql=sql+"SELECT scope_identyty()
result=cursor.execute(sql)
result.fetchone()

Open in new window

If I try this I will get the error that says "the command was not a query". I don't want to do this in 2 steps because I want to avoid network traffic and also to gain speed.  

Is it possible to do this in Python 3?
LVL 27
ZberteocAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
try the following query:

INSERT INTO table (col)
OUTPUT Inserted.IDColumn
VALUES (somevalue)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocAuthor Commented:
Works like a charm with SQL server. Thanks,  BriCrowe, you are on the points list. :)

However, I will wait for other suggestion because I need to be able to do that in other platforms as well, i.e. MySQL.
0
Brian CroweDatabase AdministratorCommented:
For mySql try using

INSERT INTO table(col)
VALUES (somevalue);
SELECT LAST_INSERT_ID()

Don't forget the semi-colon between commands.  Your original SQL solution probably would have worked with the semi-colon as well.
0
ZberteocAuthor Commented:
That doesn't work with SQL. I haven't tried it with mysql though. It is possible the mysql driver will support it, in which case problem solved. I can't test it right now. Can anyone confirm?
0
ZberteocAuthor Commented:
That worked. For now SQL is my concern.

Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.