1.创建数据库表
CREATE TABLE `store` (
`s_id` int(11) NOT NULL AUTO_INCREMENT,
`s_date` varchar(15) NOT NULL,
`s_storecode` varchar(10) NOT NULL,
`s_transactions` varchar(8) DEFAULT NULL,
PRIMARY KEY (`s_id`),
UNIQUE KEY `pk_u_test` (`s_date`,`s_storecode`)
) ENGINE=InnoDB AUTO_INCREMENT=6169 DEFAULT CHARSET=utf8;
CREATE TABLE `store_tmp` (
`s_id` int(11) NOT NULL AUTO_INCREMENT,
`s_date` varchar(15) NOT NULL,
`s_storecode` varchar(10) NOT NULL,
`s_transactions` varchar(8) DEFAULT NULL,
PRIMARY KEY (`s_id`),
UNIQUE KEY `pk_u_test_tmp` (`s_date`,`s_storecode`)
) ENGINE=InnoDB AUTO_INCREMENT=559 DEFAULT CHARSET=utf8;
2.准备测试数据(格式为YYYYMMDD.txt)
2016-11-14 Q0001 45
2016-11-14 Q0002 452
2016-11-14 Q0003 874
2016-11-14 Q0004 1435
2016-11-14 Q0005 6331
2016-11-14 Q0006 145
2016-11-14 Q0007 90
3.python脚本(名字为read.py)
# coding = utf-8
import datetime
import mysql.connector
# 获取文件名,格式为YYYYMMDD
def getFileName(hourNum):
today = datetime.datetime.now()
olday = today + datetime.timedelta(hours=hourNum)
#日期格式化后返回
return olday.strftime('%Y%m%d')
# 读取文件内容
def readFile(file,cursor):
for line in file:
# 读取数据并处理为tuple类型
shuju = tuple(line.replace('\n','').split(' '))
# 插入tmp数据库
cursor.execute('insert into store_tmp (s_date,s_storecode,s_transactions) values (%s,%s,%s)',shuju)
try:
# 连接数据库
conn = mysql.connector.connect(host='localhost',user='root', password='root', database='u_test')
cursor = conn.cursor()
cursor.execute('truncate store_tmp')# 清除tmp表
conn.commit() # 提交事务
# 获取文件名
fileName = getFileName(-24)
# 读取文件
file = open(fileName + '.txt')
readFile(file,cursor)
conn.commit() # 提交事务
# 迁移数据
cursor.execute('INSERT INTO store (s_date,s_storecode,s_transactions) SELECT st.s_date,st.s_storecode,st.s_transactions FROM store_tmp st LEFT JOIN store s ON s.s_date=st.s_date AND s.s_storecode=st.s_storecode WHERE s.s_id IS NULL')
conn.commit() # 提交事务
except Exception as e:
print('except:',e)
# 写入log日志
finally:
file.close()
cursor.close()
conn.close()