python cx_oracle

转载

mb5ff982de32716 2021-08-26 18:26:00

文章标签 oracle sql python sed sql语句 文章分类 代码人生

cx_oracel

　　基于python语言连接ORACLE，执行SQL的第三方库

架构

python cx_oracle_sql

python程序调用cx_oracle函数。x_Oracle内部动态加载Oracle Client库来访问Oracle数据库。数据库可以在与Python相同的机器上，也可以是远程的。

特性简介

很容易从PYPI安装
支持多个oracle客户端和数据库版本
执行SQL和PL/SQL语句
扩展的oracle数据类型支持，包括大型对象（）和sql对象绑定
连接管理，包括连接池管理
oracle数据库的高可用性
......

简单开始

cx_connection.py

1 import cx_oracle
2 
3 #create connection
4 "username, password, ip/servicename"
5 ocr_connect = cx_oracle.connect('ocr_date_2021', 'Pwd_ocr_2021, 168.63.0.5:1521/gbk)

 1 from cx_connection import ocr_connect
 2 
 3 #obtain a cursor
 4 cursor = ocr_connect.cursor()
 5 
 6 #data from binding
 7 user_id = '%12345%'
 8 
 9 #execute the query
10 sql = """select name, birthday from  user where user_id = :user_id"""
11 cursor.execute(sql, user_id = user_id)
12 
13 #loop over the result set
14 for row in cursor:
15     print(row)

执行结果如下：

python cx_oracle_sql_02

　　连接数据库，创建连接：

用户名：username
密码： password
连接串：hostname/servicename
游标对象允许SQL执行以及获取执行结果
*数据值data values 可以便是绑定在需要执行的SQL语句中格式“：var”示例中的例子为“：user_id”-->(这样不但可以避免sql注入安全风险，而且绑定对于性能和可伸缩也很重要)
游标允许迭代和显示

使用连接池创建连接

 1 # Create the session pool
 2 pool = cx_Oracle.SessionPool(user="hr", password=userpwd,
 3                              dsn="dbhost.example.com/orclpdb1", min=2,
 4                              max=5, increment=1, encoding="UTF-8")
 5 
 6 # Acquire a connection from the pool
 7 connection = pool.acquire()
 8 
 9 # Use the pooled connection
10 cursor = connection.cursor()
11 for result in cursor.execute("select * from mytab"):
12     print(result)
13 
14 # Release the connection to the pool
15 pool.release(connection)
16 
17 # Close the pool
18 pool.close()

测试套优化示例

  1 #------------------------------------------------------------------------------
  2 # Copyright (c) 2017, 2020, Oracle and/or its affiliates. All rights reserved.
  3 #------------------------------------------------------------------------------
  4 
  5 #------------------------------------------------------------------------------
  6 # Sets the environment used by the sample scripts. Production applications
  7 # should consider using External Authentication to avoid hard coded
  8 # credentials.
  9 #
 10 # You can set values in environment variables to bypass the sample requesting
 11 # the information it requires.
 12 #
 13 #     CX_ORACLE_SAMPLES_MAIN_USER: user used for most samples
 14 #     CX_ORACLE_SAMPLES_MAIN_PASSWORD: password of user used for most samples
 15 #     CX_ORACLE_SAMPLES_EDITION_USER: user for editioning
 16 #     CX_ORACLE_SAMPLES_EDITION_PASSWORD: password of user for editioning
 17 #     CX_ORACLE_SAMPLES_EDITION_NAME: name of edition for editioning
 18 #     CX_ORACLE_SAMPLES_CONNECT_STRING: connect string
 19 #     CX_ORACLE_SAMPLES_DRCP_CONNECT_STRING: DRCP connect string
 20 #     CX_ORACLE_SAMPLES_ADMIN_USER: admin user for setting up samples
 21 #     CX_ORACLE_SAMPLES_ADMIN_PASSWORD: admin password for setting up samples
 22 #
 23 # CX_ORACLE_SAMPLES_CONNECT_STRING can be set to an Easy Connect string, or a
 24 # Net Service Name from a tnsnames.ora file or external naming service,
 25 # or it can be the name of a local Oracle database instance.
 26 #
 27 # If using Instant Client, then an Easy Connect string is generally
 28 # appropriate. The syntax is:
 29 #
 30 #   [//]host_name[:port][/service_name][:server_type][/instance_name]
 31 #
 32 # Commonly just the host_name and service_name are needed
 33 # e.g. "localhost/orclpdb1" or "localhost/XEPDB1"
 34 #
 35 # If using a tnsnames.ora file, the file can be in a default
 36 # location such as $ORACLE_HOME/network/admin/tnsnames.ora or
 37 # /etc/tnsnames.ora.  Alternatively set the TNS_ADMIN environment
 38 # variable and put the file in $TNS_ADMIN/tnsnames.ora.
 39 #
 40 # The administrative user for cloud databases is ADMIN and the administrative
 41 # user for on premises databases is SYSTEM.
 42 #------------------------------------------------------------------------------
 43 
 44 import getpass
 45 import os
 46 import sys
 47 
 48 # default values
 49 DEFAULT_MAIN_USER = "pythondemo"
 50 DEFAULT_EDITION_USER = "pythoneditions"
 51 DEFAULT_EDITION_NAME = "python_e1"
 52 DEFAULT_CONNECT_STRING = "localhost/orclpdb1"
 53 DEFAULT_DRCP_CONNECT_STRING = "localhost/orclpdb1:pooled"
 54 
 55 # dictionary containing all parameters; these are acquired as needed by the
 56 # methods below (which should be used instead of consulting this dictionary
 57 # directly) and then stored so that a value is not requested more than once
 58 PARAMETERS = {}
 59 
 60 def get_value(name, label, default_value=""):
 61     value = PARAMETERS.get(name)
 62     if value is not None:
 63         return value
 64     env_name = "CX_ORACLE_SAMPLES_" + name
 65     value = os.environ.get(env_name)
 66     if value is None:
 67         if default_value:
 68             label += " [%s]" % default_value
 69         label += ": "
 70         if default_value:
 71             value = input(label).strip()
 72         else:
 73             value = getpass.getpass(label)
 74         if not value:
 75             value = default_value
 76     PARAMETERS[name] = value
 77     return value
 78 
 79 def get_main_user():
 80     return get_value("MAIN_USER", "Main User Name", DEFAULT_MAIN_USER)
 81 
 82 def get_main_password():
 83     return get_value("MAIN_PASSWORD", "Password for %s" % get_main_user())
 84 
 85 def get_edition_user():
 86     return get_value("EDITION_USER", "Edition User Name", DEFAULT_EDITION_USER)
 87 
 88 def get_edition_password():
 89     return get_value("EDITION_PASSWORD",
 90                      "Password for %s" % get_edition_user())
 91 
 92 def get_edition_name():
 93     return get_value("EDITION_NAME", "Edition Name", DEFAULT_EDITION_NAME)
 94 
 95 def get_connect_string():
 96     return get_value("CONNECT_STRING", "Connect String",
 97                      DEFAULT_CONNECT_STRING)
 98 
 99 def get_main_connect_string(password=None):
100     if password is None:
101         password = get_main_password()
102     return "%s/%s@%s" % (get_main_user(), password, get_connect_string())
103 
104 def get_drcp_connect_string():
105     connect_string = get_value("DRCP_CONNECT_STRING", "DRCP Connect String",
106                                DEFAULT_DRCP_CONNECT_STRING)
107     return "%s/%s@%s" % (get_main_user(), get_main_password(), connect_string)
108 
109 def get_edition_connect_string():
110     return "%s/%s@%s" % \
111             (get_edition_user(), get_edition_password(), get_connect_string())
112 
113 def get_admin_connect_string():
114     admin_user = get_value("ADMIN_USER", "Administrative user", "admin")
115     admin_password = get_value("ADMIN_PASSWORD", "Password for %s" % admin_user)
116     return "%s/%s@%s" % (admin_user, admin_password, get_connect_string())
117 
118 def run_sql_script(conn, script_name, **kwargs):
119     statement_parts = []
120     cursor = conn.cursor()
121     replace_values = [("&" + k + ".", v) for k, v in kwargs.items()] + \
122                      [("&" + k, v) for k, v in kwargs.items()]
123     script_dir = os.path.dirname(os.path.abspath(sys.argv[0]))
124     file_name = os.path.join(script_dir, "sql", script_name + "_exec.sql")
125     for line in open(file_name):
126         if line.strip() == "/":
127             statement = "".join(statement_parts).strip()
128             if statement:
129                 for search_value, replace_value in replace_values:
130                     statement = statement.replace(search_value, replace_value)
131                 try:
132                     cursor.execute(statement)
133                 except:
134                     print("Failed to execute SQL:", statement)
135                     raise
136             statement_parts = []
137         else:
138             statement_parts.append(line)
139     cursor.execute("""
140             select name, type, line, position, text
141             from dba_errors
142             where owner = upper(:owner)
143             order by name, type, line, position""",
144             owner = get_main_user())
145     prev_name = prev_obj_type = None
146     for name, obj_type, line_num, position, text in cursor:
147         if name != prev_name or obj_type != prev_obj_type:
148             print("%s (%s)" % (name, obj_type))
149             prev_name = name
150             prev_obj_type = obj_type
151         print("    %s/%s %s" % (line_num, position, text))