Select Records From Postgres

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial we will teach you how to select records from Postgres into a list. We’ll utilize the following features of both Postgres and Python, including the psycopg2 framework for Python. We’ll also use the SELECT query command and the Fetchall function from the psycopg2 library. We’ll build a foundation of getting records from PostgreSQL into the Python List type. Finally, we will learn to handle errors in Python using the Try Catch feature built into Python.

Postgres SELECT and WHERE

We’ll use both the “SELECT” and “WHERE” statements, query execution, error trapping, SQL execution, and the FOR loop for iterating through items in a List, which is basically Python’s version of an Array.

Using Fetchall to get all rows

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 pieces of tech you should be familiar with.

Writing queries for Postgres

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 afSamsunged by the PG Admin database management tool.

Writing Python applications

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 or use version 2.5 or newer.

Cursor and recordsets

An understanding of what cursors and recordsets are in Postgres.

The strategy to select records

Before we begin, we’ll investigate one functionality that is relevant to learning how to select records from Postgres: The List data type. Fetchall is for retrieving a recordset, typically with more than one row. We can loop through the recordset, keeping the cursor open or we can read the entire recordset into a List, close the cursor object, and do what we wish with that List. In this tutorial, we’ll use mini exercises to learn both methods. First, it will be useful to learn what a Python List is and how it works.

What is the List data type?

The Python List data type is an array with increased flexibility with handling the data type of values.

A List is a list or group of values. We can increase efficiency by using lists because they allow you to store, reference, add, remove, and sort groups of values in a similar way to how you would work with a database table. “-2, 61, -34, 21” is a list of integers. “PostgreSQL, Select, Records, Query” is a list of text items or strings. In many coding languages, even with Python’s Array, every item in the list must be the same type. With the Python List, you can have mixed data types, like so: “51, Postgres, -235, 311.4, Select”. We use the term “Item” to describe each value in an array or List.

Single dimensional list

Python lists have multidimension functionality but we’ll begin with single dimension lists for this article, to keep the learning curve low.

Syntax of a Python list

1
list_items = ["HTC", "Huawei", "Samsung"]

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

Retrieve data from list – single

How do we retrieve data from the above Python list?

1
2
t_item_brand = list_items[1]
print(t_item_brand)

The value in “t_item_brand” is now “Huawei” because list begins count at zero. So, dissimilar to how the index works in a PostgreSQL Array, “1” means the second item in the list because the list begins at zero. We call that number an “index”, not to be confused with how you index a column in Postgres.

Print list using for loop

Use the For loop to iterate through the Python list item to see each item’s value:

1
2
3
list_items = ["HTC", "Huawei", "Samsung"]
for t_item_brand in list_items
    print(t_item_brand)

Analysis

  • for: We used Python’s “for” loop to move through each item in “list_items”, using a variable named “t_item_brand” to hold the value of the current item in the looping briefly so as to print.
  • print: The Python Print function supplies the results you see below.

Results

1
2
3
HTC
Huawei
Samsung

Connect to Postgres using Python

For a detailed understanding of how to replicate this entire process, we’ll see what Python frameworks you need to import, as well as how to set up a database connection to Postgres. We’ve included Flask’s “render_template” library that is useful for showing dynamic HTML pages to the user for reporting errors in this case.

Using Python Flask framework

1
2
3
4
5
6
7
8
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

  • import render_template: For error checking, to send the user to an HTML error page we created, we use Python’s “render_template” function.
  • t_host: Set the PostgreSQL database connection configuration params like host address, port, database name, database user name, and user password.
  • db_conn: Set a database object and hand it all the parameters it requires to make a secure connection to PostgreSQL.

Using the Python Psycopg2 library

1
2
import psycopg2
db_cursor = db_conn.cursor()

Analysis

  • db_cursor: Create a “database cursor”, to be used for all our database interfacing.

Select data from Postgres

First, create a Postgres table with user names. We’ll name the table “tbl_items”:

t_name_item
Lunch
Cannister
Wallet
Truck

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

1
2
3
s = "SELECT t_name_item FROM tbl_items"
db_cursor.execute(s)
list_items = db_cursor.fetchall()

Analysis

  • SELECT t_name_item…: Postgres supplies the values in the “t_name_items” column from the “tbl_items” table.
  • db_cursor.execute(s): Forward our SQL command on to PostgreSQL.

Fetch PostgreSQL records into a Python List

1
list_items = db_cursor.fetchall()

Analysis

Using the fetchall function from the psycopg2 framework, copy all the items into a Python list called “list_items”.

Now we put all the parts together into a Python application that uses select to get records from the Postgres database:

Full Python Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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("/select_records")
def select_records():
    # Select records from Postgres
    s = "SELECT t_name_item FROM tbl_items"
    # Error catching
    try:
        # Execute the query
        db_cursor.execute(s)
        # Retrieve records from our query of Postgres into a Python List
        list_items = 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)

    # Iterate through the resulting list using a for loop and print each user name, along with a "\n" for line break:
    for i in range(len(list_items))
        print("Item " + list_items[i] + "\n")

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

Conclusion

In this article we learned how to get select records from Postgres. We utilized the following features of both Postgres and Python, including the psycopg2 framework for Python. We used the SELECT query statement and the Fetchall function from the psycopg2 library. We built a foundation of getting records from PostgreSQL into a Python List. Finally, we learned to handle errors in Python using the Try Catch feature built into Python and how to use the render_template function.

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.