Insert into rows from Python into MySQL

Question

I was creating queries in Python to populate rows in a local database using MySQL. My variable product is a tuple, which holds 33 values. I want to add all of these values into appropriate columns listed in a table called roottable (which I created in dbForge). I was getting an error in line con.execute():

TypeError: not all arguments converted during string formatting

Not sure what I'm doing wrong. I am applying the same syntax as SQlite's. Here is my code:

connection = msql.connect(host = 'localhost', user = 'me', passwd = 'password', db = 'TESTDB')
with connection:
 for product in list_product:
 #Get a tuple of standardized informtaion to store in table
 product = normalize_table_entry(product)
 con = connection.cursor()
 con.execute('INSERT INTO roottable VALUES (?,?,?,?,?,?,?,?,?,\
 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', product)
 #connection.commit()

Answer 1

Are you using MySQLdb? Unlike sqlite3, MySQLdb uses %s as the parameter marker, not ?. So, in that case, try

sql = 'INSERT INTO roottable VALUES ({})'.format(','.join(['%s']*33))

---

connection = msql.connect(host = 'localhost', user = 'me',
 passwd = 'password', db = 'TESTDB')
sql = 'INSERT INTO roottable VALUES ({})'.format(','.join(['%s']*33))
with connection:
 for product in list_product:
 #Get a tuple of standardized information to store in table
 product = normalize_table_entry(product)
 con = connection.cursor()
 con.execute(sql, product)
 #connection.commit()

---

The expression ','.join(['%s']*33) is best understood by looking at a smaller example:

In [25]: ['%s']*3
Out[25]: ['%s', '%s', '%s']
In [26]: ','.join(['%s']*3)
Out[26]: '%s,%s,%s'

• Max Kim: Yes, I'm using MySQLdb. Does the %s convert values into a string? Or could values be of any type? And if you don't mind, could you explain ['%s']*33
• unutbu: The values in product can be of any type. The database adapter should handle converting and quoting the values to strings which are then passed to the MySQL server. ['%s']*33 evaluates to a list of 33 items, each item being the string '%s'. In general, multiplying a list by an integer, n, produces a list with n shallow copies of the original list. Try it in an interactive session and see!
• Max Kim: So when you do ','.join(['%s']*33), your original list is of one element and you create 33 copies of the first element to define a new list?
• unutbu: ['%s']*3 is a list of 3 items, and ','.join(...) returns a string with the 3 items joined together with commas in between. I've update the post above, demonstrating this with a small example.