Additional Resource
Knowledge of SQL & Databases is a prerequisite of this lesson. Before you can dive into SQLAlchemy, it's important that you are already knowledgeable and comfortable with SQL & Databases. The SQL & Databases course is short and quick and will get you set up with a good working knowledge of SQL and a local MySQL installation that you can use for this lesson - and many more projects ahead.
After you complete the SQL & Databases course, return to this lesson and continue.
In this tutorial, you will learn how to integrate your Python applications with a database using SQLAlchemy. For demonstration purposes, you will use the free Sakila database that MySQL makes available.
Install SQLAlchemy
SQLALchemy is a tool that allows you to easily interact with relational databases from your python programs. Unlike other python database connectors, SQLALchemy can be used with a variety of SQL databases such as MySQL, PostgreSQL and Oracle.
In this section, we will look at how to connect to a MySQL database.
Installing the sqlalchemy package To start, we will need to install the sqlalchemy package. You can do this locally on your machine or in a virtual environment. We recommend using a virtual environment.
$ pip install sqlalchemy
Import the Sakila Database
If you do not already have the sakila demo database imported to your local MySQL installation, please refer to this page for directions on how to import the sakila DB to your local machine before proceeding.
Connect to the Database
You will also need to install a database-specific driver that SQLAlchemy can use to connect to your database. For MySQL databases that would be pymysql:
$ pip install pymysql
And for PostgreSQL you can use psycopg2:
$ pip install psycopg2
Now in order to interact with a database, we need to first establish a connection to the database. We can do this by first importing sqlalchemy and using the create_engine() method. The following are examples of how to connect to MySQL and PostgreSQL databases. The only difference is the database URL.
PostgreSQL Connection
import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://username:password@localhost/mydatabase')
MySQL Connection
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/mydatabase')
Video: Connecting to the Database
Accessing Database Metadata Information
Now that we have made a connection, we can access information about the database. For example, we can get the column names of a certain table, or the entire Metadata of a table.
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
actor = sqlalchemy.Table('actor', metadata, autoload_with=engine)
print(actor.columns.keys())
print(repr(metadata.tables['actor']))
Video: Accessing DB Metadata
Read Data with the SELECT Statement
Now that we have made a connection and looked at how to access information about the database, let's look at how to query the data. The first query we will look at is a SELECT query.
SELECT Query
We first set up the connect the same way we have seen in the past. From there, we create a Select statement. The following is the equivalent of the SQL statement SELECT * FROM actor:
query = sqlalchemy.select(actor)
Now, we can execute the query using the execute() method. The result is saved in the variable result_proxy. In order to access the data returned by the query, we fetch the results using the fetchall() method.
The data returned is stored in the variable result_set and can be used as any other Python object. In the following example, we print out the entire result set however, we could access the data in a variety of ways such as iterating over the list, accessing an item at a specific index, etc.
import sqlalchemy
from pprint import pprint
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
actor = sqlalchemy.Table('actor', metadata, autoload_with=engine)
query = sqlalchemy.select(actor)
result_proxy = connection.execute(query)
result_set = result_proxy.fetchall()
pprint(result_set)
If you are working with a large dataset, you can use the fetchmany() method instead of fetchall() and specify how many rows you want to be returned. For example:
result_set = result_proxy.fetchmany(5)
Video: Select Statement
Filtering Results
Now that we are familiar with a SELECT statement, let us take a look at how we can filter those results. The following are examples of SQL statements and their matching SQLAlchemy statements.
WHERE
The WHERE statement. Select all results "where" a given condition is true.
# SQL
SELECT * FROM sakila.actor WHERE first_name = "PENELOPE";
# python sqlalchemy
query = sqlalchemy.select(actor).where(actor.columns.first_name == 'PENELOPE')
IN
The IN statement. Select all results where the given field is "in" the given values.
# SQL
SELECT * FROM sakila.actor WHERE first_name IN ("PENELOPE", "JOHN", "UMA");
# python sqlalchemy
query = sqlalchemy.select(actor).where(actor.columns.first_name.in_(["PENELOPE", "JOHN", "UMA"]))
AND & AND NOT
AND & AND NOT statements. Select all results that meet the given boolean condition.
SELECT * FROM sakila.film WHERE length > 60 AND rating = "PG";
SELECT * FROM sakila.film WHERE length > 60 AND NOT rating = "PG";
# python sqlalchemy
query = sqlalchemy.select(film).where(sqlalchemy.and_(film.columns.length > 60, film.columns.rating == "PG"))
query = sqlalchemy.select(film).where(sqlalchemy.and_(film.columns.length > 60, film.columns.rating != "PG"))
ORDER BY
ORDER BY Statements order the result set by a given clause.
SELECT * FROM sakila.film ORDER BY replacement_cost ASC;
# python sqlalchemy
query = sqlalchemy.select(film).order_by(sqlalchemy.asc(film.columns.replacement_cost))
SUM()
THE SUM function returns the sum of a given field.
SELECT SUM(length) FROM sakila.film;
# python sqlalchemy
query = sqlalchemy.select(sqlalchemy.func.sum(film.columns.length))
Video: Filtering Data
Using JOINS
Join statements allow you to select data from more than one table. In the following example, we will take a look at how to use SQLAlchemy joins to find related data in the actor and film tables.
We first establish a connection the way we have done in the past and make an object for each of the tables we need information from.
# python
import sqlalchemy
from pprint import pprint
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
actor = sqlalchemy.Table('actor', metadata, autoload_with=engine)
film = sqlalchemy.Table('film', metadata, autoload_with=engine)
film_actor = sqlalchemy.Table('film_actor', metadata, autoload_with=engine)
To make things more clear, we will operate the statement and query. First, we create a statement join_statement by joining the three tables on the foreign key conditions. We then create a SELECT statement using the join statement from the previous line to select the film_id, first_name, and last_name`:
# python
join_statement = actor.join(film_actor, film_actor.columns.actor_id == actor.columns.actor_id).join(film, film.columns.film_id == film_actor.columns.film_id)
query = sqlalchemy.select(film.columns.film_id, film.columns.title,actor.columns.first_name, actor.columns.last_name).select_from(join_statement)
Finally, we execute the query and print the results:
# python
result_proxy = connection.execute(query)
result_set = result_proxy.fetchall()
pprint(result_set)
Video: Using Joins
Create a Database Table
SQLAlchemy will create a table if the table information passed does not already exist.
# python
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
newTable = sqlalchemy.Table('newTable', metadata,
sqlalchemy.Column('Id', sqlalchemy.Integer()),
sqlalchemy.Column('name', sqlalchemy.String(255), nullable=False),
sqlalchemy.Column('salary', sqlalchemy.Float(), default=100.0),
sqlalchemy.Column('active', sqlalchemy.Boolean(), default=True)
)
metadata.create_all(engine)
Video: Creating a Table
INSERT Statement
Building on the previous page, we will now look at how to insert data into our newly created table. We establish a connection as we have done in the past, but now, we will use the insert() method and pass the key-value pairs. In the following example, the keys are the names of the columns and the values are the new values that are going to be inserted. For example:
# python
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
newTable = sqlalchemy.Table('newTable', metadata, autoload_with=engine)
query = sqlalchemy.insert(newTable).values(Id=1, name='Software Ninjaneer', salary=60000.00, active=True)
result_proxy = connection.execute(query)
connection.commit()
In the example above, we have inserted one record, however, what if we want to insert multiple records at once? We need to create a list of dictionaries. For example:
# python
query = sqlalchemy.insert(newTable)
new_records = [{'Id':'2', 'name':'record1', 'salary':80000, 'active':False},
{'Id':'3', 'name':'record2', 'salary':70000, 'active':True}]
result_proxy = connection.execute(query,new_records)
Video: Insert Statement
Video: Inserting Multiple Records
UPDATE Statement
To update a record in our database we will use the update, values and where methods. In the following example, we update the salary column in the newTable table, where the column Id is equal to 1.
# python
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
newTable = sqlalchemy.Table('newTable', metadata, autoload=True, autoload_with=engine)
query = sqlalchemy.update(newTable).values(salary=100000).where(newTable.columns.Id == 1)
result = connection.execute(query)
Video: Update Statement
DELETE Statement
Finally, the Delete statement! By now some of the connection set up should look pretty familiar. In the following example, we will look at how to delete one or more records from a table. Using the delete and where keywords, we delete all records from the table newTable, where, the column salary is less than 100,000.
# python
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
newTable = sqlalchemy.Table('newTable', metadata, autoload=True, autoload_with=engine)
query = sqlalchemy.delete(newTable).where(newTable.columns.salary < 100000)
results = connection.execute(query)
Video: Deleting Tables
Video: Accessing Result Proxy Fields
Video: Mapping Result Proxy Records to Objects
Summary: Database Integration with Python SQLAlchemy
Using Python and SQLAlchemy you can integrate with any modern relational database quite simply. In this tutorial you learned how to:
- Connect to a database
- Query a database
- Filter your result set
- Create tables
- Insert records
- Update records
- Delete records
If you are not familiar with SQL and you have not done so already, please refer to the SQL & Databases course.