Open In App

Get column names from PostgreSQL table using Psycopg2

Improve
Improve
Like Article
Like
Save
Share
Report

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


Last Updated : 14 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads