python模块 -- pymysql

Python操作MySQL数据库

pymysql安装

[root@gyl-huawei ~]# pip3 install pymysql

Collecting pymysql

  Downloading PyMySQL-1.0.2-py3-none-any.whl (43 kB)

     |████████████████████████████████| 43 kB 410 kB/s

Installing collected packages: pymysql

Successfully installed pymysql-1.0.2

pymysql使用流程

1. 建立数据库连接(db = pymysql.connect)
2. 创建游标对象（c = db.cursor()）
3. 游标方法：c.execute(“insert”)
4. 提交到数据库：db.commit()
5. 关闭游标对象：c.close()
6. 断开数据库连接：db.close()

常用函数

1) db = pymysql.connect(参数列表)

1. host：主机地址/本地localhost
2. port: 端口号，默认3306
3. user：用户名
4. password: 密码
5. database: 库
6. charset：编码方式，推荐使用utf8

2) 数据库连接对象(db)的方式

• db.commit() 提交到数据库执行
• db.rollback() 回滚
• cur = db.cursor() 返回游标对象，用于执行具体SQL命令
• db.close() 关闭连接

3) 游标对象(cur)的方法

• cur.execute(sql命令，[列表])执行SQL命令
• cur.closes() 关闭游标对象
• cur.fetchone() 获取查询结果集的第一条数据。
• cur.fetchmany(n) 获取n条记录
• cur.fetchall() 获取所有记录

• 准备数据库：

• mysql> desc class;
  +------------+------------------+------+-----+---------+----------------+
  | Field      | Type             | Null | Key | Default | Extra          |
  +------------+------------------+------+-----+---------+----------------+
  | id         | int              | NO   | PRI | NULL    | auto_increment |
  | name       | varchar(30)      | NO   |     | NULL    |                |
  | age        | tinyint unsigned | NO   |     | NULL    |                |
  | gender     | enum('M','F')    | YES  |     | NULL    |                |
  | score      | float            | YES  |     | 0       |                |
  | entry_year | date             | YES  |     | NULL    |                |
  +------------+------------------+------+-----+---------+----------------+
  6 rows in set (0.00 sec)
  
  mysql> select * from class;
  +----+---------+-----+--------+-------+------------+
  | id | name    | age | gender | score | entry_year |
  +----+---------+-----+--------+-------+------------+
  |  1 | Zss     |  18 | F      |    70 | 2022-03-30 |
  |  3 | Wangwu  |  17 | F      |    91 | 2022-01-30 |
  |  4 | zhaoliu |  16 | M      |    82 | 2022-01-30 |
  |  5 | Aqi     |  20 | F      |    59 | 2022-03-31 |
  |  6 | Xiaoban |  25 | M      |     0 | NULL       |
  |  7 | LiLei   |  22 | F      |     0 | NULL       |
  +----+---------+-----+--------+-------+------------+
  6 rows in set (0.00 sec)

数据库查询代码：

import pymysql

# 连接数据库
db = pymysql.connect(host='192.168.1.10',
                     port=3306,
                     user='dbuser',
                     password='@abcde12345',
                     database='student',
                     charset="utf8mb4")

# 获取游标(操作数据库， 执行sql语句)
cur = db.cursor()

sql = """
    select * from class;
"""
print("=" * 20)
print("取一条记录：")
cur.execute(sql)  # 执行语句
one_row = cur.fetchone()
print(one_row)

print("=" * 20)
print("取多条记录：")
cur.execute(sql)
many_row = cur.fetchmany(2)
print(many_row)

print("=" * 20)
print("取所条记录：")
cur.execute(sql)
all_row = cur.fetchall()
print(all_row)

cur.close()
db.close()

执行结果：

