Python and Postgres Array

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

Introduction

In this article we learn how to use a Python and Postgres Array in multiple situations. We’ll first learn how to use both Python array (known in Python as a “list”) and Postgres array, then we’ll study single and multidimensional Python Lists. During this process, we will see the difference between a Python List and a Python Array. We’ll then look at various examples using PostgreSQL’s SELECT statement to get data from an array, as well as how to create and read Python Lists. you will also practice use of Python’s “for” loop and list-related functions like range and len. We’ll then make use of the render_template() Python function to show a template (error page) to the user. To cement our understanding of Python and PostgreSQL arrays, we’ll build an application and share all the code with you.

Prerequisites

  • PostgreSQL: A beginner’s knowledge of what Postgres is and how it works.
  • Variables: Basic understanding of what non-array variables are in Postgres and Python and how they work.

Arrays in Python and PostgreSQL

Arrays are lists or groups of items. An Array (or list in Python) can save us work, increase speed of development, and better utilize resources like cpu usage, memory requirements, and storage needed for your applications. In both Python and Postgres, an array looks like “1, 2, 3, 4, 5, 6” and “Jim, Ted, Sue, Mary, Tina”. We typically use the term “Items” to describe individuals in a list or array.

In this tutorial you will learn to use both the Python array (technically Python list) and the Postgres Array. During this learning process we will provide both Python and Postgres array examples.

Python List type

Arrays in Python are somewhat limited because you can not mix types within any given array. So an array might be all integers, all text items, or all boolean. For this reason, most developers prefer using List instead, for its increased flexibility. Hereinafter, when we use the term “array” in relation to Python, we mean the List type.

Python lists – just like arrays in almost every language, including Postgres Arrays – are a data type that also have multidimensional functionality. We will begin by gaining an understanding of how single dimension lists work.

1
list_of_autos = ["VW", "Nissan", "Ford"]

The code above creates a list called “list_of_autos”, sets it up to have three items, and fills those three items with text values. How do we get data out of our Python array?

1
2
t_auto_brand = list_of_autos[1]
print(t_auto_brand)

The value stored in “t_auto_brand” is now “Nissan” because the array begins counting at zero. So, as you will see later, unlike how the counting occurs in PostgreSQL Arrays, “1” in a Python array refers to the second item in the group. That number is often referred to as the “index”.

Now to iterate through a loop to see all the values in our Python array:

Python list example

1
2
3
list_of_autos = ["VW", "Nissan", "Ford"]
for t_auto_brand in list_of_autos
    print(t_auto_brand)

Analysis

  • list_of_autos: This line of code created our list, named it “list_of_autos”, and set up three automobiles in the list.
  • for: Here we wanted to iterate through each item in the Python array we named “list_of_autos” and filled with three items, so we used a “for” loop to accomplish this. Using the for loop structure, we set “t_auto_brand” to point to (and store) the item’s value each time through the loop.
  • print: We used the Print function to test, showing the contents of t_auto_brand each time through the loop.

The results from the above Python script:

1
2
3
VW
Nissan
Ford

Multidimensional array in Python

Now that you have some experience with Python arrays, we’ll take a look at a multidimensional array. Up until now we have been using a one-dimensional array for automobile makes (brands). What if we want to simultaneously keep track of models? Thus us where we need a second dimension or use of “multidimensional arrays”. See below:

Multidimensional array example

1
2
3
list_of_autos = [["VW", "Nissan", "Ford"], ["Jetta", "Altima", "Mustang GT"]]
for i in range(len(list_of_autos))
    print("Brand: " + list_of_autos[0][i] + ", Model: " + list_of_autos[1][i])

Analysis

  • Range: Python’s “range” function allows us to set up a list of 0 to 3 because we are inputting a 4 from the len() function’s output, as shown below.
  • Len: Python’s “len” function outputs the size of the array; number of values (items).

The output:

1
2
3
Brand: VW, Model: Jetta
Brand: Nissan, Model: Altima
Brand: Ford, Model: Mustang GT

Now that we see how a multidimensional Python list works, we can move on to learning how to use a Postgres array.

PostgreSQL Array

Much like the Python arrays we studied above, Postgres has a similar data type. To be super clear: in the cross section between a database column and row you can have – instead of a boolean, integer, text, etc. – an array. Here’s an example of how the Postgres array appears in a query format:

1
2
3
4
CREATE TABLE tbl_autos (
    t_name_auto text
    , arr_i_rating INTEGER[]
)

Analysis

In the SQL above, you may recognize the text data type that is being used for “t_name_auto”. The column we named “arr_i_rating” is probably new since it is a PostgreSQL array data type. The brackets (“[]”) instruct Postgres to type the column as a array.

Let’s look at that table with some test data to get a more clear perspective on how Arrays work in Postgres SQL.

1
2
3
4
5
SELECT
    t_name_auto text
    , arr_i_rating INTEGER[]
