数据处理步骤:
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:当数据表不存在时,每次覆盖整个表
#每次运行会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,)