dataframe to oracle,python 往oracle里面写DataFrame（二）

之前写了python 往oracle里面DataFrame， 在自己的笔记本上奏效，但是放在集群上去，就会失效了，报错内容为：

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-12505: TNS: 监听程序当前无法识别连接描述符中所给出的 SID

解决方案为创建引擎的方式有所变化，具体的代码入下

from sqlalchemy.dialects.oracle import \

BFILE, BLOB, CHAR, CLOB, DATE, \

DOUBLE_PRECISION, FLOAT, INTERVAL, LONG, NCLOB, \

NUMBER, NVARCHAR, NVARCHAR2, RAW, TIMESTAMP, VARCHAR, \

VARCHAR2

def mapping_df_types(df):

dtypedict = {}

for i, j in zip(df.columns, df.dtypes):

if "object" in str(j):

dtypedict.update({i: VARCHAR(256)})

if "float" in str(j):

dtypedict.update({i: NUMBER(19,8)})

if "int" in str(j):

dtypedict.update({i: VARCHAR(19)})

return dtypedict

## 往集群oracle写入数据

def put_df_toOracle_cluster(df_data , tableName , ip , port ,uname,pwd , tnsname):

import cx_Oracle as cx_Oracle

from sqlalchemy import create_engine

# ip = 'localhost'

# port = '1521'

# uname = 'chensq' # 用户名

# pwd = '123456' # 密码

# tnsname = 'orcl' # 实例名

dsnStr = cx_Oracle.makedsn(ip, port, service_name=tnsname)

connect_str = "oracle://%s:%s@%s" %(uname, pwd, dsnStr)

engine = create_engine(connect_str, encoding=encoding)

from sqlalchemy.dialects.oracle import \

BFILE, BLOB, CHAR, CLOB, DATE, \

DOUBLE_PRECISION, FLOAT, INTERVAL, LONG, NCLOB, \

NUMBER, NVARCHAR, NVARCHAR2, RAW, TIMESTAMP, VARCHAR, \

VARCHAR2

dtypedict = mapping_df_types(df_data)

print(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>开始")

print(dtypedict)

print(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>结束")

df_data.to_sql(tableName,engine,index=False,if_exists='append',dtype=dtypedict,chunksize=None)

#------------------------------

参考博客为：

【1】https://blog.csdn.net/AlanGuoo/article/details/88255194

【2】: https://blog.csdn.net/walking_visitor/article/details/84023393