Oracle Client（配环境+代码+报错+Python连接）

Oracle Client 安装

1、环境

• 日期：2019年8月1日
• 公司已经安装好Oracle服务端
• Windows版本：Windows10专业版
• 系统类型：64位操作系统，基于x64的处理器
• Python版本：Python 3.6.4 :: Anaconda, Inc.
  

2、下载网址

https://www.oracle.com/database/technologies/instant-client/downloads.html

3、解压至目录

压缩包

解压后（这里放D盘）

4、配置环境变量

控制面板\系统和安全\系统 -> 高级系统设置 -> 环境变量

新建ORACLE_HOME，值为包解压的路径

编辑PATH，添加%ORACLE_HOME%

Navicat连接测试

一个简单的建表示例

-- 建表
CREATE TABLE table_name
(
serial_number     NUMBER(10),
collect_date      DATE,
url               VARCHAR2(255),
long_text         CLOB,
price             NUMBER(10)-- 若需要精确到小数点2位，按分存储，/100还原到元
);
-- 给表添加备注
COMMENT ON TABLE table_name IS '中文表名';
-- 给表字段添加备注
COMMENT ON COLUMN table_name.serial_number IS '编号';
COMMENT ON COLUMN table_name.collect_date IS '日期';
COMMENT ON COLUMN table_name.url IS 'URL';
COMMENT ON COLUMN table_name.long_text IS '长文本';
COMMENT ON COLUMN table_name.price IS '价钱';
-- 插入
INSERT INTO table_name(collect_date) VALUES (DATE'2019-08-23');
INSERT INTO table_name(long_text) VALUES ('a');
INSERT INTO table_name(long_text) VALUES ('b');
-- 查询
SELECT * FROM table_name WHERE TO_CHAR(long_text) in ('a','b');
-- 查建表语句（表名大写）
SELECT dbms_metadata.get_ddl('TABLE','TABLE_NAME') FROM dual;
-- 删表
DROP TABLE table_name;

cx_Oracle

基础

安装命令：conda install cx_Oracle

import cx_Oracle

def execute(query):
    db = cx_Oracle.connect('用户名/密码@IP/ServiceName')
    cursor = db.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    cursor.close()
    db.close()
    return result

def commit(sql):
    db = cx_Oracle.connect('用户名/密码@IP/ServiceName')
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
    cursor.close()
    db.close()

封装成类

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


class Engine:
    def __init__(self, conn):
        self.engine = create_engine(conn, encoding='utf8')  # 这engine直到第一次被使用才真实创建

    def execute(self, sql, **kwargs):
        """含commit操作，返回<class 'sqlalchemy.engine.result.ResultProxy'>"""
        return self.engine.execute(sql, **kwargs)

    def fetchall(self, sql):
        return self.execute(sql).fetchall()  # 能解释CLOB

    def fetchone(self, sql, n=999999):
        self.yellow(sql)
        result = self.execute(sql)
        for _ in range(n):
            one = result.fetchone()
            if one:
                yield one  # <class 'sqlalchemy.engine.result.RowProxy'>

    def fetchone_dt(self, sql, n=999999):
        self.yellow(sql)
        result = self.execute(sql)
        columns = result.keys()
        length = len(columns)
        for _ in range(n):
            one = result.fetchone()
            if one:
                yield {columns[i]: one[i] for i in range(length)}

    @staticmethod
    def yellow(x):
        print('\033[033m{}\033[0m'.format(x))

    @staticmethod
    def red(x):
        print('\033[031m{}\033[0m'.format(x))


class ORM(Engine):
    """对象关系映射（Object Relational Mapping）"""
    def __init__(self, conn, Base):
        super().__init__(conn)
        _Session = sessionmaker(bind=self.engine)  # 创建ORM基类
        self.session = _Session()  # 创建ORM对象
        self.Base = Base
        self.create_all()

    def __del__(self):
        self.session.close()

    def create_all(self):
        self.Base.metadata.create_all(bind=self.engine)

    def add(self, Table, dt):
        self.session.add(Table(**dt))  # 添加到ORM对象
        self.session.commit()  # 提交

    def update(self, Table, condition, dt):
        """有则更新，没则插入"""
        q = self.session.query(Table).filter_by(**condition)
        if q.all():
            q.update(dt)  # 更新
            self.session.commit()  # 提交
        else:
            self.add(Table, dt)


