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
andpython3 -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 thepip3
package manager. This can be done with the following command:
1 | pip3 install 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) |
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.
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)) |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started