目标：
数据库的相关操作
pymysql的相关操作
使用mysql- 命令提示符下，启动mysql
C:\Users\python_hui>mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- 查看当前数据库的服务器有哪些库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| python |
| python02 |
| python05 |
| sys |
+--------------------+
7 rows in set (0.02 sec)
mysql>
- 创建数据库，查询建表语
mysql> create database python2019;
Query OK, 1 row affected (0.01 sec)
mysql> show create database python2019;
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| python2019 | CREATE DATABASE `python2019` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
- 创建数据表，创建一个学生表
mysql> create table student(
-> id int unsigned primary key auto_increment not null,
-> name varchar(20) not null,
-> gender enum("男","女","中性","保密") default "保密",
-> teacher_id int
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc student;
+------------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| teacher_id | int(11) | YES | | NULL | |
+------------+-------------------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql>
英文
un 代表否定，例如 happy 快乐， unhappy 不快乐
sign 代表符号
ed 代表过去时
- 使用sql语句插入两条数据试试感觉
根据坐位从右边的同学开始插起
mysql> insert into student(id, name, gender, teacher_id) values (1,"子粘","男",1), (2, "盆凯","男",1);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from student;
+----+------+--------+------------+
| id | name | gender | teacher_id |
+----+------+--------+------------+
| 1 | 子粘 | 男 | 1 |
| 2 | 盆凯 | 男 | 1 |
+----+------+--------+------------+
2 rows in set (0.00 sec)
mysql>
试过了手感后，感觉sql语句还没有忘光
于是，决定通过python来操作数据库了
python操作mysql-增加数据- 先试着连接数据库
# 导入pymysql模块
import pymysql
# 建立本机的数据库连接，获得连接对象
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
passwd="root",
db="python2019",
charset="utf8"
)
print(conn)
结果
C:\Users\python_hui\Anaconda3\python.exe G:/19-前端-第二回/0807python与mysql/main.py
<pymysql.connections.Connection object at 0x000002B51D5CEE10>
Process finished with exit code 0
- 使用pymysql再来插入几条数据
# 获取游标对象，需要字典形态的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# SQL语句
sql = "insert into student values(0,'成翔','男',1)"
affect_num = cursor.execute(sql)
print(sql, affect_num)
C:\Users\python_hui\Anaconda3\python.exe G:/19-前端-第二回/0807python与mysql/main.py
<pymysql.connections.Connection object at 0x00000128AE186940>
insert into student values(0,‘成翔’,‘男’,1) 1
Process finished with exit code 0
- 关于0的用法
如果有一个字段是自动增长的
我们可以给他一个0，代表着放弃人为的操作，让他自动增长
同样，还可以有其它方式，放弃人为操作，让自动增长
设置为 default
不过得注意，这样子添加的数据还没有到数据库中
mysql> select * from student;
+----+------+--------+------------+
| id | name | gender | teacher_id |
+----+------+--------+------------+
| 1 | 子粘 | 男 | 1 |
| 2 | 盆凯 | 男 | 1 |
+----+------+--------+------------+
2 rows in set (0.00 sec)
mysql>
需要让连接对象commit一下才可以
# 获取游标对象，需要字典形态的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# SQL语句
sql = "insert into student values(0,'呈祥','男',1)"
affect_num = cursor.execute(sql)
print(sql, affect_num)
conn.commit()
print("提交成功")
C:\Users\python_hui\Anaconda3\python.exe G:/19-前端-第二回/0807python与mysql/main.py
<pymysql.connections.Connection object at 0x0000024360BB6940>
insert into student values(0,‘呈祥’,‘男’,1) 1
提交成功
Process finished with exit code 0
再来查数据库
mysql> select * from student;
+----+------+--------+------------+
| id | name | gender | teacher_id |
+----+------+--------+------------+
| 1 | 子粘 | 男 | 1 |
| 2 | 盆凯 | 男 | 1 |
| 5 | 呈祥 | 男 | 1 |
+----+------+--------+------------+
3 rows in set (0.00 sec)
mysql>
发现一个现象
之前虽然查询了，但是连接没有提交
再次执行sql语句，插入的序号却发生了跳过
- 关注，execute的第二参数
# SQL语句
sql = "insert into student values(0,%s,%s,1)"
affect_num = cursor.execute(sql,["牛劲", "男"])
继续
# SQL语句
sql = "insert into student values(0,%s,%s,%s)"
affect_num = cursor.execute(sql,["王小朋", "男", 1])
经过两次的实验，数据已经可以通过pymysql插入了
接下来，尝试查询
在此之前，先记录完整代码
# 导入pymysql模块
import pymysql
# 建立本机的数据库连接，获得连接对象
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
passwd="root",
db="python2019",
charset="utf8"
)
print(conn)
# 获取游标对象，需要字典形态的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# SQL语句
sql = "insert into student values(0,%s,%s,%s)"
affect_num = cursor.execute(sql,["王小朋", "男", 1])
print(sql, affect_num)
conn.commit()
print("提交成功")
- 查询所有的男生
# 获取游标对象，需要字典形态的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# SQL语句
sql = "select * from student where gender = '男';"
affect_num = cursor.execute(sql)
print(sql, affect_num)
# 查询结果
select_res = cursor.fetchall()
print(select_res)
[{‘id’: 1, ‘name’: ‘子粘’, ‘gender’: ‘男’, ‘teacher_id’: 1}, {‘id’: 2, ‘name’: ‘盆凯’, ‘gender’: ‘男’, ‘teacher_id’: 1}, {‘id’: 5, ‘name’: ‘呈祥’, ‘gender’: ‘男’, ‘teacher_id’: 1}, {‘id’: 6, ‘name’: ‘牛劲’, ‘gender’: ‘男’, ‘teacher_id’: 1}, {‘id’: 7, ‘name’: ‘王小朋’, ‘gender’: ‘男’, ‘teacher_id’: 1}]
- 查询语句变量化
sql = "select * from student where %s = %s;"
affect_num = cursor.execute(sql %("gender", "'男'"))
注意，传参数男的时候，男被包了一层单引号，外面又被包了一层双引号的
修改数据- 修改数据的名称，在mysql中
mysql> update student set name = 朋小王 where id = 7;
ERROR 1054 (42S22): Unknown column '朋小王' in 'field list'
mysql> update student set name = '朋小王' where id = 7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+--------+------------+
| id | name | gender | teacher_id |
+----+--------+--------+------------+
| 1 | 子粘 | 男 | 1 |
| 2 | 盆凯 | 男 | 1 |
| 5 | 呈祥 | 男 | 1 |
| 6 | 牛劲 | 男 | 1 |
| 7 | 朋小王 | 男 | 1 |
+----+--------+--------+------------+
5 rows in set (0.00 sec)
mysql>
注意，字段 = 值 的时候，这里加上了一个引号的，否则就会报错
为了不浪费，先加一个数据，然后再删
添加一个数据
mysql> insert into student values("悟空", "保密", 2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into student values(0, "悟空", "保密", 2);
Query OK, 1 row affected (0.01 sec)
mysql>
注意，此法传参，参数要带全，自增长的键可以用 null 0 default 三个词来代替
删除数据原生mysql的操作
mysql> delete student where id =8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where id =8' at line 1
mysql> delete from student where id =8;
Query OK, 1 row affected (0.01 sec)
再插入一条数据
mysql> insert into student(name) values("悟空");
Query OK, 1 row affected (0.01 sec)
pymysql 删除
# 获取游标对象，需要字典形态的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# SQL语句
# delete student where id =8
# sql = "delete from student where name = '悟空'"
sql = "delete from student where %s = %s"
affect_num = cursor.execute(sql %("name", "'悟空'"))
print(sql, "--", affect_num)
# 连接对象提交操作
conn.commit()
print("操作成功")
但凡是对数据表有，添加，删除，修改操作
都一定要让表连接对象，commit一下
结束接下来，我们要把这个功能应用到pygame模块中使用
