Asyncpg: Creating a pandas dataframe from a list of returned records does not set the column names.

[apullin]

This may not be an issue with asyncpg, but I am not sure where else to report it.

I am doing a query against a Redshift db, and the result I get back is a list of Records.
However, when I pass it to pd.DataFrame.from_records, the column names are not set. However, the Records do have named keys.

async def run():
    conn = await asyncpg.connect(database= 'mydb', host='my.host.com', port= '5439', user= 'myuser', password= 'mypass')
    values = await conn.fetch('''SELECT * FROM mytable where something > 100''')
    await conn.close()
    return values

loop = asyncio.get_event_loop()
data = loop.run_until_complete(run())
df = pd.DataFrame.from_records(data)

df.columns

will give:

RangeIndex(start=0, stop=20, step=1)

However, if I look at data[0], I'll get:

<Record itemA=698 timestamp=datetime.datetime(2017, 7, 10, 2, 33, 38, 120000) itemB=Decimal('51.00') itemC=Decimal('17.95') itemD=273>

So the key names are defintely there in the Record. (all values are representative, edited from my actual record result)

I can extract the keys directly and pass them to the DataFrame constructor, but it seems like that is wrong or not a robust solution:

colnames = [key for key in data[0].keys()]
df = pd.DataFrame(data, columns = colnames)

Is this the expected behavior? Is that the best solution?

[1st1]

Yes, pandas doesn't support asyncpg records out of the box. You're on the right track with your solution, but to make it more robust I'd use a PreparedStatement.get_attributes() method, as in:

async def fetch_as_dataframe(con: asyncpg.Connection, query: str, *args):
    stmt = await con.prepare(query)
    columns = [a.name for a in stmt.get_attributes()]
    data = await stmt.fetch()
    return pandas.DataFrame(data, columns=columns)

[mikhaildubov]

Just to save future readers some time, the code above is buggy as it doesn't use the args, here is how you do it

async def fetch_as_dataframe(con: asyncpg.Connection, query: str, *args):
    stmt = await con.prepare(query)
    columns = [a.name for a in stmt.get_attributes()]
    data = await stmt.fetch(*args)
    return pandas.DataFrame(data, columns=columns)