Python之Mysql创建表&查询表

Python之Mysql创建表&查询表

1.创建表

#!/usr/bin/env python
# -*- coding:utf8 -*-
# @Time     : 2017/11/22 16:24
# @Author   : hantong
# @File     : mysql_create.py
import MySQLdb

def connect_mysql():
    db_config = {
        "host":'10.3.1.113',
        "port":'3306',
        "user":'cn_uts',
        "passwd":'cn_uts',
        "db": 'test',
        "charset":'utf-8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx
if __name__ == "__main__":
    cnx = connect_mysql()
    cus = cnx.cursor()
    student = '''create table Student(
            StdID int not null,
            StdName varchar(100) not null,
            Gender enum('M', 'F'),
            Age tinyint
    )'''

#创建student表
    course = '''create table Course(
            CouID int not null,
            CName varchar(50) not null,
            TID int not null
    )'''

#创建course表
    score = '''create table Score(
                SID int not null,
                StdID int not null,
                CID int not null,
                Grade int not null
        )'''

#创建score表
    teacher = '''create table Teacher(
                    TID int not null,
                    TName varchar(100) not null
            )'''

#创建teacher表
   tmp = '''set @i := 0;
             create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;
        '''

#创建tmp表
    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()
数据库中各表的解释：

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是showdatabases的结果取之此表。
 
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
 
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
 
STATISTICS表：提供了关于表索引的信息。是showindex from schemaname.tablename的结果取之此表。
 
USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
 
SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。
 
TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
 
COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
 
CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOWCHARACTER SET结果集取之此表。
COLLATIONS表：提供了关于各字符集的对照信息。
 
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
 
TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
 
KEY_COLUMN_USAGE表：描述了具有约束的键列。
 
ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
 
VIEWS表：给出了关于数据库中的视图的信息。需要有showviews权限，否则无法查看视图信息。
 
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

2.导入数据:

#!/usr/bin/env python
# -*- coding:utf8 -*-
# @Time     : 2017/11/22 16:43
# @Author   : hantong
# @File     : mysql_create2.py
import MySQLdb

def connect_mysql():
    db_config = {
        "host":'10.3.1.113',
        "port":'3306',
        "user":'cn_uts',
        "passwd":'cn_uts',
        "db": 'test',
        "charset":'utf-8'
    }
    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;
        '''
    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;
     
   '''
 
# StdID字段：@i就代表的就是，从10000开始，在上一句sql中设置的；
# StdName字段：substr(concat(sha1(rand()), sha1(rand())), 1, floor(rand() * 80))就代表的是，
# 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,就代表的是，
#  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
  
    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()

3.查询表数据并导出到文件

#!/usr/bin/env python
# -*- coding:utf8 -*-
# @Time     : 2017/11/22 16:48
# @Author   : hantong
# @File     : mysql_create3.py
# @File     : mysql_create2.py
import codecs

import MySQLdb

def connect_mysql():
    db_config = {
        "host":'10.3.1.113',
        "port":'3306',
        "user":'cn_uts',
        "passwd":'cn_uts',
        "db": 'test',
        "charset":'utf-8'
    }
    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;'''
    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()

解释：

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