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

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:

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:

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:

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:

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:

Connect to a PostgreSQL database using psycopg2

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

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:

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:

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:

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:

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:

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:

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:

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.

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: