Using raw SQL with Flask-SQLAlchemy

This week I was using Flask-Migrate to do a database data migration. I created an empty reversion file and put my migrate script in it so that the migrate can be recorded just like the other reversion files.

The only thing special about writing a migration script is you need to use raw SQL to do the database manipulation instead of using ORM model object from Flask-SQLAlchemy. The reason is that maybe at the time your script is written, your model definition in your application reflects your actual database structure, but it may change over time, and once that happens, your script may not work correctly anymore.

This is one thing that I think is quite important when you are doing data migration with Flask-Migrate.

Engine and Connection

Engine and Connection are deep level objects in SQLAlchemy, most of the time in our application, we will use the Session as an interface to the database. However, Engine and Connection objects become useful when direct usage of textual SQL is required, like the migration job.

What is an Engine in SQLAlchemy? Just like the name “Engine” indicates, it’s the core component to deal with the database. It contains the metadata about what the database it deals with, and references to a Dialect, which describes how to talk to a specific kind of database/DBAPI combination. It also maintains a connection pool to the database and combines with the dialect it can talk to the actual database.

Creating an Engine is easy:

from sqlalchemy import create_engine
engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

But with Flask-SQLAlchemy, if you already have an instance of DB object, you can get the existing engine by:

engine = db.engine

The Engine can create a Connection to directly issue SQL to the database, to create a connection:

connection = engine.connect()

Execute SQL to the database

Once you have got a connection, you can now execute SQL:

result = connection.execute("SELECT username FROM users")
for row in result:
    print("username:", row['username'])

The execute() task SQL directly, and it returns a ResultProxy, you can find all the available properties about ResultProxy here.

After you finished all your tasks, you should remember to close the connection:

connection.close()

Using Transactions

It’s always a good idea using the transaction to do data change so that when any exception happens during the operation, you can always rollback the change.

To use transaction with a connection is quite simple, first of all, create a transaction:

trans = connection.begin()

Now a transaction has been created, you can start your job, the usual way to do you work is wrap your code in a try / except and rollback when any exception raises:

try:
  # do you work
  # if everything goes well
  trans.commit()
except:
  trans.rollback()
  raise

Remember to raise the error in your except phase, it’s always good to yell out what’s wrong about your application.

Dealing with SQL strings

It’s difficult to write raw SQL sometimes, you need to be careful about SQL injection and also escape different kinds of characters. To help with these, we can use a helper function called text. It wraps your raw SQL and also provides parameter binding to achieve substitution of values.

It’s very easy to use with execute():

from sqlalchemy.sql import text
s = text(
    "SELECT users.fullname || ', ' || addresses.email_address AS title "
        "FROM users, addresses "
        "WHERE users.id = addresses.user_id "
        "AND users.name BETWEEN :x AND :y "
conn.execute(s, x='m', y='z').fetchall()

References

• Engine
• Working with Engines and Connections
• Using Textual SQL