I would like to convert a psycopg2 DictRow
query to a pandas dataframe, but pandas keeps complaining:
curs = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
curs.execute("SELECT * FROM mytable")
data = curs.fetchall()
print type(data)
print pd.DataFrame(list(data))
However, I always get an error although I specifically passed a list
???
<type 'list'>
TypeError: Expected list, got DictRow
The result is the same if I do pd.DataFrame(data)
Could someone please help me make this work?
It would also be nice if the column names of the dataframe worked (i.e. extract DictRow
and pass them to the dataframe).
Update:
Since I need to process the data, I would like to use the data from the psycopg2 query as is and not the pandas approach, e.g. read_sql_query
.
Best Answer
Hmm, I eventually found this hacky solution:
The
copy()
function of theDictRow
class will return an actual dictionary. With the list comprehension I create a list of (identical) dictionaries, that Pandas will happily accept.I am still puzzled why
list(data)
produced aTypeError
. Maybe someone can still enlighten me.