python与mysql

cursor=conn.cursor(cursor=pymysql.cursor.DictCursor)
不加此参数，select获取的数据用列表里嵌套元组的形式，
加了参数，获取的数据 是 列表里嵌套字典，字典的健是属性名。

 

一.先安装pymysql库

pip3 install pymysql

二.创建与数据库的连接，进行增删改查

#创建连接
import pymysql
conn= pymysql.connect(host='127.0.0.1'，port=3306,user='root',passwd='123456',db='school')
#创建游标.
cursor=conn.cursor()
#执行sql，并返回受影响的hangshu
#更改数据
effect_row=cursor.execute("update hosts set host='1.1.1.2'")#表示预执行
conn.commit()#正式执行，注意是conn,

#sql语句的连接用函数传参的方法，不要用字符串的拼接。
#effect_row=cursor.execute("update hosts set host='1.1.1.2' where nid > %s",(1,))
#增加数据
effect_row=cursor.execute("insert into hosts(host,color_id) value(%s,%s)".[("1.1.1.1",1),("1.1.1.2".2)])#表示预执行,同时插入两条数据
conn.commit()#正式执行，注意是conn,提交，不然无法保存新建或修改的数据

#!/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.execute("select * from hosts")
  
# 获取第一行数据
row_1 = cursor.fetchone()
  
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
  
conn.commit()
cursor.close()
conn.close()

 

　　注意：

　　默认获取数据是以元组的形式，如果需要字典类型的数据。

　　

　　cursor.fetchone()与cursor.fetchmany(size)的本质都是将已有的数据全部拿出来了。

　　在fetch取数据时按照顺序进行，可以使用cursor.scroll（num,mode）来移动游标的位置。

 Corsur.commit()#select不需要commit(),update,delete,insert,才需要commit

　　eg:

　　　　cursor.srcoll(1,mode='relative')相对当前位置移动

　　　　cursor.srcoll(2,mode=absolute)#相对绝对位置移动

三.SQL注入

#防止SQL注入
cursor.excuser('select  *    from   tb_user   where    userne=%s,password=%s',('alex',123))
Resurt=cursor.fetchone()
#SQL注入了
Sql='select  *    from   tb_user   where    userne="%s",password="%s"'
Sql=Sql  %('alex --',123)#--是SQL语句的注释，会导致数据任然可以取出。

四.获取自增ID

Cursor.lastrowid()

 五.存储过程

储存过程是存放在服务端，与视图一样，

in 传参

out  返回值    参数要以引用的方法，初始化始终是null。

inout  两种都可

 

注意：一个储存过程只能有一个select的返回表，即资源值

--将ss定义为 ；
delimiter   ss  
Create  produce   p1(
in a1 int,
out a2 varcher(39),
inout a3 int)
Begin
delcare temp   int     --在存储器内部定义一个变量
.....
End

import pymysql

conn=pymysql.connect(host='127.0.0.1',port=3306,user='alex',passwd='123456',db='db_school',charset='utf8')
cursor=conn.cursor(cursor=pymysql.cursore.DictCursor)
R1=cursor.callproc(p1, arge=[1,2,3])#执行存储过程,存储过程中可能有select语句
#R1=[1,2,3]

resurt1=cursor.fetchall()#获取select的返回值

R2=Cursor.execute('select @_p1_0,@_p1_1,4_p1_2')#固定写法，@_储存过程名_参数下标  R2=1
resort2=cursor.fetchall()#获取返回值，即参数也是以一个表方式返回
Print (result2)
cursor.commmit()#储存过程中可能有update,delete,insert语句
cursor.close()
Conn.close()

 六，SQL中储存过程

几种语句格式
1：
If  条件 then
....
....
Else
.....
end if
2：
While 条件do
......
End  while
3:
Repeat
.....
....
Until 条件
End repeat
4:
loop_lable; loop
......
If  条件 then
      leave  loop_lable;
End if;
End loop

2.动态执行SQL语句（即传入SQL语句）

delimiter //
drop procedure if exit  proc_sql()
Create procedure  proc_sql()--可以定义varchar 变量，这里直接用字符串代替
BEGIN
    Declare p1 int;
    Set p1 =11;
    Set   @p1=p1;--规定向字符串传入参数时要用@变量名


   Prepare  prod  from   'select  * from tb_student  where  name=?'--  ？是占位符
  Execute prod  using @p1;字符串连接，这里防止SQL注入
  deallocate   prepare prod--prod为用户自己取的名字
end //
delimiter;

 七.触发器

1.触发器包括  insert ，update，delete 前后，但没有select

2.新输入的数据为new表的数据，old为原来表一直有的，

 八.事务

1.将几个原子操作组成为一个新的原子操作。innodb引擎支持。

delimiter //
CREATE  produce p1 (
      OUT  return_code tinyint)
BEGIN
  declare exit hander  sqlexception  --这里是对报错的处理，还有警告处理  sqlwarning
      BEGIN -- error
         set  return_code=1;
        rollback;
      END;
start  transaction;   --  事务的开始
   delete from tb1;
  Insert  into  tb2(name) values('info');
   commit;
Set  return_code=0;
End  //
delimiter;

 九.自定义函数

1.函数调用  select  函数名（参数）

注意：自定义函数中不能有select语句

       特殊情况：select  nid into a from tb_student  where name='alex'

                       相当于是将 a=nid

 

delimiter //
create function f1(

     A1 int,
   A2 int)
  returns int
begin
 declare num int;

   set  num=A1+A1;
return  (num);
END;
delimiter;

 十.索引

1.索引的两个2个作用：约束，加快查询

2.  explain sql 语句检测执行过程

3.分类

  普通索引  加快查询

    主键索引     约束，加快查询，主键索引不能为null

      唯一索引    约束，加快查询，唯一索引的列可以为空

组合索引    由多个列构成，可以创建上面👆🏻几个类型

合并索引

覆盖索引

all   index之后的都走索引   range  ....

3.组合索引是两个列成为一个索引   合并索引是两个索引共同查询。

组合索引更快，组合索引是一个表，但是有最左原则，最左边的列才能走索引

合并索引是两个表同时检索

详情：www.cnblogs.com/wupeiqi/articles/5713323.html

 十一.分页  limit

limit的原理  limit   x:n   扫描到x行后再扫描n行  x+n行

             解决方法  nid>最近的一个nid    nid>m  limit x:n  先跳的m后再扫描n行

十二.　慢sql日志

用于记录慢sql的记录，日志的参数修改后要重启系统或者更改内存中的参数。