Installing SQLAlchemy and connecting to database

SQLAlchemy can be used with Python 2.7 or later. In this tutorial, we are using Python 3.5. However, you are free to use any version of Python 3.

Installing SQLAlchemy

To install SQLAlchemy type the following:

To verify whether the installation was successful or not enter the following in the Python shell.

Installing DBAPI

SQLAlchemy by default only works with SQLite database without any additional drivers. To work with other databases you have to install a DBAPI complaint driver specific to the database.

What is DBAPI?

A DBAPI is a standard which encourages the use of the same API to work with a wide variety of databases. The following table lists some databases and their DBAPI complaint drivers.

Database DBAPI driver
MySQL PyMySQL, MySQL-Connector, CyMySQL, MySQL-Python (default)
PostgreSQL psycopg2 (default), pg8000
Microsoft SQL Server PyODBC (default), pymssql
Oracle cx-Oracle (default)
Firebird fdb , (default), kinterbasdb

All the examples in this tutorial are tested against PostgreSQL but you can any database you want to. To install psycopg2 DBAPI for PostgreSQL type the following command:

Creating Engine

The first step to connect to the database is to create an Engine object. The Engine object is how we interact with the database. It consists of two components: Dialect and a Connection Pool.

SQLAlchemy Dialect

It is said that the SQL is a standard language for accessing databases. In truth, the SQL syntax varies from one database to another. Databases vendors rarely stick to the standard and prefer to add their own twist and turn to the language. For example, if we are using Firebird then the SQL query to select id and name of the first 5 rows from the employees table would be:

The equivalent query for the MySQL database would be:

To handle these differences we need Dialect. The Dialect defines the behavior of the database. In other words, it handles things like generating SQL statements, execution, result-set handling and so on. Once the appropriate driver is installed, the Dialect handles all these differences for us so that we can just focus on writing the application.

SQLAlchemy Connection Pool

The connection pool a standard way of caching connections in the memory so that they can be reused. Creating a new connection everytime an application wants to talk to the database is expensive and time-consuming. The connection pool if implemented correctly offers a significant performance boost.

In connection pooling whenever an application needs to talk to the database it fetches the connection from the pool. After performing the desired queries application releases the connection and pushes it back to the pool. In case all the connections are being used, a new connection is created and added to the pool.

To create engine (i.e Engine object), we use create_engine() function of the sqlalchemy package. At its simplest, it accepts the connection string. The connection string provides information about the data source. Its general format is as follows:

The dialect refers to the name of the database like mysql, postgresql, mssql, oracle and so on. The driver refers to the DBAPI you are using. The driver is optional, if not specified a default driver will be used (assuming it is already installed). The username and password are the credentials to login to the database server. The host is the location of the database server. The port refers to the optional database port and database is the name of the database you want to connect to.

Here is the code to create an engine for some popular databases:

The format of the connection string for the SQLite database is slightly different. Since SQLite is a file based database we don’t specify username, password, host and port in the connection string. Here is the code to create an engine for the SQLite database.

Connecting to the Database

Note that creating an engine doesn’t instantly establish a connection to the database. To obtain the connection we use connect() method of the Engine object which returns an object of type Connection.

Here is the complete code:

If you try to run the above code you will get the following error:

The problem is that we are trying to connect to a database which doesn’t exist. To create the database login to PostgreSQL server and execute the following command:

Run the script again and this time you should get the following output:

Some Additional Arguments

The following table lists some additional keyword arguments that you can pass to the create_engine() function.

Argument Description
echo A boolean argument if set to True, the engine will log all the SQL it is currently executing to the standard output. By default, it is set to False.
pool_size It specifies the number of connection to keep in the connection pool. Its default value is 5.
max_overflow It specifies the number of connections that can be opened beyond the pool_size setting, by default it is set to 10.
encoding It specifies the encoding to use by SQLAlchemy. By default, it is set to utf-8. Note that it doesn't control the encoding scheme of the database.
isolation_level The isolation level to use. This setting control how a transaction will be isolated from the other transactions. Different databases support different isolation levels. To learn more about the isolation levels check the database documentation.

Here is a script which uses some of these additional keyword arguments while creating an engine.

Run the script and you will get the output as follows:

Leave a Comment