PostgreSQL is an open source object-relational database management system. It is well known for its reliability, robustness, and performance. PostgreSQL has a variety of libraries of API (Application programmable interface) that are available for a variety of popular programming languages such as Python. It provides a lot of features for Database management such as Views, Triggers, Indexes (using B-Trees), etc.
There are several python modules that allow us to connect to and manipulate the database using PostgreSQL:
Psycopg2 is one of the most popular python drivers for PostgreSQL. It is actively maintained and provides support for different versions of python. It also provides support for Threads and can be used in multithreaded applications. For these reasons, it is a popular choice for developers.
In this article, we shall explore the features of PostgreSQl using psycopg2 by building a simple database management system in python.
sudo pip3 install psycopg2
Note: if you are using Python2, use pip install instead of pip3
Once psycopg has been installed in your system, we can connect to the database and execute queries in Python.
Creating the database
before we can access the database in python, we need to create the database in postgresql. To create the database, follow the steps given below:
- Log in to PostgreSQL:
sudo -u postgres psql
- Configure the password:
You will then be prompted to enter the password. remember this as we will use it to connect to the database in Python.
- Create a database called “test”. we will connect to this database.
CREATE DATABASE test;
Once the database and password have been configured, exit the psql server.
- database: The name of the database you are connecting to
- user: the username of your local system
- password: the password to log in to psql
- host: The host, which is set to localhost by default
- port: The port number which is 5432 by default
- Python | Getting started with psycopg2-PostGreSQL
- Oracle Database Connection in Python
- Unicodedata – Unicode Database in Python
- Inserting variables to database table using Python
- Important differences between Python 2.x and Python 3.x with examples
- Reading Python File-Like Objects from C | Python
- Python | Add Logging to Python Libraries
- Python | Add Logging to a Python Script
- Python | Set 4 (Dictionary, Keywords in Python)
- Python | Sort Python Dictionaries by Key or Value
- SHA in Python
- bin() in Python
- try and except in Python
- zip() in Python
Connecting to the database
connect() method is used to establish connection with the database. It takes 5 parameters:
conn = psycopg2.connect( database="test", user = "adith", password = "password", host = "localhost", port = "5432")
Once the connection has been established, we can manipulate the database in python.
The Cursor object is used to execute sql queries. we can create a cursor object using the connecting object (conn)
cur = conn.cursor()
Using this object, we can make changes to the database that we are connected to.
After you have executed all the queries, we need to disconnect from the connection. Not disconnecting will not cause any errors but it is generally considered a good practice to disconnect.
The execute() method takes in one parameter, the SQL query to be executed. The SQL query is taken in the form of a string that contains the SQL statement.
cur.execute("SELECT * FROM emp")
Fetching the data
Once the query has been executed, the results of the query can be obtained using the fetchall() method. This method takes no parameters and returns the result of select queries.
res = cur.fetchall()
The result of the query is stored in the res variable.
Putting it all together
Once we have created the database in PostgreSQL, we can access that database in python. We first create an emp table in the database called test with the schema: (id INTEGER PRIMARY KEY, name VARCHAR(10), salary INT, dept INT). Once the table is created without any errors, we insert values into the table.
Once the values are inserted, we can query the table to select all the rows and display them to the user using the
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.