====================
取一条记录：
(1, 'Zhangsan', 14, 'M', 70.0, datetime.date(2022, 3, 30))
====================
取多条记录：
((1, 'Zhangsan', 14, 'M', 70.0, datetime.date(2022, 3, 30)), (3, 'Wangwu', 17, 'F', 91.0, datetime.date(2022, 1, 30)))
====================
取所条记录：
((1, 'Zhangsan', 14, 'M', 70.0, datetime.date(2022, 3, 30)), (3, 'Wangwu', 17, 'F', 91.0, datetime.date(2022, 1, 30)), (4, 'zhaoliu', 16, 'M', 82.0, datetime.date(2022, 1, 30)), (5, 'Aqi', 20, 'F', 59.0, datetime.date(2022, 3, 31)), (6, 'Xiaoban', 25, 'M', 0.0, None))

数据库插入代码：

import pymysql

# 连接数据库
db = pymysql.connect(host='192.168.1.10',
                     port=3306,
                     user='dbuser',
                     password='@abcde12345',
                     database='student',
                     charset="utf8mb4")

# 获取游标(操作数据库， 执行sql语句)
cur = db.cursor()

sql = """
    insert into class (name, age ,gender) values ('%s','%d', '%s')
"""

try:
    name = input("请输入姓名：")
    age = int(input("请输入年龄："))
    gender = input("请输入性别(M|F)：")
    sqlcommit = sql % (name, age, gender)

    cur.execute(sqlcommit)
    db.commit()
except Exception as e:
    db.rollback()
    print(e)

cur.close()
db.close()

执行以上python代码：

D:\PycharmProjects\pythonstart\venv\Scripts\python.exe D:/PycharmProjects/pythonstart/lession3/mysql/insert_db.py
请输入姓名：LiLei
请输入年龄：22
请输入性别(M|F)：F

Process finished with exit code 0

数据库上查询结果：

mysql> select * from class;
+----+----------+-----+--------+-------+------------+
| id | name     | age | gender | score | entry_year |
+----+----------+-----+--------+-------+------------+
|  1 | Zhangsan |  14 | M      |    70 | 2022-03-30 |
|  3 | Wangwu   |  17 | F      |    91 | 2022-01-30 |
|  4 | zhaoliu  |  16 | M      |    82 | 2022-01-30 |
|  5 | Aqi      |  20 | F      |    59 | 2022-03-31 |
|  6 | Xiaoban  |  25 | M      |     0 | NULL       |
|  7 | LiLei    |  22 | F      |     0 | NULL       |
+----+----------+-----+--------+-------+------------+
6 rows in set (0.00 sec)

数据表修改代码：

import pymysql

db = pymysql.connect(host='192.168.1.10',
                     port=3306,
                     user='dbuser',
                     password='@abcde12345',
                     database='student',
                     charset="utf8mb4")


# 获取游标(操作数据库， 执行sql语句)
cur = db.cursor()

sql = """
    select * from class where id=%d;
"""
sid = int(input("请输入要修改的ID号："))
sqlstring = sql % sid

print("修改前：")
cur.execute(sqlstring)  # 执行语句
print(cur.fetchone())

sql2 = """
    update class set name='%s',age=%s,gender='%s' where id=%d
"""
try:
    name = input("请输入新的新名：")
    age = input("请输入新的年龄:")
    gender = input("请输入新的性别(M|F):")
    sqlstring2 = sql2 % (name, age, gender, sid)
    cur.execute(sqlstring2)
    db.commit()
except Exception as e:
    db.rollback()
    print(e)

print("修改后：")
cur.execute(sqlstring)  # 执行语句
print(cur.fetchone())

cur.close()
db.close()

执行以上python代码：

D:\PycharmProjects\pythonstart\venv\Scripts\python.exe D:/PycharmProjects/pythonstart/lession3/mysql/select_id.py
请输入要修改的ID号：1
修改前：
(1, 'Zhangsan', 14, 'M', 70.0, datetime.date(2022, 3, 30))
请输入新的新名：Zss
请输入新的年龄:18
请输入新的性别(M|F):F
修改后：
(1, 'Zss', 18, 'F', 70.0, datetime.date(2022, 3, 30))

Process finished with exit code 0