python3+mysql两表部分同步

最近有个需求,多表做汇总表,于是在演示表里测试一下,想到了python,用pymysql来测试

1.建立两张表

建立表t1

CREATE TABLE `t1`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `num` int(11) NULL DEFAULT NULL,
  `t1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `t2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

在表T1插入数据

INSERT INTO `t1` VALUES (1, '语文', '10', 20, '语文1', '语文2');
INSERT INTO `t1` VALUES (2, '数学', '11', 21, '数学1', '数学2');
INSERT INTO `t1` VALUES (3, '量子力学', '12', 22, '量子力学1', '量子力学2');
INSERT INTO `t1` VALUES (4, '美术', '13', 23, '美术1', '美术2');
INSERT INTO `t1` VALUES (5, '计算机', '14', 24, '计算机1', '计算机2');
INSERT INTO `t1` VALUES (6, '音乐', '15', 25, '音乐1', '音乐2');
INSERT INTO `t1` VALUES (7, '物理', '16', 26, '物理1', '物理2');
INSERT INTO `t1` VALUES (8, '化学', '17', 27, '化学1', '化学2');
INSERT INTO `t1` VALUES (9, '生物', '18', 28, '生物1', '生物2');

建立表t2表为空

CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

运行python程序保存为sync.py

#sync.py
import pymysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='Bl_123456',db='')
cur=conn.cursor()       #创建游标
sql="SELECT title,note,num FROM (SELECT title,note,num FROM t1 UNION ALL SELECT title,note,num from T2) c GROUP BY c.title HAVING COUNT(*)=1 ORDER BY c.title ;"
cur.execute(sql)
#获取数据库的数据（元组）
data=cur.fetchall()
result=list(data)
sql1='insert into t2(title,note,num) values(%s,%s,%s)'
cur.executemany(sql1,result)
conn.commit()
cur.close()
conn.close()
print(result)

t1数据

t2运行前数据

t2运行后数据

更新数据会被添加进t2

添加后

记录一下,以免以后找不到了.现在的记性比忘性差的太远了!