Get The Column Names From A PostgreSQL Table with the Psycopg2 Python Adapter

Written by Data Pilot
September 19, 2019
  • PostgreSQL
  • Psycopg2
  • Python

Introduction

If you’re using PostgreSQL to store and retrieve data, there may be times when you need to get the column names for a table that exists in your database. This task can be accomplished in a simple Python script that uses the system’s INFORMATION_SCHEMA views for SQL metadata. This article will show you how to retrieve the column names for a PostgreSQL table with the psycopg2 Python library.

Prerequisites for using the psycopg2 adapter

Before we start working on our Python script, we need to go over a few important prerequisites that need to be in place for this tutorial:

  • Both Python 3 and the PIP package manager need to be installed on the machine that will be making SQL transactions to a PostgreSQL server. Use the pip3 -V and python3 -V commands to find out what versions of PIP3 and Python 3 are installed on your system.

  • You’ll need to install the psycopg2 library using the pip3 package manager. This can be done with the following command:

1
pip3 install psycopg2

Screenshot of Python 3 and PIP3 versions in terminal and installation of psycopg2

Execute the a SQL statement in ‘psql’ to get the column names of a PostgreSQL table

Let’s look at how you can get the column names of a PostgreSQL table using the psql command-line interface. You can use the following command in a terminal window to connect to PostgreSQL:

1
psql objectrocket -h 127.0.0.1 -d some_database

Be sure to replace the username and database name with your own login information. Once you’re connected, you can use the following SQL statement to get the column names:

1
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'some_table';

NOTE: Make sure to replace the some_table string that’s enclosed in single quotes with an actual table name before you execute the SQL statement.

This query should return a table of results that looks like the following:

1
2
3
4
5
6
7
8
some_database=> SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'some_table';
column_name
-------------
col_uuid
col_str
col_int
col_bool
(4 rows)

Screenshot of psql command-line in a terminal getting the column names of a PostgreSQL table

Create a Python script and import the psycopg2 libraries

Now, let’s create a new Python script, making sure that it has the .py file extension. In our script, we’ll import the sql and connect libraries from the psycopg2 package:

1
2
# import the sql and connect libraries for psycopg2
from psycopg2 import sql, connect

Connect to a PostgreSQL database using psycopg2

We’ll need to declare a new Python string object (str) for the global PostgreSQL database name:

1
2
# create a global string for the PostgreSQL db name
db_name = "some_database"

Call the psycopg2 library’s connect() method

We’ll then pass the database name string to the psycopg2 library’s connect() method. When you’re following along with your own script, make sure to replace these sample values with your own username, host, and password:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
try:
    # declare a new PostgreSQL connection object
    conn = connect(
        dbname = db_name,
        user = "objectrocket",
        host = "localhost",
        password = "mypass"
    )

    # print the connection if successful
    print ("psycopg2 connection:", conn)

except Exception as err:
    print ("psycopg2 connect() ERROR:", err)
    conn = None

NOTE: If an exception is thrown while attempting to connect to a PostgreSQL database, the method will print the error and set the connection object to None.

Define a function that gets the column names from a PostgreSQL table

We’ll be using Python’s def keyword to define a function that will execute a SQL statement. This SQL statement will have PostgreSQL return all of the column names for a specific table:

1
2
# define a function that gets the column names from a PostgreSQL table
def get_columns_names(table):

Instantiate a psycopg2 cursor object for the SQL statement

Next, let’s declare an empty list that will contain the column names. We’ll need to concatenate a Python string for the SQL statement that accesses the system’s information schema using the INFORMATION_SCHEMA.COLUMNS views.

We’ll place the following code inside the function’s indentation to accomplish this:

1
2
3
4
5
6
7
8
9
10
11
12
13
    # declare an empty list for the column names
    columns = []

    # declare cursor objects from the connection    
    col_cursor = conn.cursor()

    # concatenate string for query to get column names
    # SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'some_table';
    col_names_str = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE "
    col_names_str += "table_name = '{}';".format( table )

    # print the SQL string
    print ("\ncol_names_str:", col_names_str)

Construct a psycopg2.sql.SQL object from the SQL string

We’ll need to pass the SQL string to the sql.SQL() method so that it returns a psycopg2.sql.SQL object. The code shown below uses Python’s format() function to put the table name into the SQL object:

1
2
3
4
5
6
7
8
    try:
        sql_object = sql.SQL(
            # pass SQL statement to sql.SQL() method
            col_names_str
        ).format(
            # pass the identifier to the Identifier() method
            sql.Identifier( table )
        )

