MySQL 之 Python 操作 MySQL（二）

一、导入导出数据库数据

导出现有数据库数据：

• mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据
• mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构 

导入现有数据库数据：

• mysqldump -uroot -p密码  数据库名称 < 文件路径  

二、习题经验

　　1. avg 获取平均值

　　2. 连表方式 直接连表、left join 、通过临时表连表

select student_id,avg(num) from score group by student_id HAVING avg(num)>60 
 
SELECT score.student_id,student.sname,AVG(num) from score
left join student on score.student_id = student.sid 
GROUP BY student_id
select * from score,student where score.student_id = student.sid GROUP BY score.student_id

SELECT
    T.student_id,
    T.a,
    student.sname
FROM
    (
        SELECT
            student_id,
            avg(num) AS a
        FROM
            score
        GROUP BY
            student_id
        HAVING
            avg(num) > 60
    ) AS T
LEFT JOIN student ON T.student_id = student.sid

　　3. 计算一列的行数 count(1) 

　　4. distinct 列名 ——用来去重的，自动把重复的去掉。不光有 group by 。

　　5. 关于存在的问题 可以用 in 、not in 来解决。

　　6. case when .... then ... else... end 相当于 if .... else。

　　7. 使用通配符时候 要用 like 进行匹配 例如： select * from .... where xxx like '张%' ;

　　8. 统计某个数目时，可以考虑分组后进行 count 计数。

　　9. 可以直接 left join 多张表 例如： select * from xxx left  join xxx on aa=bb left  join ....

　　10. 按照某种顺序排序   例如：order by xxx ASC/DESC

三、Python 操作 Mysql

　　3.1 pip 模块

　　　　pip3 install pymysql 

　　　　python2 默认无 pip 命令

　　　　python3 默认自带 pip 命令 python3 -m pip install --upgrade pip 升级 pip

　　3.2 pymysql 

　　　　专门用来操作 Mysql 的 python 模块，MySQLdb 不支持 py3

　　　　使用操作：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
  
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
  
  
# 提交，不然无法保存新建或者修改的数据
conn.commit()
  
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　3.3 pymysql 之增删改查

　　实例1：使用 pymysql 添加一行数据

#author:"LFD"
#date: 2018/7/7
# !/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()

# SQL 语句

cursor.execute('insert into class(caption) VALUES ("全栈二班")')

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　实例2：使用 pymysql 完成字符串拼接 SQL 语句　　禁止操作，不要用这种操作。容易导致 SQL 注入。

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()

# SQL 语句
inp = input('请输入班级：')
sql = 'insert into class(caption) VALUES ("%s")'
sql = sql %(inp,)
r = cursor.execute(sql)
print(r)    # 执行 SQL 受影响的行数

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　实例3：使用 insert 语句传递单组数据的 SQL 语句　　首选，最好使用这种方式。

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()

# SQL 语句
inp = input('请输入班级：')
r = cursor.execute('insert into class(caption) VALUES (%s)',inp)
print(r)    # 执行 SQL 受影响的行数

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　实例4：使用 insert 语句传进去多个数据，注意元组的使用。

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()

# SQL 语句
# inp = input('请输入学生信息：')
r = cursor.execute('insert into student(gender,class_id,sname) VALUES (%s,%s,%s)',('男',7,'刘飞铎'))
print(r)    # 执行 SQL 受影响的行数

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　实例5：使用 insert 语句传进去多个多组数据。excutemany('insert into .......',L)  L 是可迭代变量。

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()

# SQL 语句
# inp = input('请输入学生信息：')
l = (
('男',7,'刘飞铎'),
('女',7,'高小连'),
('女',7,'高玉芬'),
)
r = cursor.executemany('insert into student(gender,class_id,sname) VALUES (%s,%s,%s)',l)
print(r)    # 执行 SQL 受影响的行数

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　实例6：使用 update 更新数据

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()

# SQL 语句
# inp = input('请输入学生信息：')
r = cursor.execute('update student set sname=%s where sid =%s',('小可爱',1))
print(r)    # 执行 SQL 受影响的行数

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　实例7：查询，使用查询的时候不需要使用 commit() 

　　　常用的方法：fetchall，fetchone。

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()

# SQL 语句
# inp = input('请输入学生信息：')

