我如何获得查询执行时间的 psycopg2 日志记录? [英] How would I get psycopg2 logging of query execution time?

问题描述

我正在尝试获取有关由 psycopg2 执行的查询的性能统计信息，但文档/示例似乎仍然模糊不清，并且不够清晰.

I'm trying to get the performance statistics on queries executed by psycopg2, but the documentation / examples still seem fuzzy and not as clear as it could be.

我至少可以通过记录器进行调试.我需要做什么才能访问查询的性能数据?我想获取查询执行时间的数字.

I've at least got debugging working through the logger. What would I need to do to access the performance data for the query? I'm wanting to get the number for query execution time.

是否有我可以访问的方法，或者我需要初始化以输出查询执行时间的其他方法?

Is there a method I can access, or something else I need to initialize to output the query execution time?

这是我目前所拥有的拼凑而成的摘录:

Here's a pieced together extract of what I have so far:

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]

推荐答案

很容易在执行开始时设置时间戳并在结束时计算持续时间.您将需要自己的 LoggingConnection 和 LoggingCursor 的简单子类.请参阅我的示例代码.

Easy enough to set timestamp at start of execution and calculate duration at end. You'll need your own simple subclasses of LoggingConnection and LoggingCursor. See my example code.

这是基于 MinTimeLoggingConnection 的源代码，您可以在 psycopg2/extras.py 源代码中找到.

This is based on source of MinTimeLoggingConnection you can find in psycopg2/extras.py source.

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() 中，您可以更改格式，或者选择不显示，如果小于某个值.

within your filter() you can change the formatting, or choose to not display, if less than some value.

这篇关于我如何获得查询执行时间的 psycopg2 日志记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！