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="Beginner", 
    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="Beginner", 
    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

Tags:

#Python PostgreSQL #Python Pyscopg2 #Python #python

PostgreSQL - Create table using Python

Get column names from PostgreSQL table using Psycopg2

Python3

Python3

Contact Us