Open In App

Get column names from PostgreSQL table using Psycopg2

Last Updated : 14 Sep, 2021
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


Similar Reads

Do loop in Postgresql Using Psycopg2 Python
In this article, we use psycopg2 to loop through all data points using psycopg2 function in Python. We will first connect our PostgreSQL database using psycopg2.connect method, and pass the connection parameters such as the host, database, user, and password. Then we will create a cursor using the conn.cursor method. The cur.execute method, passes
4 min read
Insert Python Dictionary in PostgreSQL using Psycopg2
In this article, we are going to see how to insert a Python dictionary in PostgreSQL using Psycopg2. We can insert a python dictionary by taking the dictionary keys as column names and values as dictionary values. We import the Psycopg2 package and form a connection to the PostgreSQL database using psycopg2.connect() method. First, let's create a t
2 min read
Python PostgreSQL Connection Pooling Using Psycopg2
In this article, We will cover the basics of connection pooling using connection pooling in Python applications, and provide step-by-step instructions on how to implement connection pooling using Psycopg2. Whether you are building a small-scale application or a large-scale enterprise application, understanding how to implement connection pooling wi
6 min read
Python | Getting started with psycopg2-PostGreSQL
PostgreSQL is a powerful, open source object-relational database system. PostgreSQL runs on all major operating systems. PostgreSQL follows ACID property of DataBase system and has the support of triggers, updatable views and materialized views, foreign keys. To connect PostgreSQL we use psycopg2 . It is the best and most friendly Database adapter
1 min read
Python Psycopg2 - Concatenate columns to new column
In this article, we are going to see how to concatenate multiple columns of a table in a PostgreSQL database into one column. To concatenate two or more columns into one, PostgreSQL provides us with the concat() function. Table for demonstration: In the below code, first, a connection is formed to the PostgreSQL database 'geeks' by using the connec
2 min read
Get psycopg2 count(*) number of results
In this article, we are going to see how to get psycopg2 count(*) number of results. psycopg2 count(*) returns the number of rows from a database table holding some specific conditions. If no condition is given then it returns the total number of tuples present in the relation. Syntax: SELECT COUNT(*) FROM table_name; # to return total no. of rows
2 min read
Get column names from CSV using Python
CSV stands for Comma Separated Values and CSV files are essentially text files which are used to store data in a tabular fashion using commas (,) as delimiters. CSV is a file format and all the files of this format are stored with a .csv extension. It is a very popular and extensively used format for storing the data in a structured form. CSV files
3 min read
How to get column and row names in DataFrame?
While analyzing the real datasets which are often very huge in size, we might need to get the rows or index names and columns names in order to perform certain operations. Note: For downloading the nba dataset used in the below examples Click Here Getting row names in Pandas dataframe First, let's create a simple dataframe with nba.csv C/C++ Code #
3 min read
How to get column names from SQLAlchemy?
In this article, we will discuss how to get column names using SQLAlchemy in Python. SQLAlchemy is an open-source SQL toolkit and object-relational mapper for the Python programming language released under the MIT License. It gives full power and flexibility of SQL to an application. To follow along with this article, we need to have sqlalchemy and
3 min read
Python SQLAlchemy - Get column names dynamically
In this article, we are going to see how to dynamically get the column names of a table in SQLAlchemy using Python. Used database for demonstration:[caption width="800"]Student table[/caption] So, our student table has 5 columns namely sno, name, dob, class, and section, and our task is to fetch all these column names in our Python code. First of a
2 min read
Practice Tags :