Why Doesn't This MySQL Statement Execute?

T

Tom Borkin

Hi;
I have this test code:

if i_id == "1186":
sql = 'insert into interactions values(Null, %s, "Call Back", "%s")'
% (i_id, date_plus_2)
cursor.execute(sql)
db.commit()
print sql
It prints the sql statement, but it doesn't execute. If I copy and paste
the sql into the mysql command line it does execute without warnings or
errors. What gives?
TIA,
Tom
 
H

Hans Mulder

Hi;
I have this test code:

if i_id == "1186":
sql = 'insert into interactions values(Null, %s, "Call Back",
"%s")' % (i_id, date_plus_2)
cursor.execute(sql)
db.commit()
print sql
It prints the sql statement, but it doesn't execute. If I copy and paste
the sql into the mysql command line it does execute without warnings or
errors. What gives?

What happens if you do:


if i_id == "1186":
sql = 'insert into interactions values(Null, %s, "Call Back", %s)'
cursor.execute(sql, (i_id, date_plus_2))
db.commit()
print sql

Note the absence of quotes around the second %s in the sql command.

This should work correctly even if date_plus_2 happens to contain

Robert"); DROP TABLE interactions; --


For background information, see http://bobby-tables.com/python.html


Hope this helps,

-- HansM
 
T

Tom Borkin

Actually, what I originally had was:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""", (i_id, date_plus_2))
and that didn't work, either. I tried your variation like:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""" % (i_id, date_plus_2))
and no cigar :(
Tom
 
J

John Gordon

In said:
Actually, what I originally had was:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""", (i_id, date_plus_2))
and that didn't work, either. I tried your variation like:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""" % (i_id, date_plus_2))
and no cigar :(
Tom

Have you tried using single-quotes around Call Back, instead of
double quotes? I've noticed that SQL statements prefer single-quoted
strings (although that may be Oracle specific, as that's all I've really
worked with).
 
C

Chris Angelico

Have you tried using single-quotes around Call Back, instead of
double quotes? I've noticed that SQL statements prefer single-quoted
strings (although that may be Oracle specific, as that's all I've really
worked with).

The SQL standard specifies single quotes, but MySQL and the SQL
standard aren't always on speaking terms. It depends on the MySQL
settings as to whether "asdf" means 'asdf' or means a column named
asdf.

But if that's what the problem is, there ought to be an exception
coming back, surely? I'm not familiar with the Python MySQL bindings,
but that's what I would expect. What, specifically, does "no cigar"
mean? It executes without errors but does nothing? It purchases a gun,
aims at your shoe, and pulls the trigger?

ChrisA
 
D

Dennis Lee Bieber

The SQL standard specifies single quotes, but MySQL and the SQL
standard aren't always on speaking terms. It depends on the MySQL
settings as to whether "asdf" means 'asdf' or means a column named
asdf.

But if that's what the problem is, there ought to be an exception
coming back, surely? I'm not familiar with the Python MySQL bindings,
but that's what I would expect. What, specifically, does "no cigar"
mean? It executes without errors but does nothing? It purchases a gun,
aims at your shoe, and pulls the trigger?
Well... off the wall...

I'm going to presume the first field -- the Null -- is for a primary
key (autoincrement)...

What happens if you change the SQL to actually specify the columns
being filled AND you ignore the primary key/Null entry?

And since I don't like hard-coding the SQL...

cursor.execute("""insert into interactions (someID, action, somedate)
values (%s, %s, %s)""",
(i_id, "Call Back", date_plus_2) )

Of course you need to use the actual table column names...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,835
Latest member
KetoRushACVBuy

Latest Threads

Top