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)

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks