mysql 数据库数据建立与查询

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

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author :Alvin.xie
# @Time :2017-11-22 15:02
# @file :tianjshuju.py

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '10.89.1.10',
        'port': 3306,
        'user': 'demo',
        'passwd': '123qaz',
        'db': 'python_test',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    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;
    '''

theacher = '''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(theacher)
    cus_teacher.close()

    cnx.commit()
except Exception as e:
    cnx.rollback()
    print('error')
    raise e
finally:
    cnx.close()

以下是数据查询脚本：

!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author :Alvin.xie
# @Time :2017-11-22 15:41
# @file :searcher.py

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '10.89.1.10',
        'port': 3306,
        'user': 'demo',
        'passwd': '123qaz',
        'db': 'python_test',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    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;'''
    #sql = '''select stdname from Student group by stdname having count(1)>1;'''
    try:
        cus = cnx.cursor()
        cus.execute(sql)
        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
内容如图所示：

解释：

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