程序背景
在工作中经常会遇到需要将数据存储至数据库,而采用navicat的导入功能,在面对较大的数据时经常导入失败,即使导入成功,MySQL建表时一般默认使用该数据类型的最大值,比如varchar(225),如果我们的原始数据既有数值、文本又有时间,如果navicat不能识别其数据类型,均将其默认设置为varchar,这不是我们所需要的。所以为了更好更方便的将数据存储至数据库,就随便写了一段程序,以实现该目的。
函数功能介绍
datastore
函数主要分为3个小函数:dataChange
函数主要功能是为了转换数据,如果导入的数据里有数值或日期时间,但python将其理解成文本,这不是我们所希望的结果,故该函数可进行简单的数据转换;createTable
函数的功能是为了在数据库里创建表,其会自动根据数据类型及大小创建对应的sql数据类型;tableStore
函数的功能是将数据存储至数据库。
参数设置
data
:指定数据源,可以是DataFrame格式,也可以是文件位置(支持xlsx和csv),默认None;database
:指定要保存的数据库名,默认None;table
:指定要保存的数据表名,默认None;tableDrop
:如果指定的表名数据库里有,是否删除重新建表,默认False;primaryKey
:指定主键字段名,即将哪些字段设置为主键,如果指定的是一个主键,如:primaryKey=[‘id’],则将该字段设置为主键;如果指定的是2个或两个以上字段,则将其设置为联合主键,如:primaryKey=[‘class’,‘name’],默认[None];key
:指定索引字段,如:key=[‘age’,‘name’],默认[None];key_union
:如果指定了2个以上字段作为索引,是否需要将其设置为联合索引,默认False;comments
:指定字段注释,默认[None],如果没有指定注释,则默认将字段名作为注释;table_comment
:指定表注释,默认None,如果没有指定表注释,则默认为库名+表名;host
:指定数据库连接ip或主机名;user
:指定数据库连接用户名;password
:指定数据库连接密码;failureID
:无需指定,用于返回保存错误的主键。
代码展示
# 导入相关包 -----------------------------------------------------------------------------------------------------------
import numpy as np
import pandas as pd
import pymysql
import os
from datetime import datetime
# 自定义异常 -----------------------------------------------------------------------------------------------------------
class dataStoreError(Exception):
def __init__(self, ErrorInfo):
super().__init__(self) # 初始化父类
self.errorinfo = ErrorInfo
def __str__(self):
return self.errorinfo
# 定义类函数 ###########################################################################################################
class dataStore:
def __init__(self, data = None,
database = None,
table = None,
tableDrop = False,
primaryKey = [None],
key = [None],
key_union = False,
comments = [None],
table_comment = None,
host = 'localhost',
user = 'root',
password = '123456',
failureID = []
):
self.data = data # 指定数据源或数据地址
self.database = database # 指定存储库名
self.table = table # 指定存储表名
self.tableDrop =tableDrop # 如果建表时存在相同表名是否删除(默认False)
self.primaryKey = primaryKey # 指定主键字段(默认为空)
self.key = key # 指定索引字段(默认为空)
self.key_union = key_union # 是否将上述指定的索引字段建成联合索引(默认False, 如果为True,条件是:len(key)>2 )
self.comments = comments # 指定每个变量的注释(默认为空)【如果是list则元素个数必须与列数相等;如果是dict,则表示要替换某个字段名】
self.table_comment = table_comment # 指定表的注释(默认为空)
self.host = host # 主机名或IP地址
self.user = user # 用户名
self.password = password # 密码
self.failureID = failureID # 用于采集保存错误的ID(索引)
self.wide_connect = None
self.wide_cursor = None
self.typeList = [] # 建表语句
self.priDict = dict() # 定义主键(key是主键对应的字段名,value是主键对应的字段位置)
# 数据导入 -----------------------------------------------------------------------------------------------------
if isinstance(self.data, pd.DataFrame) or isinstance(self.data, pd.Series):
pass
elif isinstance(self.data, str) and os.path.exists(self.data):
try:
self.data = pd.read_table(open(self.data, 'rb'), sep = ',', low_memory = False)
except:
self.data = pd.read_excel(self.data)
else:
raise dataStoreError('数据加载失败')
# 如果没有指定注释则使用字段名作为注释 -------------------------------------------------------------------------
def commentChange(self):
if self.comments == [None]:
self.comments = self.data.columns.tolist()
elif isinstance(self.comments, dict):
self.comments = self.data.rename(columns = self.comments).columns
else:
if len(self.comments) == len(self.data.columns):
self.comments = self.data.columns.tolist()
else:
raise dataStoreError('输入字段注释(comments)个数与字段个数不匹配,请检查!!!')
# 宽表数据连接 -----------------------------------------------------------------------------------------------------
def mysqlConnect(self):
# 判断是否指定数据库 -------------------------------------------------------------------------------------------
if pd.isnull(self.database):
raise dataStoreError('没有指定数据库名,请指定该参数:database')
# 判断是否指定数据表 -------------------------------------------------------------------------------------------
if pd.isnull(self.table):
raise dataStoreError('没有指定数据表名,请指定该参数:table')
# 如果没有指定表的注释,则表的注释为:库名+表名 ----------------------------------------------------------------
if pd.isnull(self.table_comment):
self.table_comment = self.database + self.table
self.wide_connect = pymysql.connect(host = self.host, # 主机名或IP地址
user = self.user, # 用户名
password = self.password, # 密码
port = 3306, # 端口
charset = 'utf8') # 编码
self.wide_cursor = self.wide_connect.cursor()
# 转换数据类型 #####################################################################################################
def dataChange(self):
self.commentChange()
# 针对整数型数值定义其格式 -------------------------------------------------------------------------------------
int_fun = lambda x: 'TINYINT({})'.format(x) if x <= 3\
else 'SMALLINT({})'.format(x) if x <= 5\
else 'MEDIUMINT({})'.format(x) if x <= 8\
else 'INT({})'.format(x) if x <= 11\
else 'BIGINT({})'.format(x) if x <=20\
else 'DECIMAL({},0)'.format(x)
# 针对浮点型数值定义其格式 -------------------------------------------------------------------------------------
def decimal_fun(column):
decm = [str(i).split('.') for i in self.data[column] if pd.notnull(i)] # 按小数点拆分成两个字符串
m = max([len(i[0]) for i in decm if len(i) >= 2] + [1]) # 整数位最大长度
d = max([len(i[1]) for i in decm if len(i) >= 2] + [1]) # 小数位最大长度
if d > 6: d = 6
return 'DECIMAL({m}, {d})'.format(m = m + d + 2, d = d + 1)
self.typeList = [] # 建表语句
for column, comment in zip(self.data.columns, self.comments):
# 如果字段是整数型 -----------------------------------------------------------------------------------------
if self.data[column].dtypes in [np.dtype('int16'), np.dtype('int32'), np.dtype('int64'), np.dtype('uint16'), np.dtype('uint32'), np.dtype('uint64')]:
int_len = max(len(str(self.data[column].max())), len(str(self.data[column].min()))) + 2 # 整数长度
self.typeList.append("`{}` ".format(column) + int_fun(int_len) + " DEFAULT NULL" + " COMMENT '{}'".format(comment))
continue
# 如果字段是浮点型 -----------------------------------------------------------------------------------------
elif self.data[column].dtypes in [np.dtype('float16'), np.dtype('float32'), np.dtype('float64')]:
self.typeList.append("`{}` ".format(column) + decimal_fun(column) + " DEFAULT NULL" + " COMMENT '{}'".format(comment))
continue
# 如果字段是日期时间类型 -----------------------------------------------------------------------------------
elif self.data[column].dtypes in [np.dtype('<M8[ns]')]:
self.typeList.append("`{}` ".format(column) + "DATETIME DEFAULT NULL" + " COMMENT '{}'".format(comment))
continue
else:
try:
self.data[column] = pd.to_datetime(self.data[column], format = '%Y-%m-%d %H:%M:%S') # 将符合条件的文本转换为日期时间
self.typeList.append("`{}` ".format(column) + "DATETIME DEFAULT NULL" + " COMMENT '{}'".format(comment))
continue
except ValueError:
try: # 尝试把字符型转换为数值型(整数型或浮点型)
self.data[column] = pd.to_numeric(self.data[column])
# 如果文本被转换位整数型 -----------------------------------------------------------------------
if self.data[column].dtypes in [np.dtype('int16'), np.dtype('int32'), np.dtype('int64'), np.dtype('uint16'),
np.dtype('uint32'), np.dtype('uint64')]:
int_len = max(len(str(self.data[column].max())), len(str(self.data[column].min()))) + 2 # 整数长度
self.typeList.append("`{}` ".format(column) + int_fun(int_len) + " DEFAULT NULL" + " COMMENT '{}'".format(comment))
continue
# 如果文本被转换位浮点型 -----------------------------------------------------------------------
elif self.data[column].dtypes in [np.dtype('float16'), np.dtype('float32'), np.dtype('float64')]:
self.typeList.append("`{}` ".format(column) + decimal_fun(column) + " DEFAULT NULL" + " COMMENT '{}'".format(comment))
continue
except: # 剩下不能处理的就当文本来处理了
str_len = max([len(i) for i in self.data[column] if pd.notnull(i)] + [1]) + 2
self.typeList.append("`{}` ".format(column) + "VARCHAR({})".format(str_len) + " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL"
+ " COMMENT '{}'".format(comment))
# 如果定义了 主键 则该字段默认不为空 ---------------------------------------------------------------------------
for column, c in zip(self.data.columns, range(len(self.data.columns))):
if column in ['id', 'ID', 'Id'] + self.primaryKey:
self.typeList[c] = self.typeList[c].replace('DEFAULT NULL', 'NOT NULL')
self.priDict[column] = c
# 建表函数 #########################################################################################################
def createTable(self):
self.dataChange(); self.mysqlConnect()
# 指定主键
if len(self.primaryKey) == 1: # 如果定义的是一个主键
primaryKey_statement = ["PRIMARY KEY (`{}`)".format(col) for col in self.data.columns if col in ['id', 'ID', 'Id'] + self.primaryKey][0]
else: # 如果定义了两个以上字段作为主键则将其设为联合主键
primaryKey_statement = "PRIMARY KEY ({})".format(','.join(['`' + k + '`' for k in self.primaryKey]))
# 指定索引
if self.key_union == False: # 是否是联合索引
key_statement = ','.join(["KEY (`{}`)".format(col) for col in self.data.columns if col in
['LoanOrderId', 'UserId', 'task_no', 'reqtId', 'BankId'] + self.key])
else: # 如果是联合索引,则联合索引名为其字段的合并
key_statement = "KEY `{}` ({})".format('_'.join(self.key), ','.join(['`' + k + '`' for k in self.key]))
# 执行建表语句 -------------------------------------------------------------------------------------------------
self.wide_cursor.execute("USE {};".format(self.database))
if self.tableDrop: # 是否要删除已存在的表
self.wide_cursor.execute("DROP TABLE IF EXISTS {};".format(self.table))
self.wide_cursor.execute("CREATE TABLE if not exists `{table_}`( \
{data_demand_statement_}, \
{primary_key_}, \
{key_statement_}) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci \
COMMENT = '{table_comment_}';". \
format(table_ = self.table, # 表名
data_demand_statement_ = ','.join(self.typeList), # 字段格式语句
primary_key_ = primaryKey_statement, # 主键语句
key_statement_ = key_statement.strip(','), # 索引语句
table_comment_ = self.table_comment # 表注释
)
)
self.wide_cursor.close() # 关闭连接 ---------------------------------------------------------------------------
# 存储函数 #########################################################################################################
def tableStore(self):
self.mysqlConnect()
self.wide_cursor.execute("USE {};".format(self.database))
f = lambda x: None if pd.isnull(x) else x # 用于转换空值类型
t = lambda x: datetime.strftime(x, '%Y-%m-%d %H:%M:%S') if type(x) in [datetime, pd._libs.tslibs.timestamps.Timestamp] else x # 将时间转换为字符
data_list_nan = [[f(i) for i in j] for j in self.data.values.tolist()] # 将DataFrame转换为list 并处理空值
data_list = [[t(i) for i in j] for j in data_list_nan] # 处理时间
columnsList = '`' + '`,`'.join(list(self.data.columns)) + '`'
Value = '%s,' * self.data.shape[1]
success = 0; remove = 0; failure = 0
# 逐条存储
for line in data_list:
try:
self.wide_cursor.execute("INSERT INTO {table_}({columns_}) VALUES({value_});". \
format(table_ = self.table,
columns_ = columnsList,
value_ = Value.strip(',')),
args = line)
success += 1
except pymysql.err.IntegrityError: # 主键重复则删除该id行再插入新的一行数据
if len(self.primaryKey) == 1:
self.wide_cursor.execute("DELETE FROM {} WHERE {} = '{}';".format(self.table, line[list(self.priDict.keys())[0]],
line[list(self.priDict.values())[0]]))
else:
deletePri = ""
for i, j in self.priDict.items():
deletePri += "{} = '{}' and ".format(i, line[j])
self.wide_cursor.execute("DELETE FROM {} WHERE {}".format(self.table, deletePri[:-5]))
self.wide_cursor.execute("INSERT INTO {table_}({columns_}) VALUES({value_});". \
format(table_ = self.table,
columns_ = columnsList,
value_ = Value.strip(',')),
args = line)
remove += 1
except: # 当无法存储时,保存错误的id信息
if len(self.primaryKey) == 1:
self.failureID.append(line[list(self.priDict.values())[0]])
else:
fail = []
for fa in range(len(self.priDict)):
fail.append(line[list(f.priDict.values())[fa]])
self.failureID.append(fa)
self.wide_connect.commit() # 数据提交
print('%s --------------------\n存储成功:%d\n删除后存储成功:%d\n存储失败:%d' % (self.table, success, remove, failure))
self.wide_cursor.close() # 关闭连接 --------------------------------------------------------------------------------
运行示例
运行示例一
这里从网上随便找一些数据进行测试:
df = pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv').reset_index()
f = dataStore(database = 'test_database',
table = 'test_table',
data = df,
tableDrop= True, # 删除已存在的表
primaryKey= ['index'],
key = ['country'],
host = 'localhost',
user = 'root',
password = '12345',
table_comment = '表名——测试')
f.dataChange() # 进行数据转换
f.createTable() # 创建表
f.tableStore() # 存储表
f.dataChange() 进行数据转换,可通过 f.data 查看转换后的数据;
f.createTable() 根据数据的类型和大小在数据库里创建相应的表,结果如下所示:
在navicat里可以查看表的创建结果,达到了我们的目的;
f.tableStore() 在表创建好后运行可将数据存储至数据里,运行后会有如下结果:
可以看到有142条数据存储成功,如果在存储是发生主键重复,则会删除数据里的该条数据再插入,如果发生存储失败可通过 f.failureID 查看返回的错误主键id;
通过navicat查看数据存储结果:
到此我们完成了从数据转换到数据存储的所有过程。
运行示例二
data 参数我们也可以直接指定文件路径:
f = dataStore(database = 'test_database',
table = 'test_table',
data = 'F:/df.xlsx', # 文件路径
tableDrop= True,
primaryKey= ['index'],
key = ['country'],
host = 'localhost',
user = 'root',
password = 'haozi21',
table_comment = '表明——测试')
程序不足之处
- 主键和索引必须指定,否则会报错;
- 对文本型时间仅支持类似于 2018-12-11 12:56:34 的转换,如有需要可对该程序部分进行修改;
- 该类的3个主函数均可单独运行,由于只进行了简单测试,程序可能存在不少问题,还请见谅;
- 渣渣码字不容易,转载请注明出处,谢谢。