我正在尝试获取psycopg2执行的查询的性能统计信息,但是文档/示例看起来仍然很模糊,也不够清晰。
我至少已经通过记录器进行了调试。要访问查询的性能数据,我需要做些什么?我想要得到查询执行时间的数字。
是否有我可以访问的方法,或者需要初始化以输出查询执行时间的其他方法?
以下是我到目前为止的拼凑摘录:
import psycopg2
import psycopg2.extensions
from psycopg2.extras import LoggingConnection
import logging
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)
# set higher up in script
db_settings = {
"user": user,
"password": password,
"host": host,
"database": dbname,
}
query_txt = "[query_txt_from file]"
conn = psycopg2.connect(connection_factory=LoggingConnection, **db_settings)
conn.initialize(logger)
cur = conn.cursor()
cur.execute(query_txt)
我得到了
DEBUG:__main__: [the query executed]
发布于 2018-08-04 07:51:45
很容易在执行开始时设置时间戳,并在结束时计算持续时间。您需要自己的简单子类LoggingConnection和LoggingCursor。请参阅我的示例代码。
这是基于您可以在psycopg2/extras.py
源代码中找到的MinTimeLoggingConnection源代码。
import time
import psycopg2
import psycopg2.extensions
from psycopg2.extras import LoggingConnection, LoggingCursor
import logging
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)
# MyLoggingCursor simply sets self.timestamp at start of each query
class MyLoggingCursor(LoggingCursor):
def execute(self, query, vars=None):
self.timestamp = time.time()
return super(MyLoggingCursor, self).execute(query, vars)
def callproc(self, procname, vars=None):
self.timestamp = time.time()
return super(MyLoggingCursor, self).callproc(procname, vars)
# MyLogging Connection:
# a) calls MyLoggingCursor rather than the default
# b) adds resulting execution (+ transport) time via filter()
class MyLoggingConnection(LoggingConnection):
def filter(self, msg, curs):
return msg + " %d ms" % int((time.time() - curs.timestamp) * 1000)
def cursor(self, *args, **kwargs):
kwargs.setdefault('cursor_factory', MyLoggingCursor)
return LoggingConnection.cursor(self, *args, **kwargs)
db_settings = {
....
}
query_txt = "[query_text_from file]"
conn = psycopg2.connect(connection_factory=MyLoggingConnection, **db_settings)
conn.initialize(logger)
cur = conn.cursor()
cur.execute(query_text)
然后你就会得到:
DEBUG: __main__:[query] 3 ms
在filter()
中,您可以更改格式,如果小于某个值,也可以选择不显示。
https://stackoverflow.com/questions/32661495
复制相似问题