Execute SQL from file in SQLAlchemy

sqlalchemy.text or sqlalchemy.sql.text

• The text construct provides a straightforward method to directly execute .sql files.

from sqlalchemy import create_engine
from sqlalchemy import text
# or from sqlalchemy.sql import text

engine = create_engine('mysql://{USR}:{PWD}@localhost:3306/db', echo=True)

with engine.connect() as con:
    with open("src/models/query.sql") as file:
        query = text(file.read())
        con.execute(query)

• SQLAlchemy: Using Textual SQL
  • text()

I was able to run .sql schema files using pure SQLAlchemy and some string manipulations. It surely isn't an elegant approach, but it works.

# Open the .sql file
sql_file = open('file.sql','r')

# Create an empty command string
sql_command = ''

# Iterate over all lines in the sql file
for line in sql_file:
    # Ignore commented lines
    if not line.startswith('--') and line.strip('\n'):
        # Append line to the command string
        sql_command += line.strip('\n')

        # If the command string ends with ';', it is a full statement
        if sql_command.endswith(';'):
            # Try to execute statement and commit it
            try:
                session.execute(text(sql_command))
                session.commit()

            # Assert in case of error
            except:
                print('Ops')

            # Finally, clear command string
            finally:
                sql_command = ''

It iterates over all lines in a .sql file ignoring commented lines. Then it concatenates lines that form a full statement and tries to execute the statement. You just need a file handler and a session object.