Why Doesn't This MySQL Statement Execute?

Discussion in 'Python' started by Tom Borkin, Dec 18, 2012.

  1. Tom Borkin

    Tom Borkin Guest

    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
     
    Tom Borkin, Dec 18, 2012
    #1
    1. Advertising

  2. Tom Borkin

    Hans Mulder Guest

    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
     
    Hans Mulder, Dec 18, 2012
    #2
    1. Advertising

  3. Tom Borkin

    Tom Borkin Guest

    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
     
    Tom Borkin, Dec 19, 2012
    #3
  4. Tom Borkin

    John Gordon Guest

    In <> Tom Borkin <> 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
    B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
     
    John Gordon, Dec 19, 2012
    #4
  5. On Wed, Dec 19, 2012 at 2:57 PM, John Gordon <> wrote:
    > In <> Tom Borkin <> 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
     
    Chris Angelico, Dec 19, 2012
    #5
  6. On Wed, 19 Dec 2012 15:49:41 +1100, Chris Angelico <>
    declaimed the following in gmane.comp.python.general:

    > On Wed, Dec 19, 2012 at 2:57 PM, John Gordon <> wrote:
    > > In <> Tom Borkin <> 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
    HTTP://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Dec 19, 2012
    #6
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Roedy Green
    Replies:
    1
    Views:
    793
    Aquila Deus
    Aug 22, 2005
  2. Mr. SweatyFinger
    Replies:
    2
    Views:
    1,984
    Smokey Grindel
    Dec 2, 2006
  3. Replies:
    21
    Views:
    1,060
    Giannis Papadopoulos
    Aug 2, 2005
  4. Wayne Werner
    Replies:
    0
    Views:
    133
    Wayne Werner
    Dec 18, 2012
  5. Chris Angelico
    Replies:
    0
    Views:
    153
    Chris Angelico
    Dec 18, 2012
Loading...

Share This Page