Get Records From Postgres with Python

Introduction

In this article we will learn how to get records from Postgres with Python by combining two methods; the SELECT SQL statement and Python’s Fetchall function from the psycopg2 library. We will also explore a foundation of retrieving records from a database into Python; the List data type. We also address the idea that when you attempt to retrieve records from a database, you may run into an error, so we demonstrate error checking using Python’s Try Catch functionality.

Using Select to create recordset

We’ll make use of the Postgres “SELECT” and “WHERE” clauses, along with error trapping, SQL execution, and Python’s for loop for cycling through a List.

Using Fetchall to get all records

We will see how easy it is to use Python psycopg2’s connection, cursor, and fetchall function to retrieve all records from a given recordset query and place that data set into a Python List. Python Fetchall was designed specifically for the purpose to get records from a query directly into the Python List data type.

Prerequisites

Following are a few technologies you will hopefully be familiar with.

Basic SQL

Knowledge of the basics of writing Postgres queries with SQL. We use dBeaver’s free database management software for error checking, greater efficiency, and ease than is afforded by the PG Admin database management tool.

Basic Python

We have used Python 3.8 for this exercise, which is the latest at the time of this writing. You can download the latest Python software here for free.

Naming conventions

  • Tutorial on naming conventions showing how and why you may want to prefix variables, column names, table names, etc. as you see done in this article. For example, naming “tvariable” with the “t” prefix to define it as a “text” (string) object and “tbl_” before table names in order to clearly distinguish those objects as tables. The tutorial goes a bit deeper, as well, talking about how to name variables based on a hierarchy system.

Cursor and recordsets

A basic understanding of what a cursor and recordset are in Postgres, which is similar – if not the same – as in any modern relational database management system (RDBMS).

Overall plan to retrieve data

Before we get into it, we’ll go over one functionality that is quite relevant to learning how to retrieve records from PostgreSQL using Python: Python’s List (array) data type. Why? Because the nature of fetchall is to retrieve a recordset, which is multiple records. We can either loop through the recordset while the cursor is open or we can pull the entire recordset into an array and close the cursor object. In this article, we’ll go over both methods. First, it will be helpful to learn what a Python List – otherwise known as a Python Array – is.

What is the Python List data type?

Python List vs Array

The List data type in Python is an array with increased flexibility in how it handles values and their type.

A Python List is a group or list of values or items. We often will increase efficiency by using lists because they allow you to store, reference, add, remove, and sort groups of values in a similar manner to how you would work with a relational database table. “-3, 62, -54, 25” is a list of integers. “Postgres, Recordset, Python, Fetchall, SQL” is a list of strings or text items. In some coding languages, every item in the list must be the same data type. With the Python list, you can have mixed types, like so: “553, PostgreSQL, -33, 331.4, Fetchall”. Most use the term “Item” to describe each value in a list.

Single dimension list

While Python lists have multidimension functionality, we’ll stick to single dimension lists for this article, to increase ease of learning these new concepts.

Syntax of a Python list

list_autos = ["Volkswagen", "Jaguar", "Ford"]

The above code accomplishes two tasks at once; it creates a list called “list_autos” and fills the list with three values.

Retrieve data from list – single

How do we retrieve data from the above Python list?

t_car_brand = list_autos[1]
print(t_car_brand)

The value stored in “t_car_brand” is now “Jaguar” because list begins count at zero. So, dissimilar to how the index works in a Postgres Array, “1” here means the second item in the list. As you see, we call that number “index”.

Retrieve data from list – for loop

Now to loop through the Python list data to see each item’s value:

list_autos = ["Volkswagen", "Jaguar", "Ford"]
for t_car_brand in list_autos
    print(t_car_brand)

Analysis

  • for: We have used Python’s “for” loop to move through every item in “list_autos”, using a temporary variable called “t_car_brand” to store the current item briefly in order to print.
  • print: The Python “print()” function gives the results you see below.

Results

Volkswagen
Jaguar
Ford

Connect to Postgres using Python

For those who want a high level view of this process, we’ll show what Python libraries you need to include (import), as well as how to set up a database connection. As a bonus, we’ve also included Flask’s “render_template” library that is useful for showing dynamic HTML pages to the user, here for reporting errors.

Using the Python Flask library

from flask import Flask
from flask import render_template
t_host = "PostgreSQL database host address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)

Analysis

  • from flask import render_template: For error checking (using “try” later), to send the user to an HTML error page we created, we need Python’s “render_template()” function.
  • t_host: Set the Postgres database connection parameters like host address, port, database name, database user name, and user password.
  • db_conn: Set a database object and hand it the information it needs.

Using the Python Psycopg2 library

import psycopg2
db_cursor = db_conn.cursor()

Analysis

  • db_cursor: Designate a “database cursor”, to be used for further database operations. This is crucial in order to use the Python Fetchall() function.

Get data from Postgres with Select

First, let’s set up a Postgres table with user names. We’ll name the table “tbl_users”:

t_name_user
Stevie
Billy
Sharie
Troy
Georgie
Sally

Our goal is to get that data into a Python List. Fortunately, Python’s psycopg2 library supplies an easy method for this.

s = "SELECT t_name_user FROM tbl_users"
db_cursor.execute(s)
list_users = db_cursor.fetchall()

Analysis

  • SELECT...: Get Postgres to supply the values in the “t_name_users” column from the “tbl_users” table.
  • db_cursor.execute(s): Forward our SQL command on to PostgreSQL.

Get Postgres records into a Python List

list_users = db_cursor.fetchall()

Analysis: Using fetchall, we copy all the user names into the “list_users” Python list.

Now to put it all together:

Full Python Source Code

from flask import Flask
from flask import render_template
import psycopg2

app = Flask(__name__)
@app.route("/")

t_host = "PostgreSQL database host address" # either "localhost", a domain name, or an IP address.
t_port = "5432" # default postgres port
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

@app.route("/get_records")
def get_records():
    # SQL to get records from Postgres
    s = "SELECT t_name_user FROM tbl_users"
    # Error trapping
    try:
        # Execute the SQL
        db_cursor.execute(s)
        # Retrieve records from Postgres into a Python List
        list_users = db_cursor.fetchall()
    except psycopg2.Error as e:
        t_message = "Database error: " + e + "/n SQL: " + s
        return render_template("error.html", t_message = t_message)

    # Loop through the resulting list and print each user name, along with a line break:
    for i in range(len(list_users))
        print("User " + list_users[i] + "\n")

    # Close the database cursor and connection
    db_cursor.close()
    db_conn.close()

    # Now send the user on to the next part of your app...

Conclusion

In this article we learned and practiced how to get records from Postgres using Python and the psycopg2 library’s database connection tools, including cursor and fetchall. We also learned the ins and outs of using Python’s List data type and how it differs from a Python array. We also learned to use the Python for loop. Finally, we used the SELECT PostgreSQL statement to retrieve data from a database. A few other functions we used: range, len, and print. We then provided heavily commented source code.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.