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.

Answer 1

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

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