Hey everyone I m attempting to insert hundreds of thousands

Question

Hey everyone I m attempting to insert hundreds of thousands of rows into a postgres db using `prefect sqlalchemy SqlAlchemyConnector` with `execute many insert query seq of parameters ` where `seq of parameters` has all the rows This seems to take an extremely long time a bit of a newbie here and i m wondering if there are any approaches or advice for doing something like this Thanks

redsquare · Answer

I would look at Copy for bulk inserts <https www psycopg org psycopg3 docs basic copy html|https www psycopg org psycopg3 docs basic copy html>

Choenden Kyirong · Answer

< redsquare> using `prefect sqlalchemy SqlAlchemyConnector` do we have access to those functions ```db block=SqlAlchemyConnector load lt name gt conn = db block get client client type= connection cursor = conn cursor ``` I m trying to do this in order to access those functions from `conn cursor ` However i m getting the following error ```AttributeError trans ctx object has no attribute cursor ```

Choenden Kyirong · Answer

Or should I not use Prefect s sqlalchemy for this

redsquare · Answer

Its not part of prefect alchemy yet not sure if they will bring something like that in

redsquare · Answer

more copy vs insert reading for you <https www cybertec postgresql com en postgresql bulk loading huge amounts of data >

redsquare · Answer

for 100 s k of rows I would not use inserts

Choenden Kyirong · Answer

hmmm okok Thanks for the material I also came across this really great post <https hakibenita com fast load data python postgresql create a table in the database>

redsquare · Answer

copy ftw

Choenden Kyirong · Answer

yeah tried to find a way to use a cursor from prefect sqlalchemy doesn t seem available at the moment I guess just using the library itself is the way to go for this

Choenden Kyirong · Answer

< redsquare> actually there s way I was just digging through some documentation and found a way to get access to this the function call to `copy from ` works ```database block = SqlAlchemyConnector load lt name gt with database block get client client type= connection as conn engine = database block get client client type= engine connection = engine raw connection cursor = connection cursor logger = get run logger cursor copy from lt filename gt ``` there may be a slicker way to do it will look into that but for now testing this works