NOTE: It’s best to use the sql.SQL() and sql.Identifier() modules to build the SQL statement for you, instead of just concatenating the string yourself. Doing this can help prevent SQL injection attacks.

Execute the SQL object using the psycopg2 cursor object

The following segment of code passes the psycopg2.sql.SQL object to the cursor’s execute() method. This stores the data inside of the cursor. Then, the cursor’s fetchall() method call is used to have it return a list of the column names:

1
2
3
4
5
6
7
8
        # execute the SQL string to get list with col names in a tuple
        col_cursor.execute( sql_object )

        # get the tuple element from the liast
        col_names = ( col_cursor.fetchall() )

        # print list of tuples with column names
        print ("\ncol_names:", col_names)

Iterate over the list of PostgreSQL column names

The example shown below demonstrates how to use Python’s for keyword to iterate over the list of tuple objects returned by the cursor:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
        # iterate list of tuples and grab first element
        for tup in col_names:

            # append the col name string to the list
            columns += [ tup[0] ]
           
        # close the cursor object to prevent memory leaks
        col_cursor.close()

    except Exception as err:
        print ("get_columns_names ERROR:", err)

    # return the list of column names
    return columns

During each iteration, we append the column names to the list we declared at the beginning of the function; we use the += [] operator to do this. Make sure to return the list of column names at the end of the function using the return keyword.

Pass a PostgreSQL table name to the function call

At this point, we can now call the get_columns_names() by passing a table name to it as a string, and it should return its respective column names. The following code shows how this can be done:

1
2
3
4
5
6
7
8
# if the connection to PostgreSQL is valid
if conn != None:

    # pass a PostgreSQL string for the table name to the function
    columns = get_columns_names( "some_table" )

    print ("columns:", columns)
    print ("columns TYPE:", type(columns))

Conclusion

We’ve completed our Python script– now it’s time to test it out. Save the Python script, and use the python3 command in a terminal or command prompt window to execute it.

Screenshot of a Python 3 executing a SQL statement with psycopg2 to get a PostgreSQL table's column names

There’s no doubt that it’s helpful to know how to execute various PostgreSQL database operations from a Python script. In this tutorial, we showed you how to retrieve the column names from a PostgreSQL table using the psycopg2 adapter in Python. You can use this script as a model to write your own code that performs a similar function.

Just the Code

Throughout this article, we examined the Python code one segment at a time. Shown below is the complete script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import the sql and connect libraries for psycopg2
from psycopg2 import sql, connect

# create a global string for the PostgreSQL db name
db_name = "some_database"

try:
    # declare a new PostgreSQL connection object
    conn = connect(
        dbname = db_name,
        user = "objectrocket",
        host = "localhost",
        password = "mypass"
    )

    # print the connection if successful
    print ("psycopg2 connection:", conn)

except Exception as err:
    print ("psycopg2 connect() ERROR:", err)
    conn = None

# define a function that gets the column names from a PostgreSQL table
def get_columns_names(table):

    # declare an empty list for the column names
    columns = []

    # declare cursor objects from the connection    
    col_cursor = conn.cursor()

    # concatenate string for query to get column names
    # SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'some_table';
    col_names_str = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE "
    col_names_str += "table_name = '{}';".format( table )

    # print the SQL string
    print ("\ncol_names_str:", col_names_str)

    try:
        sql_object = sql.SQL(
            # pass SQL statement to sql.SQL() method
            col_names_str
        ).format(
            # pass the identifier to the Identifier() method
            sql.Identifier( table )
        )

        # execute the SQL string to get list with col names in a tuple
        col_cursor.execute( sql_object )

        # get the tuple element from the liast
        col_names = ( col_cursor.fetchall() )

        # print list of tuples with column names
        print ("\ncol_names:", col_names)

        # iterate list of tuples and grab first element
        for tup in col_names:

            # append the col name string to the list
            columns += [ tup[0] ]

        # close the cursor object to prevent memory leaks
        col_cursor.close()

    except Exception as err:
        print ("get_columns_names ERROR:", err)

    # return the list of column names
    return columns

# if the connection to PostgreSQL is valid
if conn != None:

    # pass a PostgreSQL string for the table name to the function
    columns = get_columns_names( "some_table" )

    print ("columns:", columns)
    print ("columns TYPE:", type(columns))
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

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.