python3 操作mysql数据库（mysql.connector 和 pymysql ）

左手的ㄟ右手 2021-07-28 12:19 605阅读 0赞

### 1. PyMySQL 的使用 ###

(1) 什么是 PyMySQL？

PyMySQL 是 Python 中用于连接 MySQL 服务器的一个库，它遵循 Python 数据库 API 规范 V2.0，并包含了 pure-Python MySQL 客户端库。

(2) 安装 PyMysql:

pip install PyMysql

(3) 参数说明:

pymysql.Connect()参数说明
    host(str):      MySQL服务器地址
    port(int):      MySQL服务器端口号
    user(str):      用户名
    passwd(str):    密码
    db(str):        数据库名称
    charset(str):   连接编码
    
    connection对象支持的方法
    cursor()        使用该连接创建并返回游标
    commit()        提交当前事务
    rollback()      回滚当前事务
    close()         关闭连接
    
    cursor对象支持的方法
    execute(op)     执行一个数据库的查询命令
    fetchone()      取得结果集的下一行
    fetchmany(size) 获取结果集的下几行
    fetchall()      获取结果集中的所有行
    rowcount()      返回数据条数或影响行数
    close()         关闭游标对象

(4) 使用 PyMySQL:

#!/usr/bin/env python
    # coding=utf-8
    
    import pymysql
    
    def connectdb():
        print('连接到mysql服务器...')
        # 打开数据库连接
        # 用户名:hp, 密码:Hp12345.,用户名和密码需要改成你自己的mysql用户名和密码，并且要创建数据库TESTDB，并在TESTDB数据库中创建好表Student
        db = pymysql.connect("localhost","hp","Hp12345.","TESTDB")
        print('连接上了!')
        return db
    
    def createtable(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # 如果存在表Sutdent先删除
        cursor.execute("DROP TABLE IF EXISTS Student")
        sql = """CREATE TABLE Student (
                ID CHAR(10) NOT NULL,
                Name CHAR(8),
                Grade INT )"""
    
        # 创建Sutdent表
        cursor.execute(sql)
    
    def insertdb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 插入语句
        sql = """INSERT INTO Student
             VALUES ('001', 'CZQ', 70),
                    ('002', 'LHQ', 80),
                    ('003', 'MQ', 90),
                    ('004', 'WH', 80),
                    ('005', 'HP', 70),
                    ('006', 'YF', 66),
                    ('007', 'TEST', 100)"""
    
        #sql = "INSERT INTO Student(ID, Name, Grade) \
        #    VALUES ('%s', '%s', '%d')" % \
        #    ('001', 'HP', 60)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
        except:
            # Rollback in case there is any error
            print '插入数据失败!'
            db.rollback()
    
    def querydb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 查询语句
        #sql = "SELECT * FROM Student \
        #    WHERE Grade > '%d'" % (80)
        sql = "SELECT * FROM Student"
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            for row in results:
                ID = row[0]
                Name = row[1]
                Grade = row[2]
                # 打印结果
                print "ID: %s, Name: %s, Grade: %d" % \
                    (ID, Name, Grade)
        except:
            print "Error: unable to fecth data"
    
    def deletedb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 删除语句
        sql = "DELETE FROM Student WHERE Grade = '%d'" % (100)
    
        try:
           # 执行SQL语句
           cursor.execute(sql)
           # 提交修改
           db.commit()
        except:
            print '删除数据失败!'
            # 发生错误时回滚
            db.rollback()
    
    def updatedb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 更新语句
        sql = "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s'" % ('003')
    
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
        except:
            print '更新数据失败!'
            # 发生错误时回滚
            db.rollback()
    
    def closedb(db):
        db.close()
    
    def main():
        db = connectdb()    # 连接MySQL数据库
    
        createtable(db)     # 创建表
        insertdb(db)        # 插入数据
        print '\n插入数据后:'
        querydb(db) 
        deletedb(db)        # 删除数据
        print '\n删除数据后:'
        querydb(db)
        updatedb(db)        # 更新数据
        print '\n更新数据后:'
        querydb(db)
    
        closedb(db)         # 关闭数据库
    
    if __name__ == '__main__':
        main()

运行结果:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYXRkYXk_size_16_color_FFFFFF_t_70][]

实例2：

