Execute .sql schema in psycopg2 in Python
I can't reply to comments of the selected answer by lack of reputation, so i'll make an answer to help with the COPY
issue.
Depending on the volume of your DB,pg_dump --inserts
outputs INSERT
s instead of COPY
s
You can just use execute
:
with self.connection as cursor:
cursor.execute(open("schema.sql", "r").read())
though you may want to set psycopg2 to autocommit
mode first so you can use the script's own transaction management.
It'd be nice if psycopg2 offered a smarter mode where it read the file in a statement-at-a-time and sent it to the DB, but at present there's no such mode as far as I know. It'd need a fairly solid parser to do it correctly when faced with $$
quoting (and its $delimiter$
variant where the deimiter may be any identifier), standard_conforming_strings
, E''
strings, nested function bodies, etc.
Note that this will not work with:
- anything containing
psql
backslash commands - COPY .. FROM STDIN
- very long input
... and therefore won't work with dumps from pg_dump