【Python学习笔记】第二十五节 Python MySQL

今天药忘吃喽~ 2023-09-30 19:56 4阅读 0赞

Python 连接到 MySQL 数据库有几种不同的连接方法，而且不是所有的方法都能与不同的操作系统很好地配合.

**MySQL connector/Python**模块是Oracle支持的官方驱动，用于通过Python连接MySQL。该连接器完全是Python语言，而mysqlclient是用C语言编写的。它也是独立的，意味着它不需要MySQL客户端库或标准库以外的任何Python模块。

**注意**，MySQL Connector/Python不支持旧的MySQL服务器认证方法，这意味着4.1之前的MySQL版本不能工作。

本文实例的运行环境：

*  Windows 10

*  MySQL 8.0

--------------------

### 一、创建数据库连接 ###

1、首先安装mysql-connector-python模块。

python -m pip install mysql-connector

![6c6ab074b3db091fe2e83748db9c008f.png][]

2、连接数据库：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root"  # 数据库密码
    )
    
    print(mydb)

运行结果

<mysql.connector.connection.MySQLConnection object at 0x000002B399503AC0>

### **二、创建数据库** ###

1、创建数据库使用 "CREATE DATABASE" 语句，以下创建一个名为 **python\_demo** 的数据库：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root"  # 数据库密码
    )
    
    print(mydb)
    
    myCursor = mydb.cursor()
    
    myCursor.execute("CREATE DATABASE python_demo")

![c1d6b4d6d44d88894a1de142ad2aa0d9.png][]

2、使用 "SHOW DATABASES" 语句来查看数据库是否存在：

![b4f71cab1b34816ea632010ec400a57e.png][]

*  我们可以直接连接数据库，如果数据库不存在，会输出错误信息：

### 三、创建数据表 ###

创建数据表使用 **"CREATE TABLE"** 语句，创建数据表前，需要确保数据库已存在，以下创建一个名为 **user** 自增主键的数据表：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    # print(mydb)
    
    myCursor = mydb.cursor()
    myCursor.execute("CREATE TABLE user (id int(11) primary key not null auto_increment,name VARCHAR(64), mobile VARCHAR(32), address VARCHAR(255))")

![d8570895da3de11a51b09f704bf64e6b.png][]

*  如果我们的表已经创建，我们需要使用 **ALTER TABLE** 来给表添加主键：

### 四、插入数据 ###

*  插入数据使用 **"INSERT INTO"** 语句：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    # print(mydb)
    
    myCursor = mydb.cursor()
    
    sql = "INSERT INTO user (name, mobile,address) VALUES (%s, %s, %s)"
    val = ("Lily", "13866669999", "America")
    myCursor.execute(sql, val)
    mydb.commit()  # 提交
    
    print(myCursor.rowcount, "插入成功。")

![4f6dcc11cc898bbd5da6ec4bffc590cd.png][]

*  **批量插入**

批量插入使用 **executemany()** 方法，该方法的第二个参数是一个元组列表，包含了我们要插入的数据：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    # print(mydb)
    
    myCursor = mydb.cursor()
    
    sql = "INSERT INTO user (name, mobile,address) VALUES (%s, %s, %s)"
    # val = ("Lily", "13866669999", "America")
    
    val = [
        ("LiLei", "13866668888", "China"),
        ("Jim", "13855558888", "America"),
        ("HanMeimei", "13811113333", "China")
    ]
    # myCursor.execute(sql, val)
    myCursor.executemany(sql, val)
    
    mydb.commit()  # 提交
    
    print(myCursor.rowcount, "插入成功。")

3 插入成功。

![842b13c7858d19404f326caf1b7c65c2.png][]

### 五、查询数据 ###

#### 1、查询数据使用 **SELECT** 语句： ####

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    myCursor.execute("SELECT * FROM user")
    
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    
    for x in result:
        print(x)

![ce78cd5750fd1ba6bcf0eb2b266899ef.png][]

