Copy Python List to Postgres

Introduction

In this article we learn how to copy a Python List to Postgres by using the “INSERT INTO” SQL command. Here’s how we’ll do it:

  • What? We’ll first learn how single and multidimensional Python Lists work, while along the way learning the difference between a Python List and a Python Array. We’ll then look at two techniques, using PostgreSQL’s INSERT statement to get the data from our list into a Postgres table. During this learning process, we’ll also practice use of the Python for loop and the len() and range() list-related functions. We’ll also make use of the render_template() Python function to show a template (error page) to the user.
  • How? We will build a application in Python to put it all together.

Prerequisites

  • We recommend you have some basic knowledge of using Postgres, 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.
  • Optional but helpful: Tutorial on naming conventions explaining why we prefix our 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.

Before writing our Python application, we’ll learn all about 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 a Python List and how does it work?

Short answer: It’s an array, but with a bit more flexibility. In case you don’t know what an array is, we’ll describe a list for you on a high level:

A List in Python is a group or array of items. Lists can increase our efficiency because they allow us to efficiently store, reference, and sort small and large groups of values. You can call “-1, 3, 15, 33, 2” a list of integers. You can call “Import, Export, Copy, Paste, Insert, Postgres, Array, Query, SQL” an array or list of strings or text items. In some languages, your list must be all of the same type. In Python’s case, you can have mixed types, like this: “5, Javascript, -3, 2.5, Database”. We use the term “Item” to describe individuals in the group that is a list.

Python lists are a datatype that have multidimensional functionality. That may sound a little complex, we w will first work on understanding how a single dimension list works.

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

Analysis: The above code simultaneously creates a list called “list_cars” and fills it with three items that all happen to be text. How do we retrieve data from the list (array) above?

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 “1” means the second item in the list. That number is often called “index”.

Let’s loop through our list to see all the values:

list_cars = ["Ford", "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 store the current item.
  • print(): Python’s “print” function gives us output.

Here are the results of the loop you see above:

Ford
Tesla
Audi

Now that you have some experience with Python lists, we’ll take a look at multidimensional lists. We’ll assume you want to track models as well as brands of cars.

list_cars = [["Ford", "Tesla", "Audi"], ["Mustang GT 500", "Model 3 AWD Performance", "Quatro"]]
for i in range(len(list_cars))
    print("My " + list_cars[0][i] + " is a " + list_cars[1][i])

Analysis – Range: The Python “range” function creates a mini-list of, in this case, 0 to 3 because we are supplying it with a 4 with the len() function described below. – Len: Python’s “len” function gives us the size of the array in number of items.

The results:

My Ford is a Mustang GT 500
My Tesla is a Model 3 AWD Performance
My Audi is a Quatro

Now that we have a fairly solid understanding of how Python’s list type works, we can move on to how to copy our list into Postgres. First, we’ll quickly go over setting up Python commands for the libraries we’ll need, as well as the exact code for connecting to your PostgreSQL database.

Connect to Postgres

from flask import Flask
from flask import render_template # to render an error page
import psycopg2
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()

Analysis

  • from flask import render_template: In case our error checking (using “try” later), and in order to send the user to an HTML error page we created, we need the “render_template()” Python function.
  • t_host…t_pw: We set up our Postgres database connection parameters like host address, port, database name, database user name, and user password.
  • db_conn: This is where we are create a database object and hand it the information it needs.
  • db_cursor: This is to create a “database cursor”, to be used for most database operations.

Copy Python List into PostgreSQL

First, a simple method you may be tempted to use. Then we’ll show you a more efficient method built right into Python’s psycopg2 library.

list_people = ['Frank', 'Tanya', 'Angi']
for i in range(len(list_people))
    s = ""
    s += "INSERT INTO tbl_users"
    s += "("
    s += "t_name_user"
    s += ") VALUES ("
    s += "%param"
    s += ")"
    db_cursor.execute(s, i)

What is inefficient about the code above? As you may have noticed, there is a “for” loop that cycles through each of the three items in our list. Each time (for each user), it runs an INSERT to create a new row in tbl_users of our Postgres database. Now, in order to fully appreciate how inefficient that is, we’ll show you another way that is also more efficient in terms of server resources and bandwidth.

list_people = ['Frank', 'Tanya', 'Angi']
s = ""
s += "INSERT INTO tbl_users"
s += "("
s += "t_name_user"
s += ") VALUES ("
s += "%param"
s += ")"
db_cursor.execute(s, list_people)

Analysis

With the new and more efficient version you see above, we have removed the “for” loop and placed the entire list as a parameter to be fed to the Postgres SQL to run ONE “insert into” command. Simple!

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_list")
def copy_list():
    list_people = ['Frank', 'Tanya', 'Angi']
    s = ""
    s += "INSERT INTO tbl_users"
    s += "("
    s += "t_name_user"
    s += ") VALUES ("
    s += "%param"
    s += ")"
    try:
        db_cursor.execute(s, list_people)
        db_cursor.commit()
    except psycopg2.Error as e:
        t_message = "Database error: " + e + "/n SQL: " + s
        return render_template("error_page.html", t_message = t_message)

    # Success!

    # 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 SECURITY: Use Stored Procedures

Protect your user data by learning about

Conclusion

In this tutorial we were schooled in how to copy a Python List into a Postgres table column. We learned that in Python parlance, “List” is pretty much an “Array” with added flexibility with typing. We used Python’s “for” loop a couple times. We also used the “INSERT INTO” database command to copy data into a new table row. A few functions and commands we touched on include: print, range, and len. Finally, we provided all of the source script for this Python application for you to use with your projects.

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.