python数据库代码_python登录到数据库

哎呀…很多时间过去了,但我最近设法用Python编写了自己的数据库记录器。因为我找不到任何例子,所以我想把我的帖子贴在这里。也许有人会发现这很有用:)与MS SQL一起工作。

数据库表可能如下所示:

CREATE TABLE [db_name].[log](

[id] [bigint] IDENTITY(1,1) NOT NULL,

[log_level] [int] NULL,

[log_levelname] [char](32) NULL,

[log] [char](2048) NOT NULL,

[created_at] [datetime2](7) NOT NULL,

[created_by] [char](32) NOT NULL,

) ON [PRIMARY]

类本身:

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 pymssql.Error as e:

print sql

print 'CRITICAL DB ERROR! Logging to database not possible!'

使用示例:

import pymssql

import time

import logging

db_server = 'servername'

db_user = 'db_user'

db_password = 'db_pass'

db_dbname = 'db_name'

db_tbl_log = 'log'

log_file_path = 'C:\\Users\\Yourname\\Desktop\\test_log.txt'

log_error_level = 'DEBUG' # LOG error level (file)

log_to_db = True # LOG to database?

class LogDBHandler(logging.Handler):

[...]

# Main settings for the database logging use

if (log_to_db):

# Make the connection to database for the logger

log_conn = pymssql.connect(db_server, db_user, db_password, db_dbname, 30)

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)

上面将同时记录到数据库和文件中。如果不需要文件-跳过“logging.basicconfig(filename=log_file_path)”行。使用“日志”记录的所有内容都将作为“日志”记录。如果出现一些外部错误(即在导入的模块或其他模块中),则错误将显示为“根”,因为“根”记录器也处于活动状态,并且正在使用数据库处理程序。