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.

That solved it right away, thank you so much. If you post as an answer I will accept. — ProgrammingWithRandy
– ProgrammingWithRandy, Commented Dec 14, 2015 at 20:20

Josep Valls · Accepted Answer · 2015-12-14 20:26:27Z

3

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

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

answered Dec 14, 2015 at 20:26

Josep Valls

5,6103 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ProgrammingWithRandy Over a year ago

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

Christian Specht · Accepted Answer · 2019-01-28 22:14:48Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related