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="Lazyroar", user='postgres', password='pass', host='localhost', port= '5432') conn.autocommit = Truecursor = 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="Lazyroar", 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
