Python SQLite如何获取正在执行的SQL字符串语句

Let's say we have a SQL statement that just needs to be completed with the parameters before getting executed against the DB. For instance:

假设我们有一个SQL语句,只需要在对DB执行之前用参数完成。例如:

sql = '''
      SELECT  id, price, date_out
      FROM sold_items
      WHERE date_out BETWEEN ? AND ?
      '''

database_cursor.execute(sql, (start_date, end_date))

How do I get the string that is parsed and executed?, something like this:

如何获取解析和执行的字符串?,如下所示:

SELECT  id, price, date_out
FROM sold_items
WHERE date_out BETWEEN 2010-12-05 AND 2011-12-01

In this simple case it's not very important, but I have other SQL Statements much more complicated, and for debugging purposes I would like to execute them myself in my sqlite manager and check the results.

在这个简单的情况下,它不是很重要,但我有其他更复杂的SQL语句,出于调试目的,我想在我的sqlite管理器中自己执行它们并检查结果。

Thanks in advance

提前致谢

3 个解决方案

#1

SQLite never actually substitutes parameters into the SQL query string itself; the parameters' values are read directly when it executes the command. (Formatting those values only to parse them again into the same values would be useless overhead.)

SQLite实际上从未将参数替换为SQL查询字符串本身;参数的值在执行命令时直接读取。 (格式化这些值只是为了将它们再次解析为相同的值,这将是无用的开销。)

But if you want to find out how the parameters would be written in SQL, you can use the quote function; something like this:

但是如果你想知道如何在SQL中编写参数,你可以使用quote函数;像这样的东西:

def log_and_execute(cursor, sql, *args):
    s = sql
    if len(args) > 0:
        # generates SELECT quote(?), quote(?), ...
        cursor.execute("SELECT " + ", ".join(["quote(?)" for i in args]), args)
        quoted_values = cursor.fetchone()
        for quoted_value in quoted_values:
            s = s.replace('?', quoted_value, 1)
    print "SQL command: " + s
    cursor.execute(sql, args)

(This code will fail if there is a ? that is not a parameter, i.e., inside a literal string.)

(如果有一个?不是参数,那么此代码将失败,即在文字字符串中。)

#2

UPDATE. I learned from this web page that since Python 3.3 you can trigger printing of executed SQL with

UPDATE。我从这个网页上了解到,从Python 3.3开始,您可以触发已执行SQL的打印

connection.set_trace_callback(print)

#3

-1

What about string formatting it?

字符串格式呢呢?

sql = """
  SELECT  id, price, date_out
  FROM sold_items
  WHERE date_out BETWEEN {0} AND {1} """.format(start_date, end_date)
  """
database_cursor.execute(sql)

#1