import pymysql.cursors
    
    # 连接数据库
    connect = pymysql.connect(
        host='localhost',
        port=3310,
        user='woider',
        passwd='3243',
        db='python',
        charset='utf8'
    )
    
    # 获取游标
    cursor = connect.cursor()
    
    # 插入数据
    sql = "INSERT INTO trade (name, account, saving) VALUES ( '%s', '%s', %.2f )"
    data = ('雷军', '13512345678', 10000)
    cursor.execute(sql % data)
    connect.commit()
    print('成功插入', cursor.rowcount, '条数据')
    
    # 修改数据
    sql = "UPDATE trade SET saving = %.2f WHERE account = '%s' "
    data = (8888, '13512345678')
    cursor.execute(sql % data)
    connect.commit()
    print('成功修改', cursor.rowcount, '条数据')
    
    # 查询数据
    sql = "SELECT name,saving FROM trade WHERE account = '%s' "
    data = ('13512345678',)
    cursor.execute(sql % data)
    for row in cursor.fetchall():
        print("Name:%s\tSaving:%.2f" % row)
    print('共查找出', cursor.rowcount, '条数据')
    
    # 删除数据
    sql = "DELETE FROM trade WHERE account = '%s' LIMIT %d"
    data = ('13512345678', 1)
    cursor.execute(sql % data)
    connect.commit()
    print('成功删除', cursor.rowcount, '条数据')
    
    # 事务处理
    sql_1 = "UPDATE trade SET saving = saving + 1000 WHERE account = '18012345678' "
    sql_2 = "UPDATE trade SET expend = expend + 1000 WHERE account = '18012345678' "
    sql_3 = "UPDATE trade SET income = income + 2000 WHERE account = '18012345678' "
    
    try:
        cursor.execute(sql_1)  # 储蓄增加1000
        cursor.execute(sql_2)  # 支出增加1000
        cursor.execute(sql_3)  # 收入增加2000
    except Exception as e:
        connect.rollback()  # 事务回滚
        print('事务处理失败', e)
    else:
        connect.commit()  # 事务提交
        print('事务处理成功', cursor.rowcount)
    
    # 关闭连接
    cursor.close()
    connect.close()

### 2. mysql.connector 的使用 ###

(1) 什么是 mysql.connector？

由于 MySQL 服务器以独立的进程运行，并通过网络对外服务，所以，需要支持 Python 的 MySQL 驱动来连接到 MySQL 服务器。

目前，有两个 MySQL 驱动：

mysql-connector-python：是 MySQL 官方的纯 Python 驱动；

MySQL-python ：是封装了 MySQL C驱动的 Python 驱动。

(2) 安装 mysql.connector:

pip install mysql-connector-python
    pip install MySQL-python

(3) 使用 mysql.connector:

#!/usr/bin/env python
    # coding=utf-8
    
    import mysql.connector
    
    def connectdb():
        print('连接到mysql服务器...')
        # 打开数据库连接
        # 用户名:hp, 密码:Hp12345.,用户名和密码需要改成你自己的mysql用户名和密码，并且要创建数据库TESTDB，并在TESTDB数据库中创建好表Student
        db = mysql.connector.connect(user="hp", passwd="Hp12345.", database="TESTDB", use_unicode=True)
        print('连接上了!')
        return db
    
    def createtable(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # 如果存在表Sutdent先删除
        cursor.execute("DROP TABLE IF EXISTS Student")
        sql = """CREATE TABLE Student (
                ID CHAR(10) NOT NULL,
                Name CHAR(8),
                Grade INT )"""
    
        # 创建Sutdent表
        cursor.execute(sql)
    
    def insertdb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 插入语句
        sql = """INSERT INTO Student
             VALUES ('001', 'CZQ', 70),
                    ('002', 'LHQ', 80),
                    ('003', 'MQ', 90),
                    ('004', 'WH', 80),
                    ('005', 'HP', 70),
                    ('006', 'YF', 66),
                    ('007', 'TEST', 100)"""
    
        #sql = "INSERT INTO Student(ID, Name, Grade) \
        #    VALUES ('%s', '%s', '%d')" % \
        #    ('001', 'HP', 60)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
        except:
            # Rollback in case there is any error
            print '插入数据失败!'
            db.rollback()
    
    def querydb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 查询语句
        #sql = "SELECT * FROM Student \
        #    WHERE Grade > '%d'" % (80)
        sql = "SELECT * FROM Student"
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            for row in results:
                ID = row[0]
                Name = row[1]
                Grade = row[2]
                # 打印结果
                print "ID: %s, Name: %s, Grade: %d" % \
                    (ID, Name, Grade)
        except:
            print "Error: unable to fecth data"
    
    def deletedb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 删除语句
        sql = "DELETE FROM Student WHERE Grade = '%d'" % (100)
    
        try:
           # 执行SQL语句
           cursor.execute(sql)
           # 提交修改
           db.commit()
        except:
            print '删除数据失败!'
            # 发生错误时回滚
            db.rollback()
    
    def updatedb(db):
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 更新语句
        sql = "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s'" % ('003')
    
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
        except:
            print '更新数据失败!'
            # 发生错误时回滚
            db.rollback()
    
    def closedb(db):
        db.close()
    
    def main():
        db = connectdb()    # 连接MySQL数据库
    
        createtable(db)     # 创建表
        insertdb(db)        # 插入数据
        print '\n插入数据后:'
        querydb(db) 
        deletedb(db)        # 删除数据
        print '\n删除数据后:'
        querydb(db)
        updatedb(db)        # 更新数据
        print '\n更新数据后:'
        querydb(db)
    
        closedb(db)         # 关闭数据库
    
    if __name__ == '__main__':
        main()

运行结果:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYXRkYXk_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYXRkYXk_size_16_color_FFFFFF_t_70]: /images/20210728/0998b4b0823641049884f96297acf130.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYXRkYXk_size_16_color_FFFFFF_t_70 1]: /images/20210728/036cc159e1384b7db4e7ab7789f8a982.png