9

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?

stevec
  • 155
  • 1
  • 3
  • 10
  • 1
    You probably want to create and drop schemas, not databases. – Gordon Linoff Apr 05 '16 at 03: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 Apr 05 '16 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: http://www.postgresql.org/docs/9.4/static/sql-createschema.html. – Gordon Linoff Apr 06 '16 at 02:16

3 Answers3

7

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())
Shailesh
  • 2,116
  • 4
  • 28
  • 48
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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • 2
    thanks, @jjane I'm begining to think it would be easier to just use: `subprocess.call('psql -f mydb.sql', shell=True)` – stevec Apr 05 '16 at 20:49
  • This only works for the first line in the file, and nothing after that. – Shailesh May 27 '17 at 07:46
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...

DPSSpatial
  • 767
  • 3
  • 11
  • 31