def update_employee_table():
# Retrieve from remote DB
try:
with DBConn('mysql') as conn:
employees = Employee(is_active=True).select_query('mysql')
with DBConn('sqlite') as sqlite_conn:
c = sqlite_conn.cursor()
c.execute("""DELETE FROM employee""")
sqlite_conn.commit()
for employee in employees:
current_employee = Employee(**employee)
current_employee.insert('sqlite')
except pymysql.Error as e:
logger.exception(
"Caught exception connecting to database\n Error: {}".format(e))
def select_query(self, db_type, order_by=None, how="ASC", limit=None):
with DBConn(db_type) as conn:
if not order_by:
query = self.select_query_format()
else:
query = self.select_query_format(order_by=order_by, how=how)
if limit:
query = "{} LIMIT {}".format(query, limit)
c = conn.cursor()
c.execute(query)
rows = c.fetchall()
return rows
def getSysInfo(serverList):
calTime(1)
global sqlList
sqlList = []
for rec in serverList:
client, host = getSvrConn(rec)
if client is None:
print("连接服务器失败")
else:
organizeSQL(client, host)
calTime(2)
for sql in sqlList:
res = DBConn().writeDB(sql)
def getServerList():
serverList = []
sql = "select ip,sysuser,syspwd from t_servers;"
data = DBConn().get_serverList(sql)
if data == 0:
print("连接数据库失败！！")
elif data == -1:
print("读取数据失败！！")
else:
for row in data:
serDict = {}
serDict["host"] = row[0]
serDict["username"] = row[1]
serDict["pwd"] = row[2]
serverList.append(serDict)
return serverList
def writeDB(client, host):
cpu = mem = disk = ""
##读取cpu信息
stdin, stdout, stderr = client.exec_command(cmdDict["cpu"])
for line in stdout:
cpu = line.strip()
#print(cpu)
##读取mem信息
stdin, stdout, stderr = client.exec_command(cmdDict["mem"])
for line in stdout:
mem = line.strip()
#print(mem)
##读取disk信息
stdin, stdout, stderr = client.exec_command(cmdDict["disk"])
for line in stdout:
disk = line.strip()
#print(disk)
client.close()
sql = "insert into t_sysinfo(serverip,cpustatus,memstatus,diskstatus) values('" + host + "','" + cpu + "','" + mem + "','" + disk + "');"
#print(sql)
res = DBConn().writeDB(sql)
def create_tables():
with DBConn('sqlite') as conn:
c = conn.cursor()
for query in table_queries:
c.execute(query)
conn.commit()
def commit_to_db(db_type, query):
with DBConn(db_type) as conn:
c = conn.cursor()
c.execute(query)
conn.commit()