PostgreSQL Python - Querying Data

Psycopg2 acts as a bridge between Python applications and PostgreSQL databases. Widely employed in diverse Python systems, from web applications to data analysis tools and other software projects, Psycopg2 enables developers to execute queries and manipulate data stored in PostgreSQL databases.

In this article, we will see What Psycopg 2?, and how to use PostgreSQL using pyscopg2 in Python for executing query data.

What is Psycopg2?

Psycopg2 is a PostgreSQL adapter for Python programming language. It acts as a bridge between Python and the PostgreSQL database that allows Python applications to interact with and manipulate data stored in a PostgreSQL database. It is widely used in Python systems to develop applications that require interaction with PostgreSQL databases including web applications, data analysis tools, and tool software projects.

How to install Psycopg2

To establish a connection to the PostgreSQL server, we will make use of the pscopg2 library in Python. You can install psycopg2 using the following command:

pip install psycopg2

If the above command causes an error then use the command:

pip uninstall psycopg2
pip install psycopg2-binary

After installing the library, the following code can be used to create a connection to the database server:

import psycopg2
def get_connection():
    try:
        return psycopg2.connect(
            database="postgres",
            user="postgres",
            password="password",
            host="127.0.0.1",
            port=5432,
        )
    except:
        return False
conn = get_connection()
if conn:
    print("Connection to the PostgreSQL established successfully.")
else:
    print("Connection to the PostgreSQL encountered and error.")

Output:

Connection to the PostgreSQL established successfully.

Running the above code will produce the following result if the database credentials provided is correct and the connection is successfully established:

How to Query data using psycopg2

Let us look at how can we query data using the psycopg2 library.

Execute the raw SQL query

Execute the below SQL query to get the data ready in our table in order to query data from:

In the above output, we can see that there are 5 records present in the students table. We will make use of these records to see how can we query data from a database table.

How to query data using fetchall()

# GET THE CONNECTION OBJECT
conn = get_connection()
# CREATE A CURSOR USING THE CONNECTION OBJECT
curr = conn.cursor()
# EXECUTE THE SQL QUERY
curr.execute("SELECT * FROM students;")
# FETCH ALL THE ROWS FROM THE CURSOR
data = curr.fetchall()
# PRINT THE RECORDS
for row in data:
    print(row)
# CLOSE THE CONNECTION
conn.close()

Output:

In the above code, we create a connection and query using SELECT * FROM students which fetches the entire dump of the students table. In order to query data in the python code, we can make use of fetchall(). The fetchall() method fetches all the records that we got from our SQL query (the SELECT query in this case) and provides them in a list. The list consists of tuples where each tuple consists of all the column values present in the particular record or row.

How to query data using fetchone()

# GET THE CONNECTION OBJECT
conn = get_connection()
# CREATE A CURSOR USING THE CONNECTION OBJECT
curr = conn.cursor()
# EXECUTE THE SQL QUERY
curr.execute("SELECT * FROM students;")
# FETCH THE FIRST ROW FROM THE CURSOR
data1 = curr.fetchone()
print(data1)
# FETCH THE SECOND ROW FROM THE CURSOR
data2 = curr.fetchone()
print(data2)
# CLOSE THE CONNECTION
conn.close()

Output:

The fetchone() method is not to be confused with the idea that it queries the first row only. The fetchone() method returns the first record from the dump that we got from the SQL query present in curr.execute() method. It behaves like a queue where we query the first record and then it gets deleted from the cursor object. Now, if we try to use the fetchone() method again, it will return the next record. The above code demonstrates the same where we fetched the first record and then used the fetchone() method again to fetch the next record in the queue. We can keep doing this until we reach the last record in the cursor object.

How to query data using fetchmany()

# GET THE CONNECTION OBJECT
conn = get_connection()
# CREATE A CURSOR USING THE CONNECTION OBJECT
curr = conn.cursor()
# EXECUTE THE SQL QUERY
curr.execute("SELECT * FROM students;")
print("First two records:")
# GET FIRST TWO RECORDS FROM DATABASE TABLE
data1 = curr.fetchmany(2)
for row in data1:
    print(row)
print("Next three records:")
# GET NEXT THREE RECORDS FROM DATABASE TABLE
data2 = curr.fetchmany(3)
for row in data2:
    print(row)
# CLOSE THE CONNECTION
conn.close()

Output:

We have seen how to query all the records and to query one-by-one records. The fetchmany() method takes in the no. of records that we want to fetch from the entire dump. It also behaves like a queue as we saw in the fetchone() method, except for the fact that it can fetch more than one record at a time. However, using fetchmany(1) is equivalent to using fetchone() method. In the above code, we used the fetchmany(2) to query the first two records and then used the fetchmany(3) to query the next 3 records. The same can be noticed in the below output.