Python连接数据库

---

1.目标

-- 数据库 子查询(理解) 存储过程(了解) python连接数据库(掌握)
-- 上节课:
-- 查询语句:带条件
-- 聚合函数
-- 排序
-- 分页
-- having和where的区别
-- 左连接 右连接 内连接 隐式链接

2.子查询

-- 就是一个查询语句中再嵌套一个查询语句
-- 作用：子查询的结果能够返回给主查询,作为条件或者作为一张临时表
-- 题目:查询员工表里面最高工资的员工信息
select * from emp;
-- 查询最高工资是多少max(列名)
select max(salary) from emp;
-- 对应员工的信息 怎么写?
-- 换种问法:可以查询出工资为9000(最高工资)的员工信息
select * from emp where salary = 9000;
select * from emp where salary = (select max(salary) from emp);

select * from emp;
select * from dept;
-- 查询工资大于5000的员工在哪个部门
-- 表连接
-- 查询员工及员工所在部门
select * from emp,dept where emp.dept_id = dept.id and salary > 5000;

-- 子查询写? 1,2,3
select * from emp where salary>5000;
select * from dept where id in (select dept_id from emp where salary > 5000);
select * from dept d,(select * from emp where salary > 5000) e where d.id = e.dept_id;

3.作业

-- 1.使用嵌套语句查询选修课程名称为‘税收基础’的学员学号和姓名
-- 2.使用嵌套语句查询选修课程编号为‘C2’的学员姓名和所属单位
-- 3.使用嵌套语句查询不选修课程编号为‘C5’的学员姓名和所属单位
-- 学生表
CREATE TABLE S(
	SNO int,
	SNAME VARCHAR(20),
	SDD VARCHAR(50),
	SA int
);

-- 课程表
CREATE TABLE C(
		CNO VARCHAR(20) NOT  NULL  PRIMARY  KEY,
	CNAME varchar(20)
);

-- 成绩表
CREATE TABLE SC(
	SNO int,
	CNO VARCHAR(20),
	SCORE INT
);


-- 学生表
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019001,'托马斯李','运营',26);
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019002,'米高扬','管理',30);
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019003,'蝙蝠侠','安防',22);
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019004,'李嘉诚','投资',45);
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019005,'雷军','开发',34);
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019006,'周小川','管理',56);
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019007,'陆奇','运营',36);
INSERT  INTO  S(SNO,SNAME,SDD,SA)   VALUES(2019008,'普京','安防',67);

-- 课程表
INSERT  INTO  C(CNO,CNAME)   VALUES('C1','税收基础');
INSERT  INTO  C(CNO,CNAME)   VALUES('C2','金融工程');
INSERT  INTO  C(CNO,CNAME)   VALUES('C3','会计');
INSERT  INTO  C(CNO,CNAME)   VALUES('C4','统计学习方法');
INSERT  INTO  C(CNO,CNAME)   VALUES('C5','大数据');
INSERT  INTO  C(CNO,CNAME)   VALUES('C6','机器学习算法');

-- 成绩表
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019001,'C2',80);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019002,'C2',78);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019003,'C1',89);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019003,'C5',60);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019004,'C4',90);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019005,'C1',87);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019005,'C2',75);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019005,'C3',80);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019005,'C4',90);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019005,'C5',86);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019005,'C6',88);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019006,'C1',99);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019006,'C2',61);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019007,'C1',62);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019007,'C2',78);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019007,'C3',77);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019007,'C4',69);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019007,'C5',98);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019007,'C6',88);
INSERT  INTO  SC(SNO,CNO,SCORE) VALUES(2019008,'C1',78);

select * from s;
select * from sc;
select * from c;

-- 1.使用嵌套语句查询选修课程名称为‘税收基础’的学员学号和姓名
/*
查询:学员学号和姓名
条件：选修课程名称为'税收基础'C
需要用到SC表 税收基础 C1

s表和c表是没有关系的，sc表
c表 税收基础对应的课程编号 cno = 'c1'
sc表找c1对应的sno(学生编号）哪几个学生学了c1(税收基础')
根据学生编号找到了学生信息 s表里面找sno = 003,005,006,007
sc表 找c1课程就能找到学生编号 s表 找003

*/
select sno,sname from s;
select * from c where cname = '税收基础';
select * from sc where CNO = 'c1'; -- 知道了学员编号是不是就能查询到对应的学员信息
select sno,sname from s where sno in (select sno from sc where cno = (select CNO from C where CNAME = '税收基础'));


select S.SNO,SNAME from S,SC where S.SNO = SC.SNO and SC.CNO = (select CNO from C where CNAME = '税收基础');

-- 2.使用嵌套语句查询选修课程编号为‘C2’的学员姓名和所属单位
/*
查询学员姓名和所属单位 s表 sname,sdd
选修了'c2'课程 001 002 005 006 007
*/
select sname,sdd from s where sno in (select  sno from sc where cno = 'c2');
select * from sc whre cno = 'C2';
select SNAME,SDD from S where SNO in (select SNO from SC where 	CNO = 'C2');

