COPY-FROM within python connection.execute: csv into postgres - no error but also no data

Question

I am trying to load data from csv to my postgres database.I am using sqlalchemy (and connection / raw sql data method instead of ORM method). However, I created successfully my tables with the respective fields. When using the copy-from nothing happens: I don't get an error but my table is also empty.

conn = eng.connect()
trans = conn.begin()

try: 
    conn.execute("""CREATE TABLE IF NOT EXISTS table_name(var1 numeric, date date, time time, datetime timestamp primary key,....);""")
    trans.commit()
except:
    trans.rollback()
    raise

try: 
    # File 1 bid
    conn.execute("""COPY table_name FROM '/home/user/csvfile.csv' 
    WITH CSV HEADER DELIMITER as ','""")
    trans.commit()
except:
    trans.rollback()
    raise

there's no error message reported from the Jupyter notebook.

What am I doing wrong?

Furthermore, I do not really understand:

file = "/home/user/csvfile.csv"
  conn.execute("""COPY table_name FROM file 
        WITH CSV HEADER DELIMITER as ','""")

produces an error in "file".

Are database and application in the same server, so that the database also has access to `/home/user/csvfile.csv`? — Jim Jones, Mar 29 '18 at 07:54
Hello Jim, yes, they are both on the same server; I am using AWS. — eternity1, Mar 29 '18 at 17:23

Ilja Everilä · Accepted Answer · 2018-03-29T06:58:40.630

Transactions are not reusable:

>>> trans = conn.begin()
2018-03-29 09:14:33,001 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
>>> trans.commit()
2018-03-29 09:14:35,449 INFO sqlalchemy.engine.base.Engine COMMIT
>>> trans.commit()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/u/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1642, in commit
    raise exc.InvalidRequestError("This transaction is inactive")
sqlalchemy.exc.InvalidRequestError: This transaction is inactive
>>> trans.rollback()
>>>

You must start a new transaction after the 1st one, or perform all operations in the same transaction.

On the other hand this does not explain why your COPY fails. After the previous explicit transaction has ended the connection falls back to autocommit. Put another way an engine or a connection commits if no transaction is in progress. But here's the catch: autocommit is based on detecting data changing operations, which is done by matching the given statement against

AUTOCOMMIT_REGEXP = re.compile(
    r'\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|GRANT|REVOKE|'
    'IMPORT FOREIGN SCHEMA|REFRESH MATERIALIZED VIEW|TRUNCATE)',
    re.I | re.UNICODE)

and as you may notice, COPY is not a part of that regexp. As said before, your best bet would be to start another explicit transaction, or to perform both actions in the same transaction. But if you wish to use autocommit some time in the future with COPY, instruct SQLAlchemy that it should autocommit:

conn.execute(text("COPY ...").execution_options(autocommit=True))

As to the latter error, FROM file does not magically access the Python variable and is a syntax error. Pass the filename as an argument to the query:

copy_stmt = text("COPY table_name FROM :file WITH CSV HEADER")
copy_stmt = copy_stmt.execution_options(autocommit=True)
conn.execute(copy_stmt, {"file": file})

Note that COPY from a file requires privileges that you ~~might~~should not have:

COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

The solution is to use COPY ... FROM STDIN, but to use that you have to use a raw DB-API connection:

file = "/home/user/csvfile.csv"
stmt = "COPY table_name FROM STDIN CSV HEADER"

raw_conn = eng.raw_connection()

# Uses the actual psycopg2 connection as a context manager
# for transaction handling.
with open(file) as f, \
        raw_conn.connection, \
        raw_conn.cursor() as cur:
    cur.copy_expert(stmt, f)

raw_conn.close()

Thank you, Ijla. That was it. Speaking about the latter two methods, autocommit and using raw connection. Going forward, is there a recommended way, or is it case dependent? — eternity1, Mar 29 '18 at 17:23

score -1 · Answer 2 · answered Mar 29 '18 at 02:10

-1

Try adding ";" to your copy command. ... delimiter ',' CSV ;" :-)

answered Mar 29 '18 at 02:10

Slumdog

470
2
4

thanks Slumdog, I added, but it doesn't work: Ilja's answer was the solution :) – eternity1 Mar 29 '18 at 17:21

COPY-FROM within python connection.execute: csv into postgres - no error but also no data

2 Answers2