Bring your towel and join one of the fastest growing data communities. Welcome to our second-generation open source orchestration platform, a completely rethought approach to dataflow automation.

Prefect Community

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!

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>

<@U03LH98F8S2>

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'```


Or should I not use Prefect’s sqlalchemy for this?

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

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

for 100's k of rows I would not use inserts

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>

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?

<@U03LH98F8S2>

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.