Execute .sql file with PyMySQL

Most SQL files contain interpreter commands such as DELIMITER that make passing the commands through to pymysql somewhat difficult. This code snippet allows you to separate out the statements in the sql file into a list for sequential execution.

The Problem

When you export a database from MySQL, the resulting file often contains commands that are intended for the MySQL CLI tool, not for a driver like PyMySQL. If you try to execute the entire file content as a single query, it will likely fail.

The Solution

We can read the file and split the content by the ; character, but we need to be careful about semicolon usage within strings or comments. A more robust way is to iterate through the file and collect statements.

import pymysql

def execute_sql_file(filename, cursor):
    with open(filename, 'r') as f:
        sql = f.read()
    
    # Simple split by semicolon (may need refinement for complex files)
    statements = sql.split(';')
    
    for statement in statements:
        if statement.strip():
            cursor.execute(statement)

# Usage
connection = pymysql.connect(host='localhost', user='user', password='password', db='database')
with connection.cursor() as cursor:
    execute_sql_file('dump.sql', cursor)
connection.commit()