excel mysql在线库存_excel操作----主要用于数据库入库

# -*- coding: utf-8 -*-

import pymysql

import xlrd

import uuid

from xlrd import XLRDError

from sdjn_tourism_api.settings import MYSQL_DATABASE, MYSQL_PORT, MYSQL_HOST, MYSQL_PASSWORD, MYSQL_USER

class MysqlHelper(object):

def __init__(self):

self.__host = MYSQL_HOST

self.__user = MYSQL_USER

self.__database = MYSQL_DATABASE

self.__port = MYSQL_PORT

self.__password = MYSQL_PASSWORD

self.__conn = pymysql.connect(host=self.__host, user=self.__user, password=self.__password,

database=self.__database, charset='utf8', port=self.__port)

self.__cursor = self.__conn.cursor()

def mysql_insert(self, sql, data):

self.__cursor.execute(sql, data)

self.__conn.commit()

def mysql_query(self, sql, str=''):

if (str != ''):

return self.__cursor.execute(sql, str)

else:

return self.__cursor.execute(sql)

def mysql_update(self, sql, str=''):

try:

result = self.__cursor.execute(sql, str)

self.__cursor.commit()

except Exception:

self.__conn.rollback()

print("发生异常")

return result

def close(self):

self.__cursor.close()

self.__conn.close()

def prepare_insertsql(self,tablename,dict_data={}):

# 数据库中表对应的键值

keys = ",".join(dict_data.keys())

# 数据库中对应的键值个数

values = ",".join(['%s'] * len(dict_data))

sql = r"insert into %s (%s) VALUES (%s)" % (tablename, keys, values)

return sql

def prepare_data(self,dict_data={}):

data=tuple([pymysql.escape_string(one) for one in dict_data.values()])

return data

class ExcelHelper(object):

def __init__(self, filepath):

try:

self.work_book = xlrd.open_workbook(filepath,

encoding_override='utf-8')

except XLRDError as e:

print(e)

def get_content(self,sheet_index,row_num,kwargs={}):

'''

读取excel的内容

:param sheet_index:sheet页位于第几个

:param row_num :从第几行开始

:param kwargs: 传递对应的数据库中的字段的字典表，

键表示对应的数据库中的字段的名称，值表示对应的excel中的列号，从0开始,值必须是int类型

:param length: 传递excel每行的个数

:return:

'''

for i in range(row_num, self.work_book.sheet_by_index(sheet_index).nrows):

dict_map={}

data_excel = self.work_book.sheet_by_index(sheet_index).row_values(i, 0, self.work_book.sheet_by_index(sheet_index).row(i).__len__())

for key in kwargs.keys():

if isinstance(kwargs.get(key),int):

dict_map[key]=data_excel[kwargs.get(key)]

else:

dict_map[key]=eval(kwargs.get(key))

yield dict_map

if __name__=='__main__':

excel_path=r'D:\inspur_file\jn济南项目\地理位置字典表.xlsx'

excel_helper=ExcelHelper(excel_path)

mysql_helper=MysqlHelper()

map_dict={'uuid':"uuid.uuid1().hex",'content':4,"distric":2,"country":0,"provence":3,"area_code":1}

#第一个sheet也的第一行开始

for dict_data in excel_helper.get_content(0,1,map_dict):

sql=mysql_helper.prepare_insertsql("t_areamap_dict",dict_data)

data=mysql_helper.prepare_data(dict_data)

mysql_helper.mysql_insert(sql,data)

print("数据导入完成")