How To Use SQLAlchemy In Python

How To Use SQLAlchemy In Python

SQLAlchemy is an object-relational mapping tool, or ORM, that allows developers to work with databases in a more Pythonic way. Tools like SQLAlchemy let you work with Python or even a different language instead of SQL to query and manipulate the data in your databases. Python has many ORM libraries you can use with SQLAlchemy being one of the most popular ones for relational databases. SQLAlchemy also works with many web frameworks including flask and databases, such as SQLite, MySQL, and Postgres. Let’s learn a little bit more about SQLAlchemy now.

Why Use SQLAlchemy?

Some programmers would rather write database interactions in Pure Python rather than raw SQL. Python developers can interact with the database using the language they are familiar with and abstract the database system. This can speed up development, especially in the early stages of a project. Some programmers might not mind switching between languages, but it can be easier to get the first prototype up and running using a single language. Programmers that are extremely good at SQL can likely write more performant queries with SQL than with an abstracted ORM. On the other hand, if you’re not savvy with SQL, then a tool like SQLAlchemy can give you perfectly usable query performance out of the box. Using an ORM moves the database complexity into the application code rather than keeping it in its own system. You’ll still need to know at least rudimentary SQL, but since an ORM simplifies working with the data layer, many developers choose to use one.

Installing SQLAlchemy

SQLalchemy is a separate library from the base Python installation so, in order to use it, we first need to install it. Installation is quite easy, all we need to do is type pip install sqlalchemy at the command line and you will see some output like so.

python $pip install sqlalchemy
Collecting sqlalchemy
  Downloading SQLAlchemy-1.4.9-cp39-cp39-win_amd64.whl (1.5 MB)
     |████████████████████████████████| 1.5 MB 328 kB/s
Collecting greenlet!=0.4.17
  Downloading greenlet-1.0.0-cp39-cp39-win_amd64.whl (95 kB)
     |████████████████████████████████| 95 kB 830 kB/s
Installing collected packages: greenlet, sqlalchemy
Successfully installed greenlet-1.0.0 sqlalchemy-1.4.9

SQLAlchemy Core (SQL Expression Language)

SQLAlchemy Core has a schema-centric view that focuses on tables, keys, and SQL concepts. SQLAlchemy Core is also known as SQL Expression Language. The SQL Expression Language is a Pythonic way of creating SQL statements and expressions and uses less abstraction compared to the full ORM model. It is focused on the actual database schema and is standardized in a way that provides a consistent language across many different backend databases. We can use the same cars.db SQLite Database we just set up in the Database In Python tutorial.

Importing SQLalchemy

The code here imports the SQLalchemy library and aliases it to the db variable.

import sqlalchemy as db

Create Engine

The first thing we want to do is make a call to the create_engine() function passing in the name of the database we want to connect to. We’ll save the result of this function in a variable called engine. The engine allows your application to have multiple database connections, and it manages those connections for you.

engine = db.create_engine('sqlite:///cars.db')

Connect To Database

To set up a connection to the database, we can use the connect() function and save the result in a variable named connection. This connection is a proxy for the true Python database API connection.

connection = engine.connect()

Load Table Into SQLAlchemy

To load a table into SQLalchemy we can use the Table() function. This function takes the name of the table, the database metadata, an auto load setting, and the engine we already created.

metadata = db.MetaData()
cars = db.Table('cars', metadata, autoload=True, autoload_with=engine)

Construct A Query

We can now construct a query using the table we stored in the cars variable. Recall that to get all the results from the database, we use SELECT * FROM cars in raw SQL. To do this in SQLalchemy, we can use the select() function and pass in the table we want to query.

query = db.select([cars])

Execute Query

To actually run the query, we need to use the execute() function and pass in the query we have constructed. This function returns a result proxy that proxies the cursor object from the Python database API.

result_proxy = connection.execute(query)

Fetching Data

The final step is to use the result proxy to call the fetchall() function. This returns a result set and we can print out the results and note that all of the records from this table are displayed.

