In this article, we are going to see how to use PostgreSQL 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
conn = get_connection()
curr = conn.cursor()
curr.execute("SELECT * FROM students;")
data = curr.fetchall()
for row in data:
print(row)
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
conn = get_connection()
curr = conn.cursor()
curr.execute("SELECT * FROM students;")
data1 = curr.fetchone()
print(data1)
data2 = curr.fetchone()
print(data2)
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
conn = get_connection()
curr = conn.cursor()
curr.execute("SELECT * FROM students;")
print("First two records:")
data1 = curr.fetchmany(2)
for row in data1:
print(row)
print("Next three records:")
data2 = curr.fetchmany(3)
for row in data2:
print(row)
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.