PostgreSQL Python – Querying Data
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:
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()
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()
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()
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