[sqlalchemy] Filtering for element in PgArray column
Hi all,
what is the proper way to filter for element in a PostgreSQL ARRAY
column? in_() does not produce valid syntax for PostgreSQL ( does
(value) instead of array[value] ).
For now I'm doing the following, but I'm not sure how to bind values and
not have such a gaping sql injection vuln (even though somevalue is
checked against a list of allowed (unicode) values, I want proper binding:
rows = session.query(Model).filter(text({0} @
{1}.format(Model.__table__.c.array_column, 'somevalue').all()
Which should produce:
SELECT * FROM model_table WHERE array_column @ ARRAY['somevalue']
--
.oO V Oo.
Work Hard,
Increase Production,
Prevent Accidents,
and
Be Happy! ;)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] PostgreSQL hstore support for SQLAlchemy
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Aug 11, 2012, at 3:18 PM, Audrius Kažukauskas wrote:
Hi,
I've been looking for a way to use PostgreSQL's hstore type in SA, and
from all the options that I have found on the net the one written by
Kyle Schaffrick (big thanks to him) and posted in this list[0] was the
most promising.
[0]
https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/IgdaTHHgQi0%5B1-25%5D
So I took it and added some missing bits. The result can be found at
https://bitbucket.org/audriusk/hstore. Apart from a couple of issues
(and lacking proper tests), I feel that it's quite usable now. Here's
the more detailed list of what's been changed:
- As was suggested by Mike Bayer in the aforementioned thread, switched
from MutableType to sqlalchemy.ext.mutable.
- Removed usage of string_encode. In my tests it wasn't working as
expected, Postgres was storing literals like '\x42' as 'x42'. As a
result, now unicode strings are accepted as well.
- Added NULL support for values and got rid of unquoted keys and values
part in parser regexp (only NULL value is unquoted).
- Fixed regexp to work with values which contain escaped quotes.
- Changed pair() to return 'hstore(key, value)' instead of 'key =
value' (according to PostgreSQL docs, the latter is deprecated).
- HStoreColumn doesn't require HStore type as an argument anymore.
- Used comparator, without it hstore specific methods were inaccessible
from mapped classes. Also added HStoreColumnProp (borrowed the idea
from GeoAlchemy).
- Moved hstore operators and functions into HStoreMethods mixin class
which is used by HStoreElement and HStoreComparator.
The aforementioned issue is that __getitem__ and contains() do not work
from comparator, the first one probably due to different way it's being
accessed, the latter is shadowed by the method from some other class.
Would be great to make at least contains() to work, if anybody has any
ideas, please tell.
This is the first time I dived deeper into SA, so the resulting code may
use stuff improperly. Comments, suggestions, fixes are welcome!
very nice job understanding quite a number of SQLAlchemy APIs, this is good
work. It reminds me also of how we still have a weakness in the Core API,
that we can't add new comparison methods at the Core level onto Column objects
and such. The column_property() here is a well-designed workaround for that.
Ideally MutationDict would be part of SQLAlchemy, from the mutable extension.
the extension should have mutable versions of basic types list, dict, set.
That's a TODO and also we'd need lots of tests.
The contains() method is addressed by allowing the escape argument through:
- --- a/hstore.py Sat Aug 11 19:17:46 2012 +0300
+++ b/hstore.py Mon Aug 13 10:58:07 2012 -0400
@@ -188,7 +188,7 @@
return self.op('?')(other)
- -def contains(self, other):
+def contains(self, other, **kw):
Boolean expression. Test if keys are a superset of the keys of
the argument hstore expression.
as for __getitem__ I don't want to expose that as a potential operator right
now, I'd rather look into a deeper change to the operator system first so that
hacks like _HStoreBinaryExpression aren't needed, then we can figure out if
there's a space for __getitem__ types of things.
And Kyle, if you're reading this, it would be really nice if you noted
under which license your initial code was published.
--
Audrius Kažukauskas
http://neutrino.lt/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (Darwin)
Comment: GPGTools - http://gpgtools.org
iQEcBAEBAgAGBQJQKRZvAAoJEDMCOcHE2v7hxO0H/0PhmViuP2ndpPCvxmtLEgx4
PFuyl7avGwOh0+64HQl1YENFtNG8ZGDINU7EilsO5HsGVTYqlbT1YsX1ecQV1ury
vLCnf6EYWAczpYa2pTgpn2mNj4WzymoFNx7uMqsoysc6nZCp2BwhMxJTEkHV5BpC
ukTDWJ4iKXCAfISDY8QaR0Ani7IjcYHN5h2Ig8v4EH9DYrocE3E6Hd87kW4N9R+N
P9zJ5/xCZBlUIxpfUsb4R8Kicv+IC+S4WAcs6oIqAllwdFTiZ/PEbRNfXuhSP4CD
zyaSUVEPeGLQDTKky86GhmA3efTjC/CH2/MeAdqaQbMYmDU7gZZiQFulwftrSms=
=1pfk
-END PGP SIGNATURE-
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
[sqlalchemy] is there a reason why lower() isn't a column operation ?
just wondering why i have to do : sqlalchemy.sql.func.lower( class.column ) == string.lower() instead of : class.column.lower() = string.lower() btw - i know in the archives people have mentioned doing an ilike search, but a WHERE lower(column) = 'string' search will search against a functioned index on postgres and I believe oracle. considerably faster on large data sets. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] is there a reason why lower() isn't a column operation ?
On Aug 13, 2012, at 1:44 PM, Jonathan Vanasco wrote: just wondering why i have to do : sqlalchemy.sql.func.lower( class.column ) == string.lower() instead of : class.column.lower() = string.lower() btw - i know in the archives people have mentioned doing an ilike search, but a WHERE lower(column) = 'string' search will search against a functioned index on postgres and I believe oracle. considerably faster on large data sets. There's hundreds of SQL functions that accept a single expression as an argument. SQLAlchemy's func system is designed so that in the vast majority of cases, these functions aren't explicit in SQLAlchemy itself, func.NAME just makes a new Function object with the name NAME. So promoting some subset of those functions to be directly present would require a clear rationale as to why those SQL functions need to be present on the Column. Since they certainly can't all be. Also adding two ways to do the same thing needs a really good reason. The particular lower() comparison you have there isn't necessarily very generic. Depending on the collation behavior of the database, it may not be necessary, or may not be enough to produce a faithful comparison if multibyte characters are in play.For example, comparing unicode strings on MySQL (http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html) might look like: _latin1 'Müller' COLLATE latin1_german2_ci = k that's a lot more involved than a simple lower() call. The real use case you're looking for here is case insensitive comparison, perhaps an operator which just does case insensitive compare at once would be easier to use, and also easier to augment with custom behavior. So there's a lot of questions to be asked about this use case, all of which is in light of that it is 100 times harder to remove a poorly considered feature than to add it. As a total coincidence, I'm working on an extensible operator system for core right now. A user-defined case insensitive compare operation would be easy to define in this new system (and actually you could define one using comparator_factory at the ORM level right now anyway). So a comparison such as the above, which might have edge cases that can't be met generically, might be better approached as a user-applied recipe.That way you get the ease of use and full capability, without adding incomplete features to SQLA directly. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Filtering for element in PgArray column
we don't currently have native support for ARRAY operations and there is some
infrastructure work that should make it easier in 0.8 to add.
for now, when you need custom operators use op():
Model.array_column.op(@)(somevalue)
On Aug 13, 2012, at 10:10 AM, Vlad K. wrote:
Hi all,
what is the proper way to filter for element in a PostgreSQL ARRAY column?
in_() does not produce valid syntax for PostgreSQL ( does (value) instead of
array[value] ).
For now I'm doing the following, but I'm not sure how to bind values and not
have such a gaping sql injection vuln (even though somevalue is checked
against a list of allowed (unicode) values, I want proper binding:
rows = session.query(Model).filter(text({0} @
{1}.format(Model.__table__.c.array_column, 'somevalue').all()
Which should produce:
SELECT * FROM model_table WHERE array_column @ ARRAY['somevalue']
--
.oO V Oo.
Work Hard,
Increase Production,
Prevent Accidents,
and
Be Happy! ;)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
[sqlalchemy] how to get into PG database, is the url the right way? newbie question
Hello, I'm new around here and I've been using SQLalchemy (SA) for a while.
I work with PostGis (PG), OpenLayers (OL), ExtJS, GeoExtJS and now with the
great SA and GeoAlchemy. I have one problem, I created a model where I
defined one table of my PG database, it has a url like this: url =
'postgresql://postgres:password@localhost:5432/pgdb'. In my HTML, I have
some maps displayed with OL and a toolbar built with ExtJS and GeoExtJS. I
put a search button there using ExtJS, and the OL protocol code to get the
data. This protocol has an url option, in this way:
var searchformPanel = new Ext.form.FormPanel(
{
width: 250,
bodyStyle: 'padding:5px',
labelAlign: 'top',
defaults:
{
anchor: '100%'
},
protocol: new OpenLayers.Protocol.HTTP(
{
url: 'http://localhost/mop/py/dbmodel.py',
format: new OpenLayers.Format.GeoJSON()
}
),
items:
etc,etc..
my problem is that in this url I wrote the whole path where my model script
is located, but after pressing the button I got nothing. I think I need
something else rather than only pointing the whole path and the python
script in the url mentioned above, is that correct? I've searched how to
connect sqlalchemy to extjs in google but didn't find any that solved this
doubt.
Any support is very welcome, thanks in advance.
Best regards,
Gery
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/7GJay8SzKUUJ.
To post to this group, send email to sqlalchemy@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] Getting maximum recursion depth exception when creating a model instance with two foreign keys
Gotcha. Thanks! On Monday, August 13, 2012 4:57:29 AM UTC+8, Audrius Kažukauskas wrote: On Sun, 2012-08-12 at 11:07:18 -0700, Zhe Wu wrote: def __init__(self, body, author, story): Here arguments are body, author, story. comment = Comment(author, story, body) And here they are author, story, body. I believe that's the source of your problem. -- Audrius Kažukauskas http://neutrino.lt/ -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/u3XtrS0FeGEJ. To post to this group, send email to sqlalchemy@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] Filtering for element in PgArray column
On 08/13/2012 08:53 PM, Michael Bayer wrote:
we don't currently have native support for ARRAY operations and there is some
infrastructure work that should make it easier in 0.8 to add.
for now, when you need custom operators use op():
Model.array_column.op(@)(somevalue)
I tried that, but that doesn't work because the second operand has to be
wrapped in ARRAY[], afaik that's the only way to lookup a value in an
array column (which is also gist indexable).
If I do
Model.array_column.op(@)('ARRAY[123]')
I get
SELECT * FROM model_table WHERE array_column @ 'ARRAY[123]'
and I need ARRAY[123] without quotes.
--
.oO V Oo.
Work Hard,
Increase Production,
Prevent Accidents,
and
Be Happy! ;)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] PostgreSQL hstore support for SQLAlchemy
On Mon, 2012-08-13 at 10:59:59 -0400, Michael Bayer wrote: very nice job understanding quite a number of SQLAlchemy APIs, this is good work. It reminds me also of how we still have a weakness in the Core API, that we can't add new comparison methods at the Core level onto Column objects and such. The column_property() here is a well-designed workaround for that. Ideally MutationDict would be part of SQLAlchemy, from the mutable extension. the extension should have mutable versions of basic types list, dict, set.That's a TODO and also we'd need lots of tests. Sounds really great (especially the ability to add comparison methods onto Column), looking forward to it. The contains() method is addressed by allowing the escape argument through: Thanks, I pushed the fix. as for __getitem__ I don't want to expose that as a potential operator right now, I'd rather look into a deeper change to the operator system first so that hacks like _HStoreBinaryExpression aren't needed, then we can figure out if there's a space for __getitem__ types of things. I decided to remove it for now, as there's get() method which works in all cases (albeit looks not as nice as using __getitem__), and I would like to keep the symmetry between Core and ORM methods. Will add it back in the future, if (when) support for it comes. Thanks for your comments! -- Audrius Kažukauskas http://neutrino.lt/ pgp8JwLxlfE5e.pgp Description: PGP signature
Re: [sqlalchemy] Filtering for element in PgArray column
On Aug 13, 2012, at 3:25 PM, Vlad K. wrote:
On 08/13/2012 08:53 PM, Michael Bayer wrote:
we don't currently have native support for ARRAY operations and there is
some infrastructure work that should make it easier in 0.8 to add.
for now, when you need custom operators use op():
Model.array_column.op(@)(somevalue)
I tried that, but that doesn't work because the second operand has to be
wrapped in ARRAY[], afaik that's the only way to lookup a value in an array
column (which is also gist indexable).
If I do
Model.array_column.op(@)('ARRAY[123]')
I get
SELECT * FROM model_table WHERE array_column @ 'ARRAY[123]'
and I need ARRAY[123] without quotes.
we can keep turning the crank here, here's a full series of examples to make
this happen:
# step 1. build a custom element to do the ARRAY[xyz] part of this.
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ColumnElement
from sqlalchemy.sql.expression import _literal_as_binds
from sqlalchemy.dialects.postgresql import ARRAY
class MyArray(ColumnElement):
type = ARRAY
def __init__(self, expr):
self.expr = _literal_as_binds(expr)
@compiles(MyArray)
def compile(element, compiler, **kw):
return ARRAY[%s] % compiler.process(element.expr)
# step 2. illustrate raw usage of MyArray with the ORM.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class MyClass(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
arrvalue = Column(ARRAY(String))
print MyClass.arrvalue.op(@)(MyArray(123))
# step 3. use ColumnProperty to define a comparator at the
# ORM level.
from sqlalchemy.orm.properties import ColumnProperty
class MyComparator(ColumnProperty.Comparator):
def array_in(self, other):
return self.__clause_element__().op(@)(MyArray(other))
class MyOtherClass(Base):
__tablename__ = 'bar'
id = Column(Integer, primary_key=True)
arrvalue = ColumnProperty(Column(ARRAY(String)),
comparator_factory=MyComparator)
print MyOtherClass.arrvalue.array_in(123)
# step 4. 0.8 will have a core method for the comparison part which is
easier.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