class Oracle(ORM):
    def show_tables(self):
        for i in self.fetchone('SELECT t.table_name,t.num_rows FROM user_tables t'):
            print(*i)

    def show_create_table(self, tb=''):
        """查看表注释"""
        if tb:
            tb = tb.upper()
            self.yellow('表注释')
            sql = "SELECT comments FROM user_tab_comments WHERE table_name='%s'" % tb
            print(self.fetchall(sql)[0][0])
            self.yellow('字段注释')
            sql = "SELECT column_name,comments FROM user_col_comments WHERE table_name='%s'" % tb
            for i in self.fetchall(sql):print(i)
            self.yellow('建表语句')
            sql = "SELECT dbms_metadata.get_ddl('TABLE','%s')FROM dual" % tb
            print(self.fetchall(sql)[0][0].strip())
        else:
            sql = 'SELECT table_name,column_name,comments FROM user_col_comments ORDER BY table_name'
            for i in self.fetchall(sql):print(i)

    def drop(self, tb):
        sql = 'DROP TABLE %s PURGE' % tb
        self.yellow(sql)
        self.execute(sql)  # 彻底删表

    def truncate(self, tb):
        sql = 'TRUNCATE TABLE %s DROP STORAGE' % tb
        self.yellow(sql)
        self.execute(sql)  # 彻底清空表

    def recreate(self, tb='all'):
        """慎用！重建数据仓库"""
        tables = self.Base.metadata.tables.keys() if tb == 'all' else [tb]
        for table in tables:
            self.drop(table)
        self.create_all()

    def count(self, tb=None):
        if tb:
            return self.fetchall('SELECT COUNT(*) FROM ' + tb)[0][0]
        for tb in self.Base.metadata.tables.keys():
            print(tb, self.count(tb))

    def proportion_not_null(self, table, field=None):
        """非空占比"""
        if field:
            # oracle表和字段是有大小写的区别；若用双引号括起，就区分大小写；若冇，系统会默认转大写
            sql = '''
            SELECT
                t1.f1 amount,
                t1.f1/t2.f1 proportion
            FROM
                (SELECT COUNT(*)f1 FROM {table} WHERE "{field}" IS NOT NULL)t1,
                (SELECT COUNT(*)f1 FROM {table})t2
            '''.format(table=table, field=field.upper())  # 将双引号里面的字段名转成大写
            result = self.fetchall(sql)[0]
            print(field, result[0], '%.2f%%' % (result[1] * 100), sep=' '*6)
        else:
            sql = "SELECT column_name FROM user_tab_columns WHERE table_name='%s'" % table.upper()
            for i, in self.fetchall(sql):
                self.proportion_not_null(table, i)

    def proportion_group_by(self, table, field, prints=''):
        """单字段各项占比"""
        sql = '''
        SELECT
            t1.f2 fullname,
            t1.f1 amount,
            t1.f1/t2.f1 proportion
        FROM
            (SELECT COUNT(*)f1,"{field}"f2 FROM {table} GROUP BY "{field}")t1,
            (SELECT COUNT(*)f1 FROM {table})t2
        ORDER BY amount DESC
        '''.format(table=table, field=field.upper())  # 将双引号里面的字段名转成大写
        result = self.fetchall(sql)
        for i, j, k in prints and result:
            print(i, j, '%.2f%%' % (k*100), sep=' '*6)
        return result

    def discretize(self, table, field, n=10, start=0.0, end=1.0):
        """单字段离散化"""
        scope = (end - start) / n
        when = '\n'.join(
            "\t\t\tWHEN {field}>=%.2f AND {field}<%.2f THEN '[%.2f,%.2f)'".format(field=field) % (
                start+i*scope, start+(i+1)*scope, start+i*scope, start+(i+1)*scope)
            for i in range(n)
        )
        sql = '''
        SELECT {field},COUNT(1) FROM
        (
        SELECT CASE\n{when}
        END {field}
        FROM {table}
        )
        GROUP BY {field}
        ORDER BY {field}
        '''.format(table=table, field=field, when=when)
        for i in self.fetchone(sql):
            print(*i)

conf

Color

CONN = ('用户名', '密码', 'IP/ServiceName')
conn = '用户名/密码@IP/ServiceName'

