how to print a constructed query with it's parameters?

[alex bodnaru]

hello friends,

in order to debug my code, i wish to print my query sql.

it's in the fashion of
query = table.query().filter(table.code='XL').filter(table.name.like('%'+q+'%')
with unicode parameters.

by just printing query, i get the select with ? parameters, but not
the additional parameters list, that contains ['XL', %q-value%]. since
it doesn't presently work ok, i'd like to print the list as well.

thanks in advance,
alex

[Glauco]

> by just printing query, i get the select with ? parameters, but not
> the additional parameters list, that contains ['XL', %q-value%]. since
> it doesn't presently work ok, i'd like to print the list as well.
>

print qry.compile()

Glauco

--
+------------------------------------------------------------+
Glauco Uri
glauco(at)sferacarta.com

Sfera Carta SoftwareŽ info(at)sferacarta.com
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
+------------------------------------------------------------+

[alex bodnaru]

hello glauco,

thanks for your answer.

but it unfortunately doesn't work for me.

could it be because i'm using elixir?

best regards,
alex

On Wed, Oct 15, 2008 at 12:10, Glauco <gla...@sferacarta.com> wrote:
>
>
>> by just printing query, i get the select with ? parameters, but not
>> the additional parameters list, that contains ['XL', %q-value%]. since
>> it doesn't presently work ok, i'd like to print the list as well.
>>
>
>
> print qry.compile()
>
>
> Glauco
>
> --
> +------------------------------------------------------------+
> Glauco Uri
> glauco(at)sferacarta.com
>

> Sfera Carta Software(R) info(at)sferacarta.com

[King Simon-NFHD78]

This question comes up a lot. For example, see
http://groups.google.com/group/sqlalchemy/browse_thread/thread/a0602ede8
18f55c7

Firstly, if you use echo=True in your call to create_engine, all SQL
will be printed to stdout. The parameters will be displayed as a list
AFTER the SQL is printed.

Eg. (from http://www.sqlalchemy.org/docs/05/ormtutorial.html)

BEGIN
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
['ed', 'Ed Jones', 'edspassword']
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS
users_fullname, users.password AS users_password
FROM users
WHERE users.name = ?
LIMIT 1 OFFSET 0
['ed']

You can control the logging more finely using the logging module - see
http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_logging for
more details.

The problem is that SQLAlchemy doesn't ever replace those '?' characters
with the actual parameter values. Those strings are passed directly to
the DBAPI driver, along with the list of parameter values. It is then up
to the DBAPI driver how it passes the query to the database. (This is
why SQLAlchemy is fairly safe from SQL Injection attacks).

Hope that helps,

Simon

[a...@svilendobrev.com]

i have another approach, which may or may not serve you.
All those '?' are bindparams, and one can eventualy get them printed
with their names - and put names where there aren't.
that's what i needed, i guess replacing names with values would be
easy job.
the code is part of tests/convertertest.py of sqlalchemyAggregator,
http://dev.gafol.net/t/aggregator/
or
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/aggregator/

class T_mark( unittest.TestCase):
...
def setUp( self):
self.m = MetaData()
#hack for better visibility
def bp( self,bindparam):
if bindparam.value is not None:
return 'const('+repr(bindparam.value)+')'
k = bindparam.key
if k.startswith( Converter._pfx): #my own bindparams
k = k[ len( Converter._pfx):]
return 'BindParam('+k+')'
self.old_bp = DefaultCompiler._truncate_bindparam
DefaultCompiler._truncate_bindparam = bp

def tearDown( self):
DefaultCompiler._truncate_bindparam = self.old_bp
...

str(expression) then does things like
:const(True) AND :BindParam(oid) = movies.id
tags.tabl = :const('movies') AND tags.oid = :BindParam(oid)

there's some more stuff going on there around compatibility with SA
0.3--0.5, but that's core.

ciao
svil

On Wednesday 15 October 2008 13:33:46 King Simon-NFHD78 wrote:
> > -----Original Message-----
> > From: sqlal...@googlegroups.com
> > [mailto:sqlal...@googlegroups.com] On Behalf Of alex bodnaru
> > Sent: 15 October 2008 11:00
> > To: SQLAlchemy
> > Subject: [sqlalchemy] how to print a constructed query with
> > it's parameters?
> >
> >
> > hello friends,
> >
> > in order to debug my code, i wish to print my query sql.
> >
> > it's in the fashion of
> > query =
> > table.query().filter(table.code='XL').filter(table.name.like('
> > %'+q+'%')
> > with unicode parameters.
> >
> > by just printing query, i get the select with ? parameters, but
> > not the additional parameters list, that contains ['XL',
> > %q-value%]. since it doesn't presently work ok, i'd like to print
> > the list as well.
> >
> > thanks in advance,
> > alex
>
> This question comes up a lot. For example, see
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/a060

>2ede8 18f55c7

[alex bodnaru]

hi friends,

i have a lot to learn from both approaches, but i have sadly appeared too lazy.

there will be no problem to imagine what the sql will be, only by
looking at the
template statement (with ?'s) and at the list of parameters.

since the template is available to print (probably by __str__), i'd
onlu ask where
the bindparams list is. eventual quotes and escapes may be imagined by
the types of
the columns.

thanks in advance,
alex

[Ants Aasma]

This seems to come up often. I took a few minutes and threw together a
semi-robust way to do this on 0.5 series. I posted it under usage
recipes in the wiki: http://www.sqlalchemy.org/trac/wiki/DebugInlineParams
It has some flaws, but should be somewhat helpful for debugging.

Ants

On Oct 15, 2:42 pm, "alex bodnaru" <alexbodn.gro...@gmail.com> wrote:
> hi friends,
>
> i have a lot to learn from both approaches, but i have sadly appeared too lazy.
>
> there will be no problem to imagine what the sql will be, only by
> looking at the
> template statement (with ?'s) and at the list of parameters.
>
> since the template is available to print (probably by __str__), i'd
> onlu ask where
> the bindparams list is. eventual quotes and escapes may be imagined by
> the types of
> the columns.
>
> thanks in advance,
> alex
>
> On Wed, Oct 15, 2008 at 12:54,  <a...@svilendobrev.com> wrote:
>
> > i have another approach, which may or may not serve you.
> > All those '?' are bindparams, and one can eventualy get them printed
> > with their names - and put names where there aren't.
> > that's what i needed, i guess replacing names with values would be
> > easy job.
> > the code is part of tests/convertertest.py of sqlalchemyAggregator,
> >http://dev.gafol.net/t/aggregator/
> > or

> >http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/agg...

> >> Eg. (fromhttp://www.sqlalchemy.org/docs/05/ormtutorial.html)

[alex bodnaru]

hi Ants,

thank you very much.

it will help me and many others in printf style debugging.

best regards,
alex

[Pavel Skvazh]

Thanks for the solution!

But i get the warning for this query:
SELECT address.name AS address_lang, "user".name AS user_name
FROM addresses
LEFT OUTER JOIN "user" ON "user".id = address.user_id

Throws
sqlalchemy-0.5.0rc2dev_r5150-py2.5.egg\sqlalchemy\sql\expression.py:
1616: SAWarning: Column 'name' on table 'Select object' being replaced
by another column with the same key. Consider use_labels for select()
statements.
self[column.key] = column