Use python and psycopg2 to execute a sql file that contains a DROP DATABASE statement

Question

I am trying to use a python function to execute a .sql file.

The sql file begins with a DROP DATABASE statement.
The first lines of the .sql file look like this:

DROP DATABASE IF EXISTS myDB; 
CREATE DATABASE myDB;

The rest of the .sql file defines all the tables and views for 'myDB'

Python Code:

def connect():
    conn = psycopg2.connect(dbname='template1', user='user01')
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    cursor = conn.cursor()

    sqlfile = open('/path/to/myDB-schema.sql', 'r')
    cursor.execute(sqlfile.read())

    db = psycopg2.connect(dbname='myDB', user='user01')
    cursor = db.cursor()
    return db,cursor

When I run the connect() function, I get an error on the DROP DATABASE statement.

ERROR:

psycopg2.InternalError: DROP DATABASE cannot be executed from a function or multi-command string

I spent a lot of time googling this error, and I can't find a solution.

I also tried adding an AUTOCOMMIT statement to the top of the .sql file, but it didn't change anything.

SET AUTOCOMMIT TO ON;

I am aware that postgreSQL doesn't allow you to drop a database that you are currently connected to, but I didn't think that was the problem here because I begin the connect() function by connecting to the template1 database, and from that connection create the cursor object which opens the .sql file.

Has anyone else run into this error, is there any way to to execute the .sql file using a python function?

You probably want to create and drop schemas, not databases. — Gordon Linoff
– Gordon Linoff, Commented Apr 5, 2016 at 3:05
@GordonLinoff - I realize that I can work around this problem, the reason I need to create/drop the database in the py code, is because the project i'm working on sets it as a requirement. the py code runs a game app, and each new instance of the game will generate a new completely new database. — stevec
– stevec, Commented Apr 5, 2016 at 17:06
. . Deleting a database in Postgres is kind of like deleting the entire server. Typically, you would want to delete a bunch of related tables, functions, and the like, which are stored in schema: postgresql.org/docs/9.4/static/sql-createschema.html. — Gordon Linoff
– Gordon Linoff, Commented Apr 6, 2016 at 2:16

Shailesh · Accepted Answer · 2017-05-27 07:48:49Z

9

This worked for me for a file consisting of SQL Queries in each line:

sql_file = open('file.sql','r')
cursor.execute(sql_file.read())

answered May 27, 2017 at 7:48

Shailesh

2,2964 gold badges31 silver badges53 bronze badges

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

Comments

jjanes · Accepted Answer · 2016-04-05 03:35:25Z

0

You are reading in the entire file and passing the whole thing to PostgreSQL as one string (as the error message says, "multi-command string". Is that what you are intending to do? If so, it isn't going to work.

Try this:

cursor.execute(sqlfile.readline())

Or, shell out to psql and let it do the work.

answered Apr 5, 2016 at 3:35

jjanes

45k5 gold badges39 silver badges48 bronze badges

2 Comments

stevec Over a year ago

thanks, @jjane I'm begining to think it would be easier to just use: subprocess.call('psql -f mydb.sql', shell=True)

Shailesh Over a year ago

This only works for the first line in the file, and nothing after that.

Inactivated Account · Accepted Answer · 2020-03-02 23:14:19Z

0

In order to deploy scripts using CRON that serve as ETL files that use .SQL, we had to expand how we call the SQL file itself.

 sql_file = os.path.join(os.path.dirname(__file__), "../sql/ccd_parcels.sql")
 sqlcurr = open(sql_file, mode='r').read()
 curDest.execute(sqlcurr)
 connDest.commit()

This seemed to please the CRON job...

answered Mar 2, 2020 at 23:14

Inactivated Account

7953 gold badges14 silver badges32 bronze badges

Collectives™ on Stack Overflow

Use python and psycopg2 to execute a sql file that contains a DROP DATABASE statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related