Skip to content
Related Articles

Related Articles

Improve Article

PostgreSQL Python – Querying Data

  • Last Updated : 31 Aug, 2021
Geek Week

In this article, we are going to see how to use PostSQL using pyscopg2 in Python for executing query data.

Establishing a connection to the PostgreSQL server using Python:

In order 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:

Python






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:

Querying 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.

Example 1: Querying data using fetchall()

Python




# This program uses fetchall()
# to query all the records from a table
  
# 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:



Output for fetchall()

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.

Example 2: Querying data using fetchone()

Python




# This program uses fetchone() to
# query one by one record from a table
  
# 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:

Output for fetchone()

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.

Example 3: Querying data using fetchmany()

Python




# This program uses fetchmany() 
# to query specified number of records from a table
  
# 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:

Output for fetchmany()

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.

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :