TypeError: not all arguments converted during string formatting While inserting two python lists in Mysql

Question

I imported MySQLdb, and established a database connection.

I am having two lists like below:

var_list = ['INFY','WIPR','SUZY','BEL' .....]
var_list2 = ['infosys', 'wipro', 'suzlon', 'bharat electronics', .....]

Which I aim to insert into the database. Therefore I write the following two string:

var_string = ', '.join('?' * len(varlist))
var_string2 = ','.join('?' * len(varlist2))

Now I am trying to insert the values into a MySQL table which has the following columns: symbol(varchar), and company(varchar).

query_string = 'INSERT INTO table VALUES (%s, %s);' %(var_string,var_string2)

cur.execute(query_string, (varlist,varlist2))

This however does not work.

willeM_ Van Onsem · Accepted Answer · 2017-01-24 18:49:48Z

1

Based on your question I think you aim to do bulk insert. You can modify your code as follows but I strongly advice against that:

query_string = 'INSERT INTO table VALUES %s;'%(','.join('(%s,%s)'*len(varlist)))
flatten = tuple(x for xs in zip(varlist,varlist2) for x in xs)
cur.execute(query_string,flatten)

Here you will generate a query_string that looks like:

INSERT INTO table VALUES (%s,%s),(%s,%s),(%s,%s),(%s,%s),(%s,%s);

And then you fill it in with a tuple ('INFY','infosys','WIPR','wipro',...)

You can better use .executemany():

cur.executemany("INSERT INTO table VALUES (%s,%s);",zip(var_list,var_list2))

This will generate longer queries, but the result is both more elegant and safe.

answered Jan 24, 2017 at 18:49

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

Collectives™ on Stack Overflow

TypeError: not all arguments converted during string formatting While inserting two python lists in Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related