Python Len for Postgres Insert

Introduction

In this article we learn how to use Python Len for Postgres Insert of records, with the help of Python’s range function and PostgreSQL’s “INSERT” SQL command. Here’s how the lesson will go:

  • What? The first step in this article is to understand how a single and multidimensional Python List (Array) works. An important additional aspect to this lesson is to understand how Python Lists and Python Arrays are different. we’ll use a parameterized Postgres query to INSERT data from a list, which is where the Python LEN and RANGE functions will come in handy in order to determine the size of our list for easier insertion into our relational database table. During this lesson, we’ll also practice use of the Python for loop. 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 complete web application in Python to get both high level (simple) and detailed views.

Prerequisites

  • We recommend you have at least beginner-level understanding of how to use PostgreSQL SQL structure, including Postgres Insert. We recommend use of the free “PGadmin” tool or another database administration tool. We are using DBeaver to manage our Postgres database and Visual Studio Code to script Python.
  • Optional: Tutorial on naming conventions explaining why we prefix our variables, columns, 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 learning what Python Len is and does and writing a full blown Python and Postgres web app, we’ll learn how the List data type works in Python because Len is a crucial and oft-used function directly related to Python Lists. After learning this, we’ll write some code using these Python functions to create a portion of our web application.

What is a Python List?

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:

Syntax

list_name = [item 1, item 2, item 3, etc.]

Analysis

  • list_name: the variable name we choose to give our list.
  • [item 1, etc]: the individual items in our list. Note strings need to be encapsulated by single quotes or double quotes.

In Python, a List is a group or array of values, each value often called an “item”. Lists increase efficiency because they allow us to 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 “Python, Len, Postgres, Insert” a list or array of text items. In some languages and when using Python array instead of list, your group of values must be all of the same type. In Python’s case, you can have mixed types, like this: “5, ‘Len and List’, -3, 2.5, ‘Python and Postgres'”. In a big way, a Python List is much like a PostgreSQL data table.

Important to keep in mind: Python lists, just like PostgreSQL arrays, are a datatype that have multidimensional functionality. Given the complexity of that concept, we’ll work first on understanding how a list with one dimension (single dimensional list) works.

list_food_heat = ["Spicy", "Savory", "Sweet"]

Analysis: The above code does two things at once: (a) creates a list called “list_food_heat”; and (b) adds three text items to the new list. How do we reference data from the list (array) above? We’ll loop through “list_food_heat” to retrieve all values:

list_food_heat = ["Spicy", "Savory", "Sweet"]
for t_food_heat in list_food_heat
    print(t_food_heat)

Analysis

  • for: This is a Python loop for cycling sequentially through each item in “list_food_heat”, designating a variable called “t_food_heat” to store the current item.
  • print(): Python’s “print” function outputs data for us to look at.

See the results of the above “for” loop:

Spicy
Savory
Sweet

Note: it’s also possible to use the “for” loop to go backwards by changing the above loop to look like this:

list_food_heat = ["Spicy", "Savory", "Sweet"]
for i in range(len(list_food_heat), 0, -1)
    print(list_food_heat[i])

The output then looks like:

Sweet
Savory
Spicy

Perhaps you noticed in the above example we used the Python Len function. So let’s analyze the code. We’ll begin by studying the Range function’s syntax.

a_list = range(start, stop, step)
  • a_list: The resulting list of numbers returned by the Python Range function.
  • start: If you are working with lists or arrays, you’ll usually want to start this at zero
  • stop: Which number do you want to stop at?
  • step: Which direction are you counting in? From 0 to x or from x to 0? If counting “down” from x to 0, you want to put -1 here. Or you could even put something like -2 if you want to count down two at a time.

OK armed with an understanding of the Python list type, your brain is primed to learn multidimensional lists. For the lesson, we will add a bit of complexity to our food paradigm by adding a specific food to go with each food heat. For the example to make more sense, we’ll change the name of our list from “list_food_heat” to “list_foods”.

list_foods = [["Spicy", "Savory", "Sweet"], ["Taco", "Stew", "Ice cream"]]
for i in range(len(list_foods))
    print("A " + list_foods[0][i] + " food is " + list_foods[1][i])

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

The results:

A Spicy food is Taco
A Savory food is Stew
A Sweet food is Ice cream

Now that we understand how Python’s list type works, we can move on to how to copy a single dimension list into Postgres. We’ll begin by using Postgres’ INSERT, along with Python’s Len and Range functions to add a record to the database.

Copy Python List into PostgreSQL

list_users = ['Francesca', 'Timothy', 'Alberto']
for i in range(len(list_users))
    s = ""
    s += "INSERT INTO tbl_users"
    s += "("
    s += "t_name_user"
    s += ") VALUES ("
    s += "%param"
    s += ")"
    db_cursor.execute(s, list_users[i])

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_users = ['Francesca', 'Timothy', 'Alberto']
for i in range(len(list_users))
    s = ""
    s += "INSERT INTO tbl_users"
    s += "("
    s += "t_name_user"
    s += ") VALUES ("
    s += "%param"
    s += ")"
    try:
        db_cursor.execute(s, list_users[i])
        db_cursor.commit()
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)

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

    # Send the user to the next part of your app...

INCREASE SECURITY even more by using stored procedures instead of in-line SQL

Protect your data by learning about:

Conclusion

In this article we learned how to use Python Len for Postgres Insert of records from a List into a relational database table. We learned that the Python “List” is the same as “Array” with some added flexibility with data type mixing. We used Python’s “for” loop a couple times. We included the “INSERT” SQL command to place data into a new record. A few functions and commands we also used were: range, print, range, and render_template. In addition, we listed the full source code for the Python web application.

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.