python - 如何使用 pymysql 将日志上传到 mysql
问题描述
我想收集并检查发生了什么错误,所以我试图在数据库中上传日志。我参考this page编写了将日志上传到mysql的代码。python 记录到数据库。但是,我收到以下错误。哪一部分是错的?另外,如果有其他方法可以轻松上传mysql中的日志,请告诉我。
import logging
import time
import pymysql
user = 'test'
passw = '******'
host = 'db'
port = ****
database = '****'
db_tbl_log = 'log'
log_file_path = 'C:\\Users\\Desktop\\test_log.txt'
log_error_level = 'DEBUG' # LOG error level (file)
log_to_db = True # LOG to database?
class LogDBHandler(logging.Handler):
'''
Customized logging handler that puts logs to the database.
pymssql required
'''
def __init__(self, sql_conn, sql_cursor, db_tbl_log):
logging.Handler.__init__(self)
self.sql_cursor = sql_cursor
self.sql_conn = sql_conn
self.db_tbl_log = db_tbl_log
def emit(self, record):
# Set current time
tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(record.created))
# Clear the log message so it can be put to db via sql (escape quotes)
self.log_msg = record.msg
self.log_msg = self.log_msg.strip()
self.log_msg = self.log_msg.replace('\'', '\'\'')
# Make the SQL insert
sql = 'INSERT INTO ' + self.db_tbl_log + ' (log_level, ' + \
'log_levelname, log, created_at, created_by) ' + \
'VALUES (' + \
'' + str(record.levelno) + ', ' + \
'\'' + str(record.levelname) + '\', ' + \
'\'' + str(self.log_msg) + '\', ' + \
'(convert(datetime2(7), \'' + tm + '\')), ' + \
'\'' + str(record.name) + '\')'
try:
self.sql_cursor.execute(sql)
self.sql_conn.commit()
# If error - print it out on screen. Since DB is not working - there's
# no point making a log about it to the database :)
except pymysql.Error as e:
print("error: ", e)
# print(sql)
# print('CRITICAL DB ERROR! Logging to database not possible!')
# Main settings for the database logging use
if (log_to_db):
# Make the connection to database for the logger
log_conn = pymysql.connect(host=host,
port=port,
user=user,
password=passw,
database=database,
charset='utf8')
log_cursor = log_conn.cursor()
logdb = LogDBHandler(log_conn, log_cursor, db_tbl_log)
# Set logger
logging.basicConfig(filename=log_file_path)
# Set db handler for root logger
if (log_to_db):
logging.getLogger('').addHandler(logdb)
# Register MY_LOGGER
log = logging.getLogger('MY_LOGGER')
log.setLevel(log_error_level)
# Example variable
test_var = 'This is test message'
# Log the variable contents as an error
log.error('This error occurred: %s' % test_var)
error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2021-02-22 16:52:06')), 'MY_LOGGER')' at line 1")
解决方案
不要自己格式化 SQL 语句,你会错过很多情况。只需将它们作为第二个参数传递:
sql = f'INSERT INTO {self.db_tbl_log} (log_level, log_levelname, log, created_at, created_by) VALUES (%s, %s, %s, %s, %s)'
self.sql_cursor.execute(sql, (record.levelno, record.levelname, self.log_msg, tm, record.name))
%s
是占位符,pymysql会将给定的参数一一转换为有效格式。
推荐阅读
- sql-server - SQL将行数据透视到SQL Server中的列,并根据日期进行分组
- sql-server - SQL Server 2019 正在返回查询中日期时间条件不匹配的记录
- python - Pip 指向哪个 PyPI 索引?
- r - 在 R 中结合两个图例
- javascript - Selenium Click 在大多数情况下都不起作用 - 元素被突出显示但点击不起作用并且页面 scolls - WebdriverException
- google-cloud-platform - 无法从 Compute Engine 访问 BigQuery
- c# - 如何在 Cypher Query 的 where IN 子句中使用 int[](使用 C#)
- c++ - cmake - 找不到共享库的头文件
- php - Laravel PHP8 Coverage 无法识别具有提升属性的构造函数
- excel - 如何隐藏开发人员选项卡