Get column names from PostgreSQL table using Psycopg2
Last Updated :
14 Sep, 2021
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 =
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
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...