[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 mike...@zzzcomputing.com 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.
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 Bayermike...@zzzcomputing.com 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
--
[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 t...@savaskarsitlari.org 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 Bayermike...@zzzcomputing.com 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
