How to execute raw SQL in SQLAlchemy
Posted by: AJ Welch
SQLAlchemy is a SQL tool built with Python that provides developers with an abundance of powerful features for designing and managing high-performance databases.
We’ll briefly explore how to use SQLAlchemy and then dive deeper into how to execute raw SQL statements from within the comfort of the Python domain language.
Using SQLAlchemy
As with all Python libraries, start by installing SQLAlchemy. Once installed we can begin the fun in Python.
Next, import sqlalchemy itself, then import a few modules so we can easily access the SQLAlchemy database engine:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import inspectIn addition to create_engine, we’re also importing a number of additional modules that we’ll need for creating a new table. Before we get to that though, ensure SQLAlchemy was installed, imported, and is working by calling .__version__ like so:
print sqlalchemy.__version__
Out[*]: 1.0.9Creating a table
We’ll be using the basic functionality of SQLAlchemy which is the SQL Expression Language to create some metadata that will contain a number of related modules (or objects) that define our new book database table:
metadata = MetaData()
books = Table('book', metadata,
Column('id', Integer, primary_key=True),
Column('title', String),
Column('primary_author', String),
)
engine = create_engine('sqlite:///bookstore.db')
metadata.create_all(engine)At the top we define metadata, then we pass that into the Table() method, where we give our table the name book. Within this, we define each column, along with important attributes like data type and primary_key.
Once our table(s) are defined and associated with our metadata object, we need to create a database engine with which we can connect. This is accomplished using the create_engine function.
engine = create_engine('sqlite:///bookstore.db')For our example, we’ll be using a simple SQLite database. You can also use connection strings for other engines such as MySQL or PostgreSQL. Here’s an example syntax for creating an engine for PostgreSQL:
engine = create_engine('postgresql://user:password@host/database')With the engine created, we now need to use the .create_all() method of our metadata object and pass the engine connection to it, which will automatically cause SQLAlchemy to generate our table for us, as seen above.
With that complete, we can use the table as we see fit. In this simple example, we’ll just use the inspect module to view the columns and verify our table was successfully created:
inspector = inspect(engine)
inspector.get_columns('book')
Out[*]:
[{'autoincrement': True,
'default': None,
'name': u'id',
'nullable': False,
'primary_key': 1,
'type': INTEGER()},
{'autoincrement': True,
'default': None,
'name': u'title',
'nullable': True,
'primary_key': 0,
'type': VARCHAR()},
{'autoincrement': True,
'default': None,
'name': u'primary_author',
'nullable': True,
'primary_key': 0,
'type': VARCHAR()}]Sure enough, using the .get_columns() method for our book table, we see our three columns were generated.
Executing SQL statements
With the basics in place, we can now try executing some raw SQL using SQLAlchemy.
Using the text module
One method for executing raw SQL is to use the text module, or Textual SQL. The most readable way to use text is to import the module, then after connecting to the engine, define the text SQL statement string before using .execute to run it:
from sqlalchemy.sql import text
with engine.connect() as con:
data = ( { "id": 1, "title": "The Hobbit", "primary_author": "Tolkien" },
{ "id": 2, "title": "The Silmarillion", "primary_author": "Tolkien" },
)
statement = text("""INSERT INTO book(id, title, primary_author) VALUES(:id, :title, :primary_author)""")
for line in data:
con.execute(statement, **line)Here we’re inserting two records into our database by using a text()-defined statement.
Using the execute method
The alternative method is to skip using text() and pass a raw SQL string to the .execute() method. For example, here we’ll use .execute() to view the new records we inserted above:
with engine.connect() as con:
rs = con.execute('SELECT * FROM book')
for row in rs:
print row
Out[*]:
(4, u'The Hobbit', u'Tolkien')
(5, u'The Silmarillion', u'Tolkien')There we have it! Simple and effective methods for executing raw SQL statements in SQLAlchemy.