result_set = result_proxy.fetchall()

print(result_set)
[('Ford', 'Mustang Mach E', 2022), ('Tesla', 'Model 3', 2020), ('Kia', 'Niro EV', 2022), ('Porsche', 'Taycan 4S', 2021)]

Adding where()

Most times, you want to get data that meets the given criteria. We can do this in SQLalchemy using the where() function. Let’s rewrite the existing select() query by chaining on a where() call. For example, let’s find all cars in the database that have a year of 2022.

import sqlalchemy as db

engine = db.create_engine('sqlite:///cars.db')

connection = engine.connect()

metadata = db.MetaData()
cars = db.Table('cars', metadata, autoload=True, autoload_with=engine)

query = db.select([cars]).where(cars.columns.year == 2022)

result_proxy = connection.execute(query)

result_set = result_proxy.fetchall()

print(result_set)
[('Ford', 'Mustang Mach E', 2022), ('Kia', 'Niro EV', 2022)]

Insert Into SQLalchemy

To insert a record into the database using SQLalchemy we can use the insert() function. Here we set up the query, and then use the connection object to execute the query.

query = cars.insert().values(make="Kia", model="Telluride", year="2021")

connection.execute(query)

If we select all records again, we can see the new entry in the database.

import sqlalchemy as db

engine = db.create_engine('sqlite:///cars.db')

connection = engine.connect()

metadata = db.MetaData()
cars = db.Table('cars', metadata, autoload=True, autoload_with=engine)

query = db.select([cars])

result_proxy = connection.execute(query)

result_set = result_proxy.fetchall()

print(result_set)
[('Ford', 'Mustang Mach E', 2022),
 ('Tesla', 'Model 3', 2020),
 ('Kia', 'Niro EV', 2022),
 ('Porsche', 'Taycan 4S', 2021),
 ('Kia', 'Telluride', 2021)]

Create Database In SQLalchemy

In this section, let’s create a new database named users.db. We can see how to create a new database and table here.

import sqlalchemy as db

engine = db.create_engine('sqlite:///users.db')

metadata = db.MetaData()

connection = engine.connect()

users = db.Table('Users', metadata,
                 db.Column('user_id', db.Integer, primary_key=True),
                 db.Column('first_name', db.Text),
                 db.Column('last_name', db.Text),
                 db.Column('email_address', db.Text))

metadata.create_all(engine)

SQLalchemy Bulk Insert

Now we can insert multiple records at the same time by passing in a list of objects to be inserted into the database.

insert_query = users.insert().values([
    {"first_name": "Bob", "last_name": "Jones", "email_address": "bjones@notrealemail.com"},
    {"first_name": "Jack", "last_name": "Erich", "email_address": "jerich@notrealemail.com"},
    {"first_name": "Rick", "last_name": "Stein", "email_address": "rstein@notrealemail.com"},
    {"first_name": "Sally", "last_name": "Green", "email_address": "sgreen@notrealemail.com"}
])

connection.execute(insert_query)

Select Using Columns

Lastly, we can again fetch all the records from the database selecting only the first name of each record.

select_query = db.select([users.columns.first_name])
query_result = connection.execute(select_query)

for person in query_result:
    print(person[0])
Bob
Jack
Rick
Sally

How To Use SQLAlchemy In Python Summary

  • Some developers prefer to skip Raw SQL
  • Object Relational Mappers all Pythonic database interaction
  • Several Libraries offer ORM functions
  • SQLalchemy is the most well known
  • SQLalchemy works with SQLite, MySql, and Postgres
  • SQLalchemy has Core and ORM components
  • Core is schema centric
  • ORM is object centric
  • ORM abstracts the database system
  • Speeds up development for Python developers
  • Makes prototyping easier
  • Raw SQL queries are faster
  • ORMs move database complexity into the application
  • ORMs may hinder your understanding of pure SQL
  • Learn More With the SQLalchemy Tutorial