*  读取指定的字段数据：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    # myCursor.execute("SELECT * FROM user")
    myCursor.execute("SELECT name, mobile, address FROM user")
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    
    for x in result:
        print(x)

![9cae05fb7bf1dc6d75461f00f49065a4.png][]

*  读取一条数据，可以使用 **fetchone()** 方法：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    # myCursor.execute("SELECT * FROM user")
    myCursor.execute("SELECT name, mobile, address FROM user")
    # result = myCursor.fetchall()  # fetchall() 获取所有记录
    result = myCursor.fetchone()  # fetchall() 获取一条记录
    print(result)

('Lily', '13866669999', 'America')

#### **2、where 条件语句** ####

如果我们要读取指定条件的数据，可以使用 **where** 语句：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    # myCursor.execute("SELECT * FROM user")
    myCursor.execute("SELECT * FROM user where address ='America'")
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    # result = myCursor.fetchone()  # fetchall() 获取一条记录
    # print(result)
    for x in result:
        print(x)

(1, 'Lily', '13866669999', 'America')
    (3, 'Jim', '13855558888', 'America')

*  **为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义查询的条件：**

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    # myCursor.execute("SELECT * FROM user where address ='America'")
    
    sql = "SELECT * FROM user where address = %s"
    param = ("China",)
    
    myCursor.execute(sql, param)
    
    
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    
    for x in result:
        print(x)

(2, 'LiLei', '13866668888', 'China')
    (4, 'HanMeimei', '13811113333', 'China')

#### **3、排序** ####

查询结果排序可以使用 **ORDER BY** 语句，默认的排序方式为升序，关键字为 **ASC**，如果要设置降序排序，可以设置关键字 **DESC**。

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    # myCursor.execute("SELECT * FROM user  ORDER BY name")
    sql = "SELECT * FROM user ORDER BY name"
    myCursor.execute(sql)
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    
    for x in result:
        print(x)

(4, 'HanMeimei', '13811113333', 'China')
    (3, 'Jim', '13855558888', 'America')
    (2, 'LiLei', '13866668888', 'China')
    (1, 'Lily', '13866669999', 'America')

*  降序排序实例：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    # myCursor.execute("SELECT * FROM user  ORDER BY name")
    sql = "SELECT * FROM user ORDER BY address DESC "
    myCursor.execute(sql)
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    
    for x in result:
        print(x)

(2, 'LiLei', '13866668888', 'China')
    (4, 'HanMeimei', '13811113333', 'China')
    (1, 'Lily', '13866669999', 'America')
    (3, 'Jim', '13855558888', 'America')

#### **4、Limit** ####

如果我们要设置查询的数据量，可以通过 **"LIMIT"** 语句来指定

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    # myCursor.execute("SELECT * FROM user  ORDER BY name")
    sql = "SELECT * FROM user LIMIT 2 "
    
    myCursor.execute(sql)
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    
    for x in result:
        print(x)

(1, 'Lily', '13866669999', 'America')
    (2, 'LiLei', '13866668888', 'China')

*  指定起始位置，使用的关键字是 **OFFSET**：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    # sql = "SELECT * FROM user LIMIT 2 "
    sql = "SELECT * FROM user LIMIT 2 OFFSET 1"
    
    myCursor.execute(sql)
    result = myCursor.fetchall()  # fetchall() 获取所有记录
    
    for x in result:
        print(x)

(2, 'LiLei', '13866668888', 'China')
    (3, 'Jim', '13855558888', 'America')

### 六、删除记录 ###

删除记录使用 **"DELETE FROM"** 语句：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    sql = "DELETE FROM user WHERE name = 'Jim'"
    
    myCursor.execute(sql)
    mydb.commit()
    
    print(myCursor.rowcount, " 条记录删除")

1  条记录删除

![be11aeff0d1e27e930175ec44c225af4.png][]

