How to import data via alembic (from a CSV file)?

Question

I try to design a database schema and use alembic (and virtualenv if that matters). I made some CSV files with testdata. Currently I copy them into the db on the interactive postgresql shell via

/copy table_name FROM '~/path/to/file.csv' WITH CSV;

I would like to automate this so I don't have to manually copy every table when I grade down and up via alembic. I tried the following:

In my alembic version file in the upgrade() method, below the generation of my tables, I added:

op.execute("copy table_name FROM '~/path/to/file.csv' WITH CSV;", execution_options=None)

But it never finds the file. This is the error:

File "/Users/me/Workspace/project/venv/lib/python3.4/site-packages/SQLAlchemy-0.9.4-py3.4-macosx-10.6-intel.egg/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) could not open file "~/Workspace/project/path/to/file.csv" for reading: No such file or directory
 "copy table_name FROM '~/Workspace/project/path/to/file.csv' WITH CSV;" {}

What am I doing wrong?

Is my problem that I try to run a postgresql command where I can use only sqlalchemy command? If so, how would I do this via sqlalchemy?

I know of the bulk import option in alembic but I would prefer to not re-format everything.

Are there other options to automate the copy operation? Are there better workflows?

Answer 1

Postgres does not run as "you" so the home directory will be different. When entering file paths in postgres, always use the full path to the file. In the case of doing this as part of an alembic upgrade, you should do something like:

import os
dirname = os.path.dirname(__file__)
filename = os.path.join(dirname, 'relative/path/to/file/you/want')
op.execute(f"copy table_name FROM '{file_name}' WITH CSV;", execution_options=None)