Ah, I missed a closing ) in my code sample there. There should be a ) before the ], to close the if statement.
-----Original Message----- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Jackson, Cameron Sent: Wednesday, 8 February 2012 10:01 AM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] String matching I'm not sure how to do exactly what you're asking, but I have an alternative suggestion: You could just query everything and do the filter at the application level (i.e. in Python), rather than in the query. Because Python is awesome, this is now easy to write: custom_string = 'foobar' parcours = self.session.query(Parcours).all() parcours = [parcour for parcour in parcours if ( custom_string.lower() in parcour.parcours_name.lower() or parcour.parcours_name.lower() in custom_string.lower()] I've done this in a few places in my current application where I needed string.startswith() logic. Of course, if you're going to have a lot of data in the table, this might not be a good idea. Querying the entire table will increase your bandwidth (although SQLAlchemy's lazy loading will help here), and filtering the list in Python will probably be slower than letting the database use it. Although, if there are a lot of clients connecting to the database, moving the filter from the DB to the clients might help to reduce the load on the database server. It's hard to say without knowing your situation. If in doubt, try both and profile. Cheers, Cam -----Original Message----- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of yannack Sent: Wednesday, 8 February 2012 4:35 AM To: sqlalchemy Subject: [sqlalchemy] String matching Hello list, I have the following problem. I wish to query my database to look for columns with names included in, or including, a given string. is there a way to do the opposite of "contains", ie, a "contained" method for string columns? Right now, I am doing the following: session.query(Parcours).filter( or_( Parcours.parcours_name.ilike('%'+custom_string+'%'), literal_column("'"+custom_string +"'").ilike('%'+Parcours.parcours_name+'%')) this works "ok" for most stuff, but breaks when custom_strings has quotes and such. Therefore, I need to improve on this: - either by escaping the custom_string ? - or by taking another approach than the "ilike" route (I use ilike instead of contains simply because I don't know how to do case- insensitive "contains" in PGSQL and my code will have to run both on sqlite and PGSQL) also, is there a case-insensitive version of the "contains" method (which would generate ilike statements instead of like in postgresql while properly escaping quotes and other stuff) ? Finally, I am also open to any thoughts and pointers people may have concerning partial/approaching string matching. At the moment, I just do this contains/contained, case-insensitive search, and finish with some manual matching, but if anyone has opinions on this, I am of course very interested! Thanks for your time, Best regards, Yannick -- 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. ------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. ------------------------------------------------------------------------- -- 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. ------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. ------------------------------------------------------------------------- -- 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.
