/ ID: 916 Title: Copy a Postgres Array to a Python List 916 Meta Description: This article shows how to copy a Postgres Array to a Python List. Code samples included. Meta Keywords: Copy Postgres Array Python List Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: Copy, PostgreSQL, Python, List Status: Published /

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_variable_name" 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:

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.

SQL SELECT t_name_user text , arr_i_grade integer[] FROM tbl_users

Gives us...

| t_name_user | arr_i_grade | |-------------|---------------:| | Stevie | 40, 55 | | Billy | 88, 60, 85 | | Sharie | 82, 81, 89 | | Troy | 90, 86, 91, 94 | | Georgie | 60, 75 | | Sally | 77 | | Freddie | 74, 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.

SQL SELECT t_name_user text , arr_i_grade integer[1] FROM tbl_users

Returns...

| t_name_user | arr_i_grade | |-------------|---------------:| | Stevie | 40 | | Billy | 88 | | Sharie | 82 | | Troy | 90 | | Georgie | 60 | | Sally | 77 | | Freddie | 74 |

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:

SQL 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:

SQL 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.

python 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?

python 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:

python 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.

python 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

python 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:

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

python 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

``` 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.