Multiple SQL Inserts with Python

Question

UPDATE
After passing execute (), the list of lines is as suggested by Nathan, below, the code executes further, but still gets stuck in the execution function. Error message:

query = query % db.literal(args) TypeError: not all arguments converted during string formatting 

So it still does not work. Does anyone know why there is a type error?
END UPDATE

I have a large mailing list in .xls format. I am using python with xlrd to get the name and email address from the xls file in two lists. Now I want to put each name and email address in the mysql database. I am using MySQLdb for this part. Obviously, I do not want to do an insert statement for each element of the list. Here is what I still have.

 from xlrd import open_workbook, cellname import MySQLdb dbname = 'h4h' host = 'localhost' pwd = ' P@ssw0rd ' user = 'root' book = open_workbook('h4hlist.xls') sheet = book.sheet_by_index(0) mailing_list = {} name_list = [] email_list = [] for row in range(sheet.nrows): """name is in the 0th col. email is the 4th col.""" name = sheet.cell(row, 0).value email = sheet.cell(row, 4).value if name and email: mailing_list[name] = email for n, e in sorted(mailing_list.iteritems()): name_list.append(n) email_list.append(e) db = MySQLdb.connect(host=host, user=user, db=dbname, passwd=pwd) cursor = db.cursor() cursor.execute("""INSERT INTO mailing_list (name,email) VALUES (%s,%s)""", (name_list, email_list)) 

Problem while executing cursor. This is the error: _mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)') I tried to put my query in var first, but then it just closed the message about passing the tuple to execute ().

What am I doing wrong? Is it possible?

The list is huge, and I definitely can't let the insert insert into the loop. I looked at using LOAD DATA INFILE, but I really don't understand how to format a file or query, and my eyes are cleared when I have to read MySQL documents. I know that I could probably use some xls online applications for mysql converter, but this is also an exercise for me. Is there a better way ?

Answer 1

To fix TypeError: not all arguments converted during string formatting - you need to use the cursor.executemany(...) method, since it accepts iterable tuples (more than one line), and cursor.execute(...) expects the parameter will be the value of one row.

After running the command, you need to make sure that the transaction is completed in order to make the changes active in the database using db.commit() .

Answer 2

You need to provide executemany() list of strings. You do not need to break the name and email into separate lists, just create one list with both values ​​in it.

 rows = [] for row in range(sheet.nrows): """name is in the 0th col. email is the 4th col.""" name = sheet.cell(row, 0).value email = sheet.cell(row, 4).value rows.append((name, email)) db = MySQLdb.connect(host=host, user=user, db=dbname, passwd=pwd) cursor = db.cursor() cursor.executemany("""INSERT INTO mailing_list (name,email) VALUES (%s,%s)""", rows) 

Update: as pointed out by @JonClements, it should be executemany() not execute() .

Answer 3

If you are interested in high code performance, this answer might be better.

Compare the excutemany method, below execute will be much faster:

 INSERT INTO mailing_list (name,email) VALUES ('Jim',' [e-mail] '),('Lucy',' [e-mail] ') 

You can easily change the answer from @Nathan Villaescusa and get a new code.

 cursor.execute("""INSERT INTO mailing_list (name,email) VALUES (%s)""".format(",".join(str(i) for i in rows)) 

here is my own test result:

 excutemany:10000 runs takes 220 seconds execute:10000 runs takes 12 seconds. 

The difference in speed will be approximately 15 times.