Connecting PostgreSQL with SQLAlchemy in Python

Python provides variety of libraries one of them is SQLAlchemy, it provides an Object Relational Mapping (ORM) system for working with databases, including PostgreSQL. It is used for the purpose of storing and managing huge amount of structured data by the developers. By connecting both of them we can create a more productive and efficient database. In this article, we will learn how to connect PostgreSQL with SQLAlchemy in Python.

What is SQLAlchemy?

It is a powerful library that helps in simplifying the process of working with the databases, and is widely used in web development and data analysis. It provides advanced features such as connection pooling, statement caching, and, load balancing. These advance features help to improve the performance of the database applications.

What is PostgreSQL?

It is an advanced open-source relational database management system, it is known for its performance, scalability, and its robustness. It is used as a backend database for web applications, and supports a wide range of programming languages.

Advantages of Connecting PostgreSQL to SQLAlchemy

Following are the advantages of connecting PostgreSQL to SQLAlchemy –

• The versatility and flexibility of the SQLAlchemy library gives an advantage to the developers to work with various database management systems.

  Hence, the developers can use the same interface and code structure while working with different databases. This helps in saving time and effort.

• SQLAlchemy provides an Object-Relational Mapping (ORM) framework that enables developers to work with PostgreSQL using python classes and objects.

  This layer which is formed simplifies the process of interacting with the database and helps to focus on the application logic than database details.

• Psycopg2 is a Python library that offers a set of tools which can be used for working with the PostgreSQL databases using python. It provides support for advanced PostgreSQL features such as server-side cursors, transaction management, and asynchronous connections. It is extensively used in web development, data analysis that is the areas where data persistence is required.

Prerequisites

Before you proceed further, make sure you have the following installed and running in your system.

• Python 3

• PostgreSQL database

• Psycopg2 and SQLAlchemy packages

How to Install the Required Python Package?

By using pip, a package installer for Python, can be used to install psycopg2 and SQLAlchemy. Open the command prompt in your system and run the following command.

pip install psycopg2
pip install sqlalchemy

• Installing the required packages is the first step. Next step is to connect PostgreSQL with SQLAlchemy. You can use SQLAlchemy’s create_engine() function to create a connection to our PostgreSQL database.

• Pass the database URL to this function; it should include database name, username, password, host, and the port number.

Example

Let’s prepare a code which will explain the use of create_engine() to connect to PostgreSQL database.

from sqlalchemy import create_engine
# for postgreSQL database credentials can be written as 
user = 'username'
password = 'your_password'
host = 'localhost'
port = '5432'
database = 'mydb'
# for creating connection string
connection_str = f’postgresql:// {user}:{password}@{host}:{port}/{database}’
# SQLAlchemy engine
engine = create_engine(connection_str)
# you can test if the connection is made or not
try:
    with engine.connect() as connection_str:
        print('Successfully connected to the PostgreSQL database')
except Exception as ex:
    print(f'Sorry failed to connect: {ex}')

We have started the code by importing the functions from SQLAlchemy library. Then we have defined the PostgreSQL database credentials. Next step was to create the connection string using these credentials. You can change the credential information with the one you have given to your database.

Moving forward we have used create_engine() function to create a SQLAlchemy engine object. Hence, this object represents the database connection and can also be used to execute SQL commands on the database.

After connecting to the PostgreSQL using SQLAlchemy, you can execute SQL commands on the database using the execute () method of the engine object.

Errors

There are chances when you may see the errors after writing the code that is at the time of performing it. Let’s go through some of the common errors and solutions to them.

• One of the basic errors one may face is Import Error, you can simply resolve this error by installing the module by using pip in the command prompt.

• While executing the SQL commands you may face an error “sqlalchemy.exc.OperationalError: (psycopg2.OperationalError)FATAL:role “username” does not exist”. This error may occur if you the username in the connection string do not exist in PostgreSQL.

• You may face the operational error where the module could not connect to the server. You can solve this error by checking the network configuration or by starting the postgreSQL server.

Conclusion

We can conclude from the article that SQLAlchemy is a powerful Python Library for working with databases, since it provides flexibility based on data manipulation and data connectivity. Using the SQL’s ORM capabilities, one can write code in python that interacts with the PostgreSQL database in a more expressive way. Hence, if you are a python developer and looking for work, with the databases in their application then SQLAlchemy can be a great choice.