Traceback (most recent call last):
File "J:/python数据比对项目/数据表/python将excel写入达梦1.py", line 200, in
excel_insert_data_to_db(dm_user_name,dm_user_password,db_ip,db_port,db_table_name,para_excel_file_path,para_excel_file_name,insert_data_num,type_len)
File "J:/python数据比对项目/数据表/python将excel写入达梦1.py", line 136, in excel_insert_data_to_db
for j in sheet.row_values(i):
AttributeError: 'Worksheet' object has no attribute 'row_values'
#encoding:utf-8
import xlrd
import os
import dmPython
import time
import openpyxl
import warnings
warnings.filterwarnings("ignore", category=UserWarning)
class DmSqlFunction(object):
def __init__(self,user,password,server,port,autoCommit=True,local_code=dmPython.PG_UTF8):
self.__user = user
self.__password = password
self.__server = server
self.__port = port
self.__autoCommit = autoCommit
self.__local_code = local_code
self.__conn = None
self.__cur = None
self.__connect_db()
def __connect_db(self):
try:
self.__conn = dmPython.connect(user=self.__user,password=self.__password,\
server=self.__server,port=self.__port,\
autoCommit=self.__autoCommit,local_code=self.__local_code,\
connection_timeout=60,login_timeout=60)
self.__cur = self.__conn.cursor()
print('\nconnect database successful\n')
except BaseException as e:
print('__connect_db:\n')
print(e)
def execute_sql(self,sql,args=(),fetch=True,executemany = False):
_rt_list = []
if self.__cur:
if fetch:
self.__cur.execute(sql,args)
_rt_list = self.__cur.fetchall()
else:
if executemany:
self.__cur.executemany(sql,args)
else:
self.__cur.execute(sql,args)
else:
print('重连：%s'%self.__conn)
self.__connect_db()
if fetch:
self.__cur.execute(sql,args)
_rt_list = self.__cur.fetchall()
else:
if executemany:
self.__cur.executemany(sql,args)
else:
self.__cur.execute(sql,args)
return _rt_list
def commit(self):
if self.__conn:
self.__conn.commit()
else:
print('重连：%s'%self.__conn)
self.__connect_db()
self.__conn.commit()
def close_db(self):
self.commit()
if self.__cur:
self.__cur.close()
self.__cur = None
if self.__conn:
self.__conn.close()
self.__conn = None
def excel_insert_data_to_db(dm_user_name,dm_user_password,dm_ip,dm_port,db_table_name,para_excel_file_path,para_excel_file_name,insert_db_data_num,type_len):
table_name = '\"'+ db_table_name + '\"'
insert_data_num = insert_db_data_num
insert_data_num_flag = 0
data_list = []
sql_drop = 'drop table {table_name}'.format(table_name=table_name)
Dm_Conn_y = DmSqlFunction(dm_user_name,dm_user_password,dm_ip,dm_port)
try:
Dm_Conn_y.execute_sql(sql_drop,fetch=False)
except BaseException as e :
print("执行sql_drop语句报错无影响：")
print("%s\n"%e)
excel_file_path = para_excel_file_path
print("当前目录:%s\n"%os.getcwd()) #获取当前目录
os.chdir(excel_file_path) #更换目录
print("更换当前目录:%s\n"%os.getcwd()) #获取当前目录
# 操作excel
excel = openpyxl.load_workbook(para_excel_file_name)
sheet = excel['Sheet1']
excel.sheetnames # 获取excel里的工作表sheet名称数组
#sheet = excel.sheet_by_index(0) #根据下标获取对应的sheet表
sheet = excel.worksheets[0]
print(sheet)
print("总共的行数:%s\n"%sheet.rows)
print("总共的列数:%s\n"%sheet.max_column)
print("一次性插入条数：%s\n"%insert_data_num)
table_column_index = 0 #表头索引下标
insert_sql = 'insert into {table_name} values('.format(table_name=table_name)
for x in range(sheet.max_column):
if x == sheet.max_column - 1:
insert_sql = insert_sql + '?)'
else:
insert_sql = insert_sql + '?,'
print("insert_sql:%s\n"%insert_sql)
rows_list = list(sheet.rows)
for i in range(len(rows_list)): #sheet.row获取总共的行数
if i == 0: #这一层主要目的是根据excel表头去创建表
rows = sheet.iter_rows(i) # 获取行对象列表
first_row = []
for cell in sheet[1]:
first_row.append(cell.value)
sql_create = 'create table {table_name} ('.format(table_name=table_name)
row_index = 0
row_values = []
for cell in sheet.iter_cols(min_row=row_index+1, max_row=row_index+1, values_only=True):
row_values.append(cell[0])
print(row_values)
for j in sheet.row_values(i):
table_column_index += 1
if j == sheet.row_values(i)[-1] : #获取总共的列数sheet.max_column
if len(j) == 0:
sql_create = sql_create + '"'+ 'empty_column' +str(table_column_index)+ '"' + r' varchar2(%s) )'%type_len
else:
sql_create = sql_create + '"'+ j + '"'+ r' varchar2(%s) )'%type_len
else:
if len(j) == 0:
sql_create = sql_create + '"'+ 'empty_column' + str(table_column_index) + '"'+ r' varchar2(%s),'%type_len
else:
sql_create = sql_create + '"'+ j + '"'+ r' varchar2(%s),'%type_len
print("sql_create:%s\n"%sql_create)
try:
Dm_Conn_y.execute_sql(sql_create,fetch=False)
print("执行sql_create语句成功\n")
print("=============================\n")
except BaseException as e :
print("执行sql_create语句报错：")
print("%s\n"%e)
dm = open('py_error_{sysdate}.txt'.format(sysdate=time.strftime("%Y-%m-%d", time.localtime()) ),'a')
dm.write("第%s条执行sql_create语句:%s 出现错误\n错误为：%s\n\n"%(i,sql_create,e))
dm.close()
print("=============================\n")
else: #这一层主要目的是根据excel表数据进行数据插入
try:
print("第%s条数据：%s\n"%(i,sheet.row_values(i)))
data_list.append(sheet.row_values(i))
print("data_list:%s\n"%data_list)
insert_data_num_flag += 1
print("insert_data_num_flag:%s insert_data_num:%s\n"%(insert_data_num_flag,insert_data_num))
if insert_data_num_flag == insert_data_num or i == sheet.nrows - 1:
Dm_Conn_y.execute_sql(insert_sql,data_list,fetch=False,executemany = True)
Dm_Conn_y.commit()
print("执行insert_sql语句成功\n")
print("=============================\n")
data_list = []
insert_data_num_flag = 0
except BaseException as e :
print("执行insert_sql语句报错：")
print("%s\n"%e)
dm = open('py_error_{sysdate}.txt'.format(sysdate=time.strftime("%Y-%m-%d", time.localtime()) ),'a')
dm.write("插入行数：%s\n执行到第%s条数据:\n%s\n出现错误\n执行sql为：%s\n错误为：%s\n\n"%(insert_data_num,i,data_list,insert_sql,e))
dm.close()
print("=============================\n")
Dm_Conn_y.commit()
Dm_Conn_y.close_db()
if __name__=='__main__':
dm_user_name = 'SYSDBA'
dm_user_password = 'SYSDBA001'
db_ip = '127.0.0.1'
db_port = 5236
db_table_name = "TEST_EXCEL"
para_excel_file_path = r'./'
para_excel_file_name = "./20231211115421598668.xlsx"
insert_data_num = 100
type_len = 20
time1 = time.time()
excel_insert_data_to_db(dm_user_name,dm_user_password,db_ip,db_port,db_table_name,para_excel_file_path,para_excel_file_name,insert_data_num,type_len)
time2 = time.time()
result_value = int(round(time2 * 1000)) - int(round(time1 * 1000))
print("耗时为：%s ms"%result_value)
