day23-python操作数据库三

2022-05-11 01:24:15

创建表
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('\n')

        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'', u'M', 21)

(17506L, u'', u'M', 21)

解释：

我们先来分析一下select查询这个语句：

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

我们先来看括号里面的语句：select StdName from Student group by StdName having count(1)>1；这个是把所有学生名字重复的学生都列出来，
最外面select是套了一个子查询，学生名字是在我们（）里面的查出来的学生名字，把这些学生的所有信息都列出来。
result = cus.fetchall()列出结果以后，我们通过fetchall()函数把所有的内容都取出来，这个result是一个tuple
通过文件写入的方式，我们把取出来的result写入到select.txt文件中。得到最终的结果。

码农公寓

相关文章