r = cursor.execute('select * from student')
print(r)    # 执行 SQL 受影响的行数
result = cursor.fetchmany(3) # 拿出数据使用的命令
print(result,end='
')

# 提交，不然无法保存新建或者修改的数据
#conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

　　3.4 fetch 数据类型

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
  
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
  
result = cursor.fetchone()
  
conn.commit()
cursor.close()
conn.close()

　　3.5 获取自增 id 　　　cursor.lastrowid

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
  
# 获取最新自增ID
new_id = cursor.lastrowid
print(new_id)

　　　　注意：如果插入多条数据的话，那么拿到的数据将是最后一条数据的 nid 

四、实战操作

　　4.1 动态导入模块

module = 'src.commons'
func_name = 'add'

import importlib    # 动态导入模块

m = importlib.import_module(module)
#print(m)    #<module 'src.commons' from 'D:\Python\newwork\2018\7\test1\src\commons.py'>
func = getattr(m,func_name)
func()

　　4.2 写配置文件

　　　数据库连接写入配置文件：　　用户名　　密码　　数据库　　端口　　编码

　　4.3 数据库中的每一张表在 Python 程序中要创建一个类（小项目的时候，表不多）

　　　例如：

class UserInfoRepository:
    def get_all(self):
        sql = 'select * from userinfo'
        return fetchall()

    def get_one_by_user_pwd(self,username,password):
        sql = "select * from userinfo where username = %s and password = %s"
        cursor.excute(sql,username,password)
        return cursor.fetchone()

    def add(self):
        sql = "insert into userinfo(....) VALUES(....)"
        conn.commit()

　　　统一对这张表进行操作的都放在该类当中，有几张表有几个类。

　　　连表操作时秉承一个原则：LEFT JOIN  谁在前面，就放在哪张表中。

五、进阶 MySQL

　　5.1 视图

　　　概念：视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名

称】即可获取结果集，并可以将其当作表来使用。

　　5.1.1 创建视图

--格式：CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
SELET nid, 
    name
FROM
    A
WHERE
    nid > 4

　　5.1.2 删除视图

--格式：DROP VIEW 视图名称

DROP VIEW v1

　　5.1.3 修改视图

-- 格式：ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW v1 AS
SELET A.nid,
    B. NAME
FROM
    A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

　　5.1.4 使用视图

select * from v1

　　视图是一个虚拟表，对主表的修改会影响到视图表。

六、存储过程

　　存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。存储过程 存储在 MySQL 的服务端。

　　6.1 创建存储过程

-- 创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;



-- 执行存储过程

call p1()

　　delimiter  //  就修改了 mysql 的终止符为 // ，写完一条语句之后不再是 ; 终止，而是 // 对语句进行终止了。

　　6.2 删除存储过程

　　　　DROP TRIGGER tri_after_insert_tb1; 

　　6.3 存储过程添加参数

对于存储过程，可以接收参数，其参数有三类： 

• • in          仅用于传入参数用
  • out        仅用于返回值用
  • inout     既可以传入又可以当作返回值

　　创建含有参数的存储过程

-- 创建存储过程
delimiter \
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end\
delimiter ;

-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

　　　存储过程可以添加参数，可以拿到返回值。

　　6.4 执行存储过程 

　　　6.4.1 通过 MySQL 终端运行

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

　　　6.4.2 通过 PyMySQL 运行

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

　　6.5 存储过程的实例

　　　　在 pymysql 中操作。

#author:"LFD"
#date: 2018/7/16
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='sqlexample')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程,获取存储过程的结果集，将返回值设置给了 @_存储过程名_序号 =
cursor.callproc('p5', args=(1, 22, 3, 4))
result1 = cursor.fetchall()
print(result1)
# 获取执行完存储的参数
cursor.execute("select @_p5_0,@_p5_1,@_p5_2,@_p5_3")
result2 = cursor.fetchall()

conn.commit()   #保险起见，加了一个 commit 以便于有其他的操作。    
cursor.close()
conn.close()


print(result2)

　　6.6 存储过程中的 if .. else ..

delimiter \
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END\
delimiter ;

　　6.7 存储过程中循环语句

delimiter \
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END\
delimiter ;

delimiter \
CREATE PROCEDURE proc_repeat ()
BEGIN

    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;

END\
delimiter ;

BEGIN
    
    declare i int default 0;
    loop_label: loop
        
        set i=i+1;
        if i<8 then
            iterate loop_label;
        end if;
        if i>=10 then
            leave loop_label;
        end if;
        select i;
    end loop loop_label;

END

　　6.8 动态执行 SQL 语句

delimiter \
DROP PROCEDURE IF EXISTS proc_sql \
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\
delimiter ;

七、触发器

　　7.1 创建基本语句（tb1 是事例表名称）

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

 　　特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

　　7.2 删除触发器

　　DROP TRIGGER tri_after_insert_tb1;

　　7.3 使用触发器

　　　　触发器的使用建立在对于表的【增/删/改】操作被动引发的。

八、事务

　　事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

delimiter \
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\
delimiter ;

 

　　rollback 就是回滚

九、总结