*  **为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义删除语句的条件：**

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    sql = "DELETE FROM user WHERE name = %s"
    param = ("HanMeimei",)
    
    myCursor.execute(sql, param)
    mydb.commit()
    
    print(myCursor.rowcount, " 条记录删除")

1  条记录删除

### 七、更新表数据 ###

数据表更新使用 **"UPDATE"** 语句：

![9399484a60a974e750f06d007fd874e1.png][]

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    sql = "UPDATE user SET name = 'Lucy' WHERE name = 'Lily'"
    
    myCursor.execute(sql)
    mydb.commit()
    
    print(myCursor.rowcount, " 条记录被修改")

1  条记录被修改

![77daa9312cf8329fffa4d814928751e1.png][]

*  **为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义更新语句的条件：**

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    sql = "UPDATE user SET name = %s WHERE name = %s"
    param = ("HanMeimei", "LiLei")
    
    myCursor.execute(sql, param)
    mydb.commit()
    
    print(myCursor.rowcount, " 条记录被修改")

1  条记录被修改

### 八、删除表 ###

删除表使用 **"DROP TABLE"** 语句， **IF EXISTS** 关键字是用于判断表是否存在，只有在存在的情况才删除：

# coding=utf-8
    
    import mysql.connector
    
    mydb = mysql.connector.connect(
        host="127.0.0.1",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="root",  # 数据库密码
        database="python_demo"  # 指定数据库
    )
    
    myCursor = mydb.cursor()
    
    myCursor.execute("SHOW TABLES")
    
    for x in myCursor:
        print(x)
    
    sql = "DROP TABLE IF EXISTS user_info"  # 删除数据表 user_info
    myCursor.execute(sql)
    
    print(" 删除数据表 user_info")
    
    myCursor.execute("SHOW TABLES")
    
    for x in myCursor:
        print(x)

('user',)
    ('user_info',)
     删除数据表 user_info
    ('user',)

[6c6ab074b3db091fe2e83748db9c008f.png]: https://img-blog.csdnimg.cn/img_convert/6c6ab074b3db091fe2e83748db9c008f.png
[c1d6b4d6d44d88894a1de142ad2aa0d9.png]: https://img-blog.csdnimg.cn/img_convert/c1d6b4d6d44d88894a1de142ad2aa0d9.png
[b4f71cab1b34816ea632010ec400a57e.png]: https://img-blog.csdnimg.cn/img_convert/b4f71cab1b34816ea632010ec400a57e.png
[d8570895da3de11a51b09f704bf64e6b.png]: https://img-blog.csdnimg.cn/img_convert/d8570895da3de11a51b09f704bf64e6b.png
[4f6dcc11cc898bbd5da6ec4bffc590cd.png]: https://img-blog.csdnimg.cn/img_convert/4f6dcc11cc898bbd5da6ec4bffc590cd.png
[842b13c7858d19404f326caf1b7c65c2.png]: https://img-blog.csdnimg.cn/img_convert/842b13c7858d19404f326caf1b7c65c2.png
[ce78cd5750fd1ba6bcf0eb2b266899ef.png]: https://img-blog.csdnimg.cn/img_convert/ce78cd5750fd1ba6bcf0eb2b266899ef.png
[9cae05fb7bf1dc6d75461f00f49065a4.png]: https://img-blog.csdnimg.cn/img_convert/9cae05fb7bf1dc6d75461f00f49065a4.png
[be11aeff0d1e27e930175ec44c225af4.png]: https://img-blog.csdnimg.cn/img_convert/be11aeff0d1e27e930175ec44c225af4.png
[9399484a60a974e750f06d007fd874e1.png]: https://img-blog.csdnimg.cn/img_convert/9399484a60a974e750f06d007fd874e1.png
[77daa9312cf8329fffa4d814928751e1.png]: https://img-blog.csdnimg.cn/img_convert/77daa9312cf8329fffa4d814928751e1.png