Chinaunix首页 | 论坛 | 博客
  • 博客访问: 229773
  • 博文数量: 57
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 557
  • 用 户 组: 普通用户
  • 注册时间: 2015-10-01 18:05
文章分类

全部博文(57)

文章存档

2017年(57)

我的朋友

分类: Python/Ruby

2017-11-22 17:38:37

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

点击(此处)折叠或打开

  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. import MySQLdb
  7. def connect_mysql():
  8.     db_config = {
  9.         'host': '10.89.1.10',
  10.         'port': 3306,
  11.         'user': 'demo',
  12.         'passwd': '123qaz',
  13.         'db': 'python_test',
  14.         'charset': 'utf8'
  15.     }
  16.     cnx = MySQLdb.connect(**db_config)
  17.     return cnx

  18. if __name__ == '__main__':
  19.     cnx = connect_mysql()


  20. students = '''set @i := 10000;
  21.         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;
  22.     '''

  23. course = '''set @i := 10;
  24.         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;
  25.     '''

  26. score = '''set @i := 10000;
  27.         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;
  28.     '''

  29. theacher = '''set @i := 100;
  30.         insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;
  31.     '''

  32. try:
  33.     cus_students = cnx.cursor()
  34.     cus_students.execute(students)
  35.     cus_students.close()

  36.     cus_course = cnx.cursor()
  37.     cus_course.execute(course)
  38.     cus_course.close()

  39.     cus_score = cnx.cursor()
  40.     cus_score.execute(score)
  41.     cus_score.close()

  42.     cus_teacher = cnx.cursor()
  43.     cus_teacher.execute(theacher)
  44.     cus_teacher.close()

  45.     cnx.commit()
  46. except Exception as e:
  47.     cnx.rollback()
  48.     print('error')
  49.     raise e
  50. finally:
  51.     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. import codecs

  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. if __name__ == '__main__':
  20.     cnx = connect_mysql()

  21.     sql = '''select * from Student where stdname in (select stdname from Student group by stdname having count(1)>1 ) order by stdname;'''
  22.     #sql = '''select stdname from Student group by stdname having count(1)>1;'''
  23.     try:
  24.         cus = cnx.cursor()
  25.         cus.execute(sql)
  26.         result = cus.fetchall()
  27.         with codecs.open('select.txt', 'w+') as f:
  28.             for line in result:
  29.                 f.write(str(line))
  30.                 f.write('\n')
  31.         cus.close()
  32.         cnx.commit()
  33.     except Exception as e:
  34.         cnx.rollback()
  35.         print('error')
  36.         raise e
  37.     finally:
  38.         cnx.close()
查询结果:
本地路径下出现: select.txt
内容如图所示:


解释:

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

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

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文件中。得到最终的结果。


阅读(1736) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~