sqlalchemy读取日志数据并保存至数据库

import datetime
import  subprocess

from sqlalchemy import Column, Integer, String, UniqueConstraint, Index
from sqlalchemy.orm import Session, sessionmaker

from sqlalchemy.orm import declarative_base
Base = declarative_base()

# filename ="cat /var/log/cron"
# # def log_format(filename):
# log_format = filename
cmd = 'cat /var/log/cron'
log = subprocess.getoutput(cmd=cmd)
out = log.split('\n')
hostname = subprocess.getoutput(cmd='hostname')



class corn_log(Base):
    __tablename__ = "corn_log"
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    hostname = Column(String(20))
    # date = Column(String(20))

    __table_args__ = (
        UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_n_ex', 'name', 'id', ),
    )

    def __repr__(self):
        return f"User(id={self.id!r}, name={self.name!r},hostname={self.hostname!r})"


#链接数据库创建表
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:root@172.17.106.2:3306/test?charset=utf8", echo=True, future=True)

Base.metadata.create_all(engine)
# with Session(engine) as session:
#     for i in out:
#         sels = [corn_log(name=i)]
#         # newsel = f'< hostname=hostname,sel={i}>'
#         session.add_all(sels)
#         print(session.add_all(sels))
#         session.commit()
user1 = corn_log(name="user1")
user2 = corn_log(name="user2")


with Session(engine) as session:
    # session.add(user1)
    # session.add(user2)
    for i in out:
        user3 = corn_log(name=f'{i}',hostname=f'{hostname}')
        session.add_all([user3])
        print('start insert:',session.add_all([user3,user2,user1]))
        session.commit()  # write changes to the database

python 读取ipmi sel日志并写入数据库
(因为虚拟机，所以用写入文本代替 cat )

from sqlalchemy import Column, Integer, String, UniqueConstraint, Index
from sqlalchemy.orm import Session, sessionmaker

from sqlalchemy.orm import declarative_base
Base = declarative_base()
#链接数据库创建表
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:root@172.17.106.2:3306/test?charset=utf8", echo=True, future=True)

Base.metadata.create_all(engine)
"""
      9b | 05/27/2024 | 10:38:12 | Power Supply PSU0_Supply | Presence detected | Asserted
      9c | 05/27/2024 | 10:38:12 | Power Supply PSU1_Supply | Presence detected | Asserted
"""
#定义数据表
class SEL(Base):
    __tablename__ = "ipmi_sel"
    id = Column(Integer, primary_key=True)
    flag = Column(String(30))
    hostname = Column(String(30))
    date = Column(String(100))
    time = Column(String(20))
    msg = Column(String(30))
    log = Column(String(30))
    status = Column(String(30))

    __table_args__ = (
        UniqueConstraint('id', 'date', name='uix_id_name'),
        Index('ix_n_ex', 'date', 'id', ),
    )

    def __repr__(self):
        return f"User(id={self.id!r}, name={self.date!r},hostname={self.msg!r})"
#create table
Base.metadata.create_all(engine)

sel = """
      9b | 05/27/2023 | 13:32:12 | Power Supply PSU0_Supply | Presence detected | Asserted
      9c | 05/27/2023 | 13:33:22 | Power Supply PSU1_Supply | Presence detected | Asserted
"""
hostname = 'k8s'

def get_host_sel(host):
    import subprocess
    from sqlalchemy.orm import Session
    cmd = f"ssh {host}  cat /root/sel"
    log = subprocess.getoutput(cmd=cmd)
    out = log.split('\n')
    # print(out)
    with Session(engine) as session:
        for i in out:
            flag, date, time, msg, log, status = i.split('|')
            # print(te)
            sels = SEL(hostname=f'{hostname}',flag=f'{flag}',date=f'{date}',time=f'{time}',msg=f'{msg}',log=f'{log}',status=f'{status}')
            session.add_all([sels])
            print('start insert:',session.add_all([sels]))
            session.commit()  # write changes to the database

get_host_sel('k8s')