python 向mysql插入数据
生成随机内容用到的方法：
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）位结束。
case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,代表：当余数为1是，就取M，其他的为F
sql语句说明
TMP='''
set @i :=0;
create table TMP as select (@i := @i + 1) as id from information_schema.tables limit 10;
'''
nformation_schema.tables表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表
mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。set这个是mysql中设置变量的特殊用法，当@i需要在select中使用的时候，必须加:，这样就创建好了一个表tmp
示例：
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time : 2017/11/22 23:51
# @Author : lijunjiang
# @File : insert.py
import MySQLdb
# SQL
# 向Student表中从100开始插入1000条随机数据
Student='''
set @i := 100;
insert into Student select @i := @i + 1, substr(concat(sha1(rand()*10) + sha1(rand())),1,3+floor(rand()*75)),case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end, 20+floor(rand() * 5) from TMP a,TMP b,TMP c;
'''
# 向 Cours 表中从10开始插入10条随机数据
Cours='''
set @i := 10;
insert into Course select @i := @i + 1, substr(sha1(rand()),1,2+floor(rand()*10)), 1 + floor(rand()*10) from TMP a;
'''
#向 Score 表中从200开始插入10000条数据
Score='''
set @i := 200;
insert into Score select @i := @i + 1, floor(101+rand()*1000), floor(11+rand()*10),floor(1+ rand()*100) from TMP a, TMP b, TMP c, TMP d;
'''
# 向Teacher 表中从1 开始插入100条数据
Teacher='''
set @i := 0;
insert into Teacher select @i := @i + 1, substr(sha1(rand()),1,3+floor(rand() * 10));
'''
def connect_mysql():
db_config = dict(host="11.11.11.11", port=3306, db="python", charset="utf8", user="python", passwd="python")
try:
cnx = MySQLdb.connect(**db_config)
except Exception as err:
raise err
return cnx
if __name__ == "__main__":
# sql = "create table test(id int not null);"
cnx = connect_mysql() # 连接mysql
# cus = cnx.cursor() # 创建一个游标对象
try:
cus = cnx.cursor()
cus.execute(Student)
cus.close()
cus = cnx.cursor()
cus.execute(Teacher)
cus.close()
cus = cnx.cursor()
cus.execute(Score)
cus.close()
cus = cnx.cursor()
cus.execute(Cours)
cus.close()
cnx.commit()
except Exception as err:
cnx.rollback()
raise err
finally:
cnx.close()
mysql> select * from Student;
+-------+-----------------------+--------+------+
| StdID | StdName | Gender | Age |
+-------+-----------------------+--------+------+
| 101 | 1.79769313486232e+308 | M | 22 |
| 102 | 97 | F | 21 |
| 103 | 4.12e+72 | M | 24 |
| 104 | 18791937 | F | 24 |
| 105 | 88 | F | 21 |
| 106 | 159612 | M | 24 |
| 107 | 93 | F | 21 |
| 108 | 527 | F | 20 |
| 109 | 4 | M | 21 |
| 110 | 1.79769313486232e+308 | F | 23 |
| 1099 | 11515 | F | 24 |
| 1100 | 1.79769313486232e+308 | F | 20 |
+-------+-----------------------+--------+------+
1000 rows in set (0.00 sec)
mysql> select * from Teacher;
+-----+--------------+
| TID | TName |
+-----+--------------+
| 1 | 8dcd0f6f4c67 |
| 2 | 852c304e |
| 3 | 23cdcaf356e |
| 99 | 21bcef63 |
| 100 | 77b76fa88f |
+-----+--------------+
100 rows in set (0.00 sec)
mysql> select * from Score limit 5;
+-----+-------+-------+-------+
| SID | StdID | CouID | Grade |
+-----+-------+-------+-------+
| 201 | 726 | 18 | 4 |
| 202 | 951 | 12 | 11 |
| 203 | 238 | 14 | 43 |
mysql> select * from Score;
| 10199 | 1010 | 15 | 67 |
| 10200 | 970 | 14 | 23 |
+-------+-------+-------+-------+
10000 rows in set (0.01 sec)
mysql> select * from Course;
+-------+------------+-----+
| CouID | CName | TID |
+-------+------------+-----+
| 11 | bd3ffa | 2 |
| 12 | eb8c | 7 |
| 13 | aab5381 | 1 |
| 14 | 490ed47b02 | 3 |
| 15 | 16328013b | 1 |
| 16 | a77a91f | 7 |
| 17 | 226b67b68 | 6 |
| 18 | e36f | 4 |
| 19 | 7987545374 | 5 |
| 20 | 424b4b1a8 | 7 |
+-------+------------+-----+
10 rows in set (0.00 sec)
