使用python和psycopg2执行包含DROP DATABASE语句的sql文件 [英] Use python and psycopg2 to execute a sql file that contains a DROP DATABASE statement

问题描述

我正在尝试使用python函数执行.sql文件。

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

该sql文件以 DROP DATABASE 语句开头。

.sql文件如下所示：

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;

.sql文件的其余部分定义了 myDB的所有表和视图

The rest of the .sql file defines all the tables and views for '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上出现错误语句。

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

错误：

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

执行DROP DATABASE

我花了很多时间搜索该错误，但我无法

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

我也尝试在.sql文件的顶部添加一条AUTOCOMMIT语句，但是它没有任何改变。

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

SET AUTOCOMMIT TO ON;

我知道postgreSQL不允许您删除当前连接的数据库，但是我认为这不是问题所在，因为我通过连接到template1数据库开始了connect（）函数，并从该连接创建了打开.sql文件的游标对象。

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.

是否还有其他人遇到此错误，是否有任何方法可以使用python函数执行.sql文件？

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

推荐答案

这对我有用，适用于每行包含SQL查询的文件：

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

这篇关于使用python和psycopg2执行包含DROP DATABASE语句的sql文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！