Skip to content

18. Writing DBMS-Independent Code

We saw earlier that in some cases it was possible to easily migrate Python code written for the MySQL DBMS to code written for the PostgreSQL DBMS. In this chapter, we show how to systematize this approach. The proposed architecture is as follows:

Image

We want the choice of connector—and therefore of the DBMS—to be made via configuration and not require rewriting the script. Note that this is only possible in cases where the script does not use proprietary DBMS extensions.

The script directory structure will be as follows:

Image

The [any_xx] scripts are based on the scripts already covered for the MySQL and PostgreSQL DBMSs. We will not go over all of them. We will focus on the [any_04] script, which is the most complex. Note that this script executes the SQL commands from the following [data/commandes.sql] file:


# delete the [people] table
drop table if exists people
# create the `personnes` table
create table people (id int primary key, first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, unique (last_name, first_name))
# insert two people
insert into people(id, first_name, last_name, age) values(1, 'Paul','Langevin',48)
insert into people(id, first_name, last_name, age) values (2, 'Sylvie','Lefur',70)
# Displaying the table
SELECT first_name, last_name, age FROM people
# Intentional error
xx
# Insert three people
insert into people(id, first_name, last_name, age) values (3, 'Pierre','Nicazou',35)
insert into people(id, first_name, last_name, age) values (4, 'Geraldine','Colou',26)
insert into people(id, first_name, last_name, age) values (5, 'Paulette', 'Girond', 56)
# Display the table
SELECT first_name, last_name, age FROM people
# List of people sorted alphabetically by last name, and for those with the same last name, sorted alphabetically by first name
SELECT last_name, first_name FROM people ORDER BY last_name ASC, first_name DESC
# List of people aged between 20 and 40, sorted by age in descending order
# then, for people of the same age, sorted alphabetically by last name, and for people with the same last name, sorted alphabetically by first name
SELECT last_name, first_name, age FROM people WHERE age BETWEEN 20 AND 40 ORDER BY age DESC, last_name ASC, first_name ASC
# Inserting Ms. Bruneau
insert into people(id, first_name, last_name, age) values(6, 'Josette','Bruneau',46)
# Update her age
update people set age=47 where last_name='Bruneau'
# List of people with the last name Bruneau
SELECT last_name, first_name, age FROM people WHERE last_name = 'Bruneau'
# Delete Ms. Bruneau
delete from people where last_name='Bruneau'
# List of people with the last name Bruneau
SELECT last_name, first_name, age FROM people WHERE last_name='Bruneau'

We modified line 2 so that the command behaves the same way for both MySQL and PostgreSQL DBMSs if the [people] table does not exist.

The [any_04] script is configured by the following [config.py] script:


def configure():
    import os

    # absolute path to this script's directory
    script_dir = os.path.dirname(os.path.abspath(__file__))

    # configuration of syspath directories
    absolute_dependencies = [
        # local directories
        f"{script_dir}/shared",
    ]

    # Setting the syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    # configure the application
    config = {
        # supported DBMS
        "dbms": {
            "mysql": {
                # DBMS connector
                "dbms_connector": "mysql.connector",
                # name of the module containing DBMS management functions
                "db_functions": "any_module",
                # connection credentials
                "user": "admpersonnes",
                "password": "nobody",
                "host": "localhost",
                "database": "dbpersonnes"
            },
            "postgresql": {
                # DBMS connector
                "db_connector": "psycopg2",
                # name of the module containing DB management functions
                "db_functions": "any_module",
                # connection credentials
                "user": "admpersonnes",
                "password": "nobody",
                "host": "localhost",
                "database": "dbpersonnes"
            }
        },
        # SQL command file
        "commands_filename": f"{script_dir}/data/commands.sql"
    }
    # Application syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    # Set the configuration
    return config

The new changes are in lines 18–43:

  • line 20: [sgbds] is a dictionary with two keys: [mysql] on line 21 and [postgresql] on line 32;
  • the value associated with these keys is a dictionary containing the elements needed to connect to a DBMS:
  • lines 21–32: the elements for connecting to the MySQL DBMS;
    • line 23: the Python connector to use;
    • line 25: the module containing shared functions;
    • lines 26–30: the connection credentials;
  • lines 32–41: the same elements for a connection to the PostgreSQL DBMS;

