cx_Oracle实现查询，修改数据库，以及对SQL语句实现参数化

最新推荐文章于 2023-12-29 09:00:00 发布

原创最新推荐文章于 2023-12-29 09:00:00 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#python #oracle #sql

本文介绍了如何使用Python的cx_Oracle库进行Oracle数据库的查询和修改操作，包括执行SQL语句及实现参数化的示例。

查询语句

Test_oracle = {
    "shengchan": {
        "test_user": {"userName": "xxx", "pwd": "xxxx", "iP": "172.xx.xxx.xxx", "dbSID": "xxx"},
    }
def search(test1,test2):
    """
    查询语句
    :param test1:参数化数据1
    :param test2:参数化数据2
    :return:
    """
    # 定义连接数据库的信息
    userName = Test_oracle["shengchan"]["test_user"]["userName"]
    pwd = Test_oracle["shengchan"]["test_user"]["pwd"]
    iP = Test_oracle["shengchan"]["test_user"]["iP"]
    dbSID = Test_oracle["shengchan"]["test_user"]["dbSID"]
    # 连接数据库
    conn = cx_Oracle.connect(userName + '/' + pwd + '@' + iP + ':xxxx/' + dbSID)
    cur = conn.cursor()  # 获取cursor
    named_params = { 'param1': test1,'param2':test2}
    sql = "SELECT * FROM test_table WHERE test_TIME = :param1 AND TYPE = :param2"
    cur.execute(sql,named_params)  # 执行sql
    data = cur.fetchall()
    return data

修改语句

Test_oracle = {
    "shengchan": {
        "test_user": {"userName": "xxx", "pwd": "xxxx", "iP": "172.xx.xxx.xxx", "dbSID": "xxx"},
    }
def update(test1,test2):
    """
    修改语句
    :param test1:参数化数据1
    :param test2:参数化数据2
    :return:
    """
    # 定义连接数据库的信息
    userName = Test_oracle["shengchan"]["test_user"]["userName"]
    pwd = Test_oracle["shengchan"]["test_user"]["pwd"]
    iP = Test_oracle["shengchan"]["test_user"]["iP"]
    dbSID = Test_oracle["shengchan"]["test_user"]["dbSID"]
    # 连接数据库
    conn = cx_Oracle.connect(userName + '/' + pwd + '@' + iP + ':xxxx/' + dbSID)
    cur = conn.cursor()  # 获取cursor
    named_params = { 'param1': test1,'param2':test2}
    sql = "UPDATE Test_table SET test_data = :param1 WHERE TYPE = :param2"
    cur.execute(sql,named_params)  # 执行sql
    # 必须使用commit(),提交事务，不然数据可能丢失
    conn.commit()
    cur.close()
    conn.close()