1.数据库造数据
name="huahua_test" sex_list = ["男","女"] def create_data(number): for i in range(number): sql = "insert into students (name,phone,age,sex) values ('%s','%s','%s','%s');" stu_name = "%s_%s" % (name,i) phone = random.randint(18300000000,18399999999) age = random.randint(1,100) sex = random.choice(sex_list) sql = sql % (stu_name,phone,age,sex) MySQL.execute_sql(sql) if __name__ == '__main__': create_data(10)
2.创建表,复制表
cursor.execute("create table students_1 select * from students")#创建表[students_1],复制表[students]
3.写一个MySQLl模块,连接数据库、执行sql,获取返回值
import pymysql mysql_info = { "host":"*.*.*.*", "user":"*", "password":"*", "db":"*", "port":3306, "charset":"utf8", "autocommit":True } def execute_sql(sql,more=True,db_info=None): if db_info: conn = pymysql.connect(**db_info) else: conn = pymysql.connect(**mysql_info) cursor = conn.cursor(pymysql.cursors.DictCursor)#创建游标,以字典的形式返回操作结果 cursor.execute(sql) if more: result = cursor.fetchall() else: result = cursor.fetchone() cursor.close() conn.close() return result
4.对比表数据
一、思路 1、生成sql语句 select * from students; select * from students_1; 2、分别执行两条sql 3、循环数据,进行对比 a = [1,2,3,4,5,6] b = [2,2,3,4,5,6] for i in range(len(a)): print(a[i],b[i]) c = {"id":1,"name":"hh","age":18,"sex":"男"} d = {"stu_id":1,"stu_name":"hh1","new_age":19,"sex":"男"} for k in c.keys(): old_value = c.get(k) new_value = d.get(k) if old_value !=new_value: print("不一样的key 是%s " % k) 二、对比表数据 【students】与【students_1】 import MySQL #自己写的MySQL模块,如此篇博客中的《3.写一个MySQLl模块,连接数据库、执行sql,获取返回值》 table_mapper = { "students":"students_1" } def compare_data(old_data,new_data): for old,new in zip(old_data,new_data): for filed in old.keys(): old_value = old[filed] #旧表里面的值 new_value = new[filed] #新表里面的值 if new_value != old_value: print("发现一条不一致的数据:id是%s,字段是 %s" % (old["id"],filed) ) for old,new in table_mapper.items(): old_sql = "select * from %s;" % old new_sql = "select * from %s;" % new old_data = MySQL.execute_sql(old_sql) new_data = MySQL.execute_sql(new_sql) compare_data(old_data,new_data)