MySQL（10） - Python与MySQL的交互
1.MySQL驱动模块Connector的语法
1.1.下载驱动
进入官网下载对应版本驱动
1.2.创建连接
方式一：
import mysql.connector con = mysql.connector.connect( host="localhost",port="3306",user="root",password="123456",database="vega" ) con.close() #运行脚本，不报错，连接成功方式二：
import mysql.connector config = { "host":"localhost", "port":3306, "user":"root", "password":"123456", "database":"vega" } con = mysql.connector.connect(**config) #运行脚本，不报错，连接成功
1.3.创建游标
创建游标，执行SQL语句
1 import mysql.connector 2 #创建连接 3 config = { 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"vega" 9 } 10 con = mysql.connector.connect(**config) 11 #创建游标 12 cursor=con.cursor() 13 #编写sql 14 sql = "SELECT * FROM t_role;" 15 #通过游标执行sql 16 cursor.execute(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 'mysql.connector.cursor_cext.CMySQLCursor'>
2.SQL注入攻击案例
1 import mysql.connector 2 3 #创建连接 4 config = { 5 "host":"localhost", 6 "port":3306, 7 "user":"root", 8 "password":"123456", 9 "database":"vega" 10 } 11 con = mysql.connector.connect(**config) 12 13 #系统登录用户信息 14 username="1 OR 1=1" 15 password="1 OR 1=1" 16 17 #创建游标 18 cursor=con.cursor() 19 #编写sql 20 sql = "SELECT COUNT(*) FROM t_user WHERE username= %s" \ 21 " AND AES_DECRYPT(UNHEX(password),'HelloWorld')=%s"; 22 #通过游标执行sql 23 cursor.execute(sql%(username,password)) 24 #打印最后结果 25 print(cursor.fetchone()[0]) #2 26 con.close()
1 import mysql.connector 2 3 #创建连接 4 config = { 5 "host":"localhost", 6 "port":3306, 7 "user":"root", 8 "password":"123456", 9 "database":"vega" 10 } 11 con = mysql.connector.connect(**config) 12 13 #系统登录用户信息 14 username="1 OR 1=1" 15 password="1 OR 1=1" 16 17 #创建游标 18 cursor=con.cursor() 19 #编写sql 20 sql = "SELECT COUNT(*) FROM t_user WHERE username= %s" \ 21 " AND AES_DECRYPT(UNHEX(password),'HelloWorld')=%s"; 22 #通过游标执行sql 23 cursor.execute(sql,(username,password)) 24 #打印最后结果 25 print(cursor.fetchone()[0]) #0 26 con.close()
3.MySQL Connector的异常处理
1 import mysql.connector 2 3 try: 4 con=mysql.connector.connect( 5 host="localhost", 6 port=3306, 7 user="root", 8 password="123456", 9 database="vega" 10 ) 11 con.start_transaction() 12 cursor = con.cursor() 13 sql="INSERT INTO t_type(type) VALUES(%s)" 14 cursor.execute(sql,("直播",)) 15 con.commit() 16 17 except Exception as e: 18 if "con" in dir(): 19 con.rollback() 20 print(e) 21 finally: 22 if "con" in dir(): 23 con.close()
4.数据库连接池
1 import mysql.connector.pooling 2 3 config={ 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"vega" 9 } 10 try: 11 pool=mysql.connector.pooling.MySQLConnectionPool( 12 **config, 13 pool_size=10 14 ) 15 con=pool.get_connection() 16 con.start_transaction() 17 cursor=con.cursor() 18 sql="UPDATE t_type SET type=%s WHERE id=6" 19 cursor.execute(sql,("斗鱼",)) 20 con.commit() 21 except Exception as e: 22 if "con" in dir(): 23 con.rollback() 24 print(e)1 # 删除数据，delete和truncate 2 3 import mysql.connector.pooling 4 config={ 5 "host":"localhost", 6 "port":3306, 7 "user":"root", 8 "password":"123456", 9 "database":"demo" 10 } 11 try: 12 pool=mysql.connector.pooling.MySQLConnectionPool( 13 **config, 14 pool_size=10 15 ) 16 con=pool.get_connection() 17 # con.start_transaction() 18 cursor=con.cursor() 19 # sql="DELETE e,d FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno " \ 20 # "WHERE d.deptno=20" 21 sql="TRUNCATE TABLE t_dept" # TRUNCATE 实现删除功能 22 cursor.execute(sql) 23 # con.commit() 24 except Exception as e: 25 # if "con" in dir(): 26 # con.rollback() 27 print(e)
1 # 使用 executemany() 2 3 import mysql.connector.pooling 4 5 config={ 6 "host":"localhost", 7 "port":3306, 8 "user":"root", 9 "password":"123456", 10 "database":"demo" 11 } 12 try: 13 pool=mysql.connector.pooling.MySQLConnectionPool( 14 **config, 15 pool_size=10 16 ) 17 con=pool.get_connection() 18 con.start_transaction() 19 cursor=con.cursor() 20 sql="INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s)" 21 data=[ 22 [100,"A部门","北京"],[110,"B部门","上海"] 23 ] 24 cursor.executemany(sql,data) 25 con.commit() 26 except Exception as e: 27 if "con" in dir(): 28 con.rollback() 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', 800.00, NULL, 20); 58 INSERT INTO `t_emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30); 59 INSERT INTO `t_emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30); 60 INSERT INTO `t_emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20); 61 INSERT INTO `t_emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30); 62 INSERT INTO `t_emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30); 63 INSERT INTO `t_emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10); 64 INSERT INTO `t_emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.00, NULL, 20); 65 INSERT INTO `t_emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10); 66 INSERT INTO `t_emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30); 67 INSERT INTO `t_emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.00, NULL, 20); 68 INSERT INTO `t_emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30); 69 INSERT INTO `t_emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, NULL, 20); 70 INSERT INTO `t_emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.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, 700.00, 1200.00); 89 INSERT INTO `t_salgrade` VALUES (2, 1201.00, 1400.00); 90 INSERT INTO `t_salgrade` VALUES (3, 1401.00, 2000.00); 91 INSERT INTO `t_salgrade` VALUES (4, 2001.00, 3000.00); 92 INSERT INTO `t_salgrade` VALUES (5, 3001.00, 9999.00); 93 COMMIT; 94 95 SET FOREIGN_KEY_CHECKS = 1;python代码
1 import mysql.connector.pooling 2 3 config={ 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"demo" 9 } 10 try: 11 pool=mysql.connector.pooling.MySQLConnectionPool( 12 **config, 13 pool_size=10 14 ) 15 con=pool.get_connection() 16 con.start_transaction() 17 cursor=con.cursor() 18 sql="DROP TABLE t_emp_new" 19 cursor.execute(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 cursor.execute(sql) 23 24 #使用INSERT语句，把部门平均底薪超过公司平均底薪的这样部门里的 25 #员工信息导入到t_emp_new表里面，并且让这些员工隶属于sales部门 26 sql="SELECT AVG(sal) AS avg FROM t_emp" 27 cursor.execute(sql) 28 temp=cursor.fetchone() 29 avg=temp[0] #公司平均工资 30 sql="SELECT deptno FROM t_emp GROUP BY deptno HAVING AVG(sal)>=%s" 31 cursor.execute(sql,[avg]) 32 temp=cursor.fetchall() 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 cursor.execute(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 cursor.execute(sql) 52 53 sql = "SELECT deptno FROM t_dept WHERE dname=%s" 54 cursor.execute(sql, ["SALES"]) 55 deptno = cursor.fetchone()[0] 56 sql = "UPDATE t_emp_new SET deptno=%s" 57 cursor.execute(sql, [deptno]) 58 con.commit() 59 except Exception as e: 60 if "con" in dir(): 61 con.close() 62 print(e)
6.案例练习（二）
1 import mysql.connector.pooling 2 3 config={ 4 "host":"localhost", 5 "port":3306, 6 "user":"root", 7 "password":"123456", 8 "database":"demo" 9 } 10 try: 11 pool=mysql.connector.pooling.MySQLConnectionPool( 12 **config, 13 pool_size=10 14 ) 15 con=pool.get_connection() 16 con.start_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=con.cursor() 21 cursor.execute(sql,("A部门","北京","B部门","上海")) 22 con.commit() 23 except Exception as e: 24 if "con" in dir(): 25 con.rollback() 26 print(e)
7.Python操作Mysql数据库的步骤
1.用什么方法创建Connection对象呢，常用参数有哪些？
- 直接在connect()函数里面加参数
View Codeimport mysql.connector con=mysql.connector.connect( host="localhost", port=3306, user="root", password="123456", database="vega" )- 参数加在字典中，然后通过可变参数赋值
View Codeimport mysql.connector config = { "host":"localhost", "port":3306, "user":"root", "password":"123456", "database":"vega" } con = mysql.connector.connect(**config)- 通过先建立连接池，再在连接池中获取连接
View Codeimport mysql.connector.pooling config={ "host":"localhost", "port":3306, "user":"root", "password":"123456", "database":"demo" } pool=mysql.connector.pooling.MySQLConnectionPool( **config, pool_size=10 ) con=pool.get_connection()
2.Connection对象常用方法有哪些？
开启事务 con.start_transaction()
提交事务 con.commit()
回滚事务 con.rollback()
创建游标对象 cursor=con.cursor()
关闭连接 con.close()
3.Cursor对象常用的方法和属性是什么？
- 执行SQL语句 cursor.excute(sql,(参数,))
- 执行多次SQL cursor.executemany(sql,data) #data参数集合
- 返回一条执行结果 cursor.fetchone()
- 返回多条执行结果 cursor.fetchall()
4.使用事务有哪些方法？
开启事务 con.start_transaction()
提交事务 con.commit()
回滚事务 con.rollback()
