pandas+sqlalchemy导入Excel数据到MySQL
excel 文件内容
prov:省份
city:城市
数据库结构
CREATE TABLE `big_data_area` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL COMMENT '省份或城市名称',
`type` int(1) NOT NULL COMMENT '类型 1:省份 2:城市',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='大数据省市数据';
程序
class ExportData(object):
def __init__(self):
# 数据库方言
self.DIALECT = 'mysql'
# 驱动
self.DRIVER = 'pymysql'
# 用户名
self.USERNAME = 'root'
# 密码
self.PASSWORD = 'root'
# 数据库地址
self.HOST = '127.0.0.1'
# 端口号
self.PORT = 3306
# 数据库名称
self.DATABASE = 'test'
# 字符编码
self.CHARSET = 'utf8'
self.SQLALCHEMY_DATABASE_URI = "{}+{}://{}:{}@{}:{}/{}?charset={}".format(self.DIALECT, self.DRIVER, self.USERNAME, self.PASSWORD, self.HOST, self.PORT, self.DATABASE, self.CHARSET)
self.SQLALCHEMY_TRACK_MODIFICATIONS = True
self.connect = create_engine(self.SQLALCHEMY_DATABASE_URI)
def export_excel(self):
# 提取Excel中的数据集
df = pd.read_excel('C:\\Users\\Administrator\\Documents\\pro_city.xlsx', sheet_name='sheet1')
# 省份
self.data_handler(df, 1)
# 城市
self.data_handler(df, 2)
def data_handler(self, data, type_value):
# 通过类型值设置字段名称
if type_value == 1:
field = 'prov'
elif type_value == 2:
field = 'city'
else:
raise Exception("类型值错误")
# 提取数据集中特定字段的列
df = data[[field]]
# 对数据集进行去重
df = df.drop_duplicates([field])
# 设置type字段的值
df['type'] = type_value
# 重新设置字段
df.columns = ['name', 'type']
print(df)
# big_data_area: 表名,self.connect: 数据库链接 if_exists='append': 表示有相同的数据时追加到表内,index=False: 表示忽略索引
pd.io.sql.to_sql(df, 'big_data_area', self.connect, if_exists='append', index=False)
print("导入数据{}成功!".format(field))
if __name__ == '__main__':
exportData = ExportData()
exportData.export_excel()
运行结果