MySQL(10)-Python与MySQL的交互-
时间:2022-05-24 作者:gltou
域名L驱动模块Connector的语法
1.1.下载驱动
进入官网下载对应版本驱动
1.2.创建连接
方式一:
import 域名ector con = 域名ect( host="localhost",port="3306",user="root",password="123456",database="vega" ) 域名e() #运行脚本,不报错,连接成功方式二:
import 域名ector config = { "host":"localhost", "port":3306, "user":"root", "password":"123456", "database":"vega" } con = 域名ect(**config) #运行脚本,不报错,连接成功1.3.创建游标
创建游标,执行SQL语句
1 import 域名ector 2 #创建连接 3 config = { 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"vega" 9 } 10 con = 域名ect(**config) 11 #创建游标 12 cursor=域名or() 13 #编写sql 14 sql = "SELECT * FROM t_role;" 15 #通过游标执行sql 16 域名ute(sql) 17 #打印最后结果 18 for i in cursor: 19 print(i) 20 \'\'\' 21 输出结果 22 (2, \'新闻编辑\') 23 (1, \'管理员\') 24 \'\'\' 25 print(cursor,type(cursor)) #CMySQLCursor: SELECT * FROM t_role; <class \'域名域名QLCursor\'>
域名注入攻击案例
1 import 域名ector 2 3 #创建连接 4 config = { 5 "host":"localhost", 6 "port":3306, 7 "user":"root", 8 "password":"123456", 9 "database":"vega" 10 } 11 con = 域名ect(**config) 12 13 #系统登录用户信息 14 username="1 OR 1=1" 15 password="1 OR 1=1" 16 17 #创建游标 18 cursor=域名or() 19 #编写sql 20 sql = "SELECT COUNT(*) FROM t_user WHERE username= %s" \ 21 " AND AES_DECRYPT(UNHEX(password),\'HelloWorld\')=%s"; 22 #通过游标执行sql 23 域名ute(sql%(username,password)) 24 #打印最后结果 25 print(域名hone()[0]) #2 26 域名e()
1 import 域名ector 2 3 #创建连接 4 config = { 5 "host":"localhost", 6 "port":3306, 7 "user":"root", 8 "password":"123456", 9 "database":"vega" 10 } 11 con = 域名ect(**config) 12 13 #系统登录用户信息 14 username="1 OR 1=1" 15 password="1 OR 1=1" 16 17 #创建游标 18 cursor=域名or() 19 #编写sql 20 sql = "SELECT COUNT(*) FROM t_user WHERE username= %s" \ 21 " AND AES_DECRYPT(UNHEX(password),\'HelloWorld\')=%s"; 22 #通过游标执行sql 23 域名ute(sql,(username,password)) 24 #打印最后结果 25 print(域名hone()[0]) #0 26 域名e()
域名L Connector的异常处理
1 import 域名ector 2 3 try: 4 con=域名ect( 5 host="localhost", 6 port=3306, 7 user="root", 8 password="123456", 9 database="vega" 10 ) 11 域名t_transaction() 12 cursor = 域名or() 13 sql="INSERT INTO t_type(type) VALUES(%s)" 14 域名ute(sql,("直播",)) 15 域名it() 16 17 except Exception as e: 18 if "con" in dir(): 19 域名back() 20 print(e) 21 finally: 22 if "con" in dir(): 23 域名e()
4.数据库连接池
1 import 域名ing 2 3 config={ 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"vega" 9 } 10 try: 11 pool=域名域名LConnectionPool( 12 **config, 13 pool_size=10 14 ) 15 con=域名connection() 16 域名t_transaction() 17 cursor=域名or() 18 sql="UPDATE t_type SET type=%s WHERE id=6" 19 域名ute(sql,("斗鱼",)) 20 域名it() 21 except Exception as e: 22 if "con" in dir(): 23 域名back() 24 print(e)1 # 删除数据,delete和truncate 2 3 import 域名ing 4 config={ 5 "host":"localhost", 6 "port":3306, 7 "user":"root", 8 "password":"123456", 9 "database":"demo" 10 } 11 try: 12 pool=域名域名LConnectionPool( 13 **config, 14 pool_size=10 15 ) 16 con=域名connection() 17 # 域名t_transaction() 18 cursor=域名or() 19 # sql="DELETE e,d FROM t_emp e JOIN t_dept d ON 域名no=域名no " \ 20 # "WHERE 域名no=20" 21 sql="TRUNCATE TABLE t_dept" # TRUNCATE 实现删除功能 22 域名ute(sql) 23 # 域名it() 24 except Exception as e: 25 # if "con" in dir(): 26 # 域名back() 27 print(e)1 # 使用 executemany() 2 3 import 域名ing 4 5 config={ 6 "host":"localhost", 7 "port":3306, 8 "user":"root", 9 "password":"123456", 10 "database":"demo" 11 } 12 try: 13 pool=域名域名LConnectionPool( 14 **config, 15 pool_size=10 16 ) 17 con=域名connection() 18 域名t_transaction() 19 cursor=域名or() 20 sql="INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s)" 21 data=[ 22 [100,"A部门","北京"],[110,"B部门","上海"] 23 ] 24 域名utemany(sql,data) 25 域名it() 26 except Exception as e: 27 if "con" in dir(): 28 域名back() 29 print(e)
5.案例练习(一)
前置条件:数据库脚本
1 SET NAMES utf8mb4; 2 SET FOREIGN_KEY_CHECKS = 0; 3 4 -- ---------------------------- 5 -- Table structure for t_bonus 6 -- ---------------------------- 7 DROP TABLE IF EXISTS `t_bonus`; 8 CREATE TABLE `t_bonus` ( 9 `empno` int(4) NOT NULL, 10 `job` varchar(20) DEFAULT NULL, 11 `sal` decimal(10,2) DEFAULT NULL, 12 `comm` decimal(10,2) DEFAULT NULL, 13 PRIMARY KEY (`empno`) 14 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 15 16 -- ---------------------------- 17 -- Table structure for t_dept 18 -- ---------------------------- 19 DROP TABLE IF EXISTS `t_dept`; 20 CREATE TABLE `t_dept` ( 21 `deptno` int(2) NOT NULL, 22 `dname` varchar(20) DEFAULT NULL, 23 `loc` varchar(20) DEFAULT NULL, 24 PRIMARY KEY (`deptno`) 25 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 26 27 -- ---------------------------- 28 -- Records of t_dept 29 -- ---------------------------- 30 BEGIN; 31 INSERT INTO `t_dept` VALUES (10, \'ACCOUNTING\', \'NEW YORK\'); 32 INSERT INTO `t_dept` VALUES (20, \'RESEARCH\', \'DALLAS\'); 33 INSERT INTO `t_dept` VALUES (30, \'SALES\', \'CHICAGO\'); 34 INSERT INTO `t_dept` VALUES (40, \'OPERATIONS\', \'BOSTON\'); 35 COMMIT; 36 37 -- ---------------------------- 38 -- Table structure for t_emp 39 -- ---------------------------- 40 DROP TABLE IF EXISTS `t_emp`; 41 CREATE TABLE `t_emp` ( 42 `empno` int(4) NOT NULL, 43 `ename` varchar(20) DEFAULT NULL, 44 `job` varchar(20) DEFAULT NULL, 45 `mgr` int(4) DEFAULT NULL, 46 `hiredate` date DEFAULT NULL, 47 `sal` decimal(10,2) DEFAULT NULL, 48 `comm` decimal(10,2) DEFAULT NULL, 49 `deptno` int(2) DEFAULT NULL, 50 PRIMARY KEY (`empno`) 51 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 52 53 -- ---------------------------- 54 -- Records of t_emp 55 -- ---------------------------- 56 BEGIN; 57 INSERT INTO `t_emp` VALUES (7369, \'SMITH\', \'CLERK\', 7902, \'1980-12-17 00:00:00\', 域名, NULL, 20); 58 INSERT INTO `t_emp` VALUES (7499, \'ALLEN\', \'SALESMAN\', 7698, \'1981-02-20 00:00:00\', 域名, 域名, 30); 59 INSERT INTO `t_emp` VALUES (7521, \'WARD\', \'SALESMAN\', 7698, \'1981-02-22 00:00:00\', 域名, 域名, 30); 60 INSERT INTO `t_emp` VALUES (7566, \'JONES\', \'MANAGER\', 7839, \'1981-04-02 00:00:00\', 域名, NULL, 20); 61 INSERT INTO `t_emp` VALUES (7654, \'MARTIN\', \'SALESMAN\', 7698, \'1981-09-28 00:00:00\', 域名, 域名, 30); 62 INSERT INTO `t_emp` VALUES (7698, \'BLAKE\', \'MANAGER\', 7839, \'1981-05-01 00:00:00\', 域名, NULL, 30); 63 INSERT INTO `t_emp` VALUES (7782, \'CLARK\', \'MANAGER\', 7839, \'1981-06-09 00:00:00\', 域名, NULL, 10); 64 INSERT INTO `t_emp` VALUES (7788, \'SCOTT\', \'ANALYST\', 7566, \'1982-12-09 00:00:00\', 域名, NULL, 20); 65 INSERT INTO `t_emp` VALUES (7839, \'KING\', \'PRESIDENT\', NULL, \'1981-11-17 00:00:00\', 域名, NULL, 10); 66 INSERT INTO `t_emp` VALUES (7844, \'TURNER\', \'SALESMAN\', 7698, \'1981-09-08 00:00:00\', 域名, 域名, 30); 67 INSERT INTO `t_emp` VALUES (7876, \'ADAMS\', \'CLERK\', 7788, \'1983-01-12 00:00:00\', 域名, NULL, 20); 68 INSERT INTO `t_emp` VALUES (7900, \'JAMES\', \'CLERK\', 7698, \'1981-12-03 00:00:00\', 域名, NULL, 30); 69 INSERT INTO `t_emp` VALUES (7902, \'FORD\', \'ANALYST\', 7566, \'1981-12-03 00:00:00\', 域名, NULL, 20); 70 INSERT INTO `t_emp` VALUES (7934, \'MILLER\', \'CLERK\', 7782, \'1982-01-23 00:00:00\', 域名, NULL, 10); 71 COMMIT; 72 73 -- ---------------------------- 74 -- Table structure for t_salgrade 75 -- ---------------------------- 76 DROP TABLE IF EXISTS `t_salgrade`; 77 CREATE TABLE `t_salgrade` ( 78 `grade` int(11) NOT NULL, 79 `losal` decimal(10,2) DEFAULT NULL, 80 `hisal` decimal(10,2) DEFAULT NULL, 81 PRIMARY KEY (`grade`) 82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 83 84 -- ---------------------------- 85 -- Records of t_salgrade 86 -- ---------------------------- 87 BEGIN; 88 INSERT INTO `t_salgrade` VALUES (1, 域名, 域名); 89 INSERT INTO `t_salgrade` VALUES (2, 域名, 域名); 90 INSERT INTO `t_salgrade` VALUES (3, 域名, 域名); 91 INSERT INTO `t_salgrade` VALUES (4, 域名, 域名); 92 INSERT INTO `t_salgrade` VALUES (5, 域名, 域名); 93 COMMIT; 94 95 SET FOREIGN_KEY_CHECKS = 1;python代码
1 import 域名ing 2 3 config={ 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"demo" 9 } 10 try: 11 pool=域名域名LConnectionPool( 12 **config, 13 pool_size=10 14 ) 15 con=域名connection() 16 域名t_transaction() 17 cursor=域名or() 18 sql="DROP TABLE t_emp_new" 19 域名ute(sql) 20 sql = "CREATE TABLE t_emp_new LIKE t_emp" #创建t_emp_new表,只将t_emp表的表结构同步到t_emp_new表中 21 # sql="CREATE TABLE t_emp_new AS (SELECT * FROM t_emp)" 创建t_emp_new表,将t_emp表的表结构和数据一起同步到t_emp_new表中 22 域名ute(sql) 23 24 #使用INSERT语句,把部门平均底薪超过公司平均底薪的这样部门里的 25 #员工信息导入到t_emp_new表里面,并且让这些员工隶属于sales部门 26 sql="SELECT AVG(sal) AS avg FROM t_emp" 27 域名ute(sql) 28 temp=域名hone() 29 avg=temp[0] #公司平均工资 30 sql="SELECT deptno FROM t_emp GROUP BY deptno HAVING AVG(sal)>=%s" 31 域名ute(sql,[avg]) 32 temp=域名hall() 33 sql="INSERT INTO t_emp_new SELECT * FROM t_emp WHERE deptno IN (" 34 for index in range(len(temp)): 35 one = temp[index][0] 36 if index < len(temp)-1: 37 sql+=str(one)+"," 38 else: 39 sql+=str(one) 40 sql+=")" 41 域名ute(sql) 42 43 sql = "DELETE FROM t_emp WHERE deptno IN (" 44 for index in range(len(temp)): 45 one = temp[index][0] 46 if index < len(temp) - 1: 47 sql += str(one) + "," 48 else: 49 sql += str(one) 50 sql += ")" 51 域名ute(sql) 52 53 sql = "SELECT deptno FROM t_dept WHERE dname=%s" 54 域名ute(sql, ["SALES"]) 55 deptno = 域名hone()[0] 56 sql = "UPDATE t_emp_new SET deptno=%s" 57 域名ute(sql, [deptno]) 58 域名it() 59 except Exception as e: 60 if "con" in dir(): 61 域名e() 62 print(e)
6.案例练习(二)
1 import 域名ing 2 3 config={ 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"demo" 9 } 10 try: 11 pool=域名域名LConnectionPool( 12 **config, 13 pool_size=10 14 ) 15 con=域名connection() 16 域名t_transaction() 17 sql="INSERT INTO t_dept "\ 18 "(SELECT MAX(deptno)+10,%s,%s FROM t_dept UNION "\ 19 "SELECT MAX(deptno)+20,%s,%s FROM t_dept)" 20 cursor=域名or() 21 域名ute(sql,("A部门","北京","B部门","上海")) 22 域名it() 23 except Exception as e: 24 if "con" in dir(): 25 域名back() 26 print(e)域名on操作Mysql数据库的步骤
1.用什么方法创建Connection对象呢,常用参数有哪些?
- 直接在connect()函数里面加参数
import 域名ector con=域名ect( host="localhost", port=3306, user="root", password="123456", database="vega" )View Code- 参数加在字典中,然后通过可变参数赋值
import 域名ector config = { "host":"localhost", "port":3306, "user":"root", "password":"123456", "database":"vega" } con = 域名ect(**config)View Code- 通过先建立连接池,再在连接池中获取连接
import 域名ing config={ "host":"localhost", "port":3306, "user":"root", "password":"123456", "database":"demo" } pool=域名域名LConnectionPool( **config, pool_size=10 ) con=域名connection()View Code域名ection对象常用方法有哪些?
开启事务 域名t_transaction()
提交事务 域名it()
回滚事务 域名back()
创建游标对象 cursor=域名or()
关闭连接 域名e()
域名or对象常用的方法和属性是什么?
- 执行SQL语句 域名te(sql,(参数,))
- 执行多次SQL 域名utemany(sql,data) #data参数集合
- 返回一条执行结果 域名hone()
- 返回多条执行结果 域名hall()
4.使用事务有哪些方法?
开启事务 域名t_transaction()
提交事务 域名it()
回滚事务 域名back()