如何获取发送到数据库的准备好的查询 [英] How to get prepared query which is sent to db

问题描述

当使用像 pyodbc 这样的实现 Python 数据库 API 规范 如何在应用参数替换后获得完全准备好的查询.我正在调用一个 Sybase 存储过程，它将通过参数替换接收 18 个参数.我想捕获进行的实际调用并记录它以帮助调试.我需要的一个更简单的例子:

When using database libraries like pyodbc that implement the Python Database API Specification how can you get the fully prepared query after parameter substitution has been applied. I'm making a call to a Sybase stored procedure that will receive 18 arguments through parameter substitution. I would like to capture the actual call that was made and log it to help in debugging. A simpler example of what I need:

pyodbc 示例

import pyodbc
conn = pyodbc.connect('DSN=test;PWD=password')
c = conn.cursor()
c.execute('CREATE TABLE stocks (symbol varchar(10), price real)')
c.execute("INSERT INTO stocks VALUES (?, ?)", ('RHAT', 35.14))
c.execute('SELECT * FROM stocks WHERE symbol=?', ('RHAT',))
print c.fetchone()

预期的最终查询(待记录)

CREATE TABLE stocks (symbol varchar(10), price real)
INSERT INTO stocks VALUES ('RHAT', 35.14)
SELECT * FROM stocks WHERE symbol = 'RHAT'

sqlite3 示例

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('CREATE TABLE stocks (symbol text, price real)')
c.execute("INSERT INTO stocks VALUES (?, ?)", ('RHAT', 35.14))
c.execute('SELECT * FROM stocks WHERE symbol=?', ('RHAT',))
print c.fetchone()

我已经放置了 sqlite3 示例，因为它不需要 pyodbc 来试用.

I've put the sqlite3 example as it doesn't need pyodbc to try it out.

更新

似乎当人们使用prepared statements时，将数据填充到模板中的过程是在 DBMS 中完成服务器端.正如 user581592 的回答中所述，经常没有方法或 API 可以从服务器获取最终版本的查询.其他值得注意的链接是 pyodbc 上的 Issue 163 进一步讨论了这一点.还有一些像 psycopg 这样的数据库库添加了一个方法 mogrify 将返回最后声明.但正如他们的文档中提到的，这不是 DB API 的一部分.

It seems that when one uses prepared statements the process of filling the data into the template is done server side in the DBMS. So frequently there is no method or api to get that final version of the query from the server, as described in user581592's answer. Other links of note are Issue 163 on pyodbc that discusses this further. Also some db libraries like psycopg have added a method mogrify that will return the final statement. But as mentioned in their documentation this is not part of the DB API.

推荐答案

来自 https://groups.google.com/forum/#!topic/pyodbc/656mRdbjFuc

pyodbc 不会以任何方式修改 SQL.一般来说，好"ODBC 驱动程序也没有.

pyodbc does not modify the SQL in any way. In general, "good" ODBC drivers don't either.

由于它不是受支持的函数，因此没有用于检索最终 SQL 的 ODBC 函数，因此 pyodbc 无法提供此功能.

Since it is not a supported function, there is no ODBC function for retrieving the final SQL, so there is no way pyodbc could offer this.

因此，最好的办法可能是在脚本中分别记录查询和参数，或者如果可行，您可以尝试在数据库上配置某种级别的查询日志记录.

So, your best bet might be to log the queries, and arguments separately in your script, or you can try to configure some level of query logging on the database, if it is feasible.

这篇关于如何获取发送到数据库的准备好的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！