python操作mysql二

 

游标

游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，游标充当指针的作用，尽管游标能遍历结果中的所有行，但它一次只指向一行，游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

 

创建游标对象

conn = pymysql.connect(**dbConfig) # **dbConfig指的是需要传入的mysql的参数，如host，port，user，passwd，db
cursor = conn.cursor()

 

游标常用的方法

cursor.close()  关闭此游标对象
cursor.execute(sql[,args])   执行sql语句，增删改查
cursor.executemany(sql,args) 执行多个sql语句，增删改查
cursor.fetchone()   得到结果集的下一行
cursor.fetchmany()  得到结果集的下几行
cursor.fetchall()   得到结果集中剩下的所有行

 

数据库连接池

通过python对mysql进行增删改查操作时，每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。

import pymysql
from DBUtils.PooledDB import PooledDB

db_config = {
        "host": "127.0.0.1",
        "port": 3306,
        "user": "root",
        "passwd": "123456",
        "db": "test",
        # "charset": "utf8"
    }

spool = PooledDB(pymysql, 5, **db_config)  # 5为连接池里的最少连接数
conn = spool.connection()  # 以后每次需要数据库连接就是用connection()函数获取连接
cur = conn.cursor()
SQL = "select * from user;"
result = cur.execute(SQL)
result = cur.fetchall()
print(result)
cur.close()
conn.close()

 

数据库的相关操作

常用函数

sql = '''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, 就代表的是，如果余数是1就代表M，否则代表F
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

 

创建表

create table Student(
stdId int not null,
stdname varchar(100),
age int,
sex enum('M', 'F'),
score int);

 

插入数据

insert into Student (stdId, stdname, age, sex, score) values (1, "yangjian", 19, "M", 95), (2, "zhangsan", 15, "F", 99);

 

查询数据

# 查询所有名字重复的学生信息
select * from Student where stdname in (select stdname from Student group by stdname having count(1)>1 ) order by stdname;

 

更新数据

update Student set score = 100 where stdname = "yangjian";

 

删除数据

truncate Student  # 清除表中的所有数据
delete from Student where stdname = "yangjian";

 

索引

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

 

创建索引

alter table table_name add index index_name (column_list);
create index index_name on table_name (column_list);

 

删除索引

drop index index_name on talbe_name;
alter table table_name drop index index_name;

 

查看索引

show index from talbe_name;

 

注意：

下面创建出来的索引是两个不同的索引。

create index idx_student_name_id on Student (stdId,stdname);

show index from Student;

+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Stdunet |          1 | idx_student_name_id |            1 | stdId       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| Stdunet |          1 | idx_student_name_id |            2 | stdname     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+