-- 3.使用嵌套语句查询不选修课程编号为‘C5’的学员姓名和所属单位
-- 03,05,07选了c5课程
select * from sc where cno = 'c5';
select SNAME,SDD from S where SNO not in (select SNO from SC where CNO = 'C5');

4.存储过程

-- 函数封装 封装sql语句 用的时候 使用存储名调用
-- 语法
/*
delimiter //
create PROCEDURE 存储名()
begin
	select * from 表名;
END
*/
-- 创建存储过程 无参数 无返回
delimiter //
create procedure test_1()
BEGIN
	select * from student3;
END//

-- 使用 call存储名()
call test_1()

-- 有参数 in 传数据进来
delimiter //
create PROCEDURE test_2(in uname varchar(20))
BEGIN
	select * from student3 where name = uname;
END//
call test_2('马云')

-- 有返回 用out 输出数据
delimiter //
create PROCEDURE test_3(in uname varchar(20),out usex varchar(20))
BEGIN
	select sex into usex from student3 where name = uname;
END//
call test_3('马云',@usex);
select @usex

5.用Python连接数据库

# 1.连接数据库
# 2.操作数据库 创建游标 用游标 执行sql语句 获得结果
# 3.关闭数据库

①导包

#pymysql安装方式 pip install pymysql
import pymysql

②连接数据库

con = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='xxx',database='you',charset='utf8')
print(con)

③操作数据库, 创建游标

cur = con.cursor()
# 写sql
sql = 'select * from student3'
# 执行sql
cur.execute(sql)

④查询几条数据

# fetchmany(指定数据) 查询所有数据fetchall  查询一条数据fetchone
# result = cur.fetchall()
# result = cur.fetchone()
result = cur.fetchmany(2)
print(result)

⑤增加 修改 删除

con = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='xxx',database='you',charset='utf8')
cur = con.cursor()
# sql='delete from student3 where id = 8'
sql1 = 'insert into student3(id,name) values(8,"aaa")'
cur.execute(sql1)
cur.execute('commit')

# 别的地方 不会重新写 用哪部分改一下 封装函数

⑥封装

import configparser

import pymysql


class MySQLDB:
    # 封装这个部分
    # 类的作用:模板，后面可以实例化使用
    # init:连接数据库 创建了游标 为什么init数据库 创建了游标:只要用到查询语句 修改语句
    # 读取配置文件里面的数据,再放到连接中连接数据库
    # 获取配置文件里面的数据 MySqlDB('db.ini',Test_db)
    def __init__(self,configpath,db):
        config = configparser.ConfigParser()
        config.read(configpath)
        #读取文件数据
        host=config[db]['host']
        port=int(config[db]['port'])
        user=config[db]['user']
        password=config[db]['password']
        database=config[db]['database']
        charset=config[db]['charset']
        self.con = pymysql.connect(host=host, port=port, user=user, password=password, database=database,
                                  charset=charset)
        self.cur = self.con.cursor()
    # 连接部分写在初始化函数里面
    # 初始化的作用是什么:调用就连接
    # 实例化类 立马进入到init初始化函数
    # 调用的时候传ip地址，端口号这类
    # def __init__(self,host,port,username,password,db,charset):
    #     try:
    #         self.con = pymysql.connect(host=host, port=port, user=username, password=password, database=db,
    #                               charset=charset)
    #     except Exception as e:
    #         print("数据库连接有问题"%e)
    #     self.cur = self.con.cursor()

    def close(self):
        self.cur.close()
        self.con.close()

    # 1.连接 2.创建游标 3.写sql语句 4.执行sql 5.获得结果
    # 查询语句
    def get_all(self,sql):
        ret = None
        try:
            self.cur.execute(sql)
            ret = self.cur.fetchall()
            self.close()
        except Exception as e:
            print(e)
        return ret

    def get_one(self, sql):
        ret = None
        try:
            self.cur.execute(sql)
            ret = self.cur.fetchone()
            self.close()
        except Exception as e:
            print(e)
        return ret


    def edit(self,sql):
        count = 0
        try:
            self.cur.execute(sql)
            count = self.cur.execute('commit')
            self.close()
        except Exception as e:
            print(e)
        return count


# 子查询 灵活运用
# 存储过程 函数去学 传参 有返回
# python连接数据库

⑦调用

from class15.demo3 import MySQLDB

# a=MySQLDB('127.0.0.1',3306,'root','xxx','you','utf8')
# b=a.get_one('select * from student3')
# print(b)

# 项目中写法 ini文件config
a = MySQLDB('db.ini','Test_db')
b = a.get_one('select * from student3')
print(b)

⑧配置文件化

文件名:db.ini
[Test_db]
host=127.0.0.1
port=3306
user=root
password=xxx
database=you
charset=utf8

[zs_db]
host=127.0.0.1
port=3306
user=root
passsword=xxx
db=you
charset=utf8