CODEWITHSUNDEEP
python-2.7mysql-python
Bhargav
Bhargav

Reputation: 484

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

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.

Upvotes: 1

Views: 889

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476750

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.

Upvotes: 1

Related Questions