第六篇 Mysql数据库

  • mysql相关
    第六篇 Mysql数据库
     1 权限管理
     2     1、创建账号
     3         # 本地账号
     4         create user 'egon1'@'localhost' identified by '123'; # mysql -uegon1 -p123
     5         # 远程帐号
     6         create user 'egon2'@'192.168.31.10' identified by '123'; # mysql -uegon2 -p123 -h 服务端ip
     7         create user 'egon3'@'192.168.31.%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip
     8         create user 'egon3'@'%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip
     9         
    10     2、授权
    11         user:*.*
    12         db:db1.*
    13         tables_priv:db1.t1
    14         columns_priv:id,name
    15         
    16         grant all on *.* to 'egon1'@'localhost';
    17         grant select on *.* to 'egon1'@'localhost';
    18         revoke select on *.* from 'egon1'@'localhost';
    19         
    20         grant select on db1.* to 'egon1'@'localhost';
    21         revoke select on db1.* from 'egon1'@'localhost';
    22         
    23         
    24         grant select on db1.t2 to 'egon1'@'localhost';
    25         revoke select on db1.t2 from 'egon1'@'localhost';
    26         
    27         grant select(id,name),update(age) on db1.t2 to 'egon1'@'localhost';
    权限管理 第六篇 Mysql数据库1数据库相关概念 第六篇 Mysql数据库
     1 SQL语句:
     2 
     3 操作文件夹(库)
     4     增
     5         create database db1 charset utf8;
     6     查
     7         show create database db1;
     8         show databases;
     9     改
    10         alter database db1 charset gbk;
    11     删
    12         drop database db1;
    13 
    14 操作文件(表)
    15     切换文件夹:use db1;
    16     查看当前所在文件夹:select database();
    17     
    18     增
    19         create table t1(id int,name char);
    20     查
    21         show create table t1;
    22         show tables;
    23         desc t1;
    24     改
    25         alter table t1 modify name char(6);
    26         alter table t1 change name NAME char(7);
    27     删
    28         drop table t1;
    29 
    30 操作文件内容(记录)
    31     增
    32         insert t1(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3');
    33     查
    34         select id,name from db1.t1;
    35         select * from db1.t1;
    36     改
    37         update db1.t1 set name='SB';
    38         update db1.t1 set name='ALEX' where id=2;
    39     删
    40         delete from t1 where id=2;
    41         清空表:
    42              delete from t1;
    43             truncate table t1;(同时删除id)
    2 初识SQL语句 第六篇 Mysql数据库
     1 1、什么是存储引擎?
     2     存储引擎就是表的类型
     3 
     4 2、查看MySQL支持的存储引擎
     5     show engines;
     6     
     7     
     8 3、指定表类型/存储引擎
     9     create table t1(id int)engine=innodb;
    10     create table t2(id int)engine=memory;
    11     create table t3(id int)engine=blackhole;
    12     create table t4(id int)engine=myisam;
    13 
    14 
    15     insert into t1 values(1);
    16     insert into t2 values(1);
    17     insert into t3 values(1);
    18     insert into t4 values(1);
    3 存储引擎介绍 第六篇 Mysql数据库
     1 create table student(
     2     id int,
     3     name char(6),
     4     born_year year,
     5     birth_date date,
     6     class_time time,
     7     reg_time datetime
     8 );
     9 
    10 insert into student values
    11 (1,'egon',now(),now(),now(),now());
    12 
    13 insert into student values
    14 (2,'alex',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12");
    4 日期类型 第六篇 Mysql数据库
     1 char:定长
     2 varchar:变长
     3 
     4 #宽度指的是字符的个数
     5 create table t13(name char(5));
     6 create table t14(name varchar(5));
     7 
     8 insert into t13 values('李杰 '); #'李杰   '
     9 insert into t14 values('李杰 '); #'李杰 '
    10 
    11 
    12 select char_length(name) from t13; #5
    13 select char_length(name) from t14; #3
    14 
    15 
    16 select name from t13 where name='李杰';
    17 select name from t13 where name like '李杰';
    18 
    19 
    20 
    21 
    22 name char(5)
    23 egon |alex |wxx  |
    24 
    25 
    26 name varchar(5)
    27 1bytes+egon|1bytes+alex|1bytes+wxx|
    28 4+egon|4+alex|3+wxx|
    5 字符类型 第六篇 Mysql数据库
     1 create table consumer(
     2     id int,
     3     name char(16),
     4     sex enum('male','female','other'),
     5     level enum('vip1','vip2','vip3'),
     6     hobbies set('play','music','read','run')
     7 );
     8 
     9 
    10 insert into consumer values
    11 (1,'egon','male','vip2','music,read');
    12 
    13 insert into consumer values
    14 (1,'egon','xxxxx','vip2','music,read');
    6 枚举和集合类型 第六篇 Mysql数据库
     1 create table t15(
     2     id int(11) unsigned zerofill
     3 );
     4 
     5 create table t16(
     6     id int,
     7     name char(6),
     8     sex enum('male','female') not null default 'male'
     9 );
    10 
    11 insert into t16(id,name) values(1,'egon');
    7.1 约束条件not null与default 第六篇 Mysql数据库7.2 unique key
    第六篇 Mysql数据库7.3 primary key 第六篇 Mysql数据库
      1 auto_increment
      2 
      3 create table t20(
      4     id int primary key auto_increment,
      5     name char(16)
      6 );
      7 
      8 insert into t20(name) values
      9 ('egon'),
     10 ('alex'),
     11 ('wxx');
     12 
     13 
     14 
     15 insert into t20(id,name) values
     16 (7,'yuanhao');
     17 
     18 insert into t20(name) values
     19 ('egon1'),
     20 ('egon2'),
     21 ('egon3');
     22 
     23 
     24 #了解
     25     show variables like 'auto_inc%';
     26 
     27     #步长:
     28     auto_increment_increment默认为1
     29     #起始偏移量
     30     auto_increment_offset默认1
     31     
     32     #设置步长
     33     set session auto_increment_increment=5;
     34     set global auto_increment_increment=5;
     35     
     36     #设置起始偏移量
     37     set global auto_increment_offset=3;
     38     强调:起始偏移量<=步长
     39     
     40     create table t21(
     41         id int primary key auto_increment,
     42         name char(16)
     43     );
     44     
     45     insert into t21(name) values
     46     ('egon'),
     47     ('alex'),
     48     ('wxx'),
     49     ('yxx');
     50     
     51     
     52 
     53 清空表:
     54     delete from t20;
     55     delete from t20 where id = 3;
     56     insert into t20(name) values
     57     ('xxx');
     58     
     59     truncate t20; #应该用它来清空表
     60     
     61     
     62     
     63 foreign key:建立表之间的关系
     64 
     65 #1、建立表关系:
     66     #先建被关联的表,并且保证被关联的字段唯一
     67     create table dep(
     68         id int primary key,
     69         name char(16),
     70         comment char(50)
     71     );
     72 
     73 
     74     #再建立关联的表
     75     create table emp(
     76         id int primary key,
     77         name char(10),
     78         sex enum('male','female'),
     79         dep_id int,
     80         foreign key(dep_id) references dep(id) 
     81         on delete cascade 
     82         on update cascade
     83     );
     84 
     85 #2、插入数据
     86 #先往被关联表插入记录
     87 insert into dep values
     88 (1,"IT","技术能力有限部门"),
     89 (2,"销售","销售能力不足部门"),
     90 (3,"财务","花钱特别多部门");
     91 
     92 #再往关联表插入记录
     93 insert into emp values
     94 (1,'egon','male',1);
     95 
     96 insert into emp values
     97 (2,'alex','male',1),
     98 (3,'wupeiqi','female',2),
     99 (4,'yuanhao','male',3),
    100 (5,'jinximn','male',2);
    101 
    102 
    103 
    104 
    105 delete from emp where dep_id=1;
    106 delete from dep where id=1;
    107 
    108 
    109 
    110 delete from dep where id=3;
    7.4 自增 &外键 第六篇 Mysql数据库
     1 两张表之间的关系:
     2     1、多对一
     3         出版社    书(foreign key(press_id) references press(id))
     4     2、多对多
     5         作者       书
     6         egon: 
     7             九阳神功
     8             九阴真经
     9         alex: 
    10             九阳神功
    11             葵花宝典
    12         yuanhao:
    13             独孤九剑
    14             降龙十巴掌
    15             葵花宝典
    16         wpq:
    17             九阳神功
    18             
    19         insert into author2book(author_id,book_id) values
    20         (1,1),
    21         (1,2),
    22         (2,1),
    23         (2,6);
    24             
    25         
    26     3、一对一
    27         customer表       student表
    28     
    8 表关系 第六篇 Mysql数据库
      1 单表查询
      2 
      3 select distinct 字段1,字段2,字段3 from 库.表 
      4     where 条件
      5     group by 分组条件
      6     having 过滤
      7     order by 排序字段
      8     limit n;
      9     
     10     
     11 #where
     12 select id,name,age from employee where id > 7;
     13     
     14 select name,post,salary from employee where post='teacher' and salary > 8000;
     15 
     16 select name,salary from employee where salary >= 20000 and salary <= 30000;
     17 select name,salary from employee where salary between 20000 and 30000;
     18 
     19 select name,salary from employee where salary < 20000 or salary > 30000;
     20 select name,salary from employee where salary not between 20000 and 30000;
     21 
     22 
     23 select * from employee where age = 73 or age = 81 or age = 28;
     24 select * from employee where age in (73,81,28);
     25 
     26 select * from employee where post_comment is Null;
     27 select * from employee where post_comment is not Null;
     28 
     29 select * from employee where name like "jin%";
     30 select * from employee where name like "jin___";
     31 
     32 
     33 #group by
     34 mysql> set global sql_mode="ONLY_FULL_GROUP_BY"; 
     35 分组之后,只能取分组的字段,以及每个组聚合结果
     36 
     37 select post from employee group by post;
     38 
     39 #聚合函数
     40 max
     41 min
     42 avg
     43 sum
     44 count
     45 
     46 #每个职位有多少个员工
     47 select post,count(id) as emp_count from employee group by post;
     48 select post,max(salary) as emp_count from employee group by post;
     49 select post,min(salary) as emp_count from employee group by post;
     50 select post,avg(salary) as emp_count from employee group by post;
     51 select post,sum(age) as emp_count from employee group by post;
     52 
     53 #没有group by则默认整体算作一组
     54 select max(salary) from employee;
     55 
     56 #group_concat
     57 select post,group_concat(name) from employee group by post;
     58 
     59 
     60 #练习:
     61 select post,group_concat(name) from employee group by post;
     62 
     63 select post,count(id) from employee where age > 50 group by post;
     64 
     65 select sex,count(id) from employee group by sex;
     66 
     67 
     68 select sex,avg(salary) from employee group by sex
     69 
     70 
     71 #having
     72 select post,group_concat(name),count(id) from employee group by post;
     73 
     74 select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
     75 
     76 
     77 select post,avg(salary) from employee group by post having avg(salary) > 10000;
     78 
     79 
     80 #order by
     81 select * from employee order by age asc; #升序
     82 select * from employee order by age desc; #降序
     83 
     84 select * from employee order by age asc,id desc; #先按照age升序排,如果age相同则按照id降序排
     85 
     86 
     87 select distinct post,count(id) as emp_count from employee
     88     where salary > 1000
     89     group by post
     90     having count(id) > 1
     91     order by emp_count desc
     92     ;
     93 
     94 
     95 #limit
     96 select * from employee limit 3;
     97 select * from employee order by salary desc limit 1;
     98 
     99 
    100 select * from employee limit 0,5;
    101 select * from employee limit 5,5;
    102 select * from employee limit 10,5;
    103 select * from employee limit 15,5;
    104 
    105 
    106 #总结:
    107     语法顺序:
    108         select distinct 字段1,字段2,字段3 from 库.表 
    109             where 条件
    110             group by 分组条件
    111             having 过滤
    112             order by 排序字段
    113             limit n;
    114 
    115     执行顺序:
    116 
    117 def from(db,table):
    118         f=open(r'%s\%s' %(db,table))
    119         return f
    120     
    121 def where(condition,f):
    122     for line in f:
    123         if condition:
    124             yield line
    125 
    126 def group(lines):
    127     pass
    128     
    129 def having(group_res):
    130     pass
    131 
    132 def distinct(having_res):
    133     pass
    134 
    135 def order(distinct_res):
    136     pass
    137     
    138 def limit(order_res)
    139     pass
    140     
    141 def select():
    142     f=from('db1','t1')
    143     lines=where('id>3',f)
    144     group_res=group(lines)
    145     having_res=having(group_res)
    146     distinct_res=distinct(having_res)
    147     order_res=order(distinct_res)
    148     res=limit(order_res)
    149     print(res)
    150     return res
    151     
    152 #正则表达式
    153 
    154  like 模糊查询:
    155 select * from employee where name like 'jin%';(# %代表任意多个字符)
    156 select * from employee where name like 'jin_' ( #'_' 表示任意单个字符)
    157 
    158  regexp 正则匹配
    159 select * from employee where name regexp '^jin';
    160 select * from employee where name regexp '^jin.*(g|n)$';
    161 
    162     
    163     
    8.1 单表查询 第六篇 Mysql数据库
     1 内连接:只取两张表的共同部分
     2 select * from employee inner join department on employee.dep_id = department.id ;
     3 
     4 左连接:在内连接的基础上保留左表的记录
     5 select * from employee left join department on employee.dep_id = department.id ;
     6 
     7 右连接:在内连接的基础上保留右表的记录
     8 select * from employee right join department on employee.dep_id = department.id ;
     9 
    10 全外连接:在内连接的基础上保留左右两表没有对应关系的记录
    11 select * from employee full join department on employee.dep_id = department.id ;
    12 
    13 
    14 select * from employee left join department on employee.dep_id = department.id
    15 union
    16 select * from employee right join department on employee.dep_id = department.id ;
    8.2 连表查询 第六篇 Mysql数据库
     1 #1、无参存储过程
     2     delimiter //
     3     create procedure p1()
     4     BEGIN
     5         select * from db7.teacher;
     6     END //
     7     delimiter ;
     8 
     9 
    10     # MySQL中调用
    11     call p1();
    12 
    13 
    14     # Python中调用
    15     cursor.callproc('p1')
    16     
    17     
    18 #2、有参存储过程
    19     delimiter //
    20     create procedure p2(in n1 int,in n2 int,out res int)
    21     BEGIN
    22         select * from db7.teacher where tid > n1 and tid < n2;
    23         set res = 1;
    24     END //
    25     delimiter ;
    26 
    27 
    28     # MySQL中调用
    29     set @x=0
    30     call p2(2,4,@x);
    31     select @x;
    32 
    33     # Python中调用
    34     cursor.callproc('p2',(2,4,0))# @_p2_0=2,@_p2_1=4,@_p2_2=0
    35     cursor.execute('select @_p3_2')
    36     cursor.fetchone()
    37     
    38     
    39 
    40     
    41 应用程序与数据库结合使用
    42 方式一:
    43     Python:调用存储过程
    44     MySQL:编写存储过程
    45 
    46     
    47 方式二:
    48     Python:编写纯生SQL
    49     MySQL:
    50     
    51 方式三:
    52     Python:ORM->纯生SQL
    53     MySQL:
    54 
    55 
    56 
    57     
    存储过程 第六篇 Mysql数据库
     1 CREATE TABLE blog (
     2     id INT PRIMARY KEY auto_increment,
     3     NAME CHAR (32),
     4     sub_time datetime
     5 );
     6 
     7 INSERT INTO blog (NAME, sub_time)
     8 VALUES
     9     ('第1篇','2015-03-01 11:31:21'),
    10     ('第2篇','2015-03-11 16:31:21'),
    11     ('第3篇','2016-07-01 10:21:31'),
    12     ('第4篇','2016-07-22 09:23:21'),
    13     ('第5篇','2016-07-23 10:11:11'),
    14     ('第6篇','2016-07-25 11:21:31'),
    15     ('第7篇','2017-03-01 15:33:21'),
    16     ('第8篇','2017-03-01 17:32:21'),
    17     ('第9篇','2017-03-01 18:31:21');
    18     
    19     
    20     
    21 select  date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m')
    函数相关(如date_format)

     

  • pymysql
    第六篇 Mysql数据库
     1 #pip3 install pymysql
     2 import pymysql
     3 
     4 user=input('user>>: ').strip()
     5 pwd=input('password>>: ').strip()
     6 
     7 # 建立链接
     8 conn=pymysql.connect(
     9     host='192.168.10.15',
    10     port=3306,
    11     user='root',
    12     password='123',
    13     db='db9',
    14     charset='utf8'
    15 )
    16 
    17 # 拿到游标
    18 cursor=conn.cursor()
    19 
    20 # 执行sql语句
    21 
    22 # sql='select * from userinfo where user = "%s" and pwd="%s"' %(user,pwd)
    23 # print(sql)
    24 
    25 sql='select * from userinfo where user = %s and pwd=%s'
    26 rows=cursor.execute(sql,(user,pwd))
    27 
    28 cursor.close()
    29 conn.close()
    30 
    31 # 进行判断
    32 if rows:
    33     print('登录成功')
    34 else:
    35     print('登录失败')
    pymysql基本使用 第六篇 Mysql数据库
     1 #1、增删改
     2 import pymysql
     3 
     4 # 建立链接
     5 conn=pymysql.connect(
     6     host='192.168.10.15',
     7     port=3306,
     8     user='root',
     9     password='123',
    10     db='db9',
    11     charset='utf8'
    12 )
    13 
    14 # 拿游标
    15 cursor=conn.cursor()
    16 
    17 # 执行sql
    18 # 增、删、改
    19 sql='insert into userinfo(user,pwd) values(%s,%s)'
    20 # rows=cursor.execute(sql,('wxx','123'))
    21 # print(rows)
    22 # rows=cursor.executemany(sql,[('yxx','123'),('egon1','111'),('egon2','2222')])
    23 # print(rows)
    24 
    25 rows=cursor.executemany(sql,[('egon3','123'),('egon4','111'),('egon5','2222')])
    26 print(cursor.lastrowid)
    27 
    28 conn.commit()
    29 # 关闭
    30 cursor.close()
    31 conn.close()
    32 
    33 
    34 
    35 #2、查询
    36 # import pymysql
    37 #
    38 # # 建立链接
    39 # conn=pymysql.connect(
    40 #     host='192.168.10.15',
    41 #     port=3306,
    42 #     user='root',
    43 #     password='123',
    44 #     db='db9',
    45 #     charset='utf8'
    46 # )
    47 
    48 # 拿游标
    49 # cursor=conn.cursor(pymysql.cursors.DictCursor)
    50 
    51 # 执行sql
    52 # 查询
    53 # rows=cursor.execute('select * from userinfo;')
    54 # print(rows)
    55 # print(cursor.fetchone())
    56 # print(cursor.fetchone())
    57 # print(cursor.fetchone())
    58 # print(cursor.fetchone())
    59 # print(cursor.fetchone())
    60 # print(cursor.fetchone())
    61 # print(cursor.fetchone())
    62 
    63 
    64 # print(cursor.fetchmany(2))
    65 
    66 # print(cursor.fetchall())
    67 # print(cursor.fetchall())
    68 
    69 
    70 
    71 # cursor.scroll(3,mode='absolute') # 相对绝对位置移动
    72 # print(cursor.fetchone())
    73 # cursor.scroll(2,mode='relative') # 相对当前位置移动
    74 # print(cursor.fetchone())
    75 
    76 #
    77 
    78 # 关闭
    79 # cursor.close()
    80 # conn.close()
    pymysql 增,删,改,查 第六篇 Mysql数据库
     1 #1、增删改
     2 import pymysql
     3 
     4 # 建立链接
     5 conn=pymysql.connect(
     6     host='192.168.10.15',
     7     port=3306,
     8     user='root',
     9     password='123',
    10     db='db7',
    11     charset='utf8'
    12 )
    13 
    14 # 拿游标
    15 cursor=conn.cursor()
    16 
    17 # 执行sql
    18 # cursor.callproc('p1')
    19 # print(cursor.fetchall())
    20 
    21 cursor.callproc('p2',(2,4,0))
    22 # print(cursor.fetchall())
    23 
    24 cursor.execute('select @_p2_2')
    25 print(cursor.fetchone())
    26 
    27 # 关闭
    28 cursor.close()
    29 conn.close()
    存储过程的执行

    操作表(utils)

    第六篇 Mysql数据库
      1 #操作表
      2 #1、自行创建测试数据;
      3 #2、查询学生总人数;
      4     select count(sid) as total_sid from student;
      5     
      6 #3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名;
      7     #成绩表与课程表连表-一学生分组-找出条件
      8 select sid,sname from 
      9     student where sid in 
     10         (select student_id 
     11         from score inner join course  #连表
     12             on score.course_id=course.cid
     13             where score>=60 
     14             and cname ='生物' or cname ='物理'
     15         group by # 学生id分组
     16             score.student_id
     17         having
     18             count(course_id)=2);
     19             
     20 
     21 #4、查询每个年级的班级数,取出班级数最多的前三个年级;
     22     select gname,count(gname) from 
     23     class inner join class_grade on class.grade_id=class_grade.gid
     24         group by 
     25             gname
     26             order by count(gname)desc
     27             limit(3);
     28     
     29 
     30 #5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩;
     31 select 
     32     sid,
     33     sname,
     34     t1.avg_score
     35 from
     36     student
     37 inner join(
     38     select
     39         student_id,
     40         avg(score)as avg_score
     41     from
     42         score
     43     group by
     44         student_id
     45     having
     46         avg(score) in(
     47             (select avg(score)as low_score from score group by student_id order by avg(score) limit 1),
     48             (select avg(score)as high_score from score group by student_id order by avg(score) desc limit 1))
     49         )as t1
     50 on
     51     student.sid=t1.student_id;
     52     
     53 
     54 #6、查询每个年级的学生人数;
     55 # 学生与班级连表-grade_id分组-统计人数
     56 
     57 select grade_id,count(sid)as total_student
     58 from
     59 (select
     60     class.grade_id,student.sid
     61 from
     62     student
     63 inner join 
     64     class
     65 on
     66     student.class_id=class.cid) as t1
     67 group by 
     68     t1.grade_id;
     69     
     70     
     71 
     72 #7、查询每位学生的学号,姓名,选课数,平均成绩;
     73 select
     74     sid,
     75     sname,
     76     t1.total_course,
     77     t1.avg_score
     78 from
     79     student
     80 left join(
     81     select
     82         student_id,
     83         count(course_id)as total_course,
     84         avg(score)as avg_score
     85     from
     86         score
     87     group by
     88         student_id
     89     )as t1
     90 on
     91 student.sid=t1.student_id;
     92 
     93 #8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数;
     94 select
     95     sid,
     96     sname,
     97     t1.course_id,
     98     t1.score
     99 from
    100     student
    101 inner join(
    102     select
    103         student_id,
    104         course_id,
    105         score
    106     from
    107         score
    108     where
    109         score in(
    110             (select score from score where student_id=2 order by score desc limit 1),
    111             (select score from score where student_id=2 order by score limit 1 )
    112             )
    113         )as t1
    114 on
    115     student.sid=t1.student_id;
    116  
    117 #9、查询姓“李”的老师的个数和所带班级数;
    118 select
    119     count(t1.tid)as total_li,
    120     count(teach2cls.cid)as total_class
    121 from
    122     teach2cls
    123 inner join(
    124     select
    125         tid
    126     from
    127         teacher
    128     where
    129         tname like "李%"
    130     )as t1
    131 on
    132     teach2cls.tid=t1.tid
    133 
    134 #10、查询班级数小于5的年级id和年级名;
    135 select 
    136     gid,
    137     gname
    138 from
    139     class_grade
    140 where 
    141     gid
    142 in
    143     (select #年纪id与对应班级数的表
    144         grade_id
    145     from
    146         class 
    147     group by
    148         grade_id
    149     having
    150         count(cid)<5);
    151     
    152 
    153 
    154 #11、查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级)
    155     #班级id    班级名称    年级  年级级别
    156         #1     一年一班    一年级    低
    157         
    158     #班级表与年纪表连表--加入级别
    159     
    160 select
    161     class.cid as '班级id',
    162     class.caption as '班级名称',
    163     class_grade.gname as '年级',
    164     case when class_grade.gid between 1 and 2 then '低'
    165     when class_grade.gid between 3 and 4 then '中'
    166     when class_grade.gid between 5 and 6 then '高' else 0 end as '年级级别'
    167 from
    168     class,
    169     class_grade
    170 where
    171     class.grade_id = class_grade.gid;
    172     
    173 #12、查询学过“张三”老师2门课以上的同学的学号、姓名;
    174     # 课程表与老师表连表--选出张三老师的课程-到成绩表以学生分类-统计符合条件学生
    175 
    176 select
    177     student_id
    178 from
    179     score
    180 where
    181     course_id
    182 in
    183     (select
    184         course.cid
    185     from
    186         course
    187     inner join
    188         teacher
    189     on
    190         course.teacher_id=teacher.tid
    191     where 
    192         teacher.tname='张三')
    193 group by
    194     student_id
    195 having
    196     count(course_id)>=2;
    197     
    198     
    199 #13、查询教授课程超过2门的老师的id和姓名;
    200 
    201 select 
    202     tid,
    203     tname
    204 from
    205     teacher
    206 where 
    207     tid 
    208 in 
    209     (select 
    210         teacher_id
    211     from
    212         course
    213     group by 
    214         teacher_id
    215     having
    216         count(cid)>2
    217     );
    218 
    219 #14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;
    220 select 
    221     sid,sname
    222 from
    223     student
    224 where 
    225     sid
    226 in
    227     (select 
    228         student_id 
    229     from 
    230         score
    231     where 
    232         course_id
    233     in 
    234         ('1','2')
    235     group by
    236         student_id
    237     having
    238         count(course_id)=2);
    239 
    240 #15、查询没有带过高年级的老师id和姓名;
    241 
    242 select
    243     tid,
    244     tname
    245 from
    246     teacher
    247 where 
    248     tid
    249 in
    250     (select
    251         tid
    252     from
    253         teach2cls
    254     where
    255         cid
    256     in
    257         (select
    258             cid 
    259         from
    260             class
    261         where
    262             grade_id<3));
    263     
    264 
    265 #16、查询学过“张三”老师所教的所有课的同学的学号、姓名;
    266 
    267 select
    268     sid,
    269     sname
    270 from
    271     student
    272 where sid in (
    273     select distinct student_id from score where course_id in (
    274         select cid from course where teacher_id in(
    275             select tid from teacher where tname='张三'
    276             )
    277         )
    278     );
    279 #17、查询带过超过2个班级的老师的id和姓名;
    280 select
    281     tid,
    282     tname
    283 from
    284     teacher
    285 where tid in (
    286     select
    287         tid
    288     from
    289         teach2cls
    290     group by
    291         tid
    292     having
    293         count(cid)>2
    294 );
    295 #18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;
    296     
    297 select
    298   sid,
    299   sname
    300 from 
    301     student
    302 where sid in (
    303     select s1.student_id
    304     from score as s1 inner join score as s2 on s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 2
    305     where s1.score < s2.score);
    306 
    307 #19、查询所带班级数最多的老师id和姓名;
    308 
    309 select
    310     tid,
    311     tname
    312 from
    313     teacher
    314 where
    315     tid in (
    316         select
    317             tid
    318         from
    319             teach2cls
    320         group by
    321             tid
    322         having
    323             count(cid)=(
    324                 select
    325                     count(cid)
    326                 from
    327                     teach2cls
    328                 group by
    329                     tid
    330                 order by
    331                     count(cid) desc
    332                 limit 1
    333             )
    334     );
    335 #20、查询有课程成绩小于60分的同学的学号、姓名;
    336 select
    337     sid,
    338     sname
    339 from
    340     student
    341 where
    342     sid in (
    343         select
    344             student_id
    345         from
    346             score
    347         where
    348             score<60
    349     );
    350  
    351 #21、查询没有学全所有课的同学的学号、姓名;
    352 
    353 select 
    354     sid,
    355     sname
    356 from
    357     student
    358 where
    359     sid
    360 not in
    361     (select 
    362         student_id
    363     from
    364         score
    365     group by
    366         student_id
    367     having
    368         count(course_id)=(select count(cid) from course)
    369         );
    370 
    371 #22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;
    372 select
    373     sid,sname
    374 from
    375     student
    376 where
    377     sid in (
    378         select
    379             distinct student_id
    380         from
    381             score
    382         where
    383             course_id in(
    384                 select
    385                     course_id
    386                 from
    387                     score
    388                 where
    389                     student_id =1
    390             )
    391     )
    392     and sid !=1;
    393 
    394 #23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名;
    395 select
    396     sid,sname
    397 from
    398     student
    399 where
    400     sid in (
    401         select
    402             distinct student_id
    403         from
    404             score
    405         where
    406             course_id in(
    407                 select
    408                     course_id
    409                 from
    410                     score
    411                 where
    412                     student_id =1
    413             )
    414     )
    415     and sid !=1;
    416 
    417 #24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名;
    418 
    419 select
    420     sid,
    421     sname
    422 from
    423     student
    424 where sid in (
    425     select student_id from score,
    426     (select course_id from score where student_id=2)as t1
    427     where score.course_id = t1.course_id and score.student_id !=2 group by score.student_id
    428     having count(score.course_id)=(select count(course_id)from score where student_id=2)
    429 );
    430 
    431 
    432 #25、删除学习“张三”老师课的score表记录;
    433 
    434 delete from score where course_id in (
    435     select cid from course where teacher_id =(
    436         select tid from teacher where tname='张三')
    437 );
    438 
    439 #26、向score表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“2”课程的同学学号;②插入“2”号课程的平均成绩;
    440 insert score(student_id,course_id,score)
    441     select t1.sid,2,t2.avg_score from(
    442     (select sid from student where sid not in (select student_id from score where course_id = 2)) as t1,
    443     (select avg(score)as avg_score from score where course_id = 2) as t2);
    444     
    445 #27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示:学生ID,语文,数学,英语,课程数和平均分;
    446 
    447 select
    448     student_id,
    449     (select score from score where course_id =(select cid from course where cname='语文') and score.student_id =s1.student_id ) as '语文',
    450     (select score from score where course_id =(select cid from course where cname='数学') and score.student_id =s1.student_id ) as '数学',
    451     (select score from score where course_id =(select cid from course where cname='英语') and score.student_id =s1.student_id ) as '英语',
    452     count(course_id) as '有效课程数',
    453     avg(score) as '有效平均分'
    454 from
    455     score as s1
    456 group by
    457     student_id
    458 order by
    459     avg(score);
    460 
    461 
    462 #28、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
    463 
    464 select
    465     course_id as id,
    466     max(score.score) as '最高分',
    467     min(score.score) as '最低分'
    468 from
    469     course
    470 left join score
    471 on score.course_id=course.cid 
    472 group by course_id;
    473 
    474 #29、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
    475 select course_id,
    476     avg(score) as avg_score,
    477     sum(case when score.score >= 60 then 1 else 0 end) / count(sid) * 100 as percent
    478 from
    479     score
    480 group by
    481     course_id
    482 order by
    483     avg(score) asc,percent desc;
    484 #30、课程平均分从高到低显示(现实任课老师);
    485 select
    486     t1.cid,
    487     t1.tname,
    488     t2.avg_score
    489 from(
    490     select
    491         teacher.tid as tid,
    492         teacher.tname as tname,
    493         course.cid as cid
    494     from
    495         teacher
    496     inner join
    497         course
    498     on teacher.tid = teacher_id
    499     )as t1
    500 inner join
    501     (select course_id,avg(score)as avg_score from score group by course_id )as t2
    502 on
    503     t1.cid=t2.course_id
    504 order by
    505     avg_score desc;
    506 
    507 #31、查询各科成绩前三名的记录(不考虑成绩并列情况) ;
    508 select
    509   student_id,
    510   score,
    511   course_id
    512 from score r1
    513 where (SELECT count(1)
    514        from (select distinct
    515                score,
    516                course_id
    517              from score) r2
    518        where r2.course_id = r1.course_id AND r2.score > r1.score) <= 2
    519 order by course_id, score DESC;
    520  
    521 #32、查询每门课程被选修的学生数;
    522 select
    523     course_id,
    524     count(student_id)
    525 from
    526     score
    527 group by
    528     course_id;
    529 
    530 #33、查询选修了2门以上课程的全部学生的学号和姓名;
    531 elect
    532     sid,
    533     sname
    534 from
    535     student
    536 where sid in(
    537     select
    538         student_id
    539     from
    540         score
    541     group by
    542         student_id
    543     having
    544         count(course_id)>2);
    545 
    546 #34、查询男生、女生的人数,按倒序排列;
    547 select
    548     gender,
    549     count(sid)
    550 from
    551     student
    552 group by
    553     gender
    554 order by   
    555     count(sid) desc;
    556 
    557 #35、查询姓“张”的学生名单;
    558 select
    559     *
    560 from
    561     student
    562 where
    563     sname like "张%";
    564 #36、查询同名同姓学生名单,并统计同名人数;
    565 select
    566     sname,
    567     count(sid)
    568 from
    569     student
    570 group by
    571     sname
    572 having
    573     count(sid)>1;
    574 #37、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
    575 select
    576     course_id,
    577     avg(score)
    578 from
    579     score
    580 group by
    581     course_id
    582 order by
    583     avg(score),
    584     course_id desc;
    585 #38、查询课程名称为“数学”,且分数低于60的学生姓名和分数;
    586 select
    587     student.sname,
    588     t1.score
    589 from
    590     student
    591 inner join  (
    592     select
    593         student_id,
    594         score
    595     from
    596         score
    597     where score.score<60 and course_id in (
    598         select
    599             cid
    600         from
    601             course
    602         where cname='数学'
    603     )
    604 )as t1
    605 on 
    606     t1.student_id=student.sid;
    607 #39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名;
    608 select
    609     sid,
    610     sname
    611 from
    612     student
    613 where sid in(
    614     select
    615         student_id
    616     from
    617         score
    618     where
    619     course_id=3 and score>80
    620 );
    621 #41、查询选修“王五”老师所授课程的学生中,成绩最高和最低的学生姓名及其成绩;
    622 select
    623     s1.student_id,
    624     low_score,
    625     s2.student_id,
    626     high_score
    627 from(
    628     select
    629         tid,
    630         student_id,
    631         score as low_score
    632     from
    633         (select student_id,cid,cname,score,tid
    634         from score
    635         inner join
    636             (select tid,tname,cid,cname from teacher inner join course on teacher.tid=course.teacher_id where tname='王五')as t1
    637         on score.course_id=t1.cid)as t2 order by score limit 1) as s1
    638     inner join (
    639         select tid,student_id,score as high_score from
    640             (select student_id,cid,cname,score,tid
    641             from score
    642             inner join
    643                 (select tid,tname,cid,cname from teacher
    644                 inner join
    645                     course
    646                 on teacher.tid=course.teacher_id where tname='王五')as t1
    647             on score.course_id=t1.cid)as t2 order by score desc limit 1) as s2
    648     on s1.tid=s2.tid;
    649 #42、查询各个课程及相应的选修人数;
    650 select
    651     course_id ,
    652     count(student_id)
    653 from
    654     score
    655 group by
    656     course_id;
    657     
    658 #43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
    659 select distinct
    660     s1.course_id,
    661     s1.student_id,
    662     s1.score,
    663     s2.course_id,
    664     s2.student_id,
    665     s2.score
    666 from
    667     score as s1,
    668     score as s2
    669 where
    670 s1.score = s2.score and s1.course_id != s2.course_id;
    671 #44、查询每门课程成绩最好的前两名学生id和姓名;
    672 select student.sid,student.sname,course.cname, score.score
    673     from score
    674 inner join (
    675     select course_id, score, ranking
    676         from (
    677              select a.course_id, a.score, count(1) as ranking
    678                 from
    679                     (select course_id, score from score group by course_id, score order by course_id, score desc)as a
    680                 inner join
    681                     (select course_id, score from score group by course_id, score order by course_id, score desc)as b
    682                 on a.course_id = b.course_id and a.score <= b.score group by course_id, score
    683         ) as t1
    684         where ranking in (1, 2) order by course_id, ranking)as s1
    685 on score.course_id = s1.course_id and score.score = s1.score
    686 inner join student
    687     on score.student_id = student.sid
    688 inner join course
    689     on score.course_id = course.cid;
    690     
    691 #45、检索至少选修两门课程的学生学号;
    692 select
    693     student_id
    694 from
    695     score
    696 group by
    697     student_id
    698 having
    699     count(course_id)>=2;
    700     
    701 #46、查询没有学生选修的课程的课程号和课程名;
    702 select
    703     course.cid,
    704     course.cname
    705 from
    706     course
    707 left join
    708     score
    709 on
    710     course.cid=score.course_id
    711 where
    712     score.student_id is null;
    713     
    714 #47、查询没带过任何班级的老师id和姓名;
    715 select
    716     teacher.tid,
    717     tname
    718 from
    719     teacher
    720 left join
    721     teach2cls
    722 on
    723     teacher.tid=teach2cls.tid
    724 where
    725     teach2cls.tcid is null;
    726     
    727 #48、查询有两门以上课程超过80分的学生id及其平均成绩;
    728 select
    729     student_id,
    730     avg(score)
    731 from
    732     score
    733 where
    734     score>80
    735 group by
    736     student_id
    737 having
    738     count(course_id)>2;
    739     
    740 #49、检索“3”课程分数小于60,按分数降序排列的同学学号;
    741 select
    742     student_id,
    743     score
    744 from
    745     score
    746 where
    747     score<60 and course_id=3
    748 order by
    749     score desc;
    750     
    751 #50、删除编号为“2”的同学的“1”课程的成绩;
    752 delete from
    753     score 
    754  where
    755  student_id='2' and course_id='1';
    756  
    757 #51、查询同时选修了物理课和生物课的学生id和姓名
    758 
    759 
    760 select
    761     student.sid,
    762     student.sname
    763 from
    764     student
    765 where sid in (
    766         select
    767             student_id
    768         from
    769             score
    770         where
    771             course_id IN (
    772                 select
    773                     cid
    774                 from
    775                     course
    776                 where cname = '物理' or cname = '生物'
    777             )
    778         group by
    779             student_id
    780         having
    781             count(course_id) = 2
    782     );
    操作表示例 第六篇 Mysql数据库
     1 """
     2 
     3 使用
     4 from mysql_utils.sql import SQLHelper
     5 
     6 sql ="select * from 36kr"
     7 print(SQLHelper.fetch_one(sql))
     8 print(SQLHelper.fetch_all(sql))
     9 
    10 # print(SQLHelper.fetchdic_one_dict(sql,{}))
    11 # print(SQLHelper.fetch_all_list_dict(sql,{}))
    12 """
    13 
    14 import pymysql
    15 
    16 
    17 class SQLHelper(object):
    18 
    19     @staticmethod
    20     def open(cursor):
    21         conn = pymysql.connect(
    22             host='127.0.0.1',
    23             port=3306,
    24             user='root',
    25             password='',
    26             db='new_source',
    27             charset='utf8'
    28         )
    29         cursor = conn.cursor(cursor=cursor)
    30         return conn,cursor
    31 
    32     @staticmethod
    33     def close(conn,cursor):
    34         conn.commit()
    35         cursor.close()
    36         conn.close()
    37 
    38     @classmethod
    39     def fetch_one(cls, sql, cursor=None):
    40         """
    41         默认返回一个元组
    42         :param sql:
    43         :param args:
    44         :param cursor:
    45         :return:
    46         """
    47         conn, cursor = cls.open(cursor)
    48         cursor.execute(sql)
    49         obj = cursor.fetchone()
    50         cls.close(conn, cursor)
    51         return obj
    52 
    53     @classmethod
    54     def fetch_all(cls, sql, cursor=None):
    55         conn, cursor = cls.open(cursor)
    56         cursor.execute(sql)
    57         obj = cursor.fetchall()
    58         cls.close(conn, cursor)
    59         return obj
    60 
    61     @classmethod
    62     def fetchdic_one_dict(cls, sql, args, cursor=pymysql.cursors.DictCursor):
    63         """
    64         默认返回一个字典
    65         :param sql:
    66         :param args:
    67         :param cursor:
    68         :return:{'id': 4, 'name': 'egon3', 'count': 123}
    69         """
    70         conn, cursor = cls.open(cursor)
    71         cursor.execute(sql, args)
    72         obj = cursor.fetchone()
    73         cls.close(conn, cursor)
    74         return obj
    75     @classmethod
    76     def fetch_all_list_dict(cls,sql, args,cursor =pymysql.cursors.DictCursor):
    77         """
    78         返回一个列表,列表元素为字典
    79         :param sql:
    80         :param args:
    81         :param cursor:
    82         :return: #[{'id': 4, 'name': 'egon3', 'count': 123}, {'id': 5, 'name': 'egon4', 'count': 111}]
    83         """
    84         conn, cursor = cls.open(cursor)
    85         cursor.execute(sql, args)
    86         obj = cursor.fetchall()
    87         cls.close(conn, cursor)
    88         return obj
    SQLHelper 第六篇 Mysql数据库
      1 #coding:utf-8
      2 import MySQLdb
      3 import json
      4 import time
      5 
      6 
      7 db = ""
      8 cursor = ""
      9 def db_init():
     10     global db
     11     global cursor
     12     # db = MySQLdb.connect("", "c396313051","123456ok","news", charset="utf8mb4")
     13     db = MySQLdb.connect("", "root","","news_crawl",port=22936,charset="utf8mb4")
     14     cursor = db.cursor()
     15     # 使用cursor()方法获取操作游标 
     16     # print("Connection is successful!")
     17 
     18 def db_close():
     19     db.close()
     20 
     21 def db_commit():
     22     db.commit()
     23 
     24 def get_unupdate_count():
     25     db_init()
     26     sql = "SELECT COUNT(*) FROM news_detail WHERE flag_content_update = '0' "
     27     try:
     28         cursor.execute(sql)
     29         result = cursor.fetchall()
     30         # print("result", result)
     31         print("result", result[0][0])
     32         return result
     33     except Exception as e:
     34         print("ERROR", e)
     35 
     36 def get_unupdate_news():
     37     db_init()
     38     sql = "SELECT * FROM news_detail WHERE flag_content_update = '0' AND web_source = 'techweb'  ORDER BY release_time DESC LIMIT 5000"
     39     try:
     40         cursor.execute(sql)
     41         result = cursor.fetchall()
     42         # print("result", result)
     43         news_list = []
     44         for row in result:
     45             news_id = row[6]
     46             news_url = row[1]
     47             news_web_source = row[5]
     48             news_list.append({
     49                 "id": news_id,
     50                 "url": news_url,
     51                 "web_source": news_web_source
     52             })
     53         print("news_list", news_list)
     54         return news_list
     55     except Exception as e:
     56         print("ERROR", e)
     57 
     58 def check_news(title, web_source):
     59     db_init()
     60     sql = "SELECT COUNT(*) FROM news_detail WHERE title = '%s' AND web_source = '%s'" %(title, web_source)
     61     try:
     62         cursor.execute(sql)
     63         result = cursor.fetchall()
     64         # print("result", result)
     65         print("result", result[0][0])
     66         return result[0][0]
     67     except Exception as e:
     68         print("ERROR", e)
     69 
     70 def update_news_content(news_id, news_content):
     71     if news_content != None:
     72         db_init()
     73         sql = "UPDATE news_detail SET content = '%s', flag_content_update = '1' WHERE id = '%s'" % (news_content, news_id)
     74         try:
     75             cursor.execute(sql)
     76             db.commit()
     77             print("content已更新!", news_id)
     78         except Exception as e:
     79             print("ERROR", e)
     80             db.rollback()
     81 
     82 def get_latest_data_50(web_source):
     83     db_init()
     84     sql = "SELECT * FROM news_detail WHERE web_source = '%s' ORDER BY release_time DESC LIMIT 50" % (web_source)
     85     try:
     86         cursor.execute(sql)
     87         result = cursor.fetchall()
     88         # print("result", result)
     89         title_list = []
     90         for row in result:
     91             title = row[0]
     92             title_list.append(title)
     93         # print("title_list", title_list)
     94         return title_list
     95     except Exception as e:
     96         print("ERROR", e)
     97 
     98 def get_latest_data(web_source):
     99     db_init()
    100     sql = "SELECT * FROM news_detail WHERE web_source = '%s' ORDER BY release_time DESC LIMIT 10" % (web_source)
    101     try:
    102         cursor.execute(sql)
    103         result = cursor.fetchall()
    104         # print("result", result)
    105         title_list = []
    106         for row in result:
    107             title = row[0]
    108             title_list.append(title)
    109         # print("title_list", title_list)
    110         return title_list
    111     except Exception as e:
    112         print("ERROR", e)
    113 
    114 def getData(source_name_val):
    115     db_init()
    116     # cursor = db.cursor()
    117     sql = "SELECT * FROM source_info WHERE source_name = '%s'" % (source_name_val)
    118     try:
    119         cursor.execute(sql)
    120         results = cursor.fetchall()
    121         for row in results:
    122             source_name = row[0]
    123             update_time = row[1]
    124             latest_news = row[2]
    125             # print("source_name", source_name)
    126             # print("update_time", update_time)
    127             # print("latest_news", json.loads(latest_news))
    128             return latest_news
    129     except Exception as e:
    130         print("ERROR",e)
    131 
    132     # 关闭数据库连接
    133     db.close()
    134 '''
    135 {"title": "寻电之路2:海外合资品牌的全面反攻", "url": "https://www.autotimes.com.cn/news/202012/1536909.html", "release_time": "2020-12-23 09:52", "source": "汽车之家", "content": "", "web_source": "qicheshidai", "source_type": "0", "polarity": 1}
    136 '''
    137 def insertDataQuick(data_object):
    138     # db_init()
    139     sql = "INSERT INTO news_detail(title,url,release_time,source,content,web_source,source_type,polarity) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
    140     # print(sql)
    141     try:
    142         cursor.execute(sql, (data_object["title"], data_object["url"], data_object["release_time"], data_object["source"], data_object["content"], data_object["web_source"],data_object["source_type"],data_object["polarity"]))
    143         # db.commit()
    144     except Exception as e:
    145         print("ERROR", e)
    146         db.rollback()
    147 def insert_Data_many(news_list):
    148     db_init()
    149     news_values = []
    150     for news in news_list:
    151         news_values.append((news["title"], news["url"], news["release_time"], news["web_source"], news["company"], news["source_type"]))
    152     cursor.executemany('INSERT INTO news_detail(title,url, release_time, web_source, company, source_type) VALUES(%s, %s, %s, %s, %s, %s)', news_values)
    153     db.commit()
    154     print("插入完成")
    155 
    156 def insertData(data_object):
    157     db_init()
    158     sql = "INSERT INTO news_detail(title,url,release_time,source,content,web_source,source_type,polarity) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
    159     # print(sql)
    160     try:
    161         cursor.execute(sql, (data_object["title"], data_object["url"], data_object["release_time"], data_object["source"], data_object["content"], data_object["web_source"], data_object["source_type"],data_object["polarity"]))
    162         db.commit()
    163     except Exception as e:
    164         print("ERROR", e)
    165         db.rollback()
    166 
    167     # cursor.close()
    168     # db.commit()
    169     # 关闭数据库连接
    170     db.close()
    171 
    172 def updateData(latest_news_val, source_name_val):
    173     db_init()
    174     latest_news = json.dumps(latest_news_val, ensure_ascii=False)
    175     sql = "UPDATE source_info SET latest_news = '%s' WHERE source_name = '%s'" % (latest_news, source_name_val)
    176     try:
    177         cursor.execute(sql)
    178         db.commit()
    179     except Exception as e:
    180         print("ERROR", e)
    181         db.rollback()
    182 
    183 
    184 
    185 # latest_news_temp = ["新闻1","xxx"]
    186 # db_init()
    187 # data_ob = {
    188 #     "title": "x",
    189 #     "url": "http://sss",
    190 #     "release_time": "2020-11-11 10:30:00",
    191 #     "source": "sohu",
    192 #     "content": "c",
    193 #     "web_source": "sohu"
    194 # }
    195 # insertData(data_ob)
    196 # insertData(data_ob)
    197 # insertData(data_ob)
    198 # getData('sohu')
    199 # updateData(latest_news_temp, 'sohu')
    200 # setData()
    201 # get_latest_data("techweb")
    202 # get_unupdate_news()
    203 # get_unupdate_count()
    204 # check_news("寻电之路2:海外合资品牌的全面反攻", "qicheshidai")
    mysql_utils

     

上一篇:在SHAP中进行模型解释需要先创建一个explaine


下一篇:【电商12】newsflash news