本文详细介绍Oracle数据库的安装配置过程，包括环境设置、下载、解压及环境变量配置，并提供Oracle与Python集成的示例代码，涵盖cx_Oracle与SQLAlchemy的使用，以及常见错误处理方法。
文章目录
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
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)
报错处理
-
首先操作系统位数、python位数、cx_Oracle版本要对应上；另外可能缺【Visual C++】
每次装完后，要 重启pycharm和python -
打开终端ping一下
检查【主机名或IP地址】、【服务名或SID】、【用户名】和【密码】是否填对 - encoding=‘utf8’
-
insert语句中出现
'之类的字符
解决方法：将可能报错的字符替换掉 -
select TO_CHAR(long_text) from table_name，目标缓冲区太小，无法储存CLOB转换字符后的数据
解决方法：不在SQL用 TO_CHAR，改在Python中用 read（如上代码所示） -
虽然
CLOB可以保存长文本，但是
SQL语句有长度限制
解决方法：把超长文本保存在一个变量中（如上代码所示） -
字符串长度>32767(2
15-1)
解决方法：使用'||'来连接字符串（如上代码所示） -
INSERT操作时，表字段命名与数据库内置名称冲突，如：ID、LEVEL、DATE等
解决方法：建立命名规范 -
使用
sqlalchemy时的报错
原因可能是目标数据库是集群部署的，可以咨询一下DBA，或见上面代码 from cx_Oracle import makedsn -
使用
sqlalchemy时的报错，插入中文字符引起
解决方法是设置os.environ['NLS_LANG'] - oracle闲置超时，会失联
DPI-1047: 64-bit Oracle Client library cannot be loaded
ORA-12170: TNS:Connect timeout occurred
中文乱码
ORA-00972: identifier is too long
ORA-64203: Destination buffer too small to hold CLOB data after character set conversion.
ORA-01704: string literal too long
PLS-00172: string literal too long
ORA-00928: missing SELECT keyword
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
UnicodeEncodeError: 'ascii' codec can't encode character
ORA-03113: end-of-file on communication channel
扫一扫
3428
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
