MySQL篇，第四章：数据库知识4
MySQL 数据库 4
数据备份(在Linux终端操作)
1、命令格式
mysqldump -u用户名 -p 源库名 > 路径/XXX.sql
2、源库名的表示方式
--all-databases 备份所有库
库名 备份单个库
-B 库1 库2 ... 备份多个库
库名 表1 表2 ... 备份指定库的指定表
3、练习
1、备份所有库为all_mysql.sql放到主目录下mydata目录中
2、备份三个库,放到mydata目录中
3、备份省、市、县三张表
ctrl + shift + t^C mkdir mydata ls mydata/ mysqldump -uroot -p db3 > ~/mydata/db3.sql cd mydata/ # mysqldump -uroot -p --all-databases > /home/tarena/mydata/all_mysql.sql # mysqldump -uroot -p -B db1 db2 db3 > /home/tarena/mydata/san_mysql.sql # mysqldump -uroot -p db3 sheng city xian > /home/tarena/mydata/db3_san.sql
数据恢复(在Linux终端操作)
1、命令格式
mysql -uroot -p 目标库名 < 路径/XXX.sql
2、从所有库的备份文件中恢复某一个库(--one-database)
mysql -uroot -p --one-database 目标库名 < 路径/all_mysql.sql
3、练习
1、先备份db3库,删除省、市、县三张表
2、在db3库中创建一张表 t888
create table t888(id int);
3、恢复db3库
4、注意
1、恢复库时如果恢复到原有库会将表中数据覆盖,新增的表不会删除
2、在恢复时如果要恢复的库不存在,则先要创建空库
1 mysql> show databases like 'db3'; 2 +----------------+ 3 | Database (db3) | 4 +----------------+ 5 | db3 | 6 +----------------+ 7 1 row in set (0.00 sec) 8 9 mysql> show databases like '%db3%'; 10 +------------------+ 11 | Database (%db3%) | 12 +------------------+ 13 | db3 | 14 +------------------+ 15 1 row in set (0.00 sec) 16 17 mysql> show databases like '%db%'; 18 +-----------------+ 19 | Database (%db%) | 20 +-----------------+ 21 | db1 | 22 | db2 | 23 | db3 | 24 +-----------------+ 25 3 rows in set (0.00 sec) 26 27 mysql> drop database db3; 28 Query OK, 16 rows affected (3.48 sec) 29 30 mysql> show databases like '%db3%'; 31 Empty set (0.00 sec) 32 33 mysql> 开始恢复 db3 \c 34 mysql> 35 mysql> create database db3; 36 Query OK, 1 row affected (0.03 sec) 37 38 mysql> 1,创建空库\c 39 mysql> use db3 40 Database changed 41 #恢复db3 42 #cd mydata 43 #mysql -uroot -p db3 < db3.sql 44 Enter password: 45 # 46 mysql> show tables; 47 +---------------+ 48 | Tables_in_db3 | 49 +---------------+ 50 | bjtab | 51 | city | 52 | jftab | 53 | new_t2 | 54 | new_t2_t2 | 55 | sheng | 56 | t1 | 57 | t2 | 58 | t3 | 59 | tt1 | 60 | tt2 | 61 | userinfo | 62 | userinfo2 | 63 | userinfo3 | 64 | userinfo4 | 65 | xian | 66 +---------------+ 67 16 rows in set (0.02 sec) 68 69 mysql> select * from sheng; 70 +----+--------+--------------------+ 71 | id | S_ID | S_name | 72 +----+--------+--------------------+ 73 | 1 | 130000 | 河北省 | 74 | 2 | 140000 | 山西省 | 75 | 3 | 150000 | 内蒙古自治区 | 76 | 4 | 160000 | 辽宁省 | 77 | 5 | 170000 | 黑龙江省 | 78 +----+--------+--------------------+ 79 5 rows in set (0.00 sec) 80 81 mysql> select * from city; 82 +----+--------+-----------------+------------+ 83 | id | C_ID | C_name | CFather_ID | 84 +----+--------+-----------------+------------+ 85 | 1 | 131100 | 石家庄市 | 130000 | 86 | 2 | 131101 | 沧州市 | 130000 | 87 | 3 | 131102 | 廊坊市 | 130000 | 88 | 4 | 131103 | 衡水市 | 130000 | 89 | 5 | 131104 | 太原市 | 140000 | 90 | 6 | 131105 | 呼和浩特市 | 150000 | 91 | 7 | 131106 | 包头市 | 150000 | 92 | 8 | 131107 | 沈阳市 | 160000 | 93 | 9 | 131108 | 大连市 | 160000 | 94 | 10 | 131109 | 无锡市 | 320000 | 95 | 11 | 131110 | 徐州市 | 320000 | 96 | 12 | 131111 | 常州市 | 320000 | 97 +----+--------+-----------------+------------+ 98 12 rows in set (0.05 sec) 99 100 mysql> 101 ### 102 mysql> drop database db3; 103 Query OK, 16 rows affected (0.43 sec) 104 105 mysql> 106 mysql> create database db3; 107 Query OK, 1 row affected (0.00 sec) 108 109 mysql> use db3; 110 Database changed 111 # 112 mysql -uroot -p --one-database db3 < ~/mydata/all_mysql.sql 113 # 114 mysql> show tables; 115 +---------------+ 116 | Tables_in_db3 | 117 +---------------+ 118 | bjtab | 119 | city | 120 | jftab | 121 | new_t2 | 122 | new_t2_t2 | 123 | sheng | 124 | t1 | 125 | t2 | 126 | t3 | 127 | tt1 | 128 | tt2 | 129 | userinfo | 130 | userinfo2 | 131 | userinfo3 | 132 | userinfo4 | 133 ##
1 mysql> 2 mysql> use db3; 3 Database changed 4 mysql> show tables; 5 +---------------+ 6 | Tables_in_db3 | 7 +---------------+ 8 | bjtab | 9 | city | 10 | jftab | 11 | new_t2 | 12 | new_t2_t2 | 13 | sheng | 14 | t1 | 15 | t2 | 16 | t3 | 17 | tt1 | 18 | tt2 | 19 | userinfo | 20 | userinfo2 | 21 | userinfo3 | 22 | userinfo4 | 23 | xian | 24 +---------------+ 25 16 rows in set (0.00 sec) 26 27 mysql> drop table sheng,city,xian; 28 Query OK, 0 rows affected (0.29 sec) 29 30 mysql> create table t888(id int); 31 Query OK, 0 rows affected (0.22 sec) 32 33 mysql> show tables; 34 +---------------+ 35 | Tables_in_db3 | 36 +---------------+ 37 | bjtab | 38 | jftab | 39 | new_t2 | 40 | new_t2_t2 | 41 | t1 | 42 | t2 | 43 | t3 | 44 | t888 | 45 | tt1 | 46 | tt2 | 47 | userinfo | 48 | userinfo2 | 49 | userinfo3 | 50 | userinfo4 | 51 +---------------+ 52 14 rows in set (0.00 sec) 53 ## 54 mysql -uroot -p db3 < ~/mydata/db3.sql 55 ## 56 mysql> 恢复之后\c 57 mysql> show tables; 58 +---------------+ 59 | Tables_in_db3 | 60 +---------------+ 61 | bjtab | 62 | city | 63 | jftab | 64 | new_t2 | 65 | new_t2_t2 | 66 | sheng | 67 | t1 | 68 | t2 | 69 | t3 | 70 | t888 | 71 | tt1 | 72 | tt2 | 73 | userinfo | 74 | userinfo2 | 75 | userinfo3 | 76 | userinfo4 | 77 | xian | 78 +---------------+ 79 17 rows in set (0.00 sec) 80 81 mysql> t888还存在\c 82 mysql> select * from sheng; 83 +----+--------+--------------------+ 84 | id | S_ID | S_name | 85 +----+--------+--------------------+ 86 | 1 | 130000 | 河北省 | 87 | 2 | 140000 | 山西省 | 88 | 3 | 150000 | 内蒙古自治区 | 89 | 4 | 160000 | 辽宁省 | 90 | 5 | 170000 | 黑龙江省 | 91 +----+--------+--------------------+ 92 5 rows in set (0.03 sec) 93 94 mysql> select * from city; 95 +----+--------+-----------------+------------+ 96 | id | C_ID | C_name | CFather_ID | 97 +----+--------+-----------------+------------+ 98 | 1 | 131100 | 石家庄市 | 130000 | 99 | 2 | 131101 | 沧州市 | 130000 | 100 | 3 | 131102 | 廊坊市 | 130000 | 101 | 4 | 131103 | 衡水市 | 130000 | 102 | 5 | 131104 | 太原市 | 140000 | 103 | 6 | 131105 | 呼和浩特市 | 150000 | 104 | 7 | 131106 | 包头市 | 150000 | 105 | 8 | 131107 | 沈阳市 | 160000 | 106 | 9 | 131108 | 大连市 | 160000 | 107 | 10 | 131109 | 无锡市 | 320000 | 108 | 11 | 131110 | 徐州市 | 320000 | 109 | 12 | 131111 | 常州市 | 320000 | 110 +----+--------+-----------------+------------+ 111 12 rows in set (0.04 sec) 112 113 mysql> select * from xian; 114 +----+--------+-----------+------------+ 115 | id | X_ID | X_name | XFather_ID | 116 +----+--------+-----------+------------+ 117 | 1 | 132100 | 河东区 | 131100 | 118 | 2 | 132101 | 正定县 | 131100 | 119 | 3 | 132102 | 固安县 | 131102 | 120 | 4 | 132102 | 香河县 | 131102 | 121 | 5 | 132103 | 哈哈 | 131112 | 122 +----+--------+-----------+------------+ 123 5 rows in set (0.00 sec) 124 125 mysql>
ER模型&ER图
1、定义
ER模型即实体-关系模型,ER图即实体-关系图
2、三个概念
1、实体
1、定义：现实世界中任何可以被认知、区分的事物
2、示例
1、学校 ：学生、教师、课程、班主任 ...
2、企业 ：职工、产品
2、属性
1、定义 ：实体所具有的特性
2、示例
1、学生属性：学号、姓名、年龄、性别、专业 ...
2、产品属性：产品编号、产品名称、规格 ...
3、关系
1、定义 ：实体之间的关系
2、分类
一对一关系(1:1) 班级和班长
一对多关系(1:n) 公司和职工、班级和学生
多对多关系(m:n) 学生和课程、商店和顾客
4、ER图的绘制
1、矩形框代表实体, 菱形框代表关系, 椭圆形代表属性
2、示例
学生选课系统
##########
workbench(图形化界面操作数据库) 5.7版本
##########
事务&事务回滚
1、事务定义
一件事从开始发生到结束的整个过程
2、属性
1、原子性：一个事务是不可分割的工作单位,事务中的各个操作要么都做,要么就都不做
2、一致性：事务必须从一个一致性状态到另一个一致性状态
3、隔离性：一个事务的执行不能被其他并发事务干扰
4、持久性：一个事务一旦提交,它对数据库的改变是永久性的
3、事务及事务回滚的应用
1、mysql中默认sql语句会自动commit到数据库
show variables like "autocommit";
2、事务应用
1、开启事务
mysql> start transaction;
## 此时autocommit被禁用,SQL命令不会对数据库中数据做修改
2、终止事务
mysql> commit; （提交） | rollback; （回滚，什么也没做）
3、注意
事务&事务回滚只针对对表记录的操作:增加、删除、修改,对创建库、创建表是无效的
4、案例
1、背景
你 ： 建行卡
你朋友：工商卡
你在建行的自动提款机给你朋友(工商银行卡)转账
2、过程
表1、建行：CCB
表2、工行：ICBC
###开始转账
mysql>start transaction;
mysql>update CCB set money=95000 where name="你";
mysql>update ICBC set ... 断电了...;
mysql>rollback
###验证
mysql>select * from CCB;
1 mysql> show variables like 'autocommit'; 2 +---------------+-------+ 3 | Variable_name | Value | 4 +---------------+-------+ 5 | autocommit | ON | 6 +---------------+-------+ 7 1 row in set (0.98 sec) 8 9 mysql> start transaction; 10 Query OK, 0 rows affected (0.04 sec) 11 12 mysql> show variables like 'autocommit'; 13 +---------------+-------+ 14 | Variable_name | Value | 15 +---------------+-------+ 16 | autocommit | ON | 17 +---------------+-------+ 18 1 row in set (0.00 sec) 19 20 mysql> show tables; 21 +---------------+ 22 | Tables_in_db3 | 23 +---------------+ 24 | bjtab | 25 | city | 26 | jftab | 27 | new_t2 | 28 | new_t2_t2 | 29 | sheng | 30 | t1 | 31 | t2 | 32 | t3 | 33 | t888 | 34 | tt1 | 35 | tt2 | 36 | userinfo | 37 | userinfo2 | 38 | userinfo3 | 39 | userinfo4 | 40 | xian | 41 +---------------+ 42 17 rows in set (0.04 sec) 43 44 mysql> insert into t888 values(1),(2),(3); 45 Query OK, 3 rows affected (0.27 sec) 46 Records: 3 Duplicates: 0 Warnings: 0 47 48 mysql> commit; 49 Query OK, 0 rows affected (0.00 sec) 50 51 mysql> 52 [1]+ 已停止 mysql -uroot -p123456 53 tarena@tedu:~$ mysql -uroot -p123456 54 mysql: [Warning] Using a password on the command line interface can be insecure. 55 Welcome to the MySQL monitor. Commands end with ; or \g. 56 Your MySQL connection id is 20 57 Server version: 5.7.22-0ubuntu0.16.04.1 (Ubuntu) 58 59 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 60 61 Oracle is a registered trademark of Oracle Corporation and/or its 62 affiliates. Other names may be trademarks of their respective 63 owners. 64 65 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 66 67 mysql> ues db3; 68 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 'ues db3' at line 1 69 mysql> 70 mysql> 71 mysql> use db3; 72 Reading table information for completion of table and column names 73 You can turn off this feature to get a quicker startup with -A 74 75 Database changed 76 mysql> start transaction; 77 Query OK, 0 rows affected (0.00 sec) 78 79 mysql> update t888 set id=10 where id=1; 80 Query OK, 1 row affected (0.00 sec) 81 Rows matched: 1 Changed: 1 Warnings: 0 82 83 mysql> insert into t888 values(20); 84 Query OK, 1 row affected (0.00 sec) 85 86 mysql> roolback; 87 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 'roolback' at line 1 88 mysql> 89 mysql> rollback; 90 Query OK, 0 rows affected (0.07 sec) 91 92 mysql> 回滚\c 93 mysql> 94 mysql> start transtaction; 95 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 'transtaction' at line 1 96 mysql> start transaction; 97 Query OK, 0 rows affected (0.00 sec) 98 99 mysql> create table t999(id int); 100 Query OK, 0 rows affected (0.55 sec) 101 102 mysql> create database dbover; 103 Query OK, 1 row affected (0.02 sec) 104 105 mysql> rollback; 106 Query OK, 0 rows affected (0.00 sec) 107 108 mysql> 109 ############## 110 mysql> use db3; 111 Reading table information for completion of table and column names 112 You can turn off this feature to get a quicker startup with -A 113 114 Database changed 115 mysql> select * from t888; 116 Empty set (0.00 sec) 117 118 mysql> select * from t888; 119 +------+ 120 | id | 121 +------+ 122 | 1 | 123 | 2 | 124 | 3 | 125 +------+ 126 3 rows in set (0.00 sec) 127 128 mysql> mysql> select * from t888; 129 +------+ 130 | id | 131 +------+ 132 | 1 | 133 | 2 | 134 | 3 | 135 +------+ 136 3 rows in set (0.00 sec) 137 138 mysql> show tables; 139 +---------------+ 140 | Tables_in_db3 | 141 +---------------+ 142 | bjtab | 143 | city | 144 | jftab | 145 | new_t2 | 146 | new_t2_t2 | 147 | sheng | 148 | t1 | 149 | t2 | 150 | t3 | 151 | t888 | 152 | t999 | 153 | tt1 | 154 | tt2 | 155 | userinfo | 156 | userinfo2 | 157 | userinfo3 | 158 | userinfo4 | 159 | xian | 160 +---------------+ 161 18 rows in set (0.00 sec) 162 163 mysql> show databases; 164 +--------------------+ 165 | Database | 166 +--------------------+ 167 | information_schema | 168 | MoShou | 169 | SANGUO | 170 | db1 | 171 | db2 | 172 | db3 | 173 | dbover | 174 | mysql | 175 | performance_schema | 176 | sys | 177 +--------------------+ 178 10 rows in set (0.00 sec) 179 180 mysql> show tables like 't999'; 181 +----------------------+ 182 | Tables_in_db3 (t999) | 183 +----------------------+ 184 | t999 | 185 +----------------------+ 186 1 row in set (0.04 sec) 187 188 mysql> show database like 'dbover'; 189 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 'database like 'dbover'' at line 1 190 mysql> show databases like 'dbover'; 191 +-------------------+ 192 | Database (dbover) | 193 +-------------------+ 194 | dbover | 195 +-------------------+ 196 1 row in set (0.03 sec) 197 198 mysql>
1 mysql> create table CCB 2 -> (name varchar(20), 3 -> money int 4 -> )default charset=utf8; 5 Query OK, 0 rows affected (0.15 sec) 6 7 mysql> insert into CCB values('转钱',100000); 8 Query OK, 1 row affected (0.00 sec) 9 10 mysql> create table ICBC( 11 -> name varchar(20), 12 -> money int 13 -> )default charset=utf8; 14 Query OK, 0 rows affected (0.12 sec) 15 16 mysql> insert into ICBC values('借钱',2000); 17 Query OK, 1 row affected (0.04 sec) 18 19 mysql> 开始转账\c 20 mysql> start transaction; 21 Query OK, 0 rows affected (0.00 sec) 22 23 mysql> update CCB set money=95000 where name='转钱'; 24 Query OK, 1 row affected (0.00 sec) 25 Rows matched: 1 Changed: 1 Warnings: 0 26 27 mysql> update ICBC set money=断电了，宕机了； 28 -> ； 29 -> 30 -> ; 31 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 '；' at line 3 32 mysql> 33 mysql> rollback; 34 Query OK, 0 rows affected (0.01 sec) 35 36 mysql> select * from CCB; 37 +--------+--------+ 38 | name | money | 39 +--------+--------+ 40 | 转钱 | 100000 | 41 +--------+--------+ 42 1 row in set (0.00 sec) 43 44 mysql> select * from ICBC; 45 +--------+-------+ 46 | name | money | 47 +--------+-------+ 48 | 借钱 | 2000 | 49 +--------+-------+ 50 1 row in set (0.00 sec) 51 52 mysql>
Python数据库编程
1、python数据库接口(Python DB-API)
1、定义：为开发人员提供的数据库应用编程接口
2、支持的数据库服务软件
MySQL、Oracle、SQL_Server、Sybase、Mongodb ...
3、Python提供的操作MySQL的模块
模块名：pymysql (python3) ,mysqldb(python2)
4、pymysql的使用流程
1、建立数据库连接
2、创建游标对象
3、使用游标对象的方法和SQL语句操控MySQL数据库
4、提交commit
5、关闭游标
6、关闭数据库连接
5、建立数据库连接
1、语法格式
对象名(db) = pymysql.connect("主机地址","用户名","密码","库名",charset="utf8")
2、connect对象(db)的方法
1、cursor() 创建一个游标对象db.cursor()
2、commit() 提交到数据库 db.commit()
3、rollback() 回滚 db.rollback()
4、close() 关闭与数据库的连接 db.close()
3、cursor游标对象(cur)的方法
1、execute() 执行sql命令
2、fetchone() 取得结果集的第一条记录
3、fetchmany(数字) 取得结果集的 几条 记录
4、fetchall() 取得结果集的所有行
5、close() 关闭游标对象
属性：rowcount : 返回命令执行所影响的条数( cur.rowcount )
pymysql.connect --->>db ---> db.cursor() ----> 执行
1 import pymysql 2 3 #1,打开数据库连接 4 db = pymysql.connect("localhost",'root', 5 '123456',charset="utf8") 6 #创建一个游标对象 7 cur = db.cursor() 8 #创建库 9 cur.execute("create database if not exists python;") 10 #切换库 11 cur.execute("use python;") 12 #创建表 13 cur.execute("create table if not exists t1(\ 14 id int primary key,\ 15 name varchar(20),\ 16 score tinyint unsigned)default charset=utf8;") 17 #在t1中插入５条记录 18 cur.execute("insert into t1 values\ 19 (1,'貂蝉',88),\ 20 (2,'赵子龙',100),\ 21 (3,'诸葛亮',80),\ 22 (4,'张飞',60),\ 23 (5,'司马懿',99);") 24 #提交到数据库 25 db.commit() 26 #关闭游标 27 cur.close() 28 #关闭数据库 29 db.close() 30 ##### 31 mysql> show databases; 32 +--------------------+ 33 | Database | 34 +--------------------+ 35 | information_schema | 36 | MoShou | 37 | SANGUO | 38 | db1 | 39 | db2 | 40 | db3 | 41 | dbover | 42 | mysql | 43 | performance_schema | 44 | python | 45 | sys | 46 +--------------------+ 47 11 rows in set (0.01 sec) 48 49 mysql> use python; 50 Reading table information for completion of table and column names 51 You can turn off this feature to get a quicker startup with -A 52 53 Database changed 54 mysql> show tables; 55 +------------------+ 56 | Tables_in_python | 57 +------------------+ 58 | t1 | 59 +------------------+ 60 1 row in set (0.03 sec) 61 62 mysql> select * from t1; 63 +----+-----------+-------+ 64 | id | name | score | 65 +----+-----------+-------+ 66 | 1 | 貂蝉 | 88 | 67 | 2 | 赵子龙 | 100 | 68 | 3 | 诸葛亮 | 80 | 69 | 4 | 张飞 | 60 | 70 | 5 | 司马懿 | 99 | 71 +----+-----------+-------+ 72 5 rows in set (0.04 sec) 73 74 mysql> desc t1; 75 +-------+---------------------+------+-----+---------+-------+ 76 | Field | Type | Null | Key | Default | Extra | 77 +-------+---------------------+------+-----+---------+-------+ 78 | id | int(11) | NO | PRI | NULL | | 79 | name | varchar(20) | YES | | NULL | | 80 | score | tinyint(3) unsigned | YES | | NULL | | 81 +-------+---------------------+------+-----+---------+-------+ 82 3 rows in set (0.12 sec) 83 84 mysql>
fetchone
1 import pymysql 2 # 3 db = pymysql.connect('localhost','root','123456', 4 'python',charset='utf8') 5 #创建游标 6 cur = db.cursor() 7 #mysql语句 8 sql_select = 'select * from t1;' 9 cur.execute(sql_select) 10 11 data = cur.fetchone() 12 print('fetchone的结果为：',data) 13 14 db.commit() 15 cur.close() 16 db.close() 17 ### 18 $ python3 Fetchone.py 19 fetchone的结果为： (1, '貂蝉', 88)
Fetchmany-Fetchall
1 import pymysql 2 3 db = pymysql.connect('localhost','root','123456',\ 4 'python',port=3306,charset='utf8') 5 cur = db.cursor() 6 sql_select = 'select * from t1;' 7 cur.execute(sql_select) 8 9 data = cur.fetchmany(2) 10 print("fetchmany的结果是：",data) 11 12 data2 = cur.fetchall() 13 print('fetchall的结果为：',data2) 14 15 db.commit() 16 cur.close() 17 db.close() 18 #### 19 python3 Fetchmany-all.py 20 fetchmany的结果是： ((1, '貂蝉', 88), (2, '赵子龙', 100)) 21 fetchall的结果为： ((3, '诸葛亮', 80), (4, '张飞', 60), (5, '司马懿', 99))
test
cur.rowcount
1 import pymysql 2 3 db = pymysql.connect('localhost','root','123456', \ 4 'python',port=3306,charset='utf8') 5 cur = db.cursor() 6 sql_select = 'select * from t1;' 7 cur.execute(sql_select) 8 print('select记录的个数：',cur.rowcount) 9 #### 10 $ python3 Fetchmany-all.py 11 select记录的个数： 5
rowback
1 import pymysql 2 3 db = pymysql.connect('localhost','root','123456' \ 4 'db3',charset='utf8') 5 cur = db.cursor() 6 7 try: 8 cur.execute('update CCB set money=95000 \ 9 where name="转钱";') 10 cur.execute('update ICBC set money=7000 \ 11 where name="借钱";') 12 db.commit() 13 print('ok') 14 except Exception as e: 15 print(e) 16 db.rollback() 17 18 #db.commit() 19 cur.close() 20 db.close()
python3 pyrollback.py ok :~/python-mysql$ python3 pyrollback.py (1064, '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 \'=7000 where name="借钱"\' at line 1') :~/python-mysql$ python3 pyrollback.py (1064, '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 \'=7000 where name="借钱"\' at line 1') 出现错误，已经回滚。 :~/python-mysql$
fengzhuang
1 from pymysql import * 2 3 class MysqlPython: 4 def __init__(self,host,port,db,user, \ 5 passwd,charset='utf8'): 6 self.host = host 7 self.port = port 8 self.db = db 9 self.user = user 10 self.passwd = passwd 11 self.charset = charset 12 13 def open(self): 14 self.con = connect(host=self.host, \ 15 port=self.port,db=self.db, \ 16 user=self.user,passwd=self.passwd, \ 17 charset=self.charset) 18 self.cursor = self.con.cursor() 19 20 def close(self): 21 self.cursor.close() 22 self.con.close() 23 24 def zhixing(self,sql): 25 self.open() 26 self.cursor.execute(sql) 27 self.con.commit() 28 self.close() 29 30 def all(self,sql): 31 try: 32 self.open() 33 self.cursor.execute(sql) 34 data = self.cursor.fetchall() 35 self.close() 36 37 return data 38 except Exception as e: 39 print(e) 40 #### 41 42 from MysqlPython import MysqlPython 43 #update 44 name = input("请输入要修改的学生姓名：") 45 score = int(input("请输入该学生的新成绩：")) 46 sql = "update t1 set score='%s' where name='%s'" \ 47 %(score,name) 48 sqlH = MysqlPython('localhost',3306,'python', \ 49 'root','123456') 50 sqlH.zhixing(sql)
