Connecting PostgreSQL with SQLAlchemy in Python

Last Updated : 23 Feb, 2022

In this article, we will discuss how to connect PostgreSQL with SQLAlchemy in Python.

In order to connect with any Database management system, it is essential to create an engine object, that serves as a central source of connection by providing a connection pool that manages the database connections. This SQLAlchemy engine is a global object which can be created and configured once and use the same engine object multiple times for different operations.

The first step in establishing a connection with the PostgreSQL database is creating an engine object using the create_engine() function of SQLAlchemy.

Syntax:

from sqlalchemy import create_engine

engine = create_engine(dialect+driver://username:password@host:port/database_name)

Parameters:

dialect - Name of the DBMS. The dialect is the system SQLAlchemy uses to communicate with various types of DBAPIs and databases like PostgreSQL, MySQL, MS SQL, etc.
driver - Name of the DB API that moves information between SQLAlchemy and the database.
Username - Name of the admin
Password - Password of the admin
host - Name of the host in which the database is hosted
port - port number through which the database can be accessed
database_name- Name of the database

Note: It is extremely unsafe to explicitly mention your password in the code. In order to avoid this, we can hash our password with urllib library as shown below

Python3

import urllib.parse

urllib.parse.quote_plus("your_password")

Establishing a connection with the PostgreSQL database:

The Engine object, returned by the create_engine() function, will not connect to the database yet, the connection will be established the first time only when it is asked to perform a task against the database. This kind of software design pattern is often referred to as Lazy Initialization. Once the engine object is created, we can make use of it to perform CRUD and other operations against the database.

PostgreSQL supports a list of python drivers like psycopg2, psycopg, py8000, asyncpg, and psycopg2cffi, which facilitates communication between the database and SQLAlchemy.

Python3

from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://user:password\
@hostname/database_name')

Using psycopg2 to connect with the PostgreSQL database.

Python3

import psycopg2

# declare the connection string specifying
# the host name database name use name 
# and password
conn_string = "host='host_name' dbname='database_name'\
user='user_name' password='your_password'"

# use connect function to establish the connection
conn = psycopg2.connect(conn_string)

The connection string used in the create_engine function or the psycopg2 function specifies three important facts like

What kind of database are we communicating with? (PostgreSQL)
What python DBAPI driver are we using? (psycopg2)
How do we locate the database? (localhost:5432)

Once the engine object is created, we can start committing and retrieving queries to and from the database.

Conclusion:

In this article, we discussed how to establish a connection to a PostgreSQL using SQLAlchemy in Python. We also discussed a bonus method on how to use the driver psycopg2 (least preferred) to connect to our PostgreSQL database.