用python在MySQL中写入数据和添加数据

在笔者之前的博文中，已介绍了用python连接与mysql数据库的知识。包括如何安装python连接mysql的pymysql包，如何通过cusor语句将python与mysql连接起来，以及如何用python查询数据库等等。
传送门：用python连接与查询mysql数据库

今天，再来说说如何用python写入数据和添加数据到mysql。

一、用python将数据写入mysql

例子：现有一个格式为dataframe的数据集data，内含“编号”“商品名称”“售价”这三个变量名，共3条数据，如图所示：

输入下列代码即可写入mysql中，该数据集成为名为price的表，被放在aaa库中。这段代码的思想是创建一个引擎（engine），内含MySQL的主机名、端口、用户名、密码和库名等信息。它相当于一把钥匙，有了这些信息就能顺利找到MySQL中的指定库。然后通过to_sql将数据集直接写入MySQL指定库中。
格式如下:
1、engine= create_engine(“mysql+pymysql:/用户名:密码@主机名:端口/待写入的数据库名字？设置文本防乱码格式”)
2、dataframe.to_sql（name=“设置的表名”,con=engine,if_exists=‘replace’,index=False,index_label=False）

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123@localhost:3306/aaa?charset=utf8mb4")
data.to_sql(name="price",con=engine,if_exists='replace',index=False,index_label=False)

现在验证数据是否已成功写入MySQL中，查询结果如下：

如图所示，数据已成功写入aaa库中。

二、用python添加数据到mysql

2.1 手动插入少量数据

与用python查询数据库一样，依然是通过游标（cusor）来实现添加数据。cursor.executemany可以添加1条或多条数据。
需要注意的是，"insert into aaa values(%s,%s,%s)"中不管数据是什么类型，统一使用%s作为占位符。
在写入sql的关于水果售价的表中继续添加几行数据：

#手动插入少量数据
import pymysql.cursors
connection=pymysql.connect(host='localhost',port=3306,user='root',password='123',db='huanghe',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
#通过cursor创建游标
cursor = connection.cursor()
#插入数据
cursor.executemany(
    "insert into aaa values(%s,%s,%s)",
    [
        (4,'orange',8),
        (5,'lemon',5),
        (6,'strawberry',15)
        ]
    )
connection.commit()

添加数据后的mysql表如下：

2.2 批量插入大量数据

上一种插入方法需要一个个手动输入数据，遇到大一点的数据量会马上扑街…这里提出一种批量插入大量数据的方法，但要在python中需要将这些数据存为dataframe格式，然后再添加到mysql表中。
继续在关于水果售价的表中添加更多的数据：
1、在python中将新的数据data1存为dataframe:


2、只要一行代码就能添加至mysql：

data1.to_sql(name="price",con=engine,if_exists='append',index=False,index_label=False)

to_sql既可以写入数据，也可以添加数据，参数“if_exists”=‘append’时可直接在原表的后面添加数据。

to_sql其他的参数说明:
name: 输出的表名
con: 与read_sql中相同，数据库链接
if_exits： 三种模式：fail，若表存在，则不输出；replace：若表存在，覆盖原来表里的数据；append：若表存在，将数据写到原表的后面。默认为fail。
index：是否将df的index单独写到一列中
index_label:指定列作为df的index输出，此时index为True

总结

to_sql是用python操作mysql的重要函数，批量导入增加数据比较方便。如果数据的格式比较常见，批量插入数据就用dataframe.to_sql()吧。而executemany适合手动插入少量数据，以及数据量较大的数据集格式不方便转为dataframe格式时，通过写循环插入数据。