SQL query works in console but not in python

Question

Hi im having issues with a sql query it works perfect in console, but when i implement into python it seems to work perfect no errors but when i check the database it hasnt worked, yet with the console it does work the same no errors yet when i check db the data is there... exact same query i use.

Any ideas?

UPDATE ex SET fbsiteurl = stringvarible, fbsitesource = '' WHERE id = 23123;

in python:

 cur = con.cursor()
 sqlquery = "UPDATE ex SET fbsiteurl = '"+somevarible+"', fbsitesource =        '"+somevarible+"' WHERE id = %d;" % recordid                                
 print sqlquery
 cur.execute(sqlquery)

query shows up fine in print no issues, if i copy the print out and paste it into a mysql console it works perfect everytime, just come python it acts like it works but dosnt really 0_o

Please don't add strings together to create your query. That's incredibly dangerous. Read your database library's documentation on "parameterized queries". For instance, that might be written as "sqlquery = 'UPDATE ex SET fbsiteurl = %s, fbsitesource= %s WHERE id = %s'; cur.execute(sqlquery, (somevariable, anothervariable, recordid))". This is critically important! — Kirk Strauser
– Kirk Strauser, Commented Jul 28, 2012 at 23:57

scott · Accepted Answer · 2012-07-28 23:49:36Z

4

connection.autocommit(), or you need to do connection.commit()

answered Jul 28, 2012 at 23:49

scott

1,4882 gold badges16 silver badges30 bronze badges

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

1 Comment

kjh Over a year ago

This worked for me as well, why do you need to do this?

Anze Jarni · Accepted Answer · 2012-07-28 23:27:40Z

1

Been there :) you need to close the cursor

answered Jul 28, 2012 at 23:27

Anze Jarni

1,1677 silver badges7 bronze badges

1 Comment

scott Over a year ago

closing the curser didnt help, how very strange this all is.

J B · Accepted Answer · 2021-03-23 13:10:34Z

0

This little gotcha continues to this day. Just to clarify, I had to use both of the above answers, as in:

        cur = self.db.cursor()
        try:
            cur.execute(sqlcommand)
            self.db.commit()
            res = cur.fetchall()
        except res is not None:
            print(res)
        finally:
            cur.close()

answered Mar 23, 2021 at 13:10

J B

4803 silver badges6 bronze badges

Collectives™ on Stack Overflow

SQL query works in console but not in python

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related