数据库的插入和查询操作

来源：互联网发布：python实现支持向量机编辑：程序博客网时间：2024/05/17 09:10

在操作设计数据库之前，我们先要设计数据库表结构，我们就来分析分析经典的学生，课程，成绩，老师这几者他们之间的关系，我们先来分析各个主体他们直接有什么属性，并确定表结构，在实际开发过程中，根据自己的业务需要和属性，设计不同的表结构：
Student
字段名类型是否为空主键描述
StdID int 否是学生ID
StdName varchar(100) 否　学生姓名
Gender enum(‘M’, ‘F’) 是　性别
Age int 是　年龄

Course
字段名类型是否为空主键描述
CouID int 否是课程ID
Cname varchar(50) 否　课程名字
TID int 否　老师ID

Score
字段名类型是否为空主键描述
SID int 否是分数ID
StdID int 否　学生id
CouID int 否　课程id
Grade int 否　分数

Teacher
字段名类型是否为空主键描述
TID int 否是老师ID
Tname varcher(100) 否　老师名字

创建表

有了表结构，下面我们就来创建表吧

#!/usr/bin/python# -*- coding:utf-8 -*-# @Time   : 2017/11/21 0021 10:52# @Author : liaochao# @File   : Demo2.pyimport MySQLdbfrom DBUtils.PooledDB import PooledDBdb_config = {        'host': '192.168.1.204',        'port': 3306,        'user': 'root',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'}pool = PooledDB(MySQLdb,10,**db_config )student = '''      create table student(        stdid int primary key not null,        stdname varchar(100) not null,        gender enum('M','F'),        age int      );'''course = '''    create table course(      couid int primary key not null,      cname varchar(100) not null,      tid int not null   );'''score = '''     create table score(      sid int primary key not null,      std int not null,      couid int not null,      grade int not null     );'''teacher ='''      create table teacher(        tid int primary key not null,        tname varchar(100) not null      );     '''tmp = '''set @i := 0;            create table tmp as select (            @i := @i + 1) as id from information_schema.tables limit 10;'''if __name__ == '__main__':#以后每次需要数据库连接就是用connection（）函数获取连接就好了    conn = pool.connection()    cur = conn.cursor()    #SQL = 'select * from test;'    try:        cur.execute(student)        cur.execute(course)        cur.execute(score)        cur.execute(teacher)        cur.execute(tmp)        # cur.execute(SQL)        # r = cur.fetchall()        # print r        cur.close()        conn.commit()    except Exception as e:        raise e    finally:        cur.close()

注意如果报错：
[MySQL] ERROR 3: Error writing file ‘./test/foo.frm’ (Errcode 28)
一般是磁盘空间不够了，注意检查磁盘空间

执行完毕后去数据库检查表是否创建

mysql> show tables;+------------------+| Tables_in_python |+------------------+| course           || employees        || score            || student          || teacher          || test             || tmp              |+------------------+7 rows in set (0.00 sec)

增加数据

#!/usr/bin/python# -*- coding:utf-8 -*-# @Time   : 2017/11/22 0022 15:55# @Author : liaochao# @File   : Demo3.py#获得随机整数的设计#增加的数据是随机数据，rand() 函数生成0-1的一个随机数#sha1() 对数字进行加密，然后就生成了一堆字符串#concat() 拼接多个字符串的函数#substr()  取多少个字符#rand()*50  0-50#floor() 这个函数代表的是去尾法取整数#男女的设计#rand()*10       /2   最后取余数#如果余数为1  就设置为M#如果余数为0   就设置为Fimport MySQLdbfrom DBUtils.PooledDB import PooledDBdb_config = {        'host': '192.168.1.204',        'port': 3306,        'user': 'root',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'}pool = PooledDB(MySQLdb,10,**db_config )student='''    set @i=1000;    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;'''if __name__ == '__main__':#以后每次需要数据库连接就是用connection（）函数获取连接就好了    conn = pool.connection()    #SQL = 'select * from test;'    try:        cur = conn.cursor()        cur.execute(student)        cur.close()        cur = conn.cursor()        cur.execute(course)        cur.close()        cur = conn.cursor()        cur.execute(score)        cur.close()        cur = conn.cursor()        cur.execute(teacher)        cur.close()        conn.commit()    except Exception as e:        raise e    finally:        cur.close()

执行完毕去数据库查看：

mysql> select count(*) from Student;+----------+| count(*) |+----------+|    10000 |+----------+1 row in set (0.01 sec)mysql> select count(*) from Course;+----------+| count(*) |+----------+|       10 |+----------+1 row in set (0.00 sec)mysql> select count(*) from Score;+----------+| count(*) |+----------+|    10000 |+----------+1 row in set (0.00 sec)mysql> select count(*) from Teacher;+----------+| count(*) |+----------+|      100 |+----------+1 row in set (0.00 sec)

如图所示，在Student的表中增加了10000条数据，id是从10000开始的。count函数时用来统计个数的。
解释；
我们知道Student有四个字段，StdID，StdName，Gender，Age；我们先来看这个select语句：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;
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
Age字段：25-floor(rand() * 5)代表的就是，25减去一个0-4的一个整数

查数据

import codecsimport MySQLdbfrom DBUtils.PooledDB import PooledDBdb_config = {        'host': '192.168.1.204',        'port': 3306,        'user': 'root',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'}pool = PooledDB(MySQLdb,10,**db_config )sql = '''        select * from student where stdname in        (select stdname from student group by stdname having count(1)>1 )         order by stdname;'''if __name__ == '__main__':#以后每次需要数据库连接就是用connection（）函数获取连接就好了    conn = pool.connection()    #SQL = 'select * from test;'    try:        cur = conn.cursor()        cur.execute(sql)        result = cur.fetchall()        with codecs.open('select.txt','w') as f:            for line in result:                f.write(str(line))                f.write('\n')        cur.close()        conn.commit()    except Exception as e:        raise e    finally:        cur.close()

结果：
本地目录出现一个select.txt文件，内容如下：
(19844L, u’315’, u’F’, 24)
(17156L, u’315’, u’F’, 25)
(14349L, u’48f’, u’F’, 25)

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

阅读全文

0 0