[sqlalchemy] Re: Generating Raw SQL statements with parameters included
Thanks Timuçin. I know of the logging functionality by setting
echo=True but I wanted to avoid the ? inside the query and have the
parameters instead without me having to do it manually so I could copy
the entire query string and execute it as is on the database. Michal's
wiki post solved that for me.
On Jan 11, 3:21 pm, Timuçin Kızılay wrote:
> I'm using SA with turbogears 2.x framework and in development mode when
> I turn on all the logging I can see the generated SA queries in the
> paste web server console. And one line after the generated query, there
> is a line which shows the parameters.
>
> here is a sample output:
>
> 22:17:53,695 INFO [sqlalchemy.engine.base.Engine.0x...cad0] SELECT
> adres_ilce.id AS adres_ilce_id, adres_ilce.kod AS adres_ilce_kod,
> adres_ilce.ad AS adres_ilce_ad, adres_ilce.adres_il_kod AS
> adres_ilce_adres_il_kod
> FROM adres_ilce
> WHERE adres_ilce.adres_il_kod = ? ORDER BY adres_ilce.ad
> 22:17:53,695 INFO [sqlalchemy.engine.base.Engine.0x...cad0] SELECT
> adres_ilce.id AS adres_ilce_id, adres_ilce.kod AS adres_ilce_kod,
> adres_ilce.ad AS adres_ilce_ad, adres_ilce.adres_il_kod AS
> adres_ilce_adres_il_kod
> FROM adres_ilce
> WHERE adres_ilce.adres_il_kod = ? ORDER BY adres_ilce.ad
> 22:17:53,696 INFO [sqlalchemy.engine.base.Engine.0x...cad0] (u'15',)
> 22:17:53,696 INFO [sqlalchemy.engine.base.Engine.0x...cad0] (u'15',)
> 22:17:53,725 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Col
> ('adres_ilce_id', 'adres_ilce_kod', 'adres_ilce_ad',
> 'adres_ilce_adres_il_kod')
> 22:17:53,725 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Col
> ('adres_ilce_id', 'adres_ilce_kod', 'adres_ilce_ad',
> 'adres_ilce_adres_il_kod')
> 22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (190,
> 0, u'- - - - - - - - - - - - - - -', 15)
> 22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (190,
> 0, u'- - - - - - - - - - - - - - -', 15)
> 22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (193,
> 52, u'ALTINYAYLA(D\u0130RM\u0130L)', 15)
> 22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (193,
> 52, u'ALTINYAYLA(D\u0130RM\u0130L)', 15)
> 22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (192,
> 51, u'A\u011eLASUN', 15)
> 22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (192,
> 51, u'A\u011eLASUN', 15)
> 22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (194,
> 53, u'BUCAK', 15)
> 22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (194,
> 53, u'BUCAK', 15)
> 22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (191,
> 1, u'BURDUR', 15)
> 22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (191,
> 1, u'BURDUR', 15)
> 22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (197,
> 56, u'G\xd6LH\u0130SAR', 15)
> 22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (197,
> 56, u'G\xd6LH\u0130SAR', 15)
> 22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (198,
> 57, u'KARAMANLI', 15)
> 22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (198,
> 57, u'KARAMANLI', 15)
> 22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (199,
> 59, u'TEFENN\u0130', 15)
> 22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (199,
> 59, u'TEFENN\u0130', 15)
> 22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (200,
> 60, u'YE\u015e\u0130LOVA', 15)
> 22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (200,
> 60, u'YE\u015e\u0130LOVA', 15)
> 22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (195,
> 54, u'\xc7AVDIR', 15)
> 22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (195,
> 54, u'\xc7AVDIR', 15)
> 22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (196,
> 55, u'\xc7ELT\u0130K\xc7\u0130', 15)
> 22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (196,
> 55, u'\xc7ELT\u0130K\xc7\u0130', 15)
> -
>
> The line just after the generated query shows the parameters:
> (u'15',) is the parameter in my sample log output.
>
> Maybe you have to turn on debug output in your application to show query
> result too. I've done something in tg logging config so my logs shows
> every sa query and results two times.
>
> 11-01-2011 19:12, Harkirat yazmış:
>
>
>
>
>
>
>
> > Thank you! I understand the security concerns. I only need this for
> > testing purposes and production will be all bind-parameter driven.
>
> > On Jan 11, 11:15 am, Michael Bayer wrote:
> >> This question comes up from time to time and I'm generally extremely
> >> uncomfortable documenting it, as SQLAlchemy carefully protects its
> >> reputation as being 100% bind-parameter driven and in no way wants to
> >> encourage the rendering of data directly into SQL strings. This is the
> >> one real area of SQL where security is a concern.
>
> >> However, I've added a wikipage for this use case which has a level of
>
Re: [sqlalchemy] Re: Generating Raw SQL statements with parameters included
I'm using SA with turbogears 2.x framework and in development mode when
I turn on all the logging I can see the generated SA queries in the
paste web server console. And one line after the generated query, there
is a line which shows the parameters.
here is a sample output:
22:17:53,695 INFO [sqlalchemy.engine.base.Engine.0x...cad0] SELECT
adres_ilce.id AS adres_ilce_id, adres_ilce.kod AS adres_ilce_kod,
adres_ilce.ad AS adres_ilce_ad, adres_ilce.adres_il_kod AS
adres_ilce_adres_il_kod
FROM adres_ilce
WHERE adres_ilce.adres_il_kod = ? ORDER BY adres_ilce.ad
22:17:53,695 INFO [sqlalchemy.engine.base.Engine.0x...cad0] SELECT
adres_ilce.id AS adres_ilce_id, adres_ilce.kod AS adres_ilce_kod,
adres_ilce.ad AS adres_ilce_ad, adres_ilce.adres_il_kod AS
adres_ilce_adres_il_kod
FROM adres_ilce
WHERE adres_ilce.adres_il_kod = ? ORDER BY adres_ilce.ad
22:17:53,696 INFO [sqlalchemy.engine.base.Engine.0x...cad0] (u'15',)
22:17:53,696 INFO [sqlalchemy.engine.base.Engine.0x...cad0] (u'15',)
22:17:53,725 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Col
('adres_ilce_id', 'adres_ilce_kod', 'adres_ilce_ad',
'adres_ilce_adres_il_kod')
22:17:53,725 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Col
('adres_ilce_id', 'adres_ilce_kod', 'adres_ilce_ad',
'adres_ilce_adres_il_kod')
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (190,
0, u'- - - - - - - - - - - - - - -', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (190,
0, u'- - - - - - - - - - - - - - -', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (193,
52, u'ALTINYAYLA(D\u0130RM\u0130L)', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (193,
52, u'ALTINYAYLA(D\u0130RM\u0130L)', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (192,
51, u'A\u011eLASUN', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (192,
51, u'A\u011eLASUN', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (194,
53, u'BUCAK', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (194,
53, u'BUCAK', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (191,
1, u'BURDUR', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (191,
1, u'BURDUR', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (197,
56, u'G\xd6LH\u0130SAR', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (197,
56, u'G\xd6LH\u0130SAR', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (198,
57, u'KARAMANLI', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (198,
57, u'KARAMANLI', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (199,
59, u'TEFENN\u0130', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (199,
59, u'TEFENN\u0130', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (200,
60, u'YE\u015e\u0130LOVA', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (200,
60, u'YE\u015e\u0130LOVA', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (195,
54, u'\xc7AVDIR', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (195,
54, u'\xc7AVDIR', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (196,
55, u'\xc7ELT\u0130K\xc7\u0130', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (196,
55, u'\xc7ELT\u0130K\xc7\u0130', 15)
-
The line just after the generated query shows the parameters:
(u'15',) is the parameter in my sample log output.
Maybe you have to turn on debug output in your application to show query
result too. I've done something in tg logging config so my logs shows
every sa query and results two times.
11-01-2011 19:12, Harkirat yazmış:
Thank you! I understand the security concerns. I only need this for
testing purposes and production will be all bind-parameter driven.
On Jan 11, 11:15 am, Michael Bayer wrote:
This question comes up from time to time and I'm generally extremely
uncomfortable documenting it, as SQLAlchemy carefully protects its reputation
as being 100% bind-parameter driven and in no way wants to encourage the
rendering of data directly into SQL strings.This is the one real area of
SQL where security is a concern.
However, I've added a wikipage for this use case which has a level of
disclaimer I think will be OK, which you can see
athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/BindsAsStrings.
On Jan 11, 2011, at 10:14 AM, Harkirat wrote:
Hi All,
When I run this
delete_stmt = appname.delete(appname.c.appid==1)
print delete_stmt
I get output
DELETE FROM appname WHERE appname.appid = ?
Is there any way I can print out raw sql statments with the parameters
included e.g.
DELETE FROM appname WHERE appname.appid = 1
Thanks!
Harkirat
--
You received this messag
[sqlalchemy] Re: Generating Raw SQL statements with parameters included
Thank you! I understand the security concerns. I only need this for testing purposes and production will be all bind-parameter driven. On Jan 11, 11:15 am, Michael Bayer wrote: > This question comes up from time to time and I'm generally extremely > uncomfortable documenting it, as SQLAlchemy carefully protects its reputation > as being 100% bind-parameter driven and in no way wants to encourage the > rendering of data directly into SQL strings. This is the one real area of > SQL where security is a concern. > > However, I've added a wikipage for this use case which has a level of > disclaimer I think will be OK, which you can see > athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/BindsAsStrings. > > On Jan 11, 2011, at 10:14 AM, Harkirat wrote: > > > > > > > > > Hi All, > > When I run this > > > delete_stmt = appname.delete(appname.c.appid==1) > > print delete_stmt > > > I get output > > > DELETE FROM appname WHERE appname.appid = ? > > > Is there any way I can print out raw sql statments with the parameters > > included e.g. > > DELETE FROM appname WHERE appname.appid = 1 > > > Thanks! > > > Harkirat > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
