Title: Execute SQL scripts in MariaDB with Python

[SQL scripts let you run a sequence of commands in MariaDB with Python]

In my recent MariaDB posts, you've seen many examples of Python programs executing SQL commands. It's a small step from there to executing SQL scripts. Normally an SQL script contains a number of SQL commands separated by semi-colons. All you need to do is split the script into commands at the semi-colons and then execute the commands one at a time.

This post shows a simple way to do that.

Running Scripts

The following run_script function runs the script in its script string parameter.

def run_script(cur, script, stop_on_error=True): '''Execute an SQL script.''' # Get type codes. type_codes = get_type_codes() try: # Process the commands. commands = [cmd.strip() for cmd in script.split(';')] for cmd in commands: try: # Ignore zero-length commands. if len(cmd) > 0: print(cmd) # Print it. cur.execute(cmd) # Execute it. # See if the command returned a result set. if cur.description is None: # No result set. Just say OK. print(' > OK') else: # Display the returned rows. for row in cur: print(f' > {row}') print() # Get column names and types from cursor.description. column_info = [(desc[0], desc[1]) for desc in cur.description] # Print the schema print("Schema of SELECT statement:") for name, type_code in column_info: print(f' Column: {name:10} Type: ' '{type_code:4} {type_codes[type_code]}') except Exception as e: # Unknown error. print(f' > ERROR: {e}') if stop_on_error: return finally: print() except Exception as e: print(f'Error: {e}')

The function first calls the get_type_codes function described shortly to get a dictionary of SQL command type codes. It then enters a try block to protect itself from errors. Unless you copied and pasted the script text from some other tool like HeidiSQL, there's nothing to stop you from making typos in the script so errors are likely, at least when you're debugging the script.

Next, the code uses a list comprehension to turn the script into a list of commands. The comprehension splits the script at semi-colons, loops through the results, and calls strip for each command to remove leading and trailing whitespace. The code doesn't try to remove unnecessary internal spaces (like several spaces in a row) or comments. It just lets the database engine ignore those as usual.

The function then loops through the command list. For each command, it displays the command's text and uses the cursor's execute method to run the command.

Next the code checks to see if the cursor has a description property. That property provides information about the values returned by a SELECT statement. If the property is None, then the command was something that doesn't return rows like a CREATE TABLE, INSERT, or UPDATE statement. In that case, the program displays "OK."

If the cursor's description is not None, the function loops through the cursor's returned values and displays them. It doesn't do any fancy formatting, it just displays the result tuples.

Next, the code displays the query's metadata. It loops through the cursor's description list and extracts each field's name and data type. The code uses the type_codes dictionary to convert the type code into a string and displays the result. For example, here's some output showing a query's result metadata.

Schema of SELECT statement: Column: AuthorId Type: 3 LONG Column: Title Type: 253 VAR_STRING Column: FirstName Type: 253 VAR_STRING Column: LastName Type: 253 VAR_STRING

Of course when you're just running a script, you may not want to see the result schema. In that case, just comment out that code.

If the program encounters an error while running a command, it display error information and checks the stop_on_error parameter to see if it should exit the function. Sometimes it's better to stop the script and sometimes it's better to skip the bad command and keep running, so this function gives you both options.

get_type_codes

MariaDB uses type codes to represent the data types of the objects it stores. You can see a list of those codes by executing the statement dir(mariadb.constants.FIELD_TYPE).

The following get_type_codes function creates a dictionary that converts the numeric type code values into strings.

