Execute .sql schema in psycopg2 in Python

Question

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?

mavroprovato · Accepted Answer · 2025-12-09 22:32:44Z

91

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

edited Dec 9 at 22:32

mavroprovato

8,4226 gold badges40 silver badges54 bronze badges

answered Jun 23, 2013 at 14:20

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

piro Over a year ago

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?

András Aszódi Over a year ago

@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.

Craig Ringer Over a year ago

@Matt3o12 It's not a memory leak; I think you need to look up what that actually means. 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 Over a year ago

@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 Over a year ago

@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.

|

Lisael · Accepted Answer · 2016-02-11 14:22:18Z

11

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

answered Feb 11, 2016 at 14:22

Lisael

1791 silver badge5 bronze badges

Collectives™ on Stack Overflow

Execute .sql schema in psycopg2 in Python

2 Answers 2

15 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related