从cx_oracle执行sql脚本文件？

Is there a way to execute a sql script file using cx_oracle in python.

有没有办法在python中使用cx_oracle执行sql脚本文件。

I need to execute my create table scripts in sql files.

我需要在sql文件中执行我的create table脚本。

2 个解决方案

#1

PEP-249, which cx_oracle tries to be compliant with, doesn't really have a method like that.

Px-249，cx_oracle试图与之兼容，并没有真正有这样的方法。

However, the process should be pretty straight forward. Pull the contents of the file into a string, split it on the ";" character, and then call .execute on each member of the resulting array. I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file.

但是，这个过程应该非常简单。将文件的内容拉成一个字符串，将其拆分为“;”字符，然后在结果数组的每个成员上调用.execute。我假设“;” character仅用于分隔文件中的oracle SQL语句。

f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')

for sql_command in sql_commands:
    curs.execute(sql_command)

#2

Another option is to use SQL*Plus (Oracle's command line tool) to run the script. You can call this from Python using the subprocess module - there's a good walkthrough here: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/.

另一种选择是使用SQL * Plus（Oracle的命令行工具）来运行脚本。你可以使用子进程模块从Python调用它 - 这里有一个很好的演练：http：//moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/。

For a script like tables.sql (note the deliberate error):

对于像tables.sql这样的脚本（注意故意的错误）：

CREATE TABLE foo ( x INT );

CREATE TABLER bar ( y INT );

You can use a function like the following:

您可以使用以下功能：

from subprocess import Popen, PIPE

def run_sql_script(connstr, filename):
    sqlplus = Popen(['sqlplus','-S', connstr], stdin=PIPE, stdout=PIPE, stderr=PIPE)
    sqlplus.stdin.write('@'+filename)
    return sqlplus.communicate()

connstr is the same connection string used for cx_Oracle. filename is the full path to the script (e.g. 'C:\temp\tables.sql'). The function opens a SQLPlus session (with '-S' to silence its welcome message), then queues "@filename" to send to it - this will tell SQLPlus to run the script.

connstr是用于cx_Oracle的相同连接字符串。 filename是脚本的完整路径（例如'C：\ temp \ tables.sql'）。该函数打开一个SQLPlus会话（使用'-S'来静音其欢迎消息），然后排队“@filename”发送给它 - 这将告诉SQLPlus运行该脚本。

sqlplus.communicate sends the command to stdin, waits for the SQL*Plus session to terminate, then returns (stdout, stderr) as a tuple. Calling this function with tables.sql above will give the following output:

sqlplus.communicate将命令发送到stdin，等待SQL * Plus会话终止，然后返回（stdout，stderr）作为元组。使用上面的tables.sql调用此函数将提供以下输出：

>>> output, error = run_sql_script(connstr, r'C:\temp\tables.sql')
>>> print output

Table created.

CREATE TABLER bar (
       *
ERROR at line 1:
ORA-00901: invalid CREATE command

>>> print error

This will take a little parsing, depending on what you want to return to the rest of your program - you could show the whole output to the user if it's interactive, or scan for the word "ERROR" if you just want to check whether it ran OK.

这将需要一些解析，具体取决于您想要返回到程序的其余部分 - 如果它是交互式的，您可以向用户显示整个输出，或者如果您只想检查它是否可以扫描单词“ERROR”跑好了。

#1