Postgres List Tables with Python

Introduction

In this tutorial we will learn how to use Postgres to list tables with Python. We’ll begin by setting up a database connection in Postgres with Python. Then we will create a new table. Finally, we will use SQL to cycle through a list of tables using Python. Here is the high level plan:

Set up a connection to Postgres

An integral part of this lesson is to connect to a PostgreSQL database using Python. We like to use Python’s psycopg2 library. Later in this tutorial, we’ll provide the exact code you need to use that library to create a database connection so you can then do the following.

Create a Postgres table

It follows that if you are seeking to get a list of tables with Python, you may also wish to create tables. After importing the psycopg2 library, we’ll send the “CREATE TABLE” command to Postgres so that we have at least one or more tables in our database. Another reason to understand PostgreSQL table creation: in this tutorial, when we list the tables with Python, we are not faced with an empty recordset.

List the Postgres tables using Python

Finally, once we have connected to Postgres, we will send the “SELECT” command to Postgres to get a list of tables. We’ll then show the table names using a Python for loop to iterate through all tables found in the database.

Prerequisites

Following are some items you may need to know in order to get the most out of this lesson:

Python coding

We used Python 3.8 here, which is the latest version of Python as of the day when we wrote this article. You are not obligated to use this version. However, if you want to, download it free here.

Writing SQL

Some experience creating and running SQL queries. In this tutorial, we stick to the easiest, least complex, queries, including use of CREATE, SELECT, WHERE, and ORDER BY. For managing Postgres, we like the free version of dBeaver’s database management tool for ease, query testing, and increased efficiency.

Naming conventions

Tutorial on naming conventions shows why and how you may wish to use naming conventions, that is to: prefix your variables, column (field) names, table names, etc. as you see in this article. For example, naming “iage” with an “i” prefix to mean “integer”, “tvariable” with a “t” prefix to designate it as “text”, and “tbl_” before table names in order to easily distinguish them as tables. The tutorial goes a bit deeper, as well, talking about how to name variables based on a hierarchy system. This increases ease of readability for you and anyone who inherits your code.

Database connection to Postgres

t_host = "PostgreSQL database host address"
t_port = "5432" # default port for postgres server
t_dbname = "database name"
t_name_user = "database user name"
t_password = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
db_cursor = db_conn.cursor()

Analysis

  • t_host: If your database is on a local machine, use “localhost” as the address, otherwise, an IP address.
  • t_port: “5432” is the default port for postgreSQL servers.
  • t_dbname: The name of your database.
  • t_name_user: The user name you set up with permissions to access the Postgres database from Python.
  • t_password: Password for the above user.
  • db_conn: Connection to your PostgreSQL server.
  • db_cursor: Cursor for reading/writing from/to the database with Python.

Create a Postgres table

Let’s look at the code in Python we’ll use to create a table in Postgres.

Example of PostgreSQL table creation

t_name_tbl = "turkeys"
s = ""
s += "CREATE TABLE " + t_name_tbl + "("
s += " id serial NOT NULL"
s += ", id_session int4 NULL DEFAULT 0"
s += ", t_name_turkey varchar(64) NULL"
s += ", t_contents text NULL"
s += ", d_created date NULL DEFAULT now()"
s += ", CONSTRAINT turkeys_pkey PRIMARY KEY (id)"
s += " ); "
s += "CREATE UNIQUE INDEX turkeys_id_idx ON public.turkeys USING btree (id);"

Analysis

  • t_name_tbl: Use a variable to store the name of the table we want to create, in this case “turkeys”.
  • CREATE TABLE: This is the Postgres SQL command we use to create a new table named “turkeys”.
  • CREATE UNIQUE INDEX: This line tells Postgres we want the id column to be autoincrementing.

Run Postgres query using Python

Now we write the code to execute the SQL we built above to create the new Postgres table.

db_cursor.execute(s)
db_conn.commit()

