Execute SQL from file in SQLAlchemy

Question

How can I execute whole sql file into database using SQLAlchemy? There can be many different sql queries in the file including begin and commit/rollback.

Answer 1

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()

Answer 2

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.