47

I have a PostgreSQL schema stored in .sql file. It looks something like:

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    facebook_id TEXT NOT NULL,
    name TEXT NOT NULL,
    access_token TEXT,
    created INTEGER NOT NULL
);

How shall I run this schema after connecting to the database?

My existing Python code works for SQLite databases:

# Create database connection
self.connection = sqlite3.connect("example.db")

# Run database schema
with self.connection as cursor:
    cursor.executescript(open("schema.sql", "r").read())

But the psycopg2 doesn't have an executescript method on the cursor. So, how can I achieve this?

Brian Burns
  • 20,575
  • 8
  • 83
  • 77
linkyndy
  • 17,038
  • 20
  • 114
  • 194

2 Answers2

84

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

Septatrix
  • 195
  • 1
  • 10
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Craig, what does psql do when fed a multi-statement string? Has it got its own parser to detect when a ; is a statement delimiter or just part of a string/comment? – piro Jun 29 '13 at 11:20
  • @piro: yes, `psql` can parse complete SQL files using the `-f` switch. From the output of `psql --help`: `-f, --file=FILENAME execute commands from file, then exit`. – András Aszódi Jul 22 '14 at 05:50
  • Isn't `open("schema.sql", "r").read()` a memory leak? What if the file is 1GB big dump? – Matt3o12 Aug 17 '14 at 11:40
  • 1
    @Matt3o12 [It's not a memory leak; I think you need to look up what that actually means](http://en.wikipedia.org/wiki/Memory_leak). It *might* use tons of RAM and exhaust available memory, yes. This was a toy example, and of course you wouldn't do something this simplistic with a big script. You'd use `psql`, parse and split the input yourself for feeding to `psycopg2`, etc. – Craig Ringer Aug 17 '14 at 13:17
  • @CraigRinger Thanks for the link but I know that it isn't a memory leak. I just don't know how to call it (if you do know, please let me know). – Matt3o12 Aug 18 '14 at 06:10
  • 1
    @Matt3o12 No specific name as far as I know. I'd just say "that code may exhaust RAM for large inputs, as it loads the whole input into RAM instead of streaming it or processing it progressively". *Memory leak* is a very specific term, and people often abuse the term so it's a tad frustrating ;-) – Craig Ringer Aug 18 '14 at 07:52
  • This isn't working for me when I do it on a certain kind of script. I have one with a "COPY ... FROM stdin;" statement and then the copy dump afterwards. Works with `psql` but not with this method. – sudo Aug 10 '15 at 18:59
  • 1
    @sudo COPY requires special handling and cannot just be sent as a plain query. psycopg2 has copy support but you must extract the copy query and data and handle it separately. – Craig Ringer Aug 10 '15 at 23:07
  • Is there a way to continue executing statements if one has a db error? Maybe just list the error like a try statement but keep going? – Mysteri0n Apr 11 '16 at 00:48
  • @Mysteri0n Use subtransactions. See the PostgreSQL manual for "savepoint". I don't know of psycopg2 has any kind of integration for transparently integrating subtransactions into exception handling in Python. – Craig Ringer Apr 11 '16 at 05:01
  • @Tammy Your proposed edit would be totally wrong if SQL was split across multiple lines. The *right* way really is a minimal lexer/parser that can find statement boundaries - correctly handling sql standard identifier and literal quoting, `E''` strings and `$$` quoting. In the absence of that, throwing the whole thing at the postgres backend works. If your schema is too big for this it likely has data that should be loaded with COPY anyway. – Craig Ringer Aug 28 '17 at 12:10
  • @CraigRinger its throwing error syntax error at or near "\" LINE 1890: \. – Sony Khan Jan 18 '19 at 13:31
  • @SonyKhan Please post a *new question* with follow-up questions, including a complete and reproducible example and all relevant version details etc. – Craig Ringer Jan 21 '19 at 04:07
10

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 INSERTs instead of COPYs

Lisael
  • 169
  • 1
  • 5