CODEWITHSUNDEEP
pythonmysql
SAR622
SAR622

Reputation: 637

TypeError when creating a MySQL table with parameters

Something is wrong either with this execute statement or with the list I'm passing into it.

I keep getting:

Traceback (most recent call last):
  File "/Users/Hardway/test_package/sqltest5.py", line 12, in <module>
    cur.execute(create_table, ('userid', 'age_of_account', 'number_of_friends', 'DA_MA', 'user_quality'))    
  File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.11-intel.egg/MySQLdb/cursors.py", line 184, in execute
    query = query % db.literal(args)
TypeError: not all arguments converted during string formatting

I've now tried everything (forcing tuples, 3 types of param variables in the string, etc) and could use some help. What am I missing?

import MySQLdb as MS
db1 = MS.connect(host="localhost", user="yosef",passwd="yosef", db ='test2')

cur = db1.cursor()

header = ('userid', 'age_of_account', 'number_of_friends', 'DA_MA', 'user_quality')
drop_table = "DROP TABLE IF EXISTS Users"
create_table = """Create table Users ({0} int, {1} int, {2} int, {3} float, {4} varchar(10))"""

try:
    cur.execute(drop_table)
    cur.execute(create_table, header,)    

    db1.commit()

except MS.Error, e:
    print "That failed"
    print e.args[0], e.args[1]
    db1.rollback()

finally:
    db1.close()

Upvotes: 1

Views: 83

Answers (2)

Eugene Yarmash
Eugene Yarmash

Reputation: 149776

You can't pass column names as query parameters to cursor.execute(). You'll have to use string formatting to interpolate them into the query:

create_table = ("CREATE TABLE Users ({} INT, {} INT, {} INT, {} FLOAT,"
                "{} VARCHAR(10))".format(*header))
cur.execute(create_table)

Of course, you need to be extremely careful that header doesn't contain user input.

Upvotes: 1

Ajeet Shah
Ajeet Shah

Reputation: 19813

You can pass values in MySQLCursor.execute but not the column names. So, use str.format:

create_table = "Create table Users ({0} int, {1} int, {2} int, {3} float, {4} varchar(10))".format(*header)
cur.execute(create_table)

Note that you need to use * to expand the tuple when using with str.format.

Upvotes: 1

Related Questions