| 00:00.22 | *** join/#storm _mup_ (~mup@li37-90.members.linode.com) |
| 00:31.56 | *** join/#storm bigjools (~quassel@60-241-216-21.static.tpgi.com.au) |
| 00:31.57 | *** join/#storm bigjools (~quassel@canonical/launchpad/bigjools) |
| 01:05.01 | *** join/#storm fbru02 (~fbru02@ip-64-134-229-131.public.wayport.net) |
| 01:07.12 | *** join/#storm pipe (~niemeyer@200.203.57.54) |
| 01:07.18 | *** part/#storm pipe (~niemeyer@200.203.57.54) |
| 01:52.45 | *** join/#storm stub (~stub@canonical/launchpad/stub) |
| 03:13.00 | *** join/#storm fbru02 (~fbru02@ip-64-134-229-131.public.wayport.net) |
| 05:58.20 | *** join/#storm munderwo-work (~markl@203-206-133-216.perm.iinet.net.au) |
| 06:00.43 | munderwo-work | Hi all. Im wanting to us the store.execute() function. I want to do a "SELECT * FROM table where table.id IN (%s)" where %s is one or more ids.. I have the ids in a list, but thats not working with the storm.execute.. anybody have an idea about how to get around this? |
| 06:01.28 | munderwo-work | and is there anything wrong with getting a raw cursor from the store and using (in my case) psycopg2 to do the sql directly? |
| 06:23.31 | marienz | munderwo-work: doesn't store.find(Foo, Foo.id.is_in(ids)) do the trick? |
| 06:25.27 | munderwo-work | marienz: its a little bit more complicated than that. we have a template that provides a schema for a report, and we need to basically put together arbitrary pieces of SQL with parameters. The above query is just one of these, and AFAIK the psycopg2 cursor would allow us to do something like this , ie properly casting a list to the appropriate SQL query.. |
| 06:27.34 | marienz | munderwo-work: well, there's "SQL" for passing raw sql into store.find, but I can't tell if I'd actually use that or try a little harder to get the "arbitrary pieces of SQL" into a form storm can use |
| 06:28.16 | munderwo-work | is the SQL is the Manual |
| 06:29.14 | munderwo-work | from storm.locals import SQL ? |
| 06:29.38 | therve | munderwo-work, the alternative is to do ', '.join("%s" for i in ids) |
| 06:30.58 | munderwo-work | therve: ahh yes... that will probably work, just wanted to avoid the string interpolation into the actual query (as there are whole paragraphs in psycopg2 telling you not to do that) but Putting the %s in and not the actual data would solve that. |
| 06:32.44 | therve | I don't think you have to do string interpolation |
| 06:33.02 | marienz | I could've sworn that if you had a list of ids what I said would work |
| 06:35.18 | therve | yeah we ought to pass things down to psycopg anyway |
| 06:36.01 | marienz | and I can't tell if your "arbitrary pieces of SQL" would be reasonably safely passed to SQL() or not without more information |
| 06:40.20 | munderwo-work | I think i've worked it out.. r = store.execute("SELECT * FROM customer_store where customer_store.id IN %s", (stores,)) |
| 06:41.14 | munderwo-work | what I was doing before was using a list (stores is a tuple in the above function). and actually even psycopg2 doesnt like it being an array either so I was wrong in that sense. |
| 06:43.43 | munderwo-work | marienz: I think the IN sql statement needs tuples rather than lists. In side storm it expects a string to be a single parameter, and if you pass a string as a parameter it actually tries to iterate over it, so if you pass '0, 10' NOT in a tuple as the parameter then the sql comes out like "SELECT * FROM customer_store where customer_store.id IN %s" % ("0", ",", " ", "1", "0") which ends up obviously not working.. |
| 06:44.07 | munderwo-work | so the lesson is that all parameters passed to an execute must be wrapped in a tuple.. |
| 06:49.15 | marienz | store.find(Person, Person.id.is_in([1, 0, 3])) generates "SELECT person.id, person.name FROM person WHERE person.id IN (?, ?, ?)', (1, 0, 3)" which yields the desired results here |
| 06:50.42 | marienz | so I don't understand what you're doing (are you *sure* you're passing a python list of python ints, and not a string representation of it?) |
| 06:51.08 | marienz | when you said "I have the ids in a list" I assumed you meant an actual python list, not a single string whose contents look like a list |
| 06:51.24 | munderwo-work | marienz: yeah the is_in() function takes a list, but execute() parameters need to be an iterable.. they could be a list, BUUUT if you want to use an IN clause in a raw SQL cursor then you need to use a tuple as the parameter and remove the brackets in the actual sql. Because psycopg2 converts a tuple to (0, 1) |
| 06:51.39 | marienz | why are you using a raw SQL cursor if you have a list of ids? |
| 06:52.00 | munderwo-work | marienz: because we get the SQL from a template for a report.. |
| 06:52.25 | marienz | I don't seem to understand what you're actually trying to do. I'll just leave you to it, but keep in mind that if you're dealing with raw sql the usual caveats about injection apply (storm can't somehow magically make this safe) |
| 06:53.18 | munderwo-work | marienz: yeah its a bit hard to understand, but we are pretty confident that we are heading in the right direction.. yup, i understand which is why i wanted to use the execute in the correct way. |
| 06:53.20 | marienz | if this was me I'd try very hard to not need raw sql, instead building up a storm expression to pass to store.find dynamically |
| 06:53.42 | marienz | there is no magical way to make raw sql safe. You can't simultaneously pass raw sql to execute and not have to worry about injection |
| 06:54.08 | marienz | (unless all of the raw sql is fixed and you're only using placeholders) |
| 06:54.20 | marienz | if you're building the sql dynamically you need to worry about injection in that process |
| 06:55.48 | munderwo-work | marienz: well the parameters are dynamic, but the rest of the SQL is static. And I was under the impression that if you did a cursor.execute(sql, params) that psycopg2 correctly escapes the parameters so that sql injection is avoided |
| 06:56.23 | marienz | right, that should be fine. But if all of your sql is static I don't get why you can't just use store.find, unless there's something particularly unusual in your sql |
| 06:56.58 | marienz | of course "I hate having to build the equivalent storm expression, I know my sql is good and just want storm to take it already" is an acceptable answer |
| 06:57.11 | munderwo-work | it just makes sense in this case. ;) |
| 06:57.39 | marienz | but you mentioning "arbitrary piecef of SQL" and "templates" makes me really worried that you're stringing together user-provided SQL and expecting storm to make that more safe than it actually is |
| 06:58.01 | munderwo-work | its not really the "I hate building storm expressions", its more "from a readability point for certain module" |
| 06:58.54 | munderwo-work | oh no the templates are ours. and really its a schema for a report. the user asks for a certain report, we read in a python dict which has a bunch offields that define the report from our source code that contains all the data we need to read the source code. |
| 06:59.07 | marienz | right, that could make sense then |
| 06:59.18 | munderwo-work | its not user provided SQL at all.. not in the slightest.. |
| 06:59.31 | marienz | sorry to nag, but I just really hate ending up partially responsible for something vulnerable to sql injection |
| 07:00.08 | munderwo-work | marienz: nah thats cool.. I totally understand.. I hope im paranoid about those things to avoid it... |
| 07:00.57 | munderwo-work | the sql injection issue is why I was continuing to struggle to get the params part of the execute to work.. I could've just concatenated them together.. |
| 07:01.43 | munderwo-work | and by arbitrary, its more that we have a generic engine class that parses the schema, so although it has to take arbitrary SQL, we have complete control over the sql that does get passed to it |
| 07:06.22 | munderwo-work | thanks for the help! |
| 07:08.11 | *** join/#storm stub (~stub@node-mt7.pool-125-24.dynamic.totbb.net) |
| 07:08.12 | *** join/#storm stub (~stub@canonical/launchpad/stub) |
| 07:08.52 | *** join/#storm stub1 (~stub@canonical/launchpad/stub) |
| 07:39.15 | *** join/#storm mgedmin_ (~mg@Maemo/community/contributor/mgedmin) |
| 08:14.47 | *** join/#storm fcorrea_ (~fcorrea@187.3.134.37) |
| 12:31.38 | *** join/#storm ant384 (~ghoul@unaffiliated/ant384) |
| 12:50.46 | *** join/#storm niemeyer (~niemeyer@200.203.57.54) |
| 12:50.46 | *** mode/#storm [+o niemeyer] by ChanServ |
| 12:57.17 | *** join/#storm fbru02 (~fbru02@ip-64-134-229-131.public.wayport.net) |
| 13:29.39 | *** join/#storm esteve (~quassel@91.126.88.6) |
| 14:47.55 | *** join/#storm fbru02 (~fbru02@ip-64-134-229-131.public.wayport.net) |
| 15:19.53 | allenap | jkakar: Hi there :) I am thinking of using commandant, but I see it's not packaged or on PyPI. If you do the latter I can ask for the former to be done. Interested? |
| 15:22.20 | *** join/#storm fbru02 (~fbru02@adsl-99-52-211-46.dsl.lsan03.sbcglobal.net) |
| 15:41.28 | *** join/#storm fbru02 (~fbru02@vz144.citysearch.com) |
| 15:48.46 | jkakar | allenap: Hi! |
| 15:48.58 | jkakar | allenap: It's packaged... but I haven't managed to get it onto PyPI yet. |
| 15:49.23 | jkakar | allenap: https://launchpad.net/~jkakar/+archive/commandant |
| 15:49.54 | allenap | jkakar: Is that package up-to-date? I can sort out PyPI then. |
| 15:49.55 | jkakar | allenap: Getting it on PyPI is on my TODO list... but that list seems to grow faster than it shrinks. :) |
| 15:50.02 | jkakar | allenap: Yeah, it's up-to-date. |
| 15:50.14 | jkakar | allenap: If you could sort out PyPI I'd really appreciate it. :) |
| 15:50.22 | allenap | jkakar: \o/ Cool, you rule. |
| 15:50.46 | jkakar | allenap: I actually want to find some time to re-do the documentation... but anyway, if you look in the README there's some stuff about how to use it that's hopefully somewhat helpful... |
| 15:51.01 | jkakar | allenap: I think it emphasize the case of using it as a command-line tool (as opposed to a Python library) too much, but anyway... |
| 15:53.31 | allenap | jkakar: Well, everything else looks great. I don't think anyone's going to quibble over that :) |
| 15:54.37 | jkakar | allenap: :) |
| 16:48.59 | *** join/#storm timsmith (~timsmith@216.64.169.240) |
| 17:07.59 | allenap | jkakar: http://pypi.python.org/pypi/commandant. What's your PyPI id so I can add you as an owner? |
| 17:08.50 | jkakar | allenap: Awesome, thanks! I guess my ID is 'jkakar'. |
| 17:09.21 | jkakar | allenap: It's so nice to see this on PyPI, thanks. :) |
| 17:09.57 | allenap | jkakar: Done :) |
| 17:10.06 | jkakar | Ta! |
| 17:14.56 | jkakar | allenap: btw, how did you upload it? I should probably add a 'make release' target that does it or something... |
| 17:24.38 | *** join/#storm timsmith (~timsmith@216.64.169.240) |
| 17:50.21 | *** join/#storm vaibhav (~vaibhav@rrcs-173-196-138-66.west.biz.rr.com) |
| 17:50.38 | *** part/#storm vaibhav (~vaibhav@rrcs-173-196-138-66.west.biz.rr.com) |
| 18:10.15 | *** join/#storm m4v (~znc@unaffiliated/m4v) |
| 19:26.23 | allenap | jkakar: I did: python setup.py sdist upload --sign |
| 19:26.35 | jkakar | allenap: Cool, thanks. |
| 19:55.21 | *** join/#storm SuperShabam (~quassel@24-104-129.146.hfc.mediarain.com) |
| 20:03.08 | *** join/#storm m4v (~znc@unaffiliated/m4v) |
| 20:51.16 | *** join/#storm fcorrea_ (~fcorrea@187.3.134.37) |
| 20:54.57 | *** join/#storm fcorrea (~fcorrea@187.3.134.37) |
| 22:00.12 | *** join/#storm peregrine81 (~peregrine@CPE-72-128-193-51.wi.res.rr.com) |
| 23:13.32 | *** join/#storm m4v (~znc@unaffiliated/m4v) |
| 23:16.22 | *** join/#storm bigjools (~quassel@60-241-216-21.static.tpgi.com.au) |
| 23:16.23 | *** join/#storm bigjools (~quassel@canonical/launchpad/bigjools) |
| 23:39.46 | *** join/#storm bigjools (~quassel@60-241-216-21.static.tpgi.com.au) |
| 23:39.47 | *** join/#storm bigjools (~quassel@canonical/launchpad/bigjools) |