使用Python和psycopg2执行包含DROP DATABASE语句的SQL文件

Question

我想使用Python函数执行一个sql文件。

这个sql文件以一个DROP DATABASE语句开始。
.sql文件的前几行如下：

DROP DATABASE IF EXISTS myDB; 
CREATE DATABASE myDB;

其余部分的.sql文件定义了“myDB”中所有表和视图。 Python代码：

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

当我运行connect()函数时，DROP DATABASE语句出错。 错误信息：

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

我花了很多时间在谷歌上搜索这个错误，但是找不到解决办法。

我还尝试在.sql文件的顶部添加AUTOCOMMIT语句，但没有任何变化。

SET AUTOCOMMIT TO ON;

我知道postgreSQL不允许你删除当前连接的数据库，但我认为这不是问题的原因，因为我在connect()函数中开始连接到template1数据库，并从该连接创建游标对象来打开.sql文件。

是否有其他人遇到过这个错误，有没有办法使用python函数执行.sql文件？

- stevec

---

你可能想要创建和删除模式，而不是数据库。 - Gordon Linoff

@GordonLinoff - 我意识到我可以解决这个问题，但我需要在py代码中创建/删除数据库的原因是因为我正在开发的项目将其设置为必需项。 py代码运行一个游戏应用程序，每个新的游戏实例都会生成一个全新的数据库。 - stevec

在Postgres中删除数据库有点像删除整个服务器。通常，您会想要删除一堆相关的表、函数等，这些都存储在模式中：http://www.postgresql.org/docs/9.4/static/sql-createschema.html。 - Gordon Linoff

Answer 1

以下是一个对我有效的文件，其中每一行都包含SQL查询：

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

- Yankee

Answer 2

您正在读取整个文件并将整个内容作为一个字符串传递给PostgreSQL（正如错误消息所说的那样，“多命令字符串”）。这是您想要做的吗？如果是，它不会起作用。

请尝试这个：

cursor.execute(sqlfile.readline())

或者，使用psql并让它完成工作。

- jjanes

---

谢谢，@jjane。我开始觉得使用以下代码会更容易：subprocess.call('psql -f mydb.sql', shell=True)。 - stevec

这仅适用于文件中的第一行，之后的内容不起作用。 - Shailesh

Answer 3

为了使用CRON部署作为ETL文件的脚本，我们不得不扩展如何调用SQL文件本身。

 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()

这似乎让CRON工作很高兴...

- DPSSpatial