pymysql中的游标和连接池，以及数据库的一些操作

一、游标的方法。

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理

通俗来说就是，操作数据和获取数据库结果都要通过游标来操作。

1.execute()

执行语句的方法。当我们定义了一个sql语句的时候可以用这个方法来执行这个语句

比如sql = select * from table1 cus.execute(sql) 这样就执行了sql语句

2.fetchall()

取所有结果，就是获得执行sql语句后获得的结果。

游标常用的方法：

常用方法:

3.cursor():创建游标对象

Cus = connect_mysql().cursour()

4.close():关闭此游标对象

5.fetchone():得到结果集的下一行

6.fetchmany([size = cursor.arraysize]):得到结果集的下几行

7.executemany (sql, args):执行多个数据库查询或命令

个人推荐，尽量不使用executemany，通过程序循环不断调用excute函数

二、数据库连接池

python编程中可以使用MySQLdb进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。

小例子：

import pymysql

from DBUtils.PooledDB import PooledDB

from DBUtils.PooledDB import PooledDB

db_config = {

"host": "192.168.48.131",

"port": 3306,

"user": "xiang",

"passwd": "xiang",

"db": "python",

# "charset": "utf8"

}

spool = PooledDB(pymysql, 5, **db_config) # 5为连接池里的最少连接数

# def connect_myssql():

conn = spool.connection() # 以后每次需要数据库连接就是用connection（）函数获取连接

cur = conn.cursor()

SQL = "select * from tmp;"

r = cur.execute(SQL)

r = cur.fetchall()

print(r)

cur.close()

conn.close()

一、数据库操作

1.建表语句

student = '''create table Student(

StdID int not null,

StdName varchar(100) not null,

Gender enum('M', 'F'),

Age tinyint

)'''

2.增加数据：

sql = '''insert into student(id, name, age, gender, score) values ('1001', 'ling', 29, 'M', 88), ('1002', 'ajing', 29, 'M', 90), ('1003', 'xiang', 33, 'M', 87);''‘

students = '''set @i := 10000;

insert into Student select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)), case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end, 25-floor(rand() * 5) from tmp a, tmp b, tmp c, tmp d;

'''

substr是一个字符串函数，从第二个参数1,开始取字符，取到3 + floor(rand() * 75)结束

floor函数代表的是去尾法取整数。

rand()函数代表的是从0到1取一个随机的小数。

rand() * 75就代表的是：0到75任何一个小数，

3+floor(rand() * 75)就代表的是：3到77的任意一个数字

concat()函数是一个对多个字符串拼接函数。

sha1是一个加密函数，sha1(rand())对生成的0到1的一个随机小数进行加密，转换成字符串的形式。

concat(sha1(rand()), sha1(rand()))就代表的是：两个0-1生成的小数加密然后进行拼接。

substr(concat(sha1(rand()), sha1(rand())), 1, floor(rand() * 80))就代表的是：从一个随机生成的一个字符串的第一位开始取，取到（随机3-77）位结束。

Gender字段：case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,就代表的是，

floor(rand()*10)代表0-9随机取一个数

floor(rand()*10) mod 2 就是对0-9取得的随机数除以2的余数，

case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,代表：当余数为1是，就取M，其他的为F

3.查数据

sql = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;''‘

解释：

我们先来分析一下select查询这个语句：

select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'

我们先来看括号里面的语句：select StdName from Student group by StdName having count(1)>1；这个是把所有学生名字重复的学生都列出来，

最外面select是套了一个子查询，学生名字是在我们（）里面的查出来的学生名字，把这些学生的所有信息都列出来。

5.删除数据

有些老师不好好上次，导致课程的及格率太低，最后名最差的5名老师将会被开除。

sql = '''delete from Teacher where TID in(

select TID from (select Course.CouID, Course.TID, Teacher.TName, count(Teacher.TID) as count_teacher from Course

left join Score on Score.Grade < 60 and Course.CouID = Score.CouID

left join Teacher on Course.TID = Teacher.TID

group by Course.TID

order by count_teacher desc

limit 5) as test )

'''

解释：

1. 先查询出Course表中的Course.TID和Course.TID

2. left join 是关联Score表，查出Score.Grade > 59，并且，课程ID和课程表的CouID要对应上

3. left join Teacher 是关联老师表，课程中的了老师ID和老师表中的老师ID对应上

4. select中加上老师的名字Teacher.Tname和count(Teacher.TID)

5. group by Course.TID，在根据老师的的TID进行分组

6. oder by 最后对count_teacher进行排序，取前5行，

7. 在通过套用一个select子查询，把所有的TID搂出来

8. 然后delete from Teacher 最后删除TID在上表中的子查询中。

6修改数据：

update = '''update Score set grade = grade + 60 where grade < 5; ''‘

索引：

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

.索引：

sql4 = '''alter table Course add primary key(CouID);'''

sql5 = '''alter table Score add index idx_StdID_CouID(StdID, CouID);'''

删除索引：

# sql6 = '''alter table Score drop index idx_StdID_CouID;''' 删除索引

sql7 = '''explain select * from Score where StdID = 16213;'''