Read PostgreSQL tables with asyncpg

Question

I know how to read PostgreSQL tables in remote server with psycopg2, sqlalchemy, dask but I am not satisfied with processing time to read the tables and started researching faster alternatives and I found asyncpg as 7x more faster than all but documentation for asyncpg is very poor compared to above referred libraries which are plenty of examples over there.

My question is: how to read PostgreSQL tables efficiently?

I have tried as below:

import asyncio
import asyncpg
import pandas as pd

from sshtunnel import SSHTunnelForwarder #Allow connection with SSH like PuttY connection
from sshtunnel import SSHTunnelForwarder, create_logger #Allow to follow the processes running

SSHTunnelForwarder(('IP_detail', Port_number),
        ssh_private_key=r'path_to_the_ssh_key_in_my_computer',

        ssh_username="username",
        #ssh_password="password", 
        remote_bind_address=('localhost', port_number),
        local_bind_address=('localhost', port_number),
        logger=create_logger(loglevel=1) #Makes processes being ran displayed
                           )

conn = await asyncpg.connect(user='username', password='password',
                                 database='database_name', host='127.0.0.1', port='port')


values = await conn.fetch('''SELECT * FROM table_name''')

values=pd.DataFrame(values)
values

With above code I get the PostgreSQL table all rows values for every columns but doesn't show column names and it shows columns numbering instead of their proper names. How to correct this?

Ashwini Kumar · Accepted Answer · 2020-03-20 15:52:42Z

1

Use dict(values) to see key-value pair of record and payload

answered Mar 20, 2020 at 15:52

Ashwini Kumar

8510 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MGB.py Over a year ago

Sorry, I did not understand your tip. Could you be more detailed?

hellycopterinjuneer · Accepted Answer · 2022-11-03 22:24:06Z

0

First, extract your column names:

columns = [c.name for c in values.get_attributes()]

Then, create your dataframe:

values = pd.DataFrame(values, columns=columns)

See https://github.com/MagicStack/asyncpg/issues/173#issuecomment-538055841

answered Nov 3, 2022 at 22:24

hellycopterinjuneer

743 bronze badges

Comments

rrobby86 · Accepted Answer · 2023-05-02 12:11:43Z

0

The link in hellycopterinjuneer's answer is correct, but the answer does not indicate that is necessary to create a prepared statement. I report here the full code from the link for convenience.

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

edited May 2, 2023 at 12:11

answered May 2, 2023 at 12:02

rrobby86

1,5041 gold badge10 silver badges17 bronze badges

Comments

Tema Torg · Accepted Answer · 2024-08-21 12:54:53Z

0

It works for me in my FastApi application without get_attributes():

values = await app.state.db.fetch("SELECT * FROM ... ")
df = DataFrame([dict(row) for row in data])

answered Aug 21, 2024 at 12:54

Tema Torg

416 bronze badges

Collectives™ on Stack Overflow

Read PostgreSQL tables with asyncpg

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related