Python 读写数据库（MySQLDB）

用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