Get column names from PostgreSQL table using Psycopg2

This article is an illustration of how to extract column names from PostgreSQL table using psycopg2 and Python.

Used table for demonstration:

Example 1:

First, we connect the PostgreSQL database using psycopg2.connect()  method, then we create a cursor using cursor() method, after that we use the cursor() then extract the first element from it using slicing.

Python3

import psycopg2

conn = psycopg2.connect(
    database="geeks",
    user='postgres',
    password='pass',
    host='localhost',
    port= '5432'
)

conn.autocommit = True
cursor = conn.cursor()

sql = '''SELECT * FROM products'''


cursor.execute(sql)
column_names = [desc[0] for desc in cursor.description]
for i in column_names:
    print(i)
conn.commit()
conn.close()

Output:

product_no
name
price

Example 2: In the second approach, we execute the following SQL command in the cursor.execute() method.

“select COLUMN_NAME from information_schema.columns where  table_schema = ‘SCHEMA_NAME’ and table_name=’TABLE_NAME'”

Python3

import psycopg2

conn = psycopg2.connect(
    database="geeks",
    user='postgres',
    password='root',
    host='localhost',
    port='5432'
)

conn.autocommit = True


with conn:
    with conn.cursor() as cursor:
        cursor.execute(
            "select COLUMN_NAME from information_schema.columns\
            where table_name='products'")
        column_names = [row[0] for row in cursor]

print("Column names:\n")

for i in column_names:
    print(i)

Output:

Column names:

product_no
name
price