TypeError when creating a MySQL table with parameters

Question

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

so this you already tried: create_table = "Create table Users ({0} int, {1} int, {2} int, {3} float, {4} varchar(10))".format('userid', 'age_of_account', 'number_of_friends', 'DA_MA', 'user_quality')? — Ma0
– Ma0, Commented Jun 27, 2016 at 13:06
Sure. As soon as I get to sit down in front of a computer and test them. Right now I've got a toddler I'm putting to bed. Doesn't make me any less grateful for the awesomeness of this community but it has prevented me from testing the code. :) — SAR622
– SAR622, Commented Jun 27, 2016 at 17:29

Eugene Yarmash · Accepted Answer · 2016-06-27 17:12:30Z

1

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.

edited Jun 27, 2016 at 17:12

answered Jun 27, 2016 at 13:06

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:10:51Z

1

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.

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Jun 27, 2016 at 17:04

Ajeet Shah

19.9k10 gold badges66 silver badges107 bronze badges

Collectives™ on Stack Overflow

TypeError when creating a MySQL table with parameters

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related