Execute the MySQL Script with Python

Example of using the Mysql Script saved in file with python language.

This code connects to the MySQL database using the mysql-connector-python library, reads a MySQL script from a file named script.sql, and executes the script using the cursor.execute() method. It also handles multiple statements in the script using the multi=True parameter. Finally, it commits any changes made to the database and closes the connection.

import mysql.connector

# Connect to the MySQL database
cnx = mysql.connector.connect(user='username', password='password',
                              host='localhost', database='database_name')

# Create a cursor object to execute queries
cursor = cnx.cursor()

# Read the MySQL script from a file
with open('script.sql', 'r') as file:
    script = file.read()

# Execute the script using the cursor object
for result in cursor.execute(script, 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))

# Commit changes to the database
cnx.commit()

# Close the connection
cnx.close()