All geek questions in one place

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

I am trying to create a database schema and use alembic (and virtualenv, if that matters). I made some CSV files with testdata. I am currently copying them to 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 need to manually copy each table when I evaluate it down and up through alembic. I tried the following:

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

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

But he never finds the file. This is mistake:

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?

I am trying to run the postgresql command, where can I use only the sqlalchemy command? If so, how can I do this through sqlalchemy?

alembic, .

? ?

+4
1

Postgres "", . postgres . , alembic, - :

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)
0

Source: https://habr.com/ru/post/1541976/

More articles:


All Articles