Python PostgreSQL integration: How to query and analyze data with ease

PostgreSQL is an object-relational database system that enables developers and data analysts to interact with data efficiently. It extends SQL language and provides advanced features for security, performance, and scalability.

Combining Python and PostgreSQL allows you to perform complex data analysis while taking advantage of the database’s robust features.

In this blog post, we’ll walk you through Python PostgreSQL integration with the help of the psycopg2 library. This integration makes it easy to query, manage, and analyze your PostgreSQL data directly from Python.

Installing Psycopg2

Psycopg is a popular PostgreSQL database adapter for Python. It uses the C programming language as a wrapper, which makes it fast and efficient. Here’s how to install Psycopg2 on your device:

pip install psycopg2

If you’d like a more straightforward installation, you can install the standalone version:

pip install psycopg2-binary

Python Postgres Integration: Connecting to the database

The next step is to establish the Python Postgres connection. This can be done by using Psycopg2’s built-in connect() function. This function creates a new database session and returns a new connection class instance. Here:

import psycopg2

# create connection
conn = psycopg2.connect(
    dbname="database_name",
    user="postgres",
    password="your_password",
    host="localhost"
   port="port_number"
)

Note that you’d have to replace those placeholder arguments with their actual values. Let’s have a breakdown of what they’re about:

• database: The name of the database you want to connect to.
• user: The name of the Postgres user.
• password: The password to authenticate the Postgres user.
• host: The database server’s IP address.
• port: PostgreSQL’s connection port number. It defaults to 5432 if not provided.

If the connection is successful, Python returns a live database connection object, which we can use to create a cursor. A cursor acts as a control structure that lets you execute SQL commands and fetch results from the database. You can create it with a single line of code:

cursor = conn.cursor()

Now that we’ve established the Postgres Python connection, we can proceed to writing queries to our Postgres with Python.

How to query PostgreSQL using Python

Let’s move on to querying our database. There are several types of queries you can perform, but when it comes to retrieving data from PostgreSQL, the cursor object provides convenient methods such as fetchone(), fetchall(), and fetchmany(). These functions allow you to control how much data you retrieve at a time. Let’s discuss them in detail:

Fetchone()

The fetchone() is used to retrieve a single row and returns it as a tuple. If you call the function again, it returns the next row. Here:

#code
print(cursor.fetchone())

#output
(1, "Dulciana", "Female", "Teacher")

Fetchall()

The Fetchall() method is used to retrieve all the rows from the result at once. Here:

print(cursor.fetchall())

#output
(1, "Dulciana", "Female", "Teacher")
(2, "Fanechka", "Male", "Developer III")
(3, "Issi", "Female", "Tax Accountant")
(4, "Briggs", "Male", "Developer IV")
(5, "Kingsly", "Male", "Executive Secretary")
(6, "Kerri", "Female", "Chief Design Engineer")
(7, "Norah", "Female", "Programmer III")
(8, "Jed", "Male", "Administrative Officer")
(9, "Staci", "Female", "Product Engineer")
(10, "Robinet", "Male", "Marketing Manager")

Fetchmany()

This function is used to get a specified number of records from the database. It gives control over the exact number of data points you want to retrieve by using a size parameter. If this parameter is excluded, it defaults to one. Here:

#code
print(cursor.fetchmany(size=5))

#output
(1, "Dulciana", "Female", "Teacher")
(2, "Fanechka", "Male", "Developer III")
(3, "Issi", "Female", "Tax Accountant")
(4, "Briggs", "Male", "Developer IV")
(5, "Kingsly", "Male", "Executive Secretary")

INSERT

You can also create records in your PostgreSQL database using Python. Let’s create a new table using the INSERT command:

cursor = conn.cursor()

cursor.execute("INSERT INTO students_data(student_name, student_age, student_subject) VALUES('James','21','Physics')");

cursor.execute("INSERT INTO students_data(student_name, student_age, student_subject) VALUES('Marvin','20','Biology')");

cursor.execute("INSERT INTO students_data(student_name, student_age, student_subject) VALUES('Michael','25','Chemistry')");

conn.commit()
cursor.close()

Here’s what the result looks like:

PostgreSQL automatically creates a separate column for student_id.

SELECT

The SELECT statement is used to read all the data available in the table. We can use the Fetchall method to read all the rows:

cursor = conn.cursor()
cur.execute('SELECT * FROM students_data;')
rows_list = cursor.fetchall()
conn.commit()
conn.close()    
print(rows_list)

#output

(1, 'James', '21', 'Physics')
(2, 'Marvin', '20', 'Biology')
(3, 'Michael', '25', 'Chemistry')

UPDATE

The SQL UPDATE statement is used for modifying existing records within a table. To perform an update, you must specify three components: the table name you are modifying, the SET clause to define which column(s) receive new values, and the WHERE clause to precisely filter which rows are affected.

For example, to change the name of the first student from "James" to "Nelson", you would execute the following query:

cur = conn.cursor()
cur.execute("UPDATE students_data SET students_name = 'Nelson' WHERE students_subject = 'Physics'")
conn.commit()
conn.close()
#output

(1, 'Nelson', '21', 'Physics')
(2, 'Marvin', '20', 'Biology')
(3, 'Michael', '25', 'Chemistry')

DELETE

This statement allows you to delete records in your table. For example, if we want to delete Marvin’s record from the students’ database:

cur = conn.cursor()
cur.execute("""DELETE from students_data WHERE student_name = 'Marvin'""");
conn.commit()
cur.close()

Conclusion

In this post, we explored Python PostgreSQL integration, showing you how to use PostgreSQL with Python. We also performed basic queries such as INSERT, SELECT, UPDATE, and DELETE.

If you’re looking for a simpler and more efficient way to connect PostgreSQL and Python without dealing with complex setups, Quadratic is a great solution.

Quadratic is an AI-powered spreadsheet that lets you connect directly to multiple databases, raw data sources, and APIs. With Quadratic, you can query your data effortlessly using both Python and SQL. It also comes with built-in AI assistance that generates code tailored to your data, helping you spend less time on setup and more time uncovering insights. Try Quadratic for free.