def get_type_codes(): type_codes = { mariadb.constants.FIELD_TYPE.TINY: 'TINY', mariadb.constants.FIELD_TYPE.SHORT: 'SHORT', mariadb.constants.FIELD_TYPE.LONG: 'LONG', mariadb.constants.FIELD_TYPE.FLOAT: 'FLOAT', mariadb.constants.FIELD_TYPE.DOUBLE: 'DOUBLE', mariadb.constants.FIELD_TYPE.NULL: 'NULL', mariadb.constants.FIELD_TYPE.TIMESTAMP: 'TIMESTAMP', mariadb.constants.FIELD_TYPE.LONGLONG: 'LONGLONG', mariadb.constants.FIELD_TYPE.INT24: 'INT24', mariadb.constants.FIELD_TYPE.DATE: 'DATE', mariadb.constants.FIELD_TYPE.TIME: 'TIME', mariadb.constants.FIELD_TYPE.DATETIME: 'DATETIME', mariadb.constants.FIELD_TYPE.YEAR: 'YEAR', mariadb.constants.FIELD_TYPE.VARCHAR: 'VARCHAR', mariadb.constants.FIELD_TYPE.BIT: 'BIT', mariadb.constants.FIELD_TYPE.JSON: 'JSON', mariadb.constants.FIELD_TYPE.NEWDECIMAL: 'NEWDECIMAL', mariadb.constants.FIELD_TYPE.ENUM: 'ENUM', mariadb.constants.FIELD_TYPE.SET: 'SET', mariadb.constants.FIELD_TYPE.TINY_BLOB: 'TINY_BLOB', mariadb.constants.FIELD_TYPE.MEDIUM_BLOB: 'MEDIUM_BLOB', mariadb.constants.FIELD_TYPE.LONG_BLOB: 'MEDIUM_BLOB', mariadb.constants.FIELD_TYPE.BLOB: 'BLOB', mariadb.constants.FIELD_TYPE.VAR_STRING: 'VAR_STRING', mariadb.constants.FIELD_TYPE.STRING: 'STRING', mariadb.constants.FIELD_TYPE.GEOMETRY: 'GEOMETRY', } return type_codes

Now the previous run_script function can display query schemas.

Running Script Files

The following run_script_file function runs the script stored in a file.

def run_script_file(cur, filename, stop_on_error=True): '''Execute an SQL script file.''' try: # Read the script file. with open(filename, 'r') as file: script = file.read() # Execute the script. run_script(cur, script, stop_on_error) except FileNotFoundError: print(f'Error: File "{filename}" was not found.') except Exception as e: print(f'Error: {e}')

This code simply opens the file, reads it into a string, and then passes that string to the run_script function described earlier.

Main Program

The example's main program is also pretty simple.

# Main program. root_password = 'my_super_secret_password' # Connect to MariaDB. try: with mariadb.connect( user='root', password=root_password, host='localhost', port=3306 # Default MariaDB port ) as conn: conn.autocommit = True # Create a cursor. with conn.cursor() as cur: # Execute the script file. script_file = 'create_db.sql' run_script_file(cur, script_file, False) except Exception as e: print(f'Error: {e}')

This code connects to the database, opens a cursor, and then calls the run_script_file function, passing it the script file name create_db.sql. (I'm using the .sql extension for script files.) That script does the following:

Drops the Script_Db database, if it exists.
Creates the Script_Db database.
Uses SHOW DATABASES to list the defined databases. (Which shows the schema for the returned data.)
Uses the new database.
Creates the Authors table.
Calls SHOW CREATE TABLE to show the table's structure. (Which shows the schema for the returned data.)
Makes some Authors records.
Creates the Books table.
Calls SHOW CREATE TABLE to show the table's structure. (Which shows the schema for the returned data.)
Makes some Books records.
Tries to drop a table that doesn't exist to show how the program handles script errors.
Tries to select data from a missing column to show how the program handles script errors.
Performs a multi-table query and displays the results. (Which shows the schema for the returned data.)

Conclusion

Running an SQL script is a handy way to set up a database, create tables, insert records, and so forth. It lets you quickly reset a database to a known state so you can prepare to run tests.

You can do the same things with Python code, but it's usually easier to write an SQL script. You can also easily run the script from multiple programs so they can share the same script code.

One drawback to this method is that you need the appropriate privileges to execute the commands in the script. For example, to build a database, create a user, or drop a table you need some pretty buff privileges and that isn't always safe. If you make mistakes while you have those privileges, you can trash the database.

A better approach is to do as little as possible with elevated privileges and then switch to an account with fewer privileges before you do most of the work. For example, you can use one script to build the database and its tables and a different script to populate the tables. You can even use an interactive tool like HeidiSQL to build the database by hand and then use fewer privileges to fill the tables.

Download the example to experiment with it and to see additional details.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.