python使用Pymysql连接数据库并写入数据

#!/usr/bin/python

# -*- coding: UTF-8 -*-



#首先导入模块pymysql,若出现报错没有此模块，就pip install pymysql

import os,sys,pymysql

def get_ip():

     #使用os.popen模块执行shell命令查看本机ip,注意外围使用双引号而非单引号,并且假设默认是第一个网卡,特殊环境请适当修改代码  

     #out = os.popen("ifconfig enp0s3 | grep 'inet '|awk '{print $2}'").read()

     out = os.popen("ifconfig|grep -oP '(?<=inet addr:)(?=(?!127\.0\.0\.1))\d+(\.\d+){3}'").read()

     out = out.strip() #此步骤是因为去头尾多余的空格，以免插入数据库出现格式问题；

     # print (out)

     res = out

     return res

# 使用 os.system执行shell脚本



p = os.system("../shell/rtc-server-monitor-log.sh")



#print(p")





#使用cursor()方法创建游标对象cursor

cursor = db.cursor()



#使用execute()方法执行sql查询当前版本

cursor.execute('select version()')



#使用fetchone()方法获取单条数据

data = cursor.fetchone()

print ("Database version : %s " % data)





#T = []

#for num  in range(p)

# T.append((num))  # 注意要用两个括号括起来

#sql插入语句：

#sql = """INSERT INTO rtc_excep_task(ip,任务id,告警类型)

#         VALUES ('10.114.123.45','31184','1')"""

#open_file = open('/tmp/rtc-server-log-fail')

#ith open("/tmp/rtc-server-log-fail") as f



#运行前清空数据表；

cursor.execute('delete from rtc_excep_task')

print('清空表rtc_excep_task完成！')

#数据库插入语句

sql = """INSERT INTO rtc_excep_task(IP,TASKID,DATE,ERRORSYSTEM)

         VALUES (%s,%s,%s,%s)"""





try:

  with open("/tmp/rtc-server-log-fail") as f:

       

        for line in f.readlines():

           line = line.strip() #去掉每行头尾空白

           _nodes = line.split(' ')

           id = _nodes[1]

           DATE = _nodes[0]

           #print(id)

           #print(DATE)

           system = "1"

           #out = get_ip()

           ipl = []

           #ip = out

           ip = get_ip()

           ipl.append(ip)

           print(ipl)

           #print(type(ipl))

        # 执行sql语句

           cursor.execute(sql,(ip,id,DATE,system))

         # 提交到数据库执行

           db.commit()

           print ("数据库插入成功！")

         # count += 1

except Exception as e :

        print(e)

        # 如果发生错误则回滚

        db.rollback()

        print("数据库插入不成功，已回滚！")

# 关闭游标

cursor.close()

# 关闭数据库连接

db.close()



vim rtc-server-monitor-log.sih

#!/bin/bash

cat /dev/null > /tmp/rtc-server-log-fail



for i  in $(find /app/INC-BDP-PIPE/logs/ -ctime -7  | grep rtc-server-monitor.log.| sort -n)

do

        #echo "$i"



        cat "$i" | grep 任务运行失败告警 | awk -F' ' '{print$1,$10}' |cut -d ! -f 1  |sort -n |uniq -u >>/tmp/rtc-server-log-fail

Done