python与oracle交互总结

近日，项目要使用oracle数据库，python与oracle数据库交互，由于之前python与mysql交互时使用的是sqlalchemy，可以实现dataframe直接读写，因此，我的第一个想法就是还使用sqlalchemy。网上查找资料后发现确实有这样的解决方案，好吧，开始！！！
先说明一下笔者的使用场景：服务器上已经安装了oracle数据库，我通过oracle客户端navicat可以查看和创建数据库，我需要做的是在本地windows开发环境下使用python与服务器上的oracle数据库进行数据交互。
1、环境准备。安装一个python包：cx_Oracle，需要注意的是最好不要直接使用pip isntall 的方案自动安装，因为它与python的版本（3.6 or 3.7? 32 or 64bit?）以及你的oracle客户端的版本有关。
说一下我的组合配置，供大家参考：
1)python: 64bit， 3.6.5，只要大版本（3.6）一致就行了。
2)oracle client: 我使用的是navicat 15， 这也是被坑无数次后才总结出来的，之前装的是navicat 12，结果后面提到的创建连接-create_engine时没有问题，但是一旦从数据库中取数据，就会报错，提示：当前数据库客户端版本多低。当我本着简单处理的原则不改变navicat版本，降低cx_oracle包的版本时，发现在安装低版本的包时报错的错误层出不穷，五花八门，算了，哥哥累了，调整思路，直接更换navicat版本。
网上下载一个navicat 15，里面附带注册机，在网上找一个安装方法，对着安装即可。安装完之后，记得将instantclient_11_2文件夹下的dll文件拷贝至安装python的根目录下。否则，等着error包围你吧。

3)cx_oracle包，我也是查阅资料结合神农尝百草后，确定了7.3.0适用，之前试过5.3与7.2.0。

2、开始编写交互程序。
我将数据库配置信息写在了一个初始化文件Pinit.txt中，如下：
dbname=HANGXIN_QY
username=HXDEVPB
password=1qaz2wsxZX
host=192.168.1.206
port=1521
service=repairpd
以上参数的意思看名称就知道了，不再赘述。
先上一下我试验成功的第一种方法，这种方法与sqlalchemy没有关系，大家可以参考一下，该方法主要借鉴了文献1。

import cx_Oracle
db=cx_Oracle.connect('HXDEVPB','1qaz2wsxZX','192.168.1.206:1521/repairpd',encoding="utf-8")
print(db.version)
cr = db.cursor()
sql = 'select * from HANGXIN_QY.HX_JUDGE'
cr.execute(sql)
rs = cr.fetchall()
zz = pd.DataFrame(rs)
print(zz)
db.close()

注意sql的写法，HX_JUDGE是表名，在其前面加了一个HANGXIN_QY，它是HX_JUDGE的所有者，即HX_JUDGE表在数据库HANGXIN_QY中。网上大部分的资料都没有这样的写法，直接就是sql = ‘select * from HX_JUDGE’，这样会一直报一个错误：无法找到HX_JUDGE这个表。这个问题的解决主要参考了文献2。
另外一个需要说明的坑是’192.168.1.206:1521/repairpd’的端口号后面跟的是服务名-repairpd，而不是数据库名称-HANGXIN_QY，但是当时笔者脑袋有点大，在这个问题上也翻车了。
这种方式获取的数据没有列名，数据中不存在后面要说的中文乱码问题。
好了，硬货要来了，接下来说一下sqlalchemy交互方式。

def initConnection(init_message):
    # caution: service name is not dbname
    engine = create_engine('oracle://' + init_message.loc["username", 1] + \
                           ':' + init_message.loc["password", 1] + '@' + \
                            init_message.loc["host", 1] + ':' + init_message.loc["port", 1] + \
                            '/' + init_message.loc["service", 1], encoding="utf-8")
    return engine

# table_name_1是表名
def read_data(table_name, init_message, engine):
    # dbname is necessary, or it will trigger error: grahp or table does not exist
    sql = "SELECT * FROM " + init_message.loc["dbname", 1] + "." + table_name
    dat = pd.read_sql(sql=sql, con=engine)
    return dat

# table_name_1是表名，result_data是要写入数据库的结果-dataframe
def write_data(table_name_1, engine, result_data):
    result_data.to_sql(table_name_1, engine, if_exists='append', index=False)

init_address = "./Pinit.txt"
init_message = pd.read_table(init_address, header=None, sep="=")
init_message.set_index([0], inplace=True)

engine = initConnection(init_message)
table_name = "HX_JUDGE"
judge_file = read_data(table_name, init_message, engine)

简要分析一下，sqlalchemy中同样需要注意写sql语句时表名前加上数据库名称。可以读到数据，但是中文全部都是“？？？”，网上一查，发现是oracle客户端的字符编码设置不对，网上查了一下在orcle中查询某一张表的编码方式的写法：

select distinct(userenv('language')) from HX_JUDGE;

执行后，返回的结果是“SIMPLIFIED CHINESE_CHINA.AL32UTF8”。好了，解决办法有了，在脚本的最前面加上下面这行代码就可以了。

# set oracle client encoding
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'

之后，再运行前述读数据的程序，发现中文显示正常了，这一点主要参考了文献3和4。
其余文献大家可以结合自身情况参考。
好了，各位看官，笔者的python&oracle出坑史讲完了，喜欢的点个赞，有不明白的欢迎在下方留言！

参考文献：
【1】https://www.jb51.net/article/144420.htm
【2】https://www.pythonheidong.com/blog/article/285445/988effd89d5bf3157f58/
【3】https://blog.csdn.net/pergoods/article/details/84880780
【4】https://www.cnblogs.com/chenjianhong/p/4144399.html
【5】https://zhuanlan.zhihu.com/p/52757324
【6】https://blog.csdn.net/walking_visitor/article/details/84023393