我正在嘗試獲取在 Oracle 資料庫上運行的 SQL 查詢的結果。我已經設法使用以下代碼獲取輸出，其中 run_sqlplus 函式運行查詢。
def run_sqlplus(sqlplus_script):
p = subprocess.Popen(['sqlplus','/as sysdba'],stdin=subprocess.PIPE,
stdout=subprocess.PIPE,stderr=subprocess.PIPE)
(stdout,stderr) = p.communicate(sqlplus_script.encode('utf-8'))
stdout_lines = stdout.decode('utf-8').split("\n")
return stdout_lines
def main():
sqlplus_script='select open_mode,protection_mode,switchover_status,database_role from v$database;'
queryResult =run_sqlplus(sqlplus_script)
print(type(queryResult))
for line in queryResult:
print(line)
輸出是：
<class 'list'>
SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 9 11:29:07 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL>
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE TO STANDBY PRIMARY
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
問題是我只需要 2 'SQL>' 之間的輸出，即：
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE TO STANDBY PRIMARY
我怎樣才能得到想要的結果。請建議。謝謝。
uj5u.com熱心網友回復：
為此使用 cx_oracle 驅動程式要容易得多，但如果您堅持使用 sqlplus，請使用 -s（靜默）和 -L 標志使其更好一些。
建議：切換到 cx_Oracle，因為它可以提供更好的控制，并且在行程運行時隱藏您的憑據也更容易。
uj5u.com熱心網友回復：
在 sqlplus 解決它之后添加'-S'。
p = subprocess.Popen(['sqlplus','-S','/as sysdba'],stdin=subprocess.PIPE, stdout=subprocess.PIPE,stderr=subprocess.PIPE)
uj5u.com熱心網友回復：
要給出在 Python 中執行此操作的完整示例，請嘗試以下操作：
import cx_Oracle
import os
import platform
if platform.system() == "Darwin":
cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME") "/Downloads/instantclient_19_8")
elif platform.system() == "Windows":
cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_14")
connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)
# Or, for a remote database, you'll need the SYS password:
# connection = cx_Oracle.connect(user="sys", password="*****", dsn="myhost.example.com/orclpdb1", mode = cx_Oracle.SYSDBA)
with connection.cursor() as cursor:
try:
sql = """select open_mode,protection_mode,switchover_status,database_role from v$database"""
cursor.execute(sql)
# column headings
columns = [col[0] for col in cursor.description]
print(columns)
# rows
for row in cursor:
print(row)
except cx_Oracle.Error as e:
error, = e.args
print(sql)
print('*'.rjust(error.offset 1, ' '))
print(error.message)
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/443329.html