Use python and psycopg2 to execute a sql file that contains a DROP DATABASE statement
<p>I am trying to use a python function to execute a .sql file.  </p>

<p>The sql file begins with a <code>DROP DATABASE</code> statement.<br>
The first lines of the .sql file look like this:</p>

<pre><code>DROP DATABASE IF EXISTS myDB; 
CREATE DATABASE myDB;
</code></pre>

<p>The rest of the .sql file defines all the tables and views for 'myDB'</p>

<p><strong>Python Code:</strong></p>

<pre><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
</code></pre>

<p>When I run the <code>connect()</code> function, I get an error on the <code>DROP DATABASE</code> statement.</p>

<p><strong>ERROR:</strong></p>

<pre><code>psycopg2.InternalError: DROP DATABASE cannot be executed from a function or multi-command string
</code></pre>

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

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

<pre><code>SET AUTOCOMMIT TO ON;
</code></pre>

<p>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.</p>

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