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() 数据库中各表的解释:
2.导入数据: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权限才能查看该表
#!/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,其他的为Ftry:
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文件中。得到最终的结果。