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

unutbu · Accepted Answer · 2013-07-17 02:54:10Z

2

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'

edited Jul 17, 2013 at 2:54

answered Jul 17, 2013 at 2:01

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

4 Comments

Max Kim Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

['%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.

Collectives™ on Stack Overflow

Insert into rows from Python into MySQL

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related