DBI trace?

[Andrew Fabbro]

Is there something akin to perl's DBI->trace in python?

I can't find anything documented in DBI modules like pyPgSQL...or is
there some otherway of getting debug-level logging out of database
calls?

sorry for more newbie-ism but can't find anything in the O'Reilly
books or Google Groups either.

Thanks,

-Drew

 

[Skip Montanaro]

Andrew> Is there something akin to perl's DBI->trace in python?

I don't know. What is DBI->trace? If you describe what you're looking for,
perhaps someone will have some ideas about whether or not it exists
currently or might be fairly easily implemented.

Skip

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

Is there something akin to perl's DBI->trace in python? [...]

I suppose you mean logging of SQL statements. There is no standard way
in the Python DB-API, I can only speak for the projects I'm involved in:

pyPgSQL: There is a hidden attribute 'toggleShowQuery' in the low-level
connection object, which will log the queries to stderr (stderr is
currently hard-coded):

#v+
gerhard ( at ) gargamel:~$ python
Python 2.2.2 (#1, Nov 30 2002, 23:19:58)
[GCC 2.95.4 20020320 [FreeBSD]] on freebsd4
Type "help", "copyright", "credits" or "license" for more information.QUERY: DECLARE "PgSQL_0811F1EC" CURSOR FOR select * from test
QUERY: FETCH 1 FROM "PgSQL_0811F1EC"
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 23
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 1043

>>> result = cursor.fetchmany(5) QUERY: FETCH 4 FROM "PgSQL_0811F1EC"
>>> result [[None, 'A'], [None, 'B'], [None, 'C'], [None, 'F'], [None, 'F']]
>>> con.commit()

QUERY: CLOSE PgSQL_0811F1EC
QUERY: COMMIT WORK
#v-

PySQLite:

In the .connect() call, there is an optional parameter command_logfile,
which will accept a file-like object (or anything that implements a
write() method).

HTH,

-- Gerhard

 

[Andrew Fabbro]

I don't know. What is DBI->trace? If you describe what you're looking for,
perhaps someone will have some ideas about whether or not it exists
currently or might be fairly easily implemented.

I guess that would help, eh? Sorry ;0

DBI->trace allows you dump the entire conversation between perl and
the database to a file. It's sort of like running your database calls
with a "-v" to see everything that the call is doing. There are
various levels of verbosity, depending on how deep you want to get.

It's very helpful for seeing exactly what the parameters passed to
your database are after interpolation, if your program is saying to
the database what you think it is saying, if the database is giving
more error output than you are seeing from perl, etc.

You can do this within the DB - for example, in postgres you can
change the logging levels in the postgresql.conf and bounce the DB.
But obviously, you don't want to have to bounce the DB every time you
want to debug.

Here's an example (specifically, this was done with
DBI->trace(1,'/tmp/dbi.log')
in perl):


-> DBI->connect(dbig:dbname=SANDBOX, andrew, ****,
HASH(0x834e070))
pg_db_login
<- connect('dbname=SANDBOX' 'andrew' ...)= DBI::db=HASH(0x8136970)
at DBI.pm
line 582
dbd_db_STORE
<- STORE('PrintError' 1)= 1 at DBI.pm line 622
dbd_db_STORE
<- STORE('AutoCommit' 1)= 1 at DBI.pm line 622
dbd_db_STORE
<- STORE('Username' 'andrew')= 1 at DBI.pm line 625
<- connect= DBI::db=HASH(0x8136970)
dbd_db_STORE
<- STORE('dbi_connect_closure' CODE(0x8366194))= 1 at DBI.pm line
639
dbd_st_prepare: statement = >
SELECT *
FROM gl
WHERE transaction_date >= ? AND transaction_date < ?
AND
( account_credit = ? OR account_debit = ? )<
dbd_st_preparse: statement = >
SELECT *
FROM gl
WHERE transaction_date >= ? AND transaction_date < ?
AND
( account_credit = ? OR account_debit = ? )<
<- prepare('
SELECT *
FROM gl
WHERE transaction_date >= ? AND transaction_date < ?
AND
( account_credit = ? OR account_debit = ? )')=
DBI::st=HASH(0x834de48) at Accounting_Utils.pm line 367
dbd_bind_ph
dbd_st_rebind
dbd_bind_ph
dbd_st_rebind
dbd_bind_ph
dbd_st_rebind
dbd_bind_ph
dbd_st_rebind
dbd_st_execute
<- execute('2003-10-01' '2003-09-01' ...)= '0E0' at
Accounting_Utils.pm
line 372
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'glid' 'account_debit' 'amount'
'account_credit'
'transaction_date' 'reconciled' 'ref' 'note' 'last_modified'
'modified_by' ] at
Accounting_Utils.pm line 376
dbd_st_fetch
1 <- fetch= undef row-1 at Accounting_Utils.pm line 376
<- fetchrow_hashref= undef row-1 at Accounting_Utils.pm line 376
<- finish= 1 at Accounting_Utils.pm line 383
dbd_db_disconnect
<- disconnect= 1 at Accounting_Utils.pm line 384
dbd_st_destroy
<- DESTROY= undef at index.cgi line 195
dbd_db_destroy
<- DESTROY= undef at index.cgi line 195
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190

dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch
dbd_st_FETCH
1 <- FETCH('NAME')= [ 'monthyear' 'account_number' 'amount' ] at
index.cgi
line 190
dbd_st_fetch

(etc.)