Installing SQLAlchemy and connecting to the database

by Alex
Installing SQLAlchemy and connecting to the database

SQLAlchemy can be used with Python 2.7 and later versions. This guide will use Python 3.8, but you can choose any version of Python 3.6+.

Installing SQLAlchemy

To install SQLAlchemy, type the following:

pip install sqlalchemy

To check if the installation was successful, type the following at the command line:


>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.8'

DBAPI Installation

By default, SQLAlchemy only works with the SQLite database without additional drivers. To work with other databases you need to install DBAPI-compatible driver according to the database. What is DBAPI? DBAPI is a standard that encourages the same API to work with more databases. The following table lists all DBAPI-compliant drivers: DatabaseDBAPI driverMySQLPyMySQL, MySQL-Connector, CyMySQL, MySQL-Python (default)PostgreSQLpsycopg2 (default), pg8000,Microsoft SQL ServerPyODBC (default), pymssqlOraclecx-Oracle(default)Firebirdfdb (default), kinterbasdb All examples in this tutorial are tested with PostgreSQL, but you can choose a database of your choice. To install DBAPI psycopg2 for PostgreSQL, enter the following command:

pip install psycopg2

Preparing to connect

The first step to connect to the database is to create an Engine object. It is the one that is responsible for interacting with the database. It consists of two elements: a dialect and a connection pool. Dialect SQLAlchemy SQL is a standard language for working with databases. However, it also differs from database to database. Database vendors rarely stick to the same version and prefer to add their own features. For example, if you use Firebird, you need the following command to get the id and name for the first 5 rows of the employees table:

select first 10 id, name from employees

And here’s how to get the same result for MySQL:

select id, name from employees limit 10

You need a dialect to handle these differences. The dialect defines the behavior of the database. In other words, it is responsible for handling SQL instructions, execution, result handling, and so on. After installing the appropriate driver, the dialect handles all the differences, allowing you to focus on creating the application itself. SQLAlchemy connection pool Connection pooling is a standard way to cache connections in memory, allowing them to be reused. It’s expensive to create a connection every time you need to contact the database. And connection pooling provides a nice performance boost. With this approach, the application pulls the connection from the pool when it needs to contact the database. After executing the queries, the connection is released and returned to the pool. A new connection is created only if all others are connected. The create_engine() function from the sqlalchemy package is used to create an engine (an Engine object). In its basic form, it accepts only the connection string. The latter includes information about the data source. This is usually in approximately the following format:

dialect+driver://username:[email protected]:port/database
  • dialect is the name of the database (mysql, postgresql, mssql, oracle and so on).
  • driver is the DBAPI used. This parameter is optional. If not specified here the default driver will be used (if installed).
  • username and password – data to access the database.
  • host – the location of the database server.
  • port – port for connecting to the database.
  • database – database name.

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


from sqlalchemy import create_engine
# Connect to MySQL server on localhost using PyMySQL DBAPI.
engine = create_engine("mysql+pymysql://root:[email protected]/mydb")
# Connect to a MySQL server at ip 23.92.23.113 using the mysql-python DBAPI.
engine = create_engine("mysql+mysqldb://root:[email protected]/mydb")
# Connect to a PostgreSQL server on localhost using psycopg2 DBAPI
engine = create_engine("postgresql+psycopg2://root:[email protected]/mydb")
# Connect to an Oracle server on the local host using the cx-Oracle DBAPI.
engine = create_engine("oracle+cx_oracle://root:[email protected]/mydb"))
# Connect to MSSQL server on localhost using PyODBC DBAPI.
engine = create_engine("oracle+pyodbc://root:[email protected]/mydb")

The format of the connection string for a SQLite database is slightly different. Since it’s a file-based database, it doesn’t need a username, password, port, and host. Here’s how to create a SQLite database engine:


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

Connecting to a Database

But creating an engine is not yet a connection to the database. To get a connection, you need to use the connect() method of the Engine object, which returns an object of type Connection.


from sqlalchemy import create_engine
# 1111 is my password for the postgres user
engine = create_engine("postgresql+psycopg2://postgres:[email protected]/sqlalchemy_tuts")
engine.connect()
print(engine)

But if you run it, the following error will occur:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError)
(Background on this error at: http://sqlalche.me/e/14/e3q8)

The problem is that an attempt is being made to connect to a database that does not exist. To create a PostgreSQL database, run the following code:


import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
# Set up a connection to postgres
connection = psycopg2.connect(user="postgres", password="1111")
connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
# Create a cursor to perform operations on the database
cursor = connection.cursor()
sql_create_database =
# Create a database
cursor.execute('create database sqlalchemy_tuts')
# Close the connection
cursor.close()
connection.close()

Run the script again to get the desired output:

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

Additional arguments

The following table lists additional keyword arguments that can be passed to create_engine(). ArgumentDescriptionhoesn’t have a Boolean value. If set to True, the engine will save SQL logs to standard output. The default value is Falsepool_sizeDefines the number of connections for the pool. Defaults to 5max_overflowDefines the number of connections outside of the pool_size value. Default – 10encoding Defines SQLAlchemy encoding. The default is UTF-8. However, this parameter does not affect the encoding of the entire database.isolation_levelThe isolation level. This setting controls the degree of isolation for a single transaction. Different databases support different levels. For this it is better to read the documentation of the specific database Here is a script that uses additional keyword arguments to create the engine:


from sqlalchemy import create_engine
engine = create_engine(
"postgresql+psycopg2://postgres:[email protected]/sqlalchemy_tuts",
echo=True, pool_size=6, max_overflow=10, encoding='latin1'
)
engine.connect()
print(engine)

Run it to get the following output:

2021-04-16 15:12:59,983 INFO sqlalchemy.engine.engine.select version()
2021-04-16 15:13:00,023 INFO sqlalchemy.engine.engine [raw sql] {}
2021-04-16 15:13:00,028 INFO sqlalchemy.engine.engine select current_schema()
2021-04-16 15:13:00,038 INFO sqlalchemy.engine.engine [raw sql] {}
2021-04-16 15:13:00,038 INFO sqlalchemy.engine.engine.show standard_conforming_strings
2021-04-16 15:13:00,048 INFO sqlalchemy.engine.engine [raw sql] {}
Engine(postgresql+psycopg2://postgres:***@localhost/sqlalchemy_tuts)

Related Posts

LEAVE A COMMENT