Analysis

  • db_cursor.execute(s): Execute the SQL commands stored in the variable we named “s”.
  • db_conn.commit(): If autocommit is turned off, we need to do this to tell Postgres that we are sure about executing this query.

Create SQL to get table list

Let’s write a query to ask the Postgres database what tables exist that fit the “public” schema.

s = ""
s += "SELECT"
s += " table_schema"
s += ", table_name"
s += " FROM information_schema.tables"
s += " WHERE"
s += " ("
s += " table_schema = 'public'"
s += " )"
s += " ORDER BY table_schema, table_name;"

Analysis

  • SELECT: The common SQL command to get data from a database.
  • table_schema: This stores whether the table is designated as public or not.
  • table_name: The name of the table being referenced.

List PostgreSQL tables

Now that we have used Python to write the SQL for Postgres, we can execute the query and loop through all the tables returned.

db_cursor.execute(s)
list_tables = db_cursor.fetchall()

for t_name_table in list_tables:
    print(t_name_table + "\n")

Analysis

  • list_tables = db_cursor.fetchall(): The Python psycopg2 fetchall() function returns all records returned from PostgreSQL. This recordset is stored in the Python list we are calling “list_tables”.
  • for t_name_table: This is the meat of our for loop, assigning the “t_name_table” variable to track each iteration of a value stored in the “list_tables” list.
  • print: Returns the table name each time through the for loop iteration, along with “n”, which sends a carriage return.

Now that we have gone through the process for Postgres to list tables using Python, we’ll write out the full application. Please note, we left out error trapping and creating any functions, in order to keep this lesson as focused and simple for you as possible.

Source code to list Postgres tables with Python

import flask # Python library for various functionalities
import psycopg2 # Python library for database connection

# connect to PostgreSQL
t_host = "Postgres database address" # this will be either "localhost", a domain name, or an IP address.
t_port = "5432" # default port for postgres server
t_dbname = "database"
t_name_user = "user name"
t_password = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
db_cursor = db_conn.cursor()

@app.route("/Main")

def CreateTable(t_name_table):
    # create table SQL
    s = ""
    s += "CREATE TABLE " + t_name_tbl + "("
    s += " id serial NOT NULL"
    s += ", id_session int4 NULL DEFAULT 0"
    s += ", t_name_item varchar(64) NULL"
    s += ", t_contents text NULL"
    s += ", d_created date NULL DEFAULT now()"
    s += ", CONSTRAINT " + t_name_tbl + "_pkey PRIMARY KEY (id)"
    s += " ); "
    s += "CREATE UNIQUE INDEX " + t_name_tbl + "_id_idx ON public." + t_name_tbl + " USING btree (id);"

    # execute the create table SQL
    db_cursor.execute(s)
    # if autocommit is not turned on:
    db_conn.commit()

def GetTableList(t_schema)
    # Retrieve the table list
    s = ""
    s += "SELECT"
    s += " table_schema"
    s += ", table_name"
    s += " FROM information_schema.tables"
    s += " WHERE"
    s += " ("
    s += " table_schema = '" + t_schema + "'"
    s += " )"
    s += " ORDER BY table_schema, table_name;"

    # Retrieve all the rows from the cursor
    db_cursor.execute(s)
    list_tables = db_cursor.fetchall()

    # Print the names of the tables
    for t_name_table in list_tables:
        print(t_name_table + "\n")

def Main():
    t_name_tbl = "turkeys"
    CreateTable(t_name_tbl)
    t_schema = "public"
    GetTableList(t_schema)

Conclusion

In this tutorial we progressed step by step through Postgres to create an app to list tables with the Python scripting language, with specific dependence on the psycopg2 library. During this lesson we had some brief exposure to various functions including Python lists (essentially, arrays), creating a PostgreSQL table, iterating through a list of tables with Python using the for loop, and the print function. Finally, we provided analysis, examples, and commented source code throughout this article.

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.