用MySQLDB
需要先安装:
yum install MySQL-python.x86_64
update
import datetime
import MySQLdb
conn= MySQLdb.connect(
host='myservicedatabase.host.name.or.ip',
port = 3306,
user='myuser',
passwd='mypassword',
db ='mydb',
)
cur = conn.cursor()
""" 根据文本文件的记录,更新数据库记录 """
result=0
i=0
fo = open("/root/READ_ONLY_ALL_MSGS.txt", "r")
while True:
row = fo.readline().strip()
row = row.strip()
i = i+1
if (i % 100) == 0:
print("No. % 5d" % i)
if row:
id,sn,quest = row.split("@@@")
sql = "update my_msgs_new set CREATE_TIME = (select CREATE_TIME from my_msgs_newbak where ID = %s) WHERE ID = %s"
param = (id,id)
result = cur.execute(sql, param)
else:
break
cur.close()
conn.commit()
fo.close()
定时统计(每9秒统计一次)
有消息表 my_msgs_new,统计当日所有app的消息数量到 stat_message_daily_map_poi 表。
CREATE TABLE `stat_message_daily_map_poi` (
`ID` varchar(36) NOT NULL,
`APP_ID` varchar(36) DEFAULT NULL COMMENT '应用编号',
`MARK_NAME` varchar(255) DEFAULT NULL COMMENT '地标名称',
`MESSAGE_COUNT` int(11) DEFAULT NULL COMMENT '当日交互消息数量',
`LONGITUDE` double DEFAULT NULL COMMENT '经度',
`LATITUDE` double DEFAULT NULL COMMENT '纬度',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='地图上地标与当日交互量的记录图';
代码:
import datetime
import MySQLdb
import time
class DailyTalkStat:
def get_connection(self):
self.conn= MySQLdb.connect(
host='myservicedatabase.host.name.or.ip',
port = 3306,
user='myuser',
passwd='mypassword',
db ='mydb',
)
self.cur = self.conn.cursor()
def run(self):
while True:
self.stat_from_table()
# 每9秒统计一次
time.sleep(9)
def stat_from_table(self):
self.get_connection()
sql = "SELECT ROBOT_ID, ROBOT_NAME , count(*) FROM robot_msgs_new where CREATE_TIME > %s GROUP BY ROBOT_ID, ROBOT_NAME"
param = (time.strftime('%Y-%m-%d', time.localtime()),)
result = self.cur.execute(sql, param)
robots = {}
for id, name, number in self.cur.fetchall():
print("%40s: %10s, %4d" % (id, name, number))
robots[id] = (id, name, number)
for (id, row) in robots.items():
sql = "update stat_message_daily_map_poi set MESSAGE_COUNT = %s WHERE APP_ID = %s"
param = (row[2], id)
n = self.cur.execute(sql, param)
# if n == 0:
# print("Zero row updated for ID: %s" % id)
self.cur.close()
self.conn.commit()
if __name__ == '__main__':
stat = DailyTalkStat()
stat.run()
运行:python dailytalkstat.py