pandas连接Oracle读写操作

1、连接Oracle

# -*- coding: utf-8 -*-
import pandas as pd
import cx_Oracle as cx
import datetime
import os
from sqlalchemy import create_engine

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
# 或者
# os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'

# 设置oci路径
os.environ['path'] = './instantclient_19_12'

user = '***'
passwd = '****'
url = '172.********:1521/aml'

# panas读Oracle 但是不能出现 clob字段
def pd_query_ora(sql_str:str):
    try:
        db = cx.connect(user, passwd, url)
        df: pd.DataFrame = pd.read_sql_query(sql_str, db)
        db.close()
        isbool:bool = True
        return df,isbool
    except Exception as e:
        # print(e)
        isbool:bool = False
        return pd.DataFrame(data=[]),isbool

# lis读Oracle 可以能出现 clob字段
# @nb.jit(parallel=True)
def read_sql(sqlstr:str):
    conn = cx.connect(user, passwd, url)
    cur = conn.cursor()

    cur.execute(sqlstr)
    rels = []
    for tup in cur:
        pram = []
        for itm in tup:
            if type(itm) == cx.LOB:
                text = itm.read()
                pram.append(text)
            elif type(itm)==datetime.datetime:
                # print(itm)
                text = datetime.datetime.strftime(itm,'%Y-%m-%d %H:%M:%S')
                pram.append(text)
            else:
                pram.append(itm)
        rels.append(pram)

    cur.close()
    conn.close()
    return rels


def connet_oracle():
    ip_post = 'oracle+cx_oracle://{user}:{passwd}@{ip_post_ocl}?charset=utf8'.format(user=user, passwd=passwd,
                                                                                 ip_post_ocl=url)
    return create_engine(ip_post, echo=False, encoding='utf-8')  # ,encoding = "UTF-8", nencoding = "UTF-8"

　　2、读写操作

import pandas as pd
import sqlalchemy.types as type
from connet_ora import connet_oracle

# 链接Oracle
engine = connet_oracle()

df = pd.read_excel('./data/template_data - 2022-06-20T113240.018.xls',dtype=str)
# data.to_csv("./data/df.csv")

# 空值处理
df[df=='nan'] = ''
df.fillna('',inplace=True)

data = df.copy()

data.to_sql('t3a_n_20220620', con=engine,if_exists='replace',index=False,chunksize=100,dtype=type.VARCHAR(255)) #,dtype='utf-8'

result = pd.read_sql("select * from CASE_tab where fin_act_desc is not null",con=engine)
print(result["fin_act_desc"])