Pythonic way to execute (with try & except) pre-constructed SQL files and obtain server responses/errors

Question

I am executing a series of sql statements 1 by 1 (outputs of initial ones are relied upon later). These queries are table creations, table alterations, and view updates.

I can execute them in Python 1 by 1, but for logging purposes and safety protocols, I need to figure out how to achieve the following outcomes:

If the query fails, obtain the response back
Utilize try and except correctly to not disturb anything that doesn't fully execute.

Here is the code I have that works today (looping through directories and files within directories). There are some variable declarations and os directory setup prior to entering the loop here.

EDIT: The tls alias is a module of self created tools that I work with, the tls.create_lake_engine() function is simply creating an engine to connect to my sql server using sqlalchemy's create_engine method.

for file in contents:
    # check if file is a sql statement or a python module
    if file.endswith('.sql'):
        # read the query
        file_contents = open(file).read()
        statements = file_contents.split(';')
        print('executing {} statements from file {}'.format(
            len(statements), file))
        statements = [x.strip() for x in statements]
        i = 0
        for statement in statements:
            i+=1
            print('\texecuting statement {}'.format(i))
            engine = tls.create_lake_engine()
            conn = engine.connect()
            trans = conn.begin()
            conn.execute(statement)
            trans.commit()

Booboo · Accepted Answer · 2021-12-09 17:39:22Z

I am not very knowledgeable when it comes to sqlalchemy, so you might wish to check some of the specifics. But it seems to me that:

You should try only creating a connection once and reusing it for every file that you process as this is more efficient than creating a connection for each file.
When processing a .sql file, as soon as you encounter an exception executing one of the statements, it might make more sense at that point to rollback all changes made in that file and to stop executing statements from that file. Anyway, that is the approach I have taken. If you wish to continue executing the .sql file, then remove the break statement from function execute_script and unconditionally execute a trans.commit regardless of whether an error occurred or not. But it sounds like from your question you do not want to "disturb anything that doesn't fully execute" and to me that means quitting and rolling back on the first error.

from sqlalchemy.sql import text

def get_statements(file):
    """
    A generator function for yielding one by one
    all the statements within the passed file.

    Lines constituting a statement are stripped of leading and trailing
    whitespace and then joined together with a single space character.
    """

    with open(file, 'r') as f:
        command = []
        for line in f:
            # Is this a comment line?
            if line.startswith('--'):
                continue
            cmd = line.strip()
            if cmd.endswith(';'):
                # remove ';'
                command.append(cmd[:-1])
                yield text(' '.join(command))
                command = []
            else:
                command.append(cmd)

def execute_script(conn, file):
    statements = list(get_statements(file))
    print(f'executing {len(statements)} statements from file {file}')
    for i, statement in enumerate(statements, start=1):
        print(f'\texecuting statement {i}')
        trans = conn.begin()
        error = False
        try:
            conn.execute(statement)
        except SQLAlchemyError as e:
            # See https://stackoverflow.com/questions/2136739/error-handling-in-sqlalchemy
            print(str(e.__dict__['orig']))
            error = True
            break # Stop execting the file
    if error:
        trans.rollback()
    else:
        trans.commit()


engine = tls.create_lake_engine()
conn = engine.connect()
for file in contents:
    # check if file is a sql statement or a python module
    if file.endswith('.sql'):
        execute_script(conn, file)

Collectives™ on Stack Overflow

Pythonic way to execute (with try & except) pre-constructed SQL files and obtain server responses/errors

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related