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.parseurllib.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_engineengine = 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 passwordconn_string = "host='host_name' dbname='database_name'\user='user_name' password='your_password'"# use connect function to establish the connectionconn = 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.
