• day23-python操作数据库三


    创建表
    import
    MySQLdb def connect_mysql(): db_config = { 'host': '192.168.1.5', 'port': 3306, 'user': 'wxp', 'passwd': '1qazXSW@', 'db': 'python', 'charset': 'utf8' } cnx = MySQLdb.connect(**db_config) return cnx if __name__ == '__main__': cnx = connect_mysql() cus = cnx.cursor() # sql = '''insert into student(id, name, age, gender, score) values ('1001', 'ling', 29, 'M', 88), ('1002', 'ajing', 29, 'M', 90), ('1003', 'xiang', 33, 'M', 87);''' #创建表 student = '''create table Student( StdID int not null, StdName varchar(100) not null, Gender enum('M', 'F'), Age tinyint )''' course = '''create table Course( CouID int not null, CName varchar(50) not null, TID int not null )''' score = '''create table Score( SID int not null, StdID int not null, CID int not null, Grade int not null )''' thearch = '''create table Teacher( TID int not null, TName varchar(100) not null )''' #set @i := 0; create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10; mysql中变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。set这个是mysql中设置变量的特殊用法,当@i需要在select中使用的时候,必须加:,这样就创建好了一个表tmp,查看tmp的数据: tmp = '''set @i := 0; create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10; ''' try: #cus.execute(student) #cus.execute(course) #cus.execute(score) cus.execute(thearch) cus.execute(tmp) cus.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.close() 输出结果: #查看表 mysql> show tables; +------------------+ | Tables_in_python | +------------------+ | Course | | Score | | Student | | Teacher | | employees | | test | | tmp | +------------------+ 7 rows in set (0.00 sec) #查看表数据 mysql> select * from tmp; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0.00 sec)
    增加数据
    import MySQLdb
    def connect_mysql():
        db_config = {
            'host': '192.168.1.5',
            'port': 3306,
            'user': 'wxp',
            'passwd': '1qazXSW@',
            'db': 'python',
            '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()
    
    
    
    输出结果:
    
    mysql> select count(*) from Student;
    +----------+
    | count(*) |
    +----------+
    |    10000 |
    +----------+
    1 row in set (0.00 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.01 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的一个整数

    
    

    现在有一个问题,为什么会出现10000条数据呢,这10000条数据时怎么生成的呢,虽然字段一一对应上了,但是怎么出来这么多数据呢?

    
    

    先来看个例子:

    
    

    select * from tmp a, tmp b, tmp c;

    
    

    最终是1000条数据,试试有一些感觉了呢,a, b, c都是tmp表的别名,相当于每个表都循环了一遍。所以最终的数据是有多少个表,就是10的多少次幂。

     
    查询数据
    
    import codecs
    
    import MySQLdb
    def connect_mysql():
        db_config = {
            'host': '192.168.1.5',
            'port': 3306,
            'user': 'wxp',
            'passwd': '1qazXSW@',
            'db': 'python',
            '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;'''
        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('
    ')
            cus.close()
            cnx.commit()
        except Exception as e:
            cnx.rollback()
            print('error')
            raise e
        finally:
            cnx.close()
    
    输出结果
    select.txt 文件中
    (13565L, u'1d5', u'M', 21)
    (18739L, u'1d5', u'F', 25)
    (11564L, u'2d4', u'F', 25)
    (16082L, u'2d4', u'M', 21)
    (14565L, u'631', u'M', 21)
    (17506L, u'631', u'M', 21)

    解释:

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

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

    1. 我们先来看括号里面的语句:select StdName from Student group by StdName having count(1)>1;这个是把所有学生名字重复的学生都列出来,
    2. 最外面select是套了一个子查询,学生名字是在我们()里面的查出来的学生名字,把这些学生的所有信息都列出来。
    3. result = cus.fetchall()列出结果以后,我们通过fetchall()函数把所有的内容都取出来,这个result是一个tuple
    4. 通过文件写入的方式,我们把取出来的result写入到select.txt文件中。得到最终的结果。
  • 相关阅读:
    .NET Framework 1.13.5 版本安装包下载链接
    可遇不可求的Question之MYSQL获取自增ID的四种方法篇
    20110917 晴
    北海道 7天6夜 自助游
    想你了
    猫忘带电话了
    [转载经验] 探亲签证申请
    帮忙打印
    打印机
    20110910 晴
  • 原文地址:https://www.cnblogs.com/wxp997/p/7887338.html
Copyright © 2020-2023  润新知