[Python]mysql-connector

        今天测试工作要将30w数据量的数据放入到表中，前端中因为有限制，一次只能插入1000条。我第一反应居然是用Selenium来搭一个脚本去通过前端傻傻的输入...

        后来被点醒了...因为我们是知道数据库的地址已经拥有账号密码的，可以直接通过一些数据库的驱动来直接通过sql语句来插入...（真的被自己蠢到了

        因为之前写项目用的java（JDBC）比较多，python这边也是第一次接触，就来学习啦。

        其实这些数据库驱动器，步骤都比较统一，连接，写SQL，驱动执行SQL，拿数据/写数据

安装

pip install mysql-connector-python

连接数据库

import mysql.connector

conn = mysql.connector.connect(
    user='root',  # 用户名
    password='',  # 密码
    host='localhost',  # 目标数据库主机地址
    auth_plugin='mysql_native_password'  # 为了兼容其他版本数据库的属性设置
)
print(conn)
conn.close()  # 关闭连接

我一开始用的是mysql-connector，但报错了

plugin 'caching_sha2_password' is not supported

然后改用mysql-conncetor-python后，再设置了“auth_plugin='mysql_native_password'就OK咯

如果连接成功了，就会返回一个连接数据库的信息

<mysql.connector.connection_cext.CMySQLConnection object at 0x000002085B314A50>

失败了，就报对应错误了。

游标cursor

        在使用mysql-connector当中一定会接触到一个概念：游标。

        一句话解决：

游标是一个方便操作数据库的接口对象

提供了，execute方法去执行sql语句，commit方法去提交事务，rollback方法回滚事务。以及有fetchone和fetchall方法去读取sql语句查询的结果。

DDL 

创建数据库

import mysql.connector

conn = mysql.connector.connect(
    user='root',  # 用户名
    password='',  # 密码
    host='localhost',  # 目标数据库主机地址
    auth_plugin='mysql_native_password'  # 为了兼容其他版本数据库的属性设置
)
print(conn)
# 创建一个游标
cursor = conn.cursor()
# 创建数据库sql语句
sql = 'create database testDatabase'
cursor.execute(sql)  # 执行sql语句

cursor.close()  # 关闭游标
conn.close()  # 关闭连接

创建表

        简单一点就先选中目标数据库，再去执行对应的sql。

import mysql.connector

conn = mysql.connector.connect(
    user='root',  # 用户名
    password='',  # 密码
    host='localhost',  # 目标数据库主机地址
    auth_plugin='mysql_native_password',  # 为了兼容其他版本数据库的属性设置
    database='testDatabase'  # 选中数据库
)
print(conn)
# 创建一个游标
cursor = conn.cursor()
# 创建数据库sql语句
sql = 'create table test(id int, name varchar(20), sex varchar(10))'
cursor.execute(sql)  # 执行sql语句

cursor.close()  # 关闭游标
conn.close()  # 关闭连接

        或者是可以先判断库存不存在，让游标去执行use database的操作。如果库不存在就先创建出来。 

DML

可以用游标当中的rowCount方法去读取select中查询的到的行数或者是DML语句中受影响的行数

DELETE

Insert

需要占位符和对应插入的字段数量对应上，使用%s进行占位

数据单条插入

conn = mysql.connector.connect(
        user='root',  # 用户名
        password='',  # 密码
        host='localhost',  # 目标数据库主机地址
        auth_plugin='mysql_native_password',  # 为了兼容其他版本数据库的属性设置
        database='testDatabase'  # 选中数据库
    )
    myCursor = conn.cursor()  # 创建游标
    # 尝试插入的sql语句
    sql = "insert into data(provider, advertiser, status, creator, deleted) values(%s, %s, %s, %s, %s)"
    # 编辑的插入value的元组
    data = ("8197", "Test000", "0", "8197", "0")
    try:
        myCursor.execute(sql, data)  # 单条sql插入
        conn.commit()
        print("插入成功")
    except mysql.connector.Error as err:
        print(err)

数据批量插入

executemany() 

conn = mysql.connector.connect(  # 创建连接
        user='root',  # 用户名
        password='',  # 密码
        host='localhost',  # 目标数据库主机地址
        auth_plugin='mysql_native_password',  # 为了兼容其他版本数据库的属性设置
        database='testDatabase'  # 选中数据库
    )
    myCursor = conn.cursor()  # 创建游标
    # 尝试插入的sql语句
    sql = "insert into data_log(id, type, field_name, old_field, new_field, updater, update_time) values(%s, %s, %s, %s, %s, %s, %s)"
    datas = []  # 存储sql字段对应value元组的列表
    count = 1  # 单次批量插入的计数
    for num in range(500):  # 共插入500条
        if count == 10:  # 单次插入10条，如果满足了，就尝试插入
            try:
                myCursor.executemany(sql, datas)  # 使用批量插入语句
                conn.commit()  # 数据更新了，commit一下
                print(datas)
                print("插入10条数据成功")
            except mysql.connector.Error as err:  # 如果插入不成功的话，就打印错误信息
                print(err)
            count = 1  # 单次10条数据插入成功了，计数重置
            datas = []  # 缓存清空
        else:
            count += 1
            data = ('150', '3', '附件', '', '', '8197', time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))  # 编辑插入数据的元组
            datas.append(data)

Update

    # 连接
    conn = mysql.connector.connect(
        user='root',  # 用户名
        password='',  # 密码
        host='localhost',  # 目标数据库主机地址
        auth_plugin='mysql_native_password',  # 为了兼容其他版本数据库的属性设置
        database='testDatabase'  # 选中数据库
    )
    # 创建游标
    myCursor = conn.cursor()
    # 创建要执行的sql语句，
    sql = "update comment set content = %s where id = %s"
    value = ("修改成功", '1')
    try:
        myCursor.execute(sql, value)
        conn.commit()
        print(myCursor.rowcount, "条数据，update成功")
    except mysql.connector.Error as err:
        print(err)

Delete

    # 创建游标
    myCursor = conn.cursor()
    # 创建要执行的sql语句，
    sql = "delete from test where id = 1"
    try:
        myCursor.execute(sql, value)
        conn.commit()
        print(myCursor.rowcount, "条数据，delete成功")
    except mysql.connector.Error as err:
        print(err)

DQL

fetchall是将游标中缓存的数据全部读取处理，fetchone是将游标中的数据按默认排序读取第一条

Select

myCursor = conn.cursor()  # 创建游标
    sql = "select * from clue_comment where clue_type = 1"
    try:
        myCursor.execute(sql)
        result = myCursor.fetchall()
        # fetchall将select到的数据全部从游标中读出来 读完之后游标里就没有数据了
        for row_data in result:
            print(row_data)
        # 此时再使用fetchone读取，读到的是None
        one_data = myCursor.fetchone()
        print(one_data)
    except mysql.connector.Error as err:
        print(err)