1. Home
2. SQLAlchemy Tutorial
3. Installing SQLAlchemy and connecting to database

Installing SQLAlchemy and connecting to database

Last updated on July 27, 2020

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:

pip install sqlalchemy

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

1
2
3
4
5

>>>
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.2.2'
>>>

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.

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:

pip install psycopg2

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:

select first 10 id, name from employees

The equivalent query for the MySQL database would be:

select id, name from employees limit 10

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:

dialect+driver://username:password@host:port/database

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

from sqlalchemy import  create_engine

# Connecting to MySQL server at localhost using PyMySQL DBAPI 
engine = create_engine("mysql+pymysql://root:pass@localhost/mydb")

# Connecting to MySQL server at 23.92.23.113 using mysql-python DBAPI 
engine = create_engine("mysql+mysqldb://root:pass@23.92.23.113/mydb")

# Connecting to PostgreSQL server at localhost using psycopg2 DBAPI 
engine = create_engine("postgresql+psycopg2://root:pass@localhost/mydb")

# Connecting to Oracle server at localhost using cx-Oracle DBAPI
engine = create_engine("oracle+cx_oracle://root:pass@localhost/mydb")

# Connecting to MSSQL server at localhost using PyODBC DBAPI
engine = create_engine("oracle+pyodbc://root:pass@localhost/mydb")

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.

1
2
3
4

from sqlalchemy import  create_engine

engine = create_engine('sqlite:///sqlite3.db') # using relative path
engine = create_engine('sqlite:////path/to/sqlite3.db') # using absolute path

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.

engine.connect() # connect to the database

Here is the complete code:

1
2
3
4
5
6

from sqlalchemy import create_engine

engine = create_engine("postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts")
engine.connect()

print(engine)

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

OperationalError: (psycopg2.OperationalError) FATAL:  database "sqlalchemy_tuts" does not exist (Background on this error at: http://sqlalche.me/e/e3q8)

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:

1
2
3
4
5
6

$ sudo -su postgres psql
postgres=# 
postgres=# create database sqlalchemy_tuts;
CREATE DATABASE
postgres=# 
postgres=# \q

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

Engine(postgres+psycopg2://postgres:***@localhost/sqlalchemy_tuts)

Some Additional Arguments #

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

from sqlalchemy import create_engine

engine = create_engine(
    "postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts", 
    echo=True, pool_size=6, max_overflow=10, encoding='latin1'
)

engine.connect()

print(engine)

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

2018-07-07 18:38:44,468 INFO sqlalchemy.engine.base.Engine select version()
2018-07-07 18:38:44,469 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,472 INFO sqlalchemy.engine.base.Engine select current_schema()
2018-07-07 18:38:44,473 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,476 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-07-07 18:38:44,477 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,479 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-07-07 18:38:44,480 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,484 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2018-07-07 18:38:44,484 INFO sqlalchemy.engine.base.Engine {}
Engine(postgres+psycopg2://postgres:***@localhost/sqlalchemy_tuts)