[Python] How to correctly use Psycopg3 COPY command? [duplicate]

I have read in several places but I haven't been able to find the solution yet. I was using psycopg2 and its method copy_expert() to read a csv file and load it to a table, but I decided to switch to the newer version of the library psycopg v3.2.9. Reading their docs I was able to do this:

import psycopg
from loguru import logger
import os
from dotenv import load_dotenv
import pandas as pd
from psycopg import sql


load_dotenv()

db_user: str = os.getenv("DB_USER", "")
db_password: str = os.getenv("DB_PASSWORD", "")
db_host: str = os.getenv("DB_HOST", "")
db_port: str = os.getenv("DB_PORT", "")
db_name: str = os.getenv("DB_NAME", "")


conn = psycopg.connect(
host=db_host,
dbname=db_name,
port=db_port,
user=db_user,
password=db_password
)
conn.autocommit = False

file_location = "my_file.csv"

try:
with open(file_location, 'r', encoding='utf-8') as f:
copy_sql = f"""
COPY my_table (float_value, string_value_1, string_value_2, timestamp_value, str_value_index)
FROM STDIN
WITH (FORMAT csv, HEADER true, DELIMITER ',');
"""
with conn.cursor() as cur:
cur.copy(copy_sql, f)
self.conn.commit()
except Exception as e:
logger.error(f"{e = }")
conn.rollback()
raise
finally:
con.close()



A sample from the csv is this:

"float_value","string_value_1","string_value_2","timestamp_value","str_value_index"
"1.0","asdasd","adasdbg","2025-06-06 04:00:06.982","88674ec12bffffff"
"1.0","asfasf","nfgnfggg","2025-06-06 04:00:06.983","88674126e7ffffff"


Executing that code does not raise any error but it finished instantly and it does not saves nothing on the table, the csv has 4 million rows so it takes a little while to copy on the database and it was working before with psycopg2 so I don't know what am I doing wrong.

This is the structure from the table:

CREATE TABLE IF NOT EXISTS my_table (
float_value DOUBLE PRECISION NOT NULL,
string_value_1 TEXT NOT NULL,
string_value_2 TEXT NOT NULL,
timestamp_value TIMESTAMPTZ NOT NULL,
str_value_index TEXT NOT NULL,
created_at_postgre TIMESTAMPTZ NOT NULL DEFAULT NOW(),
expire_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '3 minutes'),
PRIMARY KEY (str_value_index, string_value_1, string_value_2, timestamp_value)
);


Therefore, I hope someone can help me find why my code is not copying the information from my csv to the table even though my code is based on the documentation and worked when I used psycopg2 and also works if I use the same file lo load it with psql (the cli tool).

Thanks.

Edit: if I try this

with open(file_location, 'r', encoding='utf-8') as f:
with cur.copy(
"""
COPY my_table (float_value, string_value_1, string_value_2, timestamp_value, str_value_index)
FROM STDIN
WITH (FORMAT csv, HEADER true, DELIMITER ',');
""",
f
) as copy:
while data := f.read(1024*1024):
copy.write(data)
conn.commit()


I get this error:

File "path/to/file.py", line 90, in <module>
with cur.copy(
^^^^^^^^^
File "path/to/file.py", line 137, in __enter__
return next(self.gen)
^^^^^^^^^^^^^^
File "path/to/file.py", line 260, in copy
self._conn.wait(self._start_copy_gen(statement, params))
File "path/to/file.py", line 414, in wait
return waiting.wait(gen, self.pgconn.socket, interval=interval)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "psycopg_binary/_psycopg/waiting.pyx", line 213, in psycopg_binary._psycopg.wait_c
File "path/.venv/lib/python3.12/site-packages/psycopg/_cursor_base.py", line 392, in _start_copy_gen
pgq.convert(statement, params)
File "path/.venv/lib/python3.12/site-packages/psycopg/_queries.py", line 257, in convert
and len(vars) <= MAX_CACHED_STATEMENT_PARAMS
^^^^^^^^^
TypeError: object of type '_io.TextIOWrapper' has no len()

Continue reading...