数据库造数据、建表、复制表、对比表数据

1.写一个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

2.数据库造数据

import random
import MySQL #自己写的MySQL模块，如此篇博客中的《1.写一个MySQLl模块，连接数据库、执行sql，获取返回值》

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)

3.创建表，复制表

import pymysql

conn = pymysql.connect(host="*.*.*.*",
                user="*",
                password="*",
                db="*",
                port=3306,
                charset="utf8",
                autocommit=True)#链接数据库

cursor = conn.cursor(pymysql.cursors.DictCursor)#建立游标
cursor.execute("create table students_1 select * from students")

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模块，如此篇博客中的《1.写一个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)