使用Pandas读取Excel、csv、mysql等文件存入到MySQL

数据处理步骤：

  1、Pandas读取数据（如：excel、csv等）

  2、对数据做过滤、统计分析

  3、Pandas将数据存储到MySQL，用于Web页面的页面显示，或是对后序进一步的SQL分析

（处理后的数据）

步骤一：准备数据

import pandas as pd
df = pd.read_excel('C:/Users/xhl/Desktop/input/class.xlsx')

#为索引加上名称为id，以便入库成为主键
df.index.name = 'id'

步骤2：创建sqlalchemy对象连接MySQL

　　SQLAlchemy是Python中的ORM框架（Object-Relation Mapping），把关系数据库的表映射到对象上

  安装sqlalchemy前需要安装Python依赖库，即pip install mysql-connector-python

>>>from sqlalchemy import create_engine
#mysql表示连接MySQL数据库
#mysqlconnector表示使用的库，就是pip install mysql-connector-python中的
#root:MySQL的用户名，123456表示密码，后面分别是表示连接至本地，端口号，数据库名
>>>engine = create_engine("mysql+mysqlconnector://root:123456@127.0.0.1:3306/testdb",echo=False)

方法1：当数据表不存在时，每次覆盖整个表

fetchall():

返回多个元组，即返回多个记录(rows),如果没有结果 则返回 ()
需要注明：在MySQL中是NULL，而在Python中则是None

#每次运行会drop table,新建表
#name属性表示表的名字，if_exists表示若表存在就替代
>>>df.to_sql(name = 'class',con = engine,if_exists="replace")

# 查看表结构
>>>print(engine.execute("show create table class").first()[1])
CREATE TABLE `class` (
  `id` bigint(20) DEFAULT NULL,
  `class` text COLLATE utf8_bin,
  `sex` text COLLATE utf8_bin,
  `score_math` bigint(20) DEFAULT NULL,
  `score_music` bigint(20) DEFAULT NULL,
  KEY `ix_class_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

#查看一共多少条
>>>print(engine.execute("select count(1) from class").first())
(9,)

#查看数据前3条
>>>engine.execute("select * from class limit 3").fetchall()
[(0, b'A', b'male', 95, 79),
 (1, b'A', b'female', 96, 90),
 (2, b'B', b'female', 85, 85)]

方法2：当数据表存在时，每次新增数据

>>>df_new = df.loc[:2]
>>>df_new.to_sql(name='class',con=engine,if_exists='append')
>>>engine.execute("select * from class where id<=2").fetchall()
[(0, b'A', b'male', 95, 79),
 (0, b'A', b'male', 95, 79),
 (1, b'A', b'female', 96, 90),
 (1, b'A', b'female', 96, 90),
 (2, b'B', b'female', 85, 85),
 (2, b'B', b'female', 85, 85)]

 

　　通过上述可以发现，使用append会增加，同一条数据会有冗余

　　问题解决：先通过数据KEY删除旧数据

>>>for id in df_new.index:
...    #先删除要新增的数据
...    delete_sql = f"delete from class student where id={id}"
...    print(delete_sql)
...    engine.execute(delete_sql)
delete from class student where id=0
delete from class student where id=1
delete from class student where id=2

#删除数据
>>>engine.execute("select * from class where id<=2").fetchall()
[]
>>>engine.execute('select count(1) from class').first()
(6,)

#新增至数据表中
>>>df_new.to_sql(name = 'class',con = engine,if_exists = "append")
>>>engine.execute("select * from class where id<=2").fetchall()
[(0, b'A', b'male', 95, 79),
 (1, b'A', b'female', 96, 90),
 (2, b'B', b'female', 85, 85)]

>>>engine.execute("select count(1) from class ").first()
(9,)