CODEWITHSUNDEEP
pythonmysqlpymysql
ProgrammingWithRandy
ProgrammingWithRandy

Reputation: 745

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

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.

Upvotes: 4

Views: 12489

Answers (2)

Christian Specht
Christian Specht

Reputation: 36431

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()

Upvotes: 3

Josep Valls
Josep Valls

Reputation: 5560

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

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

Upvotes: 3

Related Questions