python往数据库插数

import cx_Oracle
import pymysql
import pandas as pd
import os
import time
os.environ[‘NLS_LANG’] = ‘SIMPLIFIED CHINESE_CHINA.UTF8’
#如果想导入MySQL，修改这里为MySQL即可
target_db_type = ‘Oracle’
if target_db_type.upper() == ‘ORACLE’:
#连接串
conn = cx_Oracle.connect(‘system’, ‘RPG123456’, ‘localhost:1521/orcl.168.2.45’)
cur = conn.cursor()
#在导入数据库之前，先检查一下Date类型数据格式
cur.execute(“alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’”)
elif target_db_type.upper() == ‘MYSQL’:

#连接串
conn = pymysql.connect(“192.168.56.101”, “dwapp”, “tiger”, “db”)
cur = conn.cursor()
cur.arraysize = 1000
target_table = input(“请输入要导入的表名:”)
#CSV文件是否包含表头
ifreadheader = input(“是否包含表头(y,n):”)
if ifreadheader == ‘y’:
headervalue = 0
elif ifreadheader == ‘n’:
headervalue = None

获取列信息

cur.execute(‘select * from ‘+target_table+’ where 1=0’)
val_str = ‘’
if target_db_type.upper() == ‘MYSQL’:
for i in range (1,len(cur.description)):
val_str = val_str+’%s’+’,’

MySQL批量插入语法是 insert into tb_name values(%s,%s,%s,%s)

val_str = val_str+’%s’
elif target_db_type.upper() == ‘ORACLE’:
for i in range (1,len(cur.description)):
val_str = val_str+’:’+str(i)+’,’
val_str = val_str+’:’+str(i+1)
#Oracle批量插入语法是 insert into tb_name values(:1,:2,:3)
insert_sql = ‘insert into ‘+target_table+’ values(’+val_str+’)’
#拼接insert into 目标表 values
print(insert_sql)
print(‘开始导入:’, time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
#na_filter=’’ 将空值处理为’’ 不然默认为NaN
csv_data = pd.read_csv(‘emp.csv’, header=headervalue, chunksize=50000, encoding=‘GBK’, na_filter=’’)
for chunk in csv_data:
rows = chunk.values.tolist()
print(rows)

将rows中的’‘处理为None,不然沙雕MySQL INT类型字段值’‘会变成0 VARCHAR字段值会变成’’ 而不是NULL

for x in range(0, len(rows)):
for i in range(0, len(rows[x])):
if rows[x][i] == ‘’:
rows[x][i] = None

批量插入

cur.executemany(insert_sql,rows)
conn.commit()
cur.close()
conn.close()
print(‘导入完成:’,time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))