Postgres Search All Tables For Value

Introduction

How to use Postgres to search all tables for a value. We’ll start by setting up a database connection to Postgres from Python. Next we will cycle through all tables. Finally, for each table, we will search each column in that table, looking for our target string. Here is the 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

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.

List all the Postgres columns

With every table, we will iterate through every column and then search those columns for a value. Once we have narrowed down the recordset, we will return the names of tables and their columns which match the search.

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

On naming conventions shows why and how you may wish to use naming conventions in your Python and Postgres. For example, naming “iage” with an “i” at the beginning of the variable name 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.

Connection to Postgres from Python

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.

Run Postgres SQL in Python

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.

Get list of Postgres tables

Let’s write a query to ask the Postgres database what tables exist in our PostgreSQL database.

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 Postgres.
  • table_schema: This determines whether a table is designated as “public” or other table type, such as system.
  • table_name: The name of the table being viewed.

Show all 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 – Python’s version of an array – we are naming “list_tables”.
  • for t_name_table: This is central to our for loop, assigning the “t_name_table” variable to track each iteration of values in the “list_tables” array.
  • print: Returns the table name each time through the for loop iteration.

Now that we have gone through the process for Postgres to list tables using Python, we’ll write some SQL to list all columns in each of those tables found above.

List all columns per table

s = ""
s += "SELECT"
s += " column_name"
s += " FROM information_schema.columns"
s += " WHERE"
s += " ("
s += " table_schema = '" + t_schema + "'"
s += " AND"
s += " table_name = '" + t_name_table + "'"
s += " )"
s += " ORDER BY column_name;"
db_cursor.execute(s)
list_columns = db_cursor.fetchall()

Analysis

The differentiating factors between listing all tables and listing all columns here are: we are plugging in the table name in our WHERE clause and we are requesting the following from PostgreSQL: Just like for requesting names of all tables from Postgres, here we are requesting names of all columns using “column_name” and “information_schema.columns”.

The final step is to look for our search term within all the columns returned for each table returned.

Search all tables and columns

The following SQL will be much more “normal” because we now have a table name and a column name to plug in and merely use the WHERE clause to try to find the value we are searching our database for.

s = ""
s += "SELECT UNIQUE"
s += " " + t_name_column
s += " FROM " + t_name_table
s += " WHERE"
s += " ("
s += " " + t_name_column + " = '" + t_search_term + "'"
s += " );"
db_cursor.execute(s)
column_w_match = db_cursor.fetchone()
print(t_name_table + " - " + column_w_match[0] + "\n")

Now to build the full application.

Source code to list Postgres tables with Python

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 GetColumnList(t_schema, t_search_term)
    # 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;"
    db_cursor.execute(s)
    list_tables = db_cursor.fetchall()

    # Get the names of the columns in each table
    for t_name_table in list_tables:
        s = ""
        s += "SELECT"
        s += " column_name"
        s += " FROM information_schema.columns"
        s += " WHERE"
        s += " ("
        s += " table_schema = '" + t_schema + "'"
        s += " AND"
        s += " table_name = '" + t_name_table + "'"
        s += " )"
        s += " ORDER BY column_name;"
        db_cursor.execute(s)
        list_columns = db_cursor.fetchall()

        for t_name_column in list_columns:
            s = ""
            s += "SELECT UNIQUE"
            s += " " + t_name_column
            s += " FROM " + t_name_table
            s += " WHERE"
            s += " ("
            s += " " + t_name_column + " = '" + t_search_term + "'"
            s += " )"
            db_cursor.execute(s)
            column_w_match = db_cursor.fetchone()
            print(t_name_table + " - " + column_w_match[0] + "\n")

def Main():
    t_schema = "public"
    t_search_term = "Bonanza"
    GetColumnList(t_schema, t_search_term)

Conclusion

In this tutorial we moved step by step through Postgres to search all tables for a value. Basically, search an entire database, every column in every table, for a search term which in this case is “Bonanza”. For database connectivity between Python and Postgres, we used the psycopg2 library. Finally, we provided examples and commented source code throughout the lesson.

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.