The script [any_04] that executes the SQL command file [data/commandes.sql] is as follows:


# configure the application
import config

config = config.configure()

# The syspath is configured—we can now import modules
import importlib
import sys

# Checking the syntax of the call
# argv[0] sgbd_name true / false
# 3 parameters are required
args = sys.argv
error = len(args) != 3
if not error:
    # retrieve the two parameters we need
    db_name = args[1].lower()
    with_transaction = args[2].lower()
    # Check the two parameters
    error = (with_transaction != "true" and with_transaction != "false") \
             or sgbd_name not in config["sgbds"].keys()
# error?
if error:
    print(f"syntax: {args[0]} (1) sgbd_name (2) true / false")
    sys.exit()

# configuration of sgbd_name
db_config = config["dbms"][db_name]
# sgbd_name connector
db_connector = importlib.import_module(db_config["db_connector"])
# function library
lib = importlib.import_module(sgbd_config["sgbd_functions"])


# Calculate text to display
with_transaction = with_transaction == "true"
if with_transaction:
    text = "with transaction"
else:
    text = "without transaction"

# display
commands_filename = config['commands_filename']
print("--------------------------------------------------------------------")
print(f"Executing SQL file {commands_filename} {text}")
print("--------------------------------------------------------------------")

# executing SQL commands from the file
connection = None
try:
    # connection
    connection = sgbd_connector.connect(
        host=sgbd_config['host'],
        user=sgbd_config['user'],
        password=sgbd_config['password'],
        database=sgbd_config['database'])
    # Execute the SQL command file
    errors = lib.execute_file_of_commands( sgbd_connector, connection, commands_filename, tracking=True, stop=False,
                                           with_transaction=with_transaction)
except (sgbd_connector.InterfaceError, sgbd_connector.DatabaseError) as error:
    print(f"The following error occurred: {error}")
finally:
    # Close the connection
    if connection:
        connection.close()

# display number of errors
print("--------------------------------------------------------------------")
print(f"Execution complete")
print("--------------------------------------------------------------------")
print(f"There were {len(errors)} error(s)")
# display errors
for error in errors:
    print(error)

Comments

  • lines 1-4: retrieve the application configuration [config];
  • lines 10-21: the script is called with two parameters [db_name with_transaction]:
    • [db_name]: the name of the database management system to use;
    • [with_transaction]: True if you want to execute the SQL script within a transaction, False otherwise;
  • lines 10–25: the parameters are retrieved and verified;
  • line 28: configuration of the selected DBMS;
  • line 30: the connector for the selected DBMS is imported. To do this, the [importlib] library (line 7) is used, which allows importing a module whose name is stored in a variable. The result of the [importlib.import_module] operation is a module. Thus, after line 30, everything proceeds as if the executed statement had been: 
import sgbd_connector

This allows us to write [sgbd_connector.connect] on line 52, where we use the [connect] function of the [sgbd_connector] module. It is important to remember here that [sgbd_connector] is either [mysql.connector] or [psycopg2]. Both of these modules have the [connect] function. Similarly, on line 60, we can write [sgbd_connector.InterfaceError, sgbd_connector.DatabaseError].

  • Line 32: We import the module containing the functions used by the script;
  • Line 58: The [execute_file_of_commands] function from the module containing the functions used by the script is called. Compared to previous versions, this function’s signature has one additional parameter—the first one. We pass the Python connector [sgbd_connector] to the function for it to use;
  • Apart from these points, the [any_04] script remains unchanged from previous versions;

The [any_module] function library is as follows:


# ---------------------------------------------------------------------------------

def display_info(cursor):
    …


