mysql 数据库数据建立与查询

数据库初始没有数据，我们使用随机函数生成数据
以下是数据模拟创建脚本：

点击(此处)折叠或打开

1. #!/usr/bin/env python
   
2. # -*- coding:utf-8 -*-
   
3. # Author :Alvin.xie
   
4. # @Time :2017-11-22 15:02
   
5. # @file :tianjshuju.py
   
6. 
   
7. import MySQLdb
   
8. def connect_mysql():
   
9.     db_config = {
   
10.         'host': '10.89.1.10',
    
11.         'port': 3306,
    
12.         'user': 'demo',
    
13.         'passwd': '123qaz',
    
14.         'db': 'python_test',
    
15.         'charset': 'utf8'
    
16.     }
    
17.     cnx = MySQLdb.connect(**db_config)
    
18.     return cnx
    
19. 
    
20. if __name__ == '__main__':
    
21.     cnx = connect_mysql()
    
22. 
    
23. 
    
24. students = '''set @i := 10000;
    
25.         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;
    
26.     '''
    
27. 
    
28. course = '''set @i := 10;
    
29.         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;
    
30.     '''
    
31. 
    
32. score = '''set @i := 10000;
    
33.         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;
    
34.     '''
    
35. 
    
36. theacher = '''set @i := 100;
    
37.         insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;
    
38.     '''
    
39. 
    
40. try:
    
41.     cus_students = cnx.cursor()
    
42.     cus_students.execute(students)
    
43.     cus_students.close()
    
44. 
    
45.     cus_course = cnx.cursor()
    
46.     cus_course.execute(course)
    
47.     cus_course.close()
    
48. 
    
49.     cus_score = cnx.cursor()
    
50.     cus_score.execute(score)
    
51.     cus_score.close()
    
52. 
    
53.     cus_teacher = cnx.cursor()
    
54.     cus_teacher.execute(theacher)
    
55.     cus_teacher.close()
    
56. 
    
57.     cnx.commit()
    
58. except Exception as e:
    
59.     cnx.rollback()
    
60.     print('error')
    
61.     raise e
    
62. finally:
    
63.     cnx.close()

以下是数据查询脚本：

点击(此处)折叠或打开

1. !/usr/bin/env python
   
2. # -*- coding:utf-8 -*-
   
3. # Author :Alvin.xie
   
4. # @Time :2017-11-22 15:41
   
5. # @file :searcher.py
   
6. 
   
7. import codecs
   
8. 
   
9. import MySQLdb
   
10. def connect_mysql():
    
11.     db_config = {
    
12.         'host': '10.89.1.10',
    
13.         'port': 3306,
    
14.         'user': 'demo',
    
15.         'passwd': '123qaz',
    
16.         'db': 'python_test',
    
17.         'charset': 'utf8'
    
18.     }
    
19.     cnx = MySQLdb.connect(**db_config)
    
20.     return cnx
    
21. 
    
22. 
    
23. if __name__ == '__main__':
    
24.     cnx = connect_mysql()
    
25. 
    
26.     sql = '''select * from Student where stdname in (select stdname from Student group by stdname having count(1)>1 ) order by stdname;'''
    
27.     #sql = '''select stdname from Student group by stdname having count(1)>1;'''
    
28.     try:
    
29.         cus = cnx.cursor()
    
30.         cus.execute(sql)
    
31.         result = cus.fetchall()
    
32.         with codecs.open('select.txt', 'w+') as f:
    
33.             for line in result:
    
34.                 f.write(str(line))
    
35.                 f.write('\n')
    
36.         cus.close()
    
37.         cnx.commit()
    
38.     except Exception as e:
    
39.         cnx.rollback()
    
40.         print('error')
    
41.         raise e
    
42.     finally:
    
43.         cnx.close()

查询结果：
本地路径下出现： select.txt
内容如图所示：

解释：

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

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

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

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

4.      result = cus.fetchall()列出结果以后，我们通过fetchall()函数把所有的内容都取出来，这个result是一个tuple

5.      通过文件写入的方式，我们把取出来的result写入到select.txt文件中。得到最终的结果。