Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Why Doesn't This MySQL Statement Execute?

Reply
Thread Tools

Why Doesn't This MySQL Statement Execute?

 
 
Tom Borkin
Guest
Posts: n/a
 
      12-18-2012
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

 
Reply With Quote
 
 
 
 
Hans Mulder
Guest
Posts: n/a
 
      12-18-2012
On 18/12/12 22:34:08, Tom Borkin wrote:
> 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
 
Reply With Quote
 
 
 
 
Tom Borkin
Guest
Posts: n/a
 
      12-19-2012
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

 
Reply With Quote
 
John Gordon
Guest
Posts: n/a
 
      12-19-2012
In <(E-Mail Removed)> Tom Borkin <(E-Mail Removed)> writes:

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

--
John Gordon A is for Amy, who fell down the stairs
http://www.velocityreviews.com/forums/(E-Mail Removed) B is for Basil, assaulted by bears
-- Edward Gorey, "The Gashlycrumb Tinies"

 
Reply With Quote
 
Chris Angelico
Guest
Posts: n/a
 
      12-19-2012
On Wed, Dec 19, 2012 at 2:57 PM, John Gordon <(E-Mail Removed)> wrote:
> In <(E-Mail Removed)> Tom Borkin <(E-Mail Removed)> writes:
>
>> 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).


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
 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      12-19-2012
On Wed, 19 Dec 2012 15:49:41 +1100, Chris Angelico <(E-Mail Removed)>
declaimed the following in gmane.comp.python.general:

> On Wed, Dec 19, 2012 at 2:57 PM, John Gordon <(E-Mail Removed)> wrote:
> > In <(E-Mail Removed)> Tom Borkin <(E-Mail Removed)> writes:
> >
> >> 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).

>
> 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...
--
Wulfraed Dennis Lee Bieber AF6VN
(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Why Doesn't This MySQL Statement Execute? Chris Angelico Python 0 12-18-2012 10:42 PM
Re: Why Doesn't This MySQL Statement Execute? Wayne Werner Python 0 12-18-2012 10:02 PM
why why why why why Mr. SweatyFinger ASP .Net 4 12-21-2006 01:15 PM
findcontrol("PlaceHolderPrice") why why why why why why why why why why why Mr. SweatyFinger ASP .Net 2 12-02-2006 03:46 PM



Advertisments