python操作mysql

  1. 云栖社区>
  2. 博客>
  3. 正文

python操作mysql

余二五 2017-11-22 17:08:00 浏览528
展开阅读全文

一、设计表结构

Student

字段名 类型 是否为空 主键 描述
StdID int 学生ID
StdName varchar(100)
学生姓名
Gender enum('M', 'F')
性别
Age tinyint
年龄

Course

字段名 类型 是否为空 主键 描述
CouID int 课程ID
Cname varchar(50)
课程名字
TID int
老师ID

Score

字段名 类型 是否为空 主键 描述
SID int 分数ID
StdID int
学生id
CouID int
课程id
Grade int
分数

Teacher

字段名 类型 是否为空 主键 描述
TID int 老师ID
Tname varcher(100)
老师名字



二、创建mysql表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
import MySQLdb
def connect_mysql():
   db_config = {
       "host""192.168.2.230",
       "port"3306,
       "user""root",
       "passwd""123456",
       "db""python",
       "charset""utf8"
   }
   try:
       cnx = MySQLdb.connect(**db_config)
   except Exception as e:
       raise e
   return cnx
if __name__ == '__main__':
    cnx = connect_mysql()
    cus = cnx.cursor()
    # sql  = '''insert into student(id, name, age, gender, score) values ('101', 'huang', 27, 'M', 99), ('102', 'zhao', 27, 'M', 90), ('103', 'li', 26, 'M', 87);'''
    student = '''create table Student(
            StdID int not null,
            StdName varchar(100) not null,
            Gender enum('M', 'F'),
            Age tinyint
    )'''
    course = '''create table Course(
            CouID int not null,
            CName varchar(50) not null,
            TID int not null
    )'''
    score = '''create table Score(
                SID int not null,
                StdID int not null,
                CouID int not null,
                Grade int not null
        )'''
    teacher = '''create table Teacher(
                    TID int not null,
                    TName varchar(100) not null
            )'''
    tmp = '''set @i := 0;
            create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;
        '''
    try:
        cus.execute(student)
        cus.execute(course)
        cus.execute(score)
        cus.execute(teacher)
        cus.execute(tmp)
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()



说明:

1)mysql中变量不用事前申明,直接使用“@变量名”;set是mysql中设置变量的特殊用法,当@i需要在select中使用的时候,必须加“:”

2)如上,从information_schema.tables表中取10条数据(任何表都可以),然后把变量@i作为id列的值,分10次不断输出,最后根据select的结果,创建表tmp(用于后面增删改查)


运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> show tables;
+------------------+
| Tables_in_python |
+------------------+
| Course           |
| Score            |
| Student          |
| Teacher          |
| employees        |
| test             |
| tmp              |
+------------------+
mysql> select from tmp;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+




三、添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
import MySQLdb
def connect_mysql():
   db_config = {
       "host""192.168.2.230",
       "port"3306,
       "user""root",
       "passwd""123456",
       "db""python",
       "charset""utf8"
   }
   try:
       cnx = MySQLdb.connect(**db_config)
   except Exception as e:
       raise e
   return cnx