正则表达式高亮

from corpora.oracle.cx import Analysis
from segment import tk, clean


class Highlight(Analysis):
    """文本字符串查询"""
    def instr(self, keyword, field, table):
        for i in super().instr(keyword, field, table):
            yield clean.replace_tag(i)

    def instr_highlight_s(self, keyword, field, table, half=54):
        tk.highlight_prints(self.instr(keyword, field, table), keyword, half)

    def regexp_instr(self, pattern, field, table):
        for i in super().regexp_instr(pattern, field, table):
            yield clean.replace_tag(i)

    def regexp_instr_highlight(self, pattern, field, table, max_len=23):
        tk.highlight_print_re(self.regexp_instr(pattern, field, table), pattern, max_len)

    def regexp_instr_highlight_s(self, pattern, field, table, half=54, max_len=23):
        tk.highlight_prints_re(self.regexp_instr(pattern, field, table), pattern, half, max_len)

sqlalchemy

Oracle编码环境

import os  # 解决【UnicodeEncodeError: 'ascii' codec can't encode character】问题
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
# os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

Oracle专用字段

from sqlalchemy.dialects.oracle import VARCHAR2, NUMBER

建表写数据

from cx_Oracle import makedsn
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 连接数据库（ORA-12505: TNS:listener does not currently know of SID given in connect descriptor）
ip = ''
port = ''
tnsname = ''  # 实例名
uname = ''  # 用户名
pwd = ''  # 密码
dsnStr = makedsn(ip, port, service_name=tnsname)
connect_str = "oracle://%s:%s@%s" % (uname, pwd, dsnStr)
# 创建连接引擎，这个engine是lazy模式，直到第一次被使用才真实创建
engine = create_engine(connect_str, encoding='utf-8')

# 创建对象的基类
Base = declarative_base()

class Student(Base):
    # 表名
    __tablename__ = 'student'
    # 表字段
    sid = Column(String(20), primary_key=True)
    age = Column(Integer)

# 建表（继承Base的所有表）
Base.metadata.create_all(bind=engine)

# 使用ORM操作数据库
Session = sessionmaker(bind=engine)  # 创建ORM基类
session = Session()  # 创建ORM对象
tb_obj = Student(sid='a6', age=18)  # 创建表对象
session.add(tb_obj)  # 添加到ORM对象（插入数据）
session.commit()  # 提交
session.close()  # 关闭ORM对象

# 删表（继承Base的所有表）
Base.metadata.drop_all(engine)

报错处理

DPI-1047: 64-bit Oracle Client library cannot be loaded

首先操作系统位数、python位数、cx_Oracle版本要对应上；另外可能缺【Visual C++】
每次装完后，要 重启pycharm和python

ORA-12170: TNS:Connect timeout occurred

打开终端ping一下
检查【主机名或IP地址】、【服务名或SID】、【用户名】和【密码】是否填对

中文乱码

encoding=‘utf8’

ORA-00972: identifier is too long

insert语句中出现 '之类的字符
解决方法：将可能报错的字符替换掉

ORA-64203: Destination buffer too small to hold CLOB data after character set conversion.

select TO_CHAR(long_text) from table_name，目标缓冲区太小，无法储存CLOB转换字符后的数据
解决方法：不在SQL用 TO_CHAR，改在Python中用 read（如上代码所示）

ORA-01704: string literal too long

虽然 CLOB可以保存长文本，但是 SQL语句有长度限制
解决方法：把超长文本保存在一个变量中（如上代码所示）

PLS-00172: string literal too long

字符串长度>32767(2 ¹⁵-1)
解决方法：使用 '||'来连接字符串（如上代码所示）

ORA-00928: missing SELECT keyword

INSERT操作时，表字段命名与数据库内置名称冲突，如：ID、LEVEL、DATE等
解决方法：建立命名规范

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

使用 sqlalchemy时的报错
原因可能是目标数据库是集群部署的，可以咨询一下DBA，或见上面代码 from cx_Oracle import makedsn

UnicodeEncodeError: 'ascii' codec can't encode character

使用 sqlalchemy时的报错，插入中文字符引起
解决方法是设置 os.environ['NLS_LANG']

ORA-03113: end-of-file on communication channel

oracle闲置超时，会失联