According to the docs, engine.execute autocommits. However, the COPY command does not commit when ran in the code below. When I directly run the exact same queries in the postgres command line it works.
The .csv contains ~3e6 rows and ~25 columns, so I wonder if this has to do with the issue and if there’s a workaround.
Relevant code and logs (sensitive details omitted with <...>):
### py 3.10.8, psycopg2 & sqlalchemy installed via poetry, poetry run jupyter notebook
from sqlalchemy import create_engine
### Set PSQL_PW, PSQL_IP, PSQL_PORT, DB_NAME, headers, file_path
engine = create_engine(f"postgresql://postgres:{PSQL_PW}@{PSQL_IP}:{PSQL_PORT}/{DB_NAME}", echo = True)
TABLE_NAME = "test_table"
engine.execute(text(f"CREATE TABLE {TABLE_NAME} ({headers});"))
# 2022-10-18 22:46:36,834 INFO sqlalchemy.engine.Engine CREATE TABLE test_table (<headers>);
# 2022-10-18 22:46:36,836 INFO sqlalchemy.engine.Engine [cached since 19.43s ago] {}
# 2022-10-18 22:46:36,844 INFO sqlalchemy.engine.Engine COMMIT
# <sqlalchemy.engine.cursor.LegacyCursorResult at 0x21450e592d0>
engine.execute(text(f"COPY {TABLE_NAME} FROM :file_path WITH (FORMAT csv, HEADER);"), file_path = file_path).rowcount
# 2022-10-18 23:05:17,487 INFO sqlalchemy.engine.Engine COPY test_table FROM %(file_path)s WITH (FORMAT csv, HEADER);
# 2022-10-18 23:05:17,488 INFO sqlalchemy.engine.Engine [cached since 1080s ago] {'file_path': '/home/postgres/pgdata/data/<file_name>.csv'}
# 3139538
engine.execute(text(f"SELECT COUNT(*) FROM {TABLE_NAME}")).first()[0]
# 0
>Solution :
SQLAlchemy does not autocommit by default. It will depend on the dialect being used.
For postgresql, the sqlalchemy engine sends a non auto-committable transaction behind the scenes and it will wait for commit command.
For autocommits, you will need to explicitly enable it in your engine or connection.
eg:
engine = create_engine('postgresql://test', isolation_level="AUTOCOMMIT")
documentation: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#transaction-isolation-level