在python中使用cx_Oracle使用PL/SQL和DML/DDL解析SQL文件 [英] Parse SQL file with PL/SQL and DML/DDL using cx_Oracle in python
问题描述
我有一个要使用cx_Oracle python库在oracle中解析和执行的SQL文件. SQL文件包含经典DML/DDL和PL/SQL，例如.它看起来像这样:
I have an SQL file which I want to parse and execute in oracle using cx_Oracle python library. The SQL file contains both classic DML/DDL and PL/SQL, eg. it can look like this:
create.sql:
-- This is some ; malicious comment
CREATE TABLE FOO(id numeric);
BEGIN
INSERT INTO FOO VALUES(1);
INSERT INTO FOO VALUES(2);
INSERT INTO FOO VALUES(3);
END;
/
CREATE TABLE BAR(id numeric);
如果我在SQLDeveloper或SQL * Plus中使用此文件，它将分为3个查询并执行.
if I use this file in SQLDeveloper or SQL*Plus, it will be split into 3 queries and executed.
但是，cx_Oracle.connect(...).cursor().execute(...)一次只能进行一个查询，而不能查询整个文件.我不能简单地使用string.split(';')拆分字符串(如此处建议执行来自cx_oracle?的sql脚本文件?)，因为注释将被拆分(并且将导致错误)，并且PL/SQL块将不会作为单个命令执行，从而导致错误.
However, cx_Oracle.connect(...).cursor().execute(...) can take only ONE query at a time, not an entire file. I cannot simply split the string using string.split(';') (as suggested here execute a sql script file from cx_oracle? ), because both the comment will be split (and will cause an error) and the PL/SQL block will not be executed as single command, thus causing an error.
在Oracle论坛上( https://forums.oracle.com/forums/thread.jspa?threadID = 841025 )，我发现cx_Oracle本身不支持解析整个文件.我的问题是-是否有工具可以帮我做到这一点?例如.我可以调用一个python库将文件拆分为查询吗?
On the Oracle forum ( https://forums.oracle.com/forums/thread.jspa?threadID=841025 ) I've found that cx_Oracle itself does not support such thing as parse entire file. My question is -- is there a tool to do this for me? Eg. a python library I can call to split my file into queries?
最好的解决方案似乎是直接使用SQL * Plus.我使用了以下代码:
The best solutions seems to use SQL*Plus directly. I've used this code:
# open the file
f = open(file_path, 'r')
data = f.read()
f.close()
# add EXIT at the end so that SQL*Plus ends (there is no --no-interactive :(
data = "%s\n\nEXIT" % data
# write result to a temp file (required, SQL*Plus takes a file name argument)
f = open('tmp.file', 'w')
f.write(data)
f.close()
# execute SQL*Plus
output = subprocess.check_output(['sqlplus', '%s/%s@%s' % (db_user, db_password, db_address), '@', 'tmp.file'])
# if an error was found in the result, raise an Exception
if output.find('ERROR at line') != -1:
raise Exception('%s\n\nStack:%s' % ('ERROR found in SQLPlus result', output))
推荐答案
可以同时执行多个语句，但这是半hacky.您需要包装语句并一次执行一个语句.
It's possible to execute multiple statements at the same time but it's semi-hacky. You need to wrap your statements and execute them one at a time.
>>> import cx_Oracle
>>>
>>> a = cx_Oracle.connect('schema/pw@db')
>>> curs = a.cursor()
>>> SQL = (("""create table tmp_test ( a date )"""),
... ("""insert into tmp_test values ( sysdate )""")
... )
>>> for i in SQL:
... print i
...
create table tmp_test ( a date )
insert into tmp_test values ( sysdate )
>>> for i in SQL:
... curs.execute(i)
...
>>> a.commit()
>>>
您已经注意到，这不能解决分号问题，对此没有一个简单的答案.在我看来，您有3种选择:
As you've noted this doesn't solve the semi-colon problem, for which there is no easy answer. As I see it you have 3 options:
-
编写一个过于复杂的解析器，我认为这根本不是一个好选择.
Write an over-complicated parser, which I don't think is a good option at all.
请勿从Python执行SQL脚本；在两个单独的SQL脚本中都有代码，因此解析很容易，可以在一个单独的Python文件中，可以在您的Python代码中嵌入，也可以在数据库中的过程中进行……等等.这可能是我的首选.
Do not execute SQL scripts from Python; have the code in either separate SQL scripts so the parsing is easy, in a separate Python file, embedded in your Python code, in a procedure in the database... etc. This is probably my preferred option.
使用subprocess并以这种方式调用脚本.这是最简单，最快的选项，但根本不使用cx_Oracle.
Use subprocess and call the script that way. This is the simplest and quickest option but doesn't use cx_Oracle at all.
>>> import subprocess
>>> cmdline = ['sqlplus','schema/pw@db','@','tmp_test.sql']
>>> subprocess.call(cmdline)
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 13 09:40:41 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL>
SQL> CREATE TABLE FOO(id number);
Table created.
SQL>
SQL> BEGIN
2 INSERT INTO FOO VALUES(1);
3 INSERT INTO FOO VALUES(2);
4 INSERT INTO FOO VALUES(3);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> CREATE TABLE BAR(id number);
Table created.
SQL>
SQL> quit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
0
>>>
这篇关于在python中使用cx_Oracle使用PL/SQL和DML/DDL解析SQL文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
