Getting started with PostgreSQL in Python

Use PostgreSQL quickly and easily in your Python applications

Photo by XPS on Unsplash

PostgreSQL, also called Postgres, is one of the most popular free and open-source relational database management systems available. If you’re a Python developer or data professional, there might be many times when you want to use a database connector inside a Python application, and it’s important to get comfortable doing so.

To run this exercise, get Postgres installed and running on your machine.

Using psycopg2

Psycopg2 is an efficient and flexible Postgres adapter for Python. It was designed for multithreaded applications that might need different threads to share the same database connection and carry out many concurrent operations. It’s very straightforward and easy to use with Python.

To get started, install the psycopg2 driver:

pip install psycopg2

Create a connection string in a config.py file. We’re using the default admin user postgres here just to get started.

Put this into your config.py:

#config.py
PGConnString="dbname='postgres' user='postgres' host='localhost' password=<your password here>"

Create another file in the same directory called postgres_demo.py. Then import the following items and connect to the database:

#postgres_demo.py
import psycopg2
import config
try:
   conn = psycopg2.connect(config.PGConnString)
except:
   print ("Can't connect to database")

Congratulations! You’re now connected to Postgres. Let’s get started using it.

Creating a table

In the example below, we’re writing a list of queries to create, insert into, and select from a table. Notice we’re just using a default schema here.

queries = ("""DROP TABLE IF EXISTS students""",
"""CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            student_name VARCHAR(255) NOT NULL
            )
            """,
...

Creating additional queries

We now need queries for inserting data into the table and selecting it.

...
"""INSERT INTO students
    VALUES (123, 'Alice'), (456, 'Bob'), (789, 'Carl')
    """,
"""SELECT * FROM students""")
...

Running the queries

There are several ways we can select from a table and get back results, including using a built-in cursor. A cursor is a pointer to the result set of a query. You use a cursor to execute a query as follows:

...
cursor = conn.cursor()
for query in queries:
    cursor.execute(query)
result = cursor.fetchall()
cursor.close()
conn.commit()
print(result)

The result will be returned as a list of tuples, which we can convert into different output formats as needed.

Below are two very useful alternatives we can use instead: JSON objects and Pandas dataframes.

Using JSON

Very often we want to execute a query and get the result as a JSON object. The default cursor gives us the result of our query as a list, but we really want the result as a dictionary or a JSON object. Here’s how to do that:

import json
from psycopg2.extras import RealDictCursor
query = “””SELECT * FROM students”””
cursor = conn.cursor(cursor_factory=RealDictCursor)
cursor.execute(query)
print(json.dumps(cursor.fetchall()))

Here is the result:

[{"student_id": 123, "student_name": "Alice"}, {"student_id": 456, "student_name": "Bob"}, {"student_id": 789, "student_name": "Carl"}]

Notice we’re using a RealDictCursor instead of a regular cursor. This returns the result as a dictionary, which we convert to JSON using json.dumps().

Using Pandas

Pandas has a built-in read_sql_query() function that returns the result of a query as a dataframe:

import pandas as pd
query = "select * from students;"
df = pd.read_sql_query(query, conn)

Pandas results

We don’t even have to create a cursor object here; this is all handled internally. We can cleanly and easily integrate this result into a Pandas pipeline.

Here is the full source code for this exercise:

Conclusion

We’re now up and running using Postgres in Python! We’ve learned how to integrate two very useful data formats with Postgres (JSON structures and Pandas dataframes). In future exercises we’ll be working with more advanced SQL queries and Postgres functions.