FROM
    tbl_autos

Gives us…

t_name_autoarr_i_rating
Mustang40, 55
Versa88, 60, 85
Cobolt82, 81, 89
Wunk90, 86, 91, 94
Touring60, 75
Jetta77
Tycano74, 42, 73, 78

Note that the above results retrieved from our Postgres SQL had “{” and “}” brackets that we removed to provide a more clear and easy to understand visual on the actual array data.

Next, we will get results from “tbl_autos”, retrieving only part of the array, so you can get a deeper understanding of how Postgres arrays work.

Postgres array item

1
2
3
4
5
SELECT
    t_name_auto text
    , arr_i_rating INTEGER[1]
FROM
    tbl_autos

Results

t_name_autoarr_i_rating
Mustang40
Versa88
Cobolt82
Wunk90
Touring60
Jetta77
Tycano74

Analysis

The [1] in arr_i_rating integer[1] told Postgres’ SQL interpreter to return – for each row – the first item in the “arr_i_rating” column. Note: Python (as do most languages) starts with zero instead of 1 as the first item in an array or list.

Multidimensional array in Postgres

The above array type is also called a one dimensional array because for each “item” in that array, we stored only one value. If you want to store two values, you call it a two dimensional array or multidimensional array, which would look like:

Multidimensional Postgres array example

1
2
3
4
5
SELECT
    t_name_auto text
    , arr_i_rating INTEGER[x][y]
FROM
    tbl_autos

Here’s another look at how array creation might work in Postgres using SQL:

1
2
DECLARE
    arr_names TEXT[] := ARRAY['Wins', 'Won', 'Will win'];

Now, we’ll write some code using the array data type to create a portion of our import application.

Copy PostgreSQL Array into Python List

First, our PostgreSQL table with array type for a column to track rating scores for each car:

t_name_autoarr_i_rating
Mustang40, 55
Versa88, 60, 85
Cobolt82, 81, 89
Wunk90, 86, 91, 94
Touring60, 75
Jetta77
Tycano74, 42, 73, 78

Our target is to get the data you see above into a Python List. Python’s psycopg2 library supplies an easy method for the database connection to Postgres from Python part of this goal.

1
2
3
s = "SELECT t_name_auto, arr_i_rating FROM tbl_autos"
db_cursor.execute(s)
list_autos = db_cursor.fetchall()

Analysis

  • SELECT…: Instruct Postgres to supply values in the “t_name_autos” column and the “arr_i_rating” array from the tabled we called “tbl_autos”.
  • db_cursor.execute(s): Send the Postgres SQL command.
  • list_autos: Copy all the returned car names and rating scores from t_name_auto and arr_i_rating into the “array_cars” array.

Our table looks like…

t_name_autoarr_i_grade
Mustang{2018, 80}, {2019, 85}
Versa{2018, 88}, {2019, 80}, {2020, 85}
Cobolt{2017, 82}, {2018, 85}, {2019, 89}
Wunk{2018, 89}, {2019, 95}, {2020, 94}
Touring{2018, 65}, {2019, 76}
Jetta{2019, 87}
Tycano{2018, 72}, {2019, 79}, {2020, 78}

Expanding our example:

1
2
3
4
5
6
s = "SELECT t_name_auto, arr_i_rating FROM tbl_autos"
db_cursor.execute(s)
list_autos = db_cursor.fetchall()
for y in range(len(list_autos))
    for x in range(len(list_autos[y]))
        print("Car " + list_autos[y] + " scored these ratings: " + list_autos[y][x] & "\n")

Analysis

  • “for loop”: This Python For Loop iterates through each row retrieved from PostgreSQL into the Python list called “list_autos”.
  • “print”: list_autos[y] refers to the name of the automobile in that first column. list_autos[y][x] refers to the years and grades.

Combining all we have learned about PostgreSQL and Python Arrays, we can now write a full Python application:

Code in Python to use Postgres Array

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
import psycopg2

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

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

@app.route("/copy_array")
def copy_array():
    s = "SELECT t_name_auto, arr_i_rating FROM tbl_autos"
    try:
        db_cursor.execute(s)
        list_autos = db_cursor.fetchall()
    except psycopg2.Error as e:
        # code here to redirect...

    # Iterate through the resulting list:
    for y in range(len(list_autos))
        for x in range(len(list_autos[y]))
            print("Car " + list_autos[y] + " scored these ratings: " + list_autos[y][x] & "\n")

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

    # Now redirect the user to the next part of your application.

Conclusion

We studied the details of how to use the Python and Postgres Array in different situations. We also learned that in Python the “List” is an “Array” with added flexibility. Some incidentals include learning how to use the Python For loop. Finally, we learned to use some other functions, including print, range, and len. We then provided commented source code to assist in building a Python application focused on using arrays in both Python and PostgreSQL.

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.