飙血推荐
  • HTML教程
  • MySQL教程
  • JavaScript基础教程
  • php入门教程
  • JavaScript正则表达式运用
  • Excel函数教程
  • AngularJS教程
  • UEditor使用文档
  • ThinkPHP5.0教程

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() 

 

标签:编程
湘ICP备14001474号-3  投诉建议:234161800@qq.com   部分内容来源于网络,如有侵权,请联系删除。