1

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

2
  • Any order, wheter correct or "incorrect", can only be guaranteed when you explicitly specify it (using order by or listing column names instead of * in the select list). And why do you care about the order anyway?
    – mustaccio
    Jun 15, 2022 at 11:31
  • @johnwick Did you find an answer to this? I'm having the same issue where I need the columns to be returned in the same order specified in the query.
    – Richard
    Nov 27, 2023 at 16:03

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.