if __name__ == '__main__':
    cnx = connect_mysql()
    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;
        '''
    course = '''set @i := 10;
            insert into Course select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 40)),  1 + floor(rand() * 100) from tmp a;
        '''
    score = '''set @i := 10000;
            insert into Score select @i := @i +1, floor(10001 + rand()*10000), floor(11 + rand()*10), floor(1+rand()*100) from tmp a, tmp b, tmp c, tmp d;
        '''
    teacher = '''set @i := 100;
            insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;
        '''
    try:
        cus_students = cnx.cursor()
        cus_students.execute(students)
        cus_students.close()
        cus_course = cnx.cursor()
        cus_course.execute(course)
        cus_course.close()
        cus_score = cnx.cursor()
        cus_score.execute(score)
        cus_score.close()
        cus_teacher = cnx.cursor()
        cus_teacher.execute(teacher)
        cus_teacher.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()



运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> select count(*) from Course;
+----------+
count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.08 sec)
mysql> select count(*) from Score;
+----------+
count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from Student;
+----------+
count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from Teacher;
+----------+
count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)



说明:

Student表

字段名 类型 是否为空 主键 描述
StdID int 学生ID
StdName varchar(100)
学生姓名
Gender enum('M', 'F')
性别
Age tinyint
年龄


语句分析:

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

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


3.1.StdID字段:select @i:=@i+1

解释:表示id号,@i从上一个sql语句中的10000开始


3.2.StdName字段:substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75))

解释:表示姓名是随机生成一个字符串


1)函数rand()

产生0到1之间的浮点数

例子:

1
2
3
4
5
6
7
mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.23552189044237312 |
+---------------------+
1 row in set (0.00 sec)



2)函数sha1()

加密,转化成字符串

例子:

1
2
3
4
5
6
7
mysql> select sha1(rand());
+------------------------------------------+
| sha1(rand())                             |
+------------------------------------------+
| 4e56e84d74f4a526eb5debe8221810b260503dde |
+------------------------------------------+
1 row in set (0.00 sec)



3)函数floor(浮点数)

取整(去掉小数部分)

例子:

1
2
3
4
5
6
7
mysql> select floor(rand() * 75);
+--------------------+
| floor(rand() * 75) |
+--------------------+
|                 26 |
+--------------------+
1 row in set (0.00 sec)



4)函数concat(字符串1,字符串2)

用于拼接两个字符串

例子:

1
2
3
4
5
6
7
mysql> select concat(sha1(rand()), sha1(rand()));
+----------------------------------------------------------------------------------+
| concat(sha1(rand()), sha1(rand()))                                               |
+----------------------------------------------------------------------------------+
| 0d6ba94bdecc5f328f8bf3e6e141063cabb5960ca0109cccce8f7e040e352865dc0bdd5c7feffac2 |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)



5)函数substr(字符串,起始位置,长度)

用于截取字符串

例子:

1
2
3
4
5
6
mysql> select substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75));
+-----------------------------------------------------------------------+
| substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)) |
+-----------------------------------------------------------------------+
| 9a91c6627be63546576ca7d500cecba34231e                                 |
+-----------------------------------------------------------------------+



3.3.Gender字段:case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end

解释:表示0-9取得的随机数除以2的余数,为1则性别M;为0则性别为F


3.4.Age字段:25-floor(rand() * 5)

解释:表示年龄在25+0=25岁至25+4=29岁之间的随机数


3.5.语句select * from tmp a, tmp b, tmp c

解释:a,b,c都是tmp表的别名,相当于每个表都循环了一遍,最终10的3次方个数据



Course表、Score表、Teacher表...




四、查询数据

需求:10000条数据,可能会有同学的名字是一样的,在数据库中查出来所有名字有重复的同学的所有信息,然后写入到文件中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
import codecs
import MySQLdb
def connect_mysql():
   db_config = {
       "host""192.168.2.230",
       "port"3306,
       "user""root",
       "passwd""123456",
       "db""python",
       "charset""utf8"
   }
   try:
       cnx = MySQLdb.connect(**db_config)
   except Exception as e:
       raise e
   return cnx
if __name__ == '__main__':
    cnx = connect_mysql()
     
    # 把所有学生名字重复的学生都列出来
    sql = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''
    try:
        cus = cnx.cursor()
        cus.execute(sql)
         
        # 将游标得到的结果集中所有行(tuple形式)取出,并写进select.txt文件中
        result = cus.fetchall()   
        with codecs.open('select.txt''w+') as f:
            for line in result:
                f.write(str(line))
                f.write('\n')
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()


运行结果:本地select.txt文件

(18559L, u'167', u'F', 24)

(19579L, u'167', u'F', 21)

(13281L, u'3d3', u'M', 22)

(15806L, u'3d3', u'M', 22)

(16476L, u'4dd', u'F', 24)

(18134L, u'4dd', u'F', 24)

(12116L, u'db3', u'M', 22)

(14153L, u'db3', u'F', 25)



五、删除数据

需求:开除及格率最差的5名老师

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
import MySQLdb
def connect_mysql():
   db_config = {
       "host""192.168.2.230",
       "port"3306,
       "user""root",
       "passwd""123456",
       "db""python",
       "charset""utf8"
   }
   try:
       cnx = MySQLdb.connect(**db_config)
   except Exception as e:
       raise e
   return cnx
if __name__ == '__main__':
    cnx = connect_mysql()
    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 )
    '''
    try:
        cus = cnx.cursor()
        cus.execute(sql)
        result = cus.fetchall()
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()


运行结果:

无报错信息



六、修改数据

需求:分数低于5分的成绩所有都加60分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import MySQLdb
def connect_mysql():
   db_config = {
       "host""192.168.2.230",
       "port"3306,
       "user""root",
       "passwd""123456",
       "db""python",
       "charset""utf8"
   }
   try:
       cnx = MySQLdb.connect(**db_config)
   except Exception as e:
       raise e
   return cnx
if __name__ == '__main__':
    cnx = connect_mysql()
    sql = '''select *, (grade+60) as newGrade from Score where Grade <5;'''
    update = '''update Score set grade = grade + 60 where grade < 5;  '''
    try:
        cus_start = cnx.cursor()
        cus_start.execute(sql)
        result1 = cus_start.fetchall()
        print(len(result1))
        cus_start.close()
        cus_update = cnx.cursor()
        cus_update.execute(update)
        cus_update.close()
        cus_end = cnx.cursor()
        cus_end.execute(sql)
        result2 = cus_end.fetchall()
        print(len(result2))
        cus_end.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()



运行结果:

374

0



七、添加索引

 需求:创建Course的CouID的字段为主键,Score的SID字段为主键,Student的StdID字段为主键,Teacher的TID字段为主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import MySQLdb
def connect_mysql():
   db_config = {
       "host""192.168.2.230",
       "port"3306,
       "user""root",
       "passwd""123456",
       "db""python",
       "charset""utf8"
   }
   try:
       cnx = MySQLdb.connect(**db_config)
   except Exception as e:
       raise e
   return cnx
if __name__ == '__main__':
    cnx = connect_mysql()
    sql1 = '''alter table Teacher add primary key(TID);'''
    sql2 = '''alter table Student add primary key(StdID);'''
    sql3 = '''alter table Score add primary key(SID);'''
    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;'''   delete index
    sql7 = '''explain select * from Score where StdID = 16213;'''
    try:
        cus = cnx.cursor()
        cus.execute(sql1)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql2)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql3)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql4)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql5)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql7)
        result = cus.fetchall()
        print(result)
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()


运行结果:

((1L, u'SIMPLE', u'Score', u'ref', u'idx_StdID_CouID', u'idx_StdID_CouID', u'4', u'const', 1L, None),)










本文转自 huangzp168 51CTO博客,原文链接:http://blog.51cto.com/huangzp/2043384,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注