I have to migrate this code snippet to psycopg (version 3), ideally using it through SQLAlchemy 2.0+:

from io import StringIO
import psycopg2

conn = psycopg2.connect(**dbconfig)
curs = conn.cursor()

file_object = StringIO()
for i, feat in enumerate(myarray): # myarray is containing 1e6 features
    file_object.write(f"{feat}n")

file_object.seek(0)


sql = "COPY mytable (attribute) FROM STDIN WITH CSV DELIMITER ',';"
curs.copy_expert(sql, file_object)
...
conn.commit()

I had to use copy_expert because a simple curs.execute in that loop was literally taking days whereas the copy_expert method was only taking about 3 minutes.

This was the help about copy_expert under psycopg2:

Help on built-in function copy_expert:

copy_expert(...) method of psycopg2.extensions.cursor instance
    copy_expert(sql, file, size=8192) -- Submit a user-composed COPY statement.
    `file` must be an open, readable file for COPY FROM or an open, writable
    file for COPY TO. The optional `size` argument, when specified for a COPY
    FROM statement, will be passed to file's read method to control the read
    buffer size.

I want to upgrade this code to the newer psycop package (3.2.1) and ideally sqlalchemy (2.0.34) because I’m facing the following error when trying to use copy_expert:

AttributeError: ‘Cursor’ object has no attribute ‘copy_expert’

To this end, I read trough: https://www.psycopg.org/articles/2020/11/15/psycopg3-copy/
but I’m not sure on how to upgrade my code, my current tries are not working (nothing get written to the database). E.g.:

# Using an SQLAlchemy engine instance:
with engine.connect() as conn:

    file_object = StringIO()
    for i, feat in enumerate(myarray): # myarray is containing 1e6 features
        file_object.write(f"{feat}n")

    with curs.copy(sql) as copy: # not sure where does the cursor object used here come from
        while data := file_object.read():
            copy.write(data)

...
conn.commit()

Also, the write then read operations on the file_object don’t seem to be optimal, perhaps it’s possible to combine them?