How do I import a MySQL database in a Python script?

Question

I've seen some similar questions about this on StackOverflow but haven't found an answer that works; see http://stackoverflow.com/questions/4408714/execute-sql-file-with-python-mysqldb AND http://stackoverflow.com/questions/10593876/execute-sql-file-in-python-with-mysqldb?lq=1

Here is my code:

import pymysql
import sys
import access # holds credentials
import mysql_connector # connects to MySQL, is fully functional
class CreateDB(object):
 def __init__(self):
 self.cursor = None
 self.conn = pymysql.connect(host, user, passwd)
 def create_database(self):
 try:
 with self.conn.cursor() as cursor:
 for line in open('file.sql'):
 cursor.execute(line)
 self.conn.commit()
 except Warning as warn:
 f = open(access.Credentials().error_log, 'a')
 f.write('Warning: %s ' % warn + '\nStop.\n')
 sys.exit()
create = CreateDB()
create.create_database()

When I run my script I get the following error:

pymysql.err.InternalError: (1065, 'Query was empty')

My .sql file is successfully loaded when I import directly through MySQL and there is a single query on each line of the file. Does anybody have a solution for this? I have followed the suggestions on other posts but have not had any success.

• Josep Valls: Any empty lines in the beginning or ending of the file?
• Josep Valls: Consider if line.strip(): cursor.execute(line)
• ProgrammingWithRandy: That solved it right away, thank you so much. If you post as an answer I will accept.

Answer 1

Take care of empty lines in the end of the file by:

if line.strip(): cursor.execute(line)

• ProgrammingWithRandy: I just deleted all blank lines but this works too, thanks!

Answer 2

You can execute all the SQL in the file at once, by using the official MySQL Connector/Python and the Multi parameter in its cursor.execute method.

Quote from the second link:

If multi is set to True, execute() is able to execute multiple statements specified in the operation string. It returns an iterator that enables processing the result of each statement.

Example code from the link, slightly modified:

import mysql.connector
file = open('script.sql')
sql = file.read()
cnx = mysql.connector.connect(user='u', password='p', host='h', database='d')
cursor = cnx.cursor()
for result in cursor.execute(sql, multi=True):
 if result.with_rows:
 print("Rows produced by statement '{}':".format(
 result.statement))
 print(result.fetchall())
 else:
 print("Number of rows affected by statement '{}': {}".format(
 result.statement, result.rowcount))
cnx.close()