Adding Tuple to execute?

Help
Alsandair
2011-04-27
2012-09-19
1 2 > >> (Page 1 of 2)
  • Alsandair
    Alsandair
    2011-04-27

    Hi all, I have a problem passing a tuple to cursor.execute()

    this works:

    sql="SELECT NAME FROM ListsTable WHERE ID=%s"%(ListID)

    c.execute(sql)

    as does this (IE the variable as a single element):

    sql="SELECT NAME FROM ListsTable WHERE ID=%s"

    c.execute(sql, ListID)

    but passing it as a single element tuple doesn't:

    sql="SELECT NAME FROM ListsTable WHERE ID=%s"

    c.execute(sql,(ListID,))

    (This doesn't always throw an error but sometimes it gives:)

    File "/var/www/cgi-bin/MySQLdb/cursors.py", line 159, in execute

    query = query % db.literal(args)

    TypeError: not enough arguments for format string

    This is a problem as other queries have multiple variables so i need to pass
    them as a tuple.. anyone got any ideas? I'm using python 2.3.6 and mysqldb
    1.2.3

    cheers

     
  • Cat slave
    Cat slave
    2011-04-27

    In your context, it's weird to have multiple values for "ID=?". Can you show
    the content of "ListID" ?

    As for example, here is what you may be able to do:

    sql="SELECT NAME FROM ListsTable WHERE ID IN (%s)" % (','.join(['%s'] * len(ListID)),)
    c.execute(sql, ListID)   or c.execute(sql, *ListID)
    

    I can't remember the exact syntax.

    Each element of ListID need a '%s' to be replaced by execute().

     
  • Alsandair
    Alsandair
    2011-04-27

    there's only one value for ID, ListID is a single numeric value, the example
    above was just descriptive of the problem, sorry :)

    I'm fine to use ListID as a single parameter in the way that works above, but
    other queries will need multiple parameters which is where the problem kicks
    in

     
  • Cat slave
    Cat slave
    2011-04-27

    You still should not used this way as you will get used to it. Imagine your
    value for another query would be:

    value = "it's ok"
    sql = "select * from t where status = %s" % (value,)
    c.execute(sql)
    

    will break because of invalid sql.

     
  • Alsandair
    Alsandair
    2011-04-27

    That's strange, the docs said mysqldb doesn't accept single parameters but
    would accept a single element tuple? i thought that was how it was supposed to
    be done?

    How would i pass 2 parameters like i'm trying to do here?

    sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"

    c.execute(sql,(Page,(Page*20)+20))

     
  • Alsandair
    Alsandair
    2011-04-27

    this one works btw so i'm confident the parameters are fine:

    sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"%(Page,
    (Page*20)+20)

    c.execute(sql)

     
  • Cat slave
    Cat slave
    2011-04-27

    If you have a table like this:

    mysql> create table t (lastname varchar(30), firstname varchar(30));
    

    Your method only works with "non-delimited values" (like numbers), but won't
    work with strings.

    sql = 'select * from t where lastname=%s and firstname=%s' % ('last', 'first',)
    cur.execute(sql)
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 166, in execute
        self.errorhandler(self, exc, value)
      File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
        raise errorclass, errorvalue
    _mysql_exceptions.OperationalError: (1054, "Unknown column 'last' in 'where clause'")
    

    You would need to do it like this:

    >>> cur.execute('select * from t where lastname=%s and firstname=%s', ('last', 'first'))
    

    This will also prevent SQL injections by correctly escaping the values.

    Hope this clears things for you.

     
  • Alsandair
    Alsandair
    2011-04-28

    yeah because the script uses user data I'd like to paramaterise the data like
    your second example, unfortunately the string substitution method you used
    (first one) works, whereas the correct way with tuples in your second example
    doesn't work, as in my code snippets above :(

     
  • Alsandair
    Alsandair
    2011-04-28

    Just to clarify what I'm asking:

    Is there any reason why your tuple method wouldn't work even though the data
    is definitely fine? I've posted examples of my attempts above.

    String substitution method works fine

    Passing the values as a tuple doesn't work (The values and table are
    definitely fine, as they work for string substitution)

    No idea why.

     
  • Cat slave
    Cat slave
    2011-04-28

    Can you repost exactly what you did that worked and that not worked.

    Use BBCode please ( b l a h b l a h )

     
  • Alsandair
    Alsandair
    2011-05-09

    Just as a quick check, is mysqldb compatible with python 2.3.6? it said it was
    on the site I got it from, but the errors I get are from within the mysqldb
    library itself (any mention of conn.literal gives the errors like below:)

    File "/var/www/cgi-bin/MySQLdb/cursors.py", line 159, in execute

    query = query % db.literal(args)

    TypeError: not enough arguments for format string

    even if i try to use .literal in my main code to rule out the mysqldb library

     
  • Alsandair
    Alsandair
    2011-05-10

    anyway, to answer your request.

    This works:

    sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"%(Page, (Page*20)+20)
    c.execute(sql)
    

    When I try to paramaterize it however, it won't accept the arguments in a
    tuple:

    sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"
    var=Page
    var2=(Page*20)+20
    params=(var,var2)
    c.execute(sql,params)
    

    instead, the latter gives this error:

    Traceback (most recent call last):
      File "process.py", line 202, in ?
        main()
      File "process.py", line 97, in main
        c.execute(sql,params)
      File "/var/www/cgi-bin/MySQLdb/cursors.py", line 148, in execute
        query = query % db.literal(args)
    TypeError: not enough arguments for format string
    

    I've tried downgrading to mysqldb 1.2.1_p2 but it's giving this same result
    (My version of python is 2.3.6)

     

  • Anonymous
    2011-05-10

    Can you insert a print statement before your execute() call and print sql,
    params

     
  • Alsandair
    Alsandair
    2011-05-10

    hi, because it's on a webserver i normally just dump values to a text file,
    here's what I've done for those values:

    dumped.write("params is "+str(params[0])+","+str(params[1])+"\nsql is "+sql)
    

    the result:

    params is 0,20
    sql is SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s
    

    (dumping params to the text file was erroring as they're ints so i had to cast
    to str, if that helps)

     

  • Anonymous
    2011-05-10

    ... Try this instead:

    dumped.write("sql = "+repr(sql)+"; params = "+repr(params)")
    
     

  • Anonymous
    2011-05-10

    Err, remove that last "

     
  • Alsandair
    Alsandair
    2011-05-10

    cheers :) that gives:

    sql = 'SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s'; params = (0, 20)
    
     

  • Anonymous
    2011-05-10

    And for that combination, you get the TypeError?

     
  • Alsandair
    Alsandair
    2011-05-10

    yup :( it's weird, I was sure in the docs it said the input ALWAYS had to be a
    tuple, but like i said in my first post, i can pass a single int as the 2nd
    element, but if i tried to send it as a single-element tuple it failed :/

    to test it, I just tried to output this from my main, but got the same error
    as I usually get in th elib, just this time in m ymain (db is the name of my
    database object)

     db.literal((1,2))
    

    trying to print that from main gave the same TypeError, which was why I was
    wondering maybe if the versions of python and mysqldb I'm using aren't
    compatible

    beyond that I've no clue :(

     
  • Alsandair
    Alsandair
    2011-05-10

    The same variables work normally in a string substitution so i know the values
    etc should be fine, I'm just going over the script now that i know it all
    works and trying to move them instead as second paramaters to execute for
    security and that's where it all dies, so the actual sql logic and values
    passed to it should definitely be fine

     
  • Cat slave
    Cat slave
    2011-05-10

    Sorry, I don't have access to a python 2.3 to test it. I only have 2.6
    available.

     
  • Alsandair
    Alsandair
    2011-05-10

    That's ok, I've already downgraded my mysqldb to 1.2.1, i might downgrade it a
    few more times to test it in a few minutes

     
  • Alsandair
    Alsandair
    2011-05-10

    ah ok, downgrading to mysqldb 1.0.0 changes from the script crashing and
    producing the above error in the error log, at least this time it behaves a
    big better and just triggers an except which writes the following to a text
    file:

    ('not enough arguments for format string',)
    

    which is the same problem I guess, I think it's cursed

     
  • Alsandair
    Alsandair
    2011-05-10

    oh, also something else (it's 4am here and my mind's wandering..) when i
    downgraded to 1.0.0 i got this error:

    File "/var/www/cgi-bin/MySQLdb/__init__.py", line 31, in ?
    raise ImportError, "this is MySQLdb version %s, but _mysql is version %s" %\\
    ImportError: this is MySQLdb version (1, 0, 0, 'final', 1), but _mysql is version (1, 2, 1, 'final', 2)
    Premature end of script headers: process.py
    

    also had this with 1.2.3 (but not 1.2.1)

    since i wiped the library off my webserver and copied everything from the same
    package on this site I don't know why that error would be there (I just
    commented out the error check in init to test it)

    don't want to doubt the libraries provided on this project page though, I'm
    assuming them to be ok..

     
  • Alsandair
    Alsandair
    2011-05-30

    Does anyone have any ideas?

     
1 2 > >> (Page 1 of 2)