# ---------------------------------------------------------------------------------
def execute_list_of_commands(db_connector, connection, sql_commands: list,
                             continue: bool = False, stop: bool = True, with_transaction: bool = True):
    …

    # initializations
    cursor = None
    connection.autocommit = not with_transaction
    errors = []
    try:
        # request a cursor
        cursor = connection.cursor()
        # execute the SQL commands contained in sql_commands
        # execute them one by one
        for command in sql_commands:
            # Remove leading and trailing whitespace from the current command
            command = command.strip()
            # Is the command empty or a comment? If so, move on to the next command
            if command == '' or command[0] == "#":
                continue
            # Execute the current command
            error = None
            try:
                cursor.execute(command)
            except (db_connector.InterfaceError, db_connector.DatabaseError) as error:
                error = error
            # Was there an error?
            …


# ---------------------------------------------------------------------------------
def execute_file_of_commands(db_connector, connection, sql_filename: str,
                             track: bool = False, stop: bool = True, with_transaction: bool = True):
    …

    # executing the SQL file
    try:
        # open the file for reading
        file = open(sql_filename, "r")
        # processing
        return execute_list_of_commands(db_connector, connection, file.readlines(), follow, stop, with_transaction)
    except BaseException as error:
        # Return an array of errors
        return [f"The file {sql_filename} could not be processed: {error}"]
    finally:
        pass

The [sgbd_connector] parameter was used on line 31 to specify the type of intercepted exceptions.

Running the [any_04] script with the parameters [mysql false] yields the following results:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/anysgbd/any_04.py mysql false
--------------------------------------------------------------------
Executing the SQL file C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\anysgbd/data/commandes.sql without a transaction
--------------------------------------------------------------------
[drop table if exists personnes]: Execution successful
number of rows modified: 0
[create table people (id int primary key, first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, unique (last_name, first_name))] : Execution successful
number of rows modified: 0
[insert into people(id, first_name, last_name, age) values(1, 'Paul','Langevin',48)] : Execution successful
Number of rows modified: 1
[insert into people(id, first_name, last_name, age) values (2, 'Sylvie','Lefur',70)] : Execution successful
number of rows modified: 1
[select first_name, last_name, age from people]: Execution successful
first_name, last_name, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx: Error (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the correct syntax to use near 'xx' at line 1)
[insert into people(id, first_name, last_name, age) values (3, 'Pierre','Nicazou',35)] : Execution successful
number of rows affected: 1
[insert into people(id, first_name, last_name, age) values (4, 'Geraldine','Colou',26)] : Execution successful
number of rows affected: 1
[insert into people(id, first_name, last_name, age) values (5, 'Paulette','Girond',56)] : Execution successful
Number of rows modified: 1
[select first_name, last_name, age from people]: Execution successful
first_name, last_name, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
('Pierre', 'Nicazou', 35)
('Geraldine', 'Colou', 26)
('Paulette', 'Girond', 56)
*****************
[select last_name, first_name from people order by last_name asc, first_name desc] : Execution successful
last_name, first_name,
************
('Colou', 'Geraldine')
('Girond', 'Paulette')
('Langevin', 'Paul')
('Lefur', 'Sylvie')
('Nicazou', 'Pierre')
************
[select last_name, first_name, age from people where age between 20 and 40 order by age desc, last_name asc, first_name asc] : Execution successful
last_name, first_name, age,
*****************
('Nicazou', 'Pierre', 35)
('Colou', 'Geraldine', 26)
*****************
[insert into people(id, first_name, last_name, age) values(6, 'Josette','Bruneau',46)] : Execution successful
number of rows modified: 1
[update people set age=47 where last_name='Bruneau']: Execution successful
number of rows modified: 1
[select last_name, first_name, age from people where last_name='Bruneau']: Execution successful
last_name, first_name, age,
*****************
('Bruneau', 'Josette', 47)
*****************
[delete from people where last_name='Bruneau']: Execution successful
number of rows modified: 1
[select last_name, first_name, age from people where last_name='Bruneau']: Execution successful
last_name, first_name, age,
*****************
*****************
--------------------------------------------------------------------
Execution complete
--------------------------------------------------------------------
There was 1 error
xx: Error (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the correct syntax to use near 'xx' at line 1)

Process finished with exit code 0
https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.