数据库（新增数据、建立数据表、复制、对比表数据）

1.　　写一个MySQLl模块，连接数据库、执行sql，获取返回值

 1 import pymysql
 2 
 3 mysql_info = {
 4     "host":"*.*.*.*",
 5     "user":"*",
 6     "password":"*",
 7     "db":"*",
 8     "port":3306,
 9     "charset":"utf8",
10     "autocommit":True
11 }
12 def execute_sql(sql,more=True,db_info=None):
13     if db_info:
14         conn = pymysql.connect(**db_info)
15     else:
16         conn = pymysql.connect(**mysql_info) 
17 
18     cursor = conn.cursor(pymysql.cursors.DictCursor)#    创建游标，以字典的形式返回操作结果
19     cursor.execute(sql)
20     if more:
21         result = cursor.fetchall()
22     else:
23         result = cursor.fetchone()
24     cursor.close()
25     conn.close()
26     return result

2.　　数据库新增数据

 1 import randomimport MySQL #自己写的MySQL模块，如此篇博客中的《1.写一个MySQLl模块，连接数据库、执行sql，获取返回值》
 2 name="Chj_Test"
 3 sex_list = ["男","女"]
 4 
 5 def create_data(number):
 6     for i in range(number):
 7         sql = "insert into students (name,phone,age,sex) values ('%s','%s','%s','%s');"
 8         stu_name = "%s_%s" % (name,i)
 9         phone = random.randint(15900000000、13595175325)
10         age = random.randint(1,100)
11         sex = random.choice(sex_list)
12         sql = sql % (stu_name,phone,age,sex)
13         MySQL.execute_sql(sql)
14 
15 if __name__ == '__main__':
16     create_data(10)

3.　　复制已有数据表

 1 #   【MySQL】数据库表操作
 2 import pymysql
 3 
 4 conn = pymysql.connect(host="*.*.*.*",
 5                 user="*",
 6                 password="*",
 7                 db="*",
 8                 port=3306,
 9                 charset="utf8",
10                 autocommit=True)#    连接数据库
11 cursor = conn.cursor(pymysql.cursors.DictCursor)#   建立游标
12 cursor.execute("create table CHJ select * from students")

4.　　对比数据表内数据

 1 import CS2
 2 
 3 table_mapper = {
 4     "students":"students_new"
 5 }
 6 
 7 def compare_data(old_data,new_data):
 8     for old,new in zip(old_data,new_data):
 9         for filed in old.keys():
10             old_value = old[filed] #旧表里面的值
11             new_value = new[filed] #新表里面的值
12             if new_value != old_value:
13                 print("发现一条不一致的数据:id是%s,字段是 %s" % (old["id"],filed) )
14 
15 for old,new in table_mapper.items():
16     old_sql = "select * from %s;" % old
17     new_sql = "select * from %s;" % new
18     old_data = CS2.execute_sql(old_sql)
19     new_data = CS2.execute_sql(new_sql)
20     compare_data(old_data,new_data)