Get column name and column type in the same query in the correct order using psycopg2

Question

I have a postgresql database and I am querying it using psycopg2 and I need to get the data in the correct order as in table I have found different ways to get the column names from table and column types of the table with 2 different queries.

cur = con.cursor()

cur.execute(
    "SELECT * FROM first"
)
column_names = [desc[0] for desc in cur.description]
print(column_names)

This gives me a list of column names IN THE CORRECT ORDER but I don't have column types, like this

['Region', 'Country', 'Item_Type', 'Sales_Channel', 'Order_Priority', 'Order_ID', 'Units_Sold', 'Units_Price', 'Unit_Cost', 'Total_Revenue', 'Total_Cost', 'Total_Profit', 'Order_Date', 'Ship_Date']

The second method is to do the following

cur.execute(
    "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'first';"
)

ans = cur.fetchall()
print(ans)

This give me the column names and column types as a list of tuples BUT NOT IN THE CORRECT ORDER

[('Ship_Date', 'date'), ('Unit_Cost', 'numeric'), ('Total_Revenue', 'numeric'), ('Total_Cost', 'numeric'), ('Total_Profit', 'numeric'), ('Order_Date', 'date'), ('Order_ID', 'bigint'), ('Units_Sold', 'integer'), ('Units_Price', 'numeric'), ('Country', 'text'), ('Item_Type', 'text'), ('Sales_Channel', 'text'), ('Order_Priority', 'text'), ('Region', 'text')]

I am aware that I can run these 2 queries separately and then join the results with python to get the desired output but is there a way to do it with just one query

Is there any way to get both column name and column type in the correct order without doing 2 queries?

It would be great if there is a way to get data types from first method as I can get column name, column type and data from just one query

Thanks

Answer 1

def get_query_results_as_list_of_dicts(query):
        """
        runs a query and returns the result as a dict rather than a tuple
        :param query: SQL formatted string
        :return: list of dictionary objects. one for each result returned from the database
        """
        # Execute the query
        cur.execute(query)

        # Fetch all the rows as a list of dictionaries
        list_of_dicts = [dict(row) for row in cur.fetchall()]

        return list_of_dicts

Answer 2

You should use the RealDictCursor when creating the cursor for psycopg2.

import psycopg2
from psycopg2.extras import RealDictCursor

# Connect to the database
db = psycopg2.connect(
    dbname="your_dbname",
    user="your_username",
    password="your_password",
    host="your_host",
    port="your_port"
)

# Create a cursor using RealDictCursor
cursor = db.cursor(cursor_factory=RealDictCursor)

query = f"SELECT * FROM your_table"

# Execute the query
cursor.execute(query)
data = cursor.fetchall()

for row in data:
    print(row['id'])
    print(row['column_name'])

# Close the cursor and connection
cursor.close()
db.close()