pthon 基础 9.8 增加数据
#/usr/bin/python
#-*- coding:utf-8 -*-
#@Time :2017/11/24 2:59
#@Auther :liuzhenchuan
#@File :增加#数据.py
#select * from tmp 10行数据
#select * from tmp a ,tmp b, tmp c: 1000行数据=10*10*10
#tmp a tmp b tmp c 对应表1 表2 表3
#获得随机字符串的设计
#增加的数据是随机数据，rand() 函数生成0-1 的一个随机数
#shal() 对数字进行加密，然后就生成了一堆字符串
#concat() 拼接多个字符串的函数
#substr() 取多少个字符
#获得随机整数的设计
# rand()*50 通过rand() 函数获得一个0-1的数，然后再乘以50 取到的数就是0-50
# floor() 这个函数代表的是去尾法取整数
# 男女的设计
# rand()*10 /2 最后取余数
# 如果余数为1，就设置为M
#如果余数为0，就设置为F
import MySQLdb
student = '''
set @a = 10000;
insert into student select @a := @a + 1, substr(concat(sha1(rand()), sha1(rand())), 1, 5+floor(rand()* 50)), case floor(rand()*10) mod 2 when 1 then "M" else "F" end, 20+floor(rand() * 8) from tmp a, tmp b, tmp c, tmp d;
'''
# 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;
# 我用下去这种insert就会有同名字的学生名
course = '''set @i := 10;
insert into course select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 45)), 100 + 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;
'''
def connect_mysql():
db_config = {
"host": "192.168.16.70",
"port": 3306,
"user": "root",
"passwd": "123123",
"db": "students",
"charset": "utf8"
}
try:
cnx = MySQLdb.connect(**db_config)
except Exception as e:
raise e
return cnx
if __name__ == "__main__":
cnx = connect_mysql()
try:
cus = cnx.cursor()
cus.execute(student)
cus.close()
cus = cnx.cursor()
cus.execute(course)
cus.close()
cus = cnx.cursor()
cus.execute(score)
cus.close()
cus = cnx.cursor()
cus.execute(teacher)
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
raise e
finally:
cnx.close()
查看数据库：
ysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| course |
| score |
| student |
| teacher |
| tmp |
+--------------------+
mysql> select *from course limit 10;
+-------+---------------------------------------------+-----+
| couid | cname | tid |
+-------+---------------------------------------------+-----+
| 11 | 86c85fe76342ff5e376264621 | 100 |
| 12 | ccef65aa5b377ac39ddd7dc44 | 143 |
| 13 | 0e8a391f36 | 146 |
| 14 | 6755d9fa73 | 146 |
| 15 | 8c79d4684d88065b1cecbe76 | 143 |
| 16 | 6bf78ad3dd4f5db7751fcfff6e661d526e814c54 | 166 |
| 17 | 2210cf9debd4a096373fd452d057f5c0f | 141 |
| 18 | 16e99f932a599ca8f940eeabebc04996c | 126 |
| 19 | a839d37dffd0cdd80bc7d | 181 |
| 20 | 17601ade7e74aa81f3fdf23d47af85bf2e8ed232288 | 152 |
+-------+---------------------------------------------+-----+
10 rows in set (0.00 sec)
mysql> select *from score limit 10;
+-------+-------+-----+-------+
| sid | stdid | cid | grade |
+-------+-------+-----+-------+
| 10001 | 10742 | 19 | 78 |
| 10002 | 14902 | 12 | 12 |
| 10003 | 12126 | 18 | 99 |
| 10004 | 17672 | 19 | 5 |
| 10005 | 16303 | 11 | 15 |
| 10006 | 17104 | 12 | 43 |
| 10007 | 18091 | 18 | 37 |
| 10008 | 15446 | 17 | 53 |
| 10009 | 17175 | 11 | 96 |
| 10010 | 16939 | 17 | 100 |
+-------+-------+-----+-------+
10 rows in set (0.00 sec)
mysql> select *from student limit 10;
+-------+-------------------------------------------------------+--------+-------+
| stdid | stdname | gender | agent |
+-------+-------------------------------------------------------+--------+-------+
| 10001 | 05be2e1f6be2dba3496d | M | 27 |
| 10002 | 8d21176c8a4cad420d9adec8dd6022146ed0b69dabe80c56c91 | F | 24 |
| 10003 | 582cf648fbb65fcae2eb0 | F | 22 |
| 10004 | ece0f0bb303e98859d9d41b6f246864e22d86ad405048ee | M | 23 |
| 10005 | e484bfbd4011fcebfa0d18271975b0834a1ccedd | M | 26 |
| 10006 | bb1ecba374c1dbdda1be | F | 22 |
| 10007 | 0b30cf54b756d4665e8fc41c7450433f590aefc37c8ea9afbbe8e | M | 27 |
| 10008 | 3cdf0dddcd8911386fd36b2bf3 | F | 27 |
| 10009 | f699c14f237ad1778fe2f1d | F | 20 |
| 10010 | a4b36903fd1c0e2f8f4151706e9d95ebfdf068b2f | F | 27 |
+-------+-------------------------------------------------------+--------+-------+
10 rows in set (0.00 sec)
mysql> select *from teacher limit 10;
+-----+------------------------------------------------------------------------+
| tid | tname |
+-----+------------------------------------------------------------------------+
| 101 | 7684b4b |
| 102 | 497c1eeb229d5faf8a88213f1a28ad5ceab755e222b832f50ef3b71f73860 |
| 103 | f7307 |
| 104 | 9c44341e4fd84d1545f5293df4762695e962540b39ebb528d58c509ab5 |
| 105 | d1cd631d236e48a8efccb032c833f867e863bbe361ef8cfd0869601 |
| 106 | 2cf3b9da2484eb560a8000be460f529dd17c |
| 107 | edc0ad15105a4dde02214a482fd0ac695517e307999fb7695bf1f6cfead6 |
| 108 | 8109966982f40ad1819d9c94023f4227eb019bd779305c0b8e9a87a7b0ddd436e97842 |
| 109 | 76433 |
| 110 | 438032fb74888de54a09e2cbd846650c85079f8feda82 |
+-----+------------------------------------------------------------------------+
10 rows in set (0.00 sec)
