Open In App

Connecting PostgreSQL with SQLAlchemy in Python

Improve
Improve
Like Article
Like
Save
Share
Report

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.



Last Updated : 23 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads