Copy a Postgres Array to a Python List

Introduction

In this article we learn how to copy a Postgres Array into a Python List using arrays, lists, the “INSERT” SQL command. Here’s how:

  • What? We’ll first learn how Postgres array type works, 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.
  • How? We will build a Python web application to finish the lesson.

Prerequisites

  • Postgres: Basic understanding of PostgreSQL, whether you use the free “PGadmin” tool or another database administration tool. And in this case, Python is crucial. In this case, we are using Visual Studio Code to write Python and Dbeaver to manage our Postgres database.
  • Naming conventions: Optional: Naming conventions tutorial explaining why we prefix variables, column names, table names, etc. as you see us doing in this article. For example, naming “t_variablename” with the “t” you see at the beginning in order to delineate it as a “text” (string) object and “tbl_” before table names in order to clearly mark those objects as tables.

How Arrays work

An array is essentially a group or list of items. In the programming and database world, arrays can save us a huge amount of work and increase efficiency. You can call “1, 2, 3, 4, 5, 6” an array of integers or numeric items. You can call “Jim, Ted, Sue, Mary, Tina” an array of strings or array of text items. “Items” is often used to describe each individual in the group that is an array.

Postgres Array

Postgres has a data type called Array. So within a “cell” (cross section between a database column and row), you can have – instead of an integer or text data type – an array data type. Here’s an example of how that looks in SQL:

CREATE TABLE tbl_users (
    t_name_user text
    , arr_i_grade INTEGER[]
)

Analysis

In the SQL above, you may recognize the text data type that is being used for “t_name_user”. The column we named “arr_i_grade” is probably new for you as it is an array data type. The brackets (“[]”) are how we tell Postgres “type this column as an array.”

Let’s look at that table filled with some data so we can get a visual perspective on the concept of how Arrays work in PostgreSQL.

SELECT
    t_name_user text
    , arr_i_grade INTEGER[]
FROM
    tbl_users

Gives us…

t_name_userarr_i_grade
Stevie40, 55
Billy88, 60, 85
Sharie82, 81, 89
Troy90, 86, 91, 94
Georgie60, 75
Sally77
Freddie74, 42, 73, 78

Note: In reality, the data returned from PostgreSQL had “{” and “}” curly brackets that we removed from to give you a less cluttered and easier to grok view of the data.

Next, we retrieve data from the “tbl_users” table, pulling only part of the array, so you can get a deeper understanding of how a Postgres array works.

SELECT
    t_name_user text
    , arr_i_grade INTEGER[1]
FROM
    tbl_users

Returns…

t_name_userarr_i_grade
Stevie40
Billy88
Sharie82
Troy90
Georgie60
Sally77
Freddie74

Analysis

The [1] in arr_i_grade integer[1] told the SQL query interpretation engine to give us the first item in the “arr_i_grade” column and that column happens to be an array. Note, as you will see later, Python (as does most languages) starts with zero instead of 1 as the index for accessing arrays.

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, which would look like:

SELECT
    t_name_user text
    , arr_i_grade INTEGER[x][y]
FROM
    tbl_users

Here’s another view on how array creation might work in Postgres:

DECLARE
    arr_names TEXT[] := ARRAY['Jim', 'Tim', 'Pim'];

That’s the basic gist of how arrays work in Postgres.

Before we write an application for copying data from a Postgres Array into a Python List, we’ll learn how a List works in Python. After learning this, we’ll write some code using the LIST data type to create a portion of our import application.

What is Python’s List data type?

Short answer: It is an array with increased flexibility in how it handles types.

A List in Python is a group of values or items. Lists increase efficiency because they allow you to efficiently store, reference, add, remove, and sort groups of values. You can call “-2, 6, -11, 35” a list of integers. You can call “Copy, Postgres, Array, Python, List, SQL” an array or list of text items or strings. In some programming languages, lists have to contain items where every item in the list must be the same type. In Python’s case, you can have mixed types, like this: “3, Python, -1, 5.2, List”. Most coders use the word “Item” to describe individual values in a list.

Python lists are a datatype that also have multidimension functionality. We will begin by gaining an understanding of how one dimension lists work.

list_cars = ["BMW", "Tesla", "Audi"]

The above code simultaneously creates a list called “list_cars” and fills the list with three values. How do we retrieve data from the above Python list?

t_car_brand = list_cars[1]
print(t_car_brand)

The value stored in “t_car_brand” is now “Tesla” because list begins count at zero. So, unlike how the index works in a Postgres Array, “1” here means the second item in the list. That number is often called “index”.

Now we loop through the list to see all the values:

list_cars = ["BMW", "Tesla", "Audi"]
for t_car_brand in list_cars
    print(t_car_brand)

Analysis

  • for: We are using Python’s “for” loop to cycle through every item in “list_cars”, designating a variable called “t_car_brand” to temporarily store the current item in the local variable.
  • print: Python’s “print()” function provides output.

The results from the above Python script:

BMW
Tesla
Audi

Now that you have some experience with Python lists, we’ll take a look at a multidimensional list. We’ll now track models, in addition to brands of automobiles.

list_cars = [["BMW", "Tesla", "Audi"], ["M5", "3 AWD Performance", "Quatro"]]
for i in range(len(list_cars))
    print("My " + list_cars[0][i] + " model is " + list_cars[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:

My BMW model is M5
My Tesla model is 3 AWD Performance
My Audi model is Quatro

Now that we have understand how a Python list works, we can move on to how to copy a Postgres array into a Python list. First, let’s briefly look at how to set up Python required libraries, primarily for database management.

Connect to Postgres

from flask import Flask
from flask import render_template
import psycopg2
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)
db_cursor = db_conn.cursor()

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.
  • db_cursor: Designate a “database cursor”, to be used for further database operations.

Copy Postgres Array into a Python List

First, a Postgres table where one of its columns is an array type:

t_name_userarr_i_grade
Stevie40, 55
Billy88, 60, 85
Sharie82, 81, 89
Troy90, 86, 91, 94
Georgie60, 75
Sally77
Freddie74, 42, 73, 78

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, arr_i_grade 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 and the “arr_i_grade” array column from the “tbl_users” table.
  • db_cursor.execute(s): Forward our SQL command on to PostgreSQL.
  • list_users...: Copy all the user names into the “array_users” array.

Now let’s put it all together:

Full Source Code in Python

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("/copy_array")
def copy_array():

s = "SELECT t_name_user, arr_i_grade FROM tbl_users"
    try:
        db_cursor.execute(s)
        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)

    # Success!
    # Loop through the resulting list:
    for i in range(len(list_users))
        print("User " + list_users[0][i] + " scored these grades: " + list_users[1])

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

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

INCREASE YOUR DATABASE SECURITY: Use Stored Procedures

Conclusion

In this article we studied and implemented how to copy a Postgres Array into a Python List. We also learned that “List” is an extra-flexible “Array”. We used the Python “for” type loop. We also used the “SELECT” database command to read data from PostgreSQL. A few functions and commands we also used: print, len, and range. We then provided commented source code for your ease.

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.