python cx_Oracle连接Oracle数据库查询

代码

import cx_Oracle
import pandas as pd

sql = '''
SELECT * FROM DB_NAME.TABLE_NAME WHERE ROWNUM<6
'''

def queryOracle(sql):
    #连接数据库
    conn = cx_Oracle.connect('用户名/密码@主机ip地址/orcl')
    #创建游标
    curs = conn.cursor()
    #执行查询
    curs.execute (sql)
    #返回查询结果
    data = curs.fetchall()
    #获取字段名
    column_names = [c[0] for c in curs.description]
    #将数据存入DataFrame
    df = pd.DataFrame(data, columns=column_names)
    curs.close()
    conn.close()
    return df

带参数查询

firstName = 'Peter'

sql = '''
SELECT * FROM DB_NAME.TABLE_NAME WHERE FIRSTNAME = :fn
'''

#连接数据库
conn = cx_Oracle.connect('用户名/密码@主机ip地址/orcl')
#创建游标
curs = conn.cursor()
#执行查询
curs.execute (sql, fn=firstName)
#返回查询结果
data = curs.fetchall()
#获取字段名
column_names = [c[0] for c in curs.description]
#将数据存入DataFrame
df = pd.DataFrame(data, columns=column_names)
curs.close()
conn.close()

官方文档地址

https://cx-oracle.readthedocs.io/en/latest/user_guide/introduction.html