Postgres Copy Tables with Python

Introduction

How do we use Postgres to copy tables with Python? The first thing we need to do is make sure we have a working Postgres database connection. Fortunately, Python has a library that addresses database connectivity for PostgreSQL. We’ll start off by setting up a database connection for Postgres with Python. Then we will create a new table we can use as the “original” Postgres table to duplicate with our Python code. Finally, we will use SQL to cycle through all public tables in our PostgreSQL database using Python, so we can see our new duplicate table. Here is the overview:

Create a Postgres data connection

An important part of this tutorial is to connect to PostgreSQL using Python. We recommend Python’s psycopg2 library. Later in this article, we will share the code you need to use that library to set up a database connection so you can then accomplish the following:

Create a PostgreSQL table

If your end goal is to duplicate a Postgres table with Python, you may also want to create a table to copy. After import of the psycopg2 library, we’ll execute “CREATE TABLE” in Postgres so that we have at least one or more tables in our database.

Duplicate a PostgreSQL table

During the use of an application, there may be times where you need to create a temporary version of an existing table. It could even be permanent. Either way, the ability to use one SQL command in PostgreSQL to copy a table with Python can be quite useful.

Show public PostgreSQL tables with Python

After we’ve estabished a Postgres connection and then duplicated a table, we will execute the “SELECT” command for Postgres to display a list of tables in the database. We’ll then show the table names using Python’s for loop to step through and show all tables found in the PostgreSQL database.

Prerequisites

Following are some ideas and/or strategies you may want to know in order to get the most efficiency from this experience:

Python scripting

We are currently using Python 3.8, which is the latest version of Python as of the writing of this article. You are not obligated to use this version, as none of the functions or features used here are brand new. That said, you can download it here for free.

Writing queries

Some knowledge in creation and execution of SQL. In this lesson, we stick to the easiest, least complex, queries, including use of CREATE, SELECT, WHERE, and ORDER BY. For managing your Postgres database installation, whether you run it local or on a server in the cloud, we prefer the free version of dBeaver’s database management tool for efficiency, ease, and testing our SQL.

Naming conventions

Article on the importance of naming conventions teaches why and how you benefit from using naming conventions, including: 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 and columns based on a hierarchical system.

Database connection to Postgres

t_host = "PostgreSQL db host address"
t_port = "5432"
t_dbname = "db name"
t_name_user = "db user name"
t_password = "db 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 computer, use “localhost” as the address, otherwise, an IP address.
  • t_port: “5432” is the default port for postgres 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 PostgreSQL table

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

Example of table creation in Postgres

t_name_tbl = "animals"
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);"

Analysis

  • t_name_tbl: Use a variable to save the name of the table we want to create, in this case “animals”, increasing modularity of these features we will later place into functions.
  • CREATE TABLE: This is the Postgres SQL command for table creation of a new table we are calling “animals”.
  • CREATE UNIQUE INDEX: This command tells Postgres we want the id column to be autoincrementing.

Run Postgres query using Python

Now let’s plan and write the script needed to execute the SQL we built above to create the new Postgres table. This part involves only two additional Python commands:

db_cursor.execute(s)
db_conn.commit()

Analysis

  • db_cursor.execute(s): Put the SQL commands stored in the variable we named “s” into action.
  • db_conn.commit(): If autocommit is turned off, we need to “commit” to inform Postgres that we are “committed” to executing this query.

SQL to duplicate a Postgres table

Syntax to copy a Postgres table

CREATE TABLE products_new AS (SELECT * FROM products);

CREATE TABLE products_new AS (SELECT * FROM products) WITH NO DATA;

Analysis

  • CREATE_TABLE: “products_new” represents the name you want your new – duplicate – table to have. “products” represents the source table to have Postgres copy.
  • WITH NO DATA: Tells Postgres to only copy the structure of the source table; no records.

Query to get Postgres table list

Now to write some SQL to instruct the Postgres database to show us what “public” tables exist.

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 SQL command to retrieve data from a PostgreSQL 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 then use Python’s FOR loop to cycle through all the returned table names.

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

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

Analysis

  • show_tables = db_cursor.fetchall(): The Python psycopg2 fetchall() function retrieves records returned from PostgreSQL. This recordset is stored in the Python list we have named “show_tables”.
  • for t_name_table: Here we assign the “t_name_table” variable to track every iteration of a value stored in the “show_tables” list.
  • print: Returns the name of the table each time through the for loop. Finally, concatenation of “n” 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 off:
    db_conn.commit()

def DupeTable(t_name_source, t_name_destination):
    CREATE TABLE products_new AS (SELECT * FROM products);
    s = ""
    s += "CREATE TABLE"
    s += " " + t_name_destination
    s += " AS ("
    s += "SELECT"
    s += " *"
    s += " FROM " + t_name_source
    s += " );"
    # execute the copy table SQL
    db_cursor.execute(s)
    # if autocommit is off:
    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 = "animals"
    CreateTable(t_name_tbl)
    t_name_source = "animals"
    t_name_destination = "animals_temp"
    DupeTable(t_name_source, t_name_destination)
    t_schema = "public"
    GetTableList(t_schema)

Conclusion

We moved step by step here using Python and Postgres to build an application to copy tables using Python, with use on the psycopg2 library. During this article we also learned various functions including Python lists (arrays) in conjunction with the fetchall function, learned to create a PostgreSQL table, iterate through a table list with Python using the for loop, as well as using the print function to display results. Finally, we provided source code for the entire Python/PostgreSQL app.

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.