Re: [sqlalchemy] Use regexp in like
On 9/20/2010 10:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
Can anyone offer me some suggestions here? Is this a bug?
Thanks,
Michael
--
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] Use regexp in like
On Sep 21, 2010, at 8:12 AM, Michael Hipp wrote:
On 9/20/2010 10:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a
syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
Can anyone offer me some suggestions here? Is this a bug?
I'm not sure how the ? is being used for a bind param. the psycopg2 dialect
uses %(name)s format. SIMILAR TO works fine and you can see %(name)s format
is used:
from sqlalchemy import *
from sqlalchemy.sql import column
e = create_engine('postgresql://scott:ti...@localhost/test', echo=True)
e.execute(select([literal_column('lane').op('SIMILAR TO')('car')]))
2010-09-21 08:19:49,339 INFO sqlalchemy.engine.base.Engine.0x...1570 select
version()
2010-09-21 08:19:49,339 INFO sqlalchemy.engine.base.Engine.0x...1570 {}
2010-09-21 08:19:49,341 INFO sqlalchemy.engine.base.Engine.0x...1570 select
current_schema()
2010-09-21 08:19:49,341 INFO sqlalchemy.engine.base.Engine.0x...1570 {}
2010-09-21 08:19:49,343 INFO sqlalchemy.engine.base.Engine.0x...1570 SELECT
'lane' SIMILAR TO %('lane'_1)s AS anon_1
2010-09-21 08:19:49,343 INFO sqlalchemy.engine.base.Engine.0x...1570
{'lane'_1: 'car'}
I get the same result with psycopg2 2.0.12 and 2.2.2 .
--
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] Use regexp in like
On 9/21/2010 7:23 AM, Michael Bayer wrote:
On Sep 21, 2010, at 8:12 AM, Michael Hipp wrote:
On 9/20/2010 10:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
Can anyone offer me some suggestions here? Is this a bug?
I'm not sure how the ? is being used for a bind param. the psycopg2 dialect
uses %(name)s format. SIMILAR TO works fine and you can see %(name)s format
is used:
from sqlalchemy import *
from sqlalchemy.sql import column
e = create_engine('postgresql://scott:ti...@localhost/test', echo=True)
e.execute(select([literal_column('lane').op('SIMILAR TO')('car')]))
2010-09-21 08:19:49,339 INFO sqlalchemy.engine.base.Engine.0x...1570 select
version()
2010-09-21 08:19:49,339 INFO sqlalchemy.engine.base.Engine.0x...1570 {}
2010-09-21 08:19:49,341 INFO sqlalchemy.engine.base.Engine.0x...1570 select
current_schema()
2010-09-21 08:19:49,341 INFO sqlalchemy.engine.base.Engine.0x...1570 {}
2010-09-21 08:19:49,343 INFO sqlalchemy.engine.base.Engine.0x...1570 SELECT
'lane' SIMILAR TO %('lane'_1)s AS anon_1
2010-09-21 08:19:49,343 INFO sqlalchemy.engine.base.Engine.0x...1570
{'lane'_1: 'car'}
I get the same result with psycopg2 2.0.12 and 2.2.2 .
Sigh. I'm a moron.
I'm getting this problem when running unit tests. And it took me a day and a
half to remember that I'm doing that against an SQLite in-memory database, not
pg. Which means the SIMILAR TO op is never going to work, I suppose.
So now I have to figure out how to run unit tests against postgresql or else
live with code having no test coverage. Neither are very attractive.
I apologize for taking your time. Thanks for trying to help.
Michael
--
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] Use regexp in like
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Thanks,
Michael
--
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] Use regexp in like
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
Michael
--
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] Use regexp in like
On Sep 20, 2010, at 11:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a
syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
is this pg8000 ? the ? as binds are suspect. that would be my guess.
Michael
--
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.
--
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] Use regexp in like
On 9/20/2010 10:57 AM, Michael Bayer wrote:
On Sep 20, 2010, at 11:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
is this pg8000 ? the ? as binds are suspect. that would be my guess.
I've never used pg8000; this is psycopg2:
import psycopg2
psycopg2.__version__
'2.2.1 (dt dec ext pq3)'
Any help?
Thanks,
Michael
--
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] Use regexp in like
On 9/14/2010 2:23 PM, Michael Hipp wrote: Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Can anyone suggest an approach to search a field with a regexp? Thanks, Michael -- 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] Use regexp in like
On Sep 17, 2010, at 9:14 AM, Michael Hipp wrote: On 9/14/2010 2:23 PM, Michael Hipp wrote: Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Can anyone suggest an approach to search a field with a regexp? if you were using Postgresql, you could use somecolumn.op(~)(someregexp) http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP cant speak for other platforms though you'd have to consult their documentation. Thanks, Michael -- 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. -- 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] Use regexp in like
On Sep 17, 2010, at 11:12 AM, Michael Bayer wrote: On Sep 17, 2010, at 10:58 AM, Michael Bayer wrote: On Sep 17, 2010, at 9:14 AM, Michael Hipp wrote: On 9/14/2010 2:23 PM, Michael Hipp wrote: Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Can anyone suggest an approach to search a field with a regexp? if you were using Postgresql, you could use somecolumn.op(~)(someregexp) http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP cant speak for other platforms though you'd have to consult their documentation. PG also supports MATCH since I see we have some unit tests for that, i.e. column.match(other). I'm not seeing offhand in PG's docs what it interprets the MATCH operator as, i.e. is it a ~, SIMILAR TO, not sure. I guess the reasons I've never had interest in regexp matching in databases are: 1. its always bad to search through tables without being able to use indexes 2. if you're needing to dig into text, it suggests the atoms of that text should be represented individually in their own column (i.e. normalize) 3. no really, I'm doing flat out full text searching on documents and don't want to reinvent. Well then I'd use a full blown text extension (http://www.postgresql.org/docs/8.3/static/textsearch.html) or a separate search engine. The SQLAlchemy match operator compiles to PostgreSQL full-text search (@@ to_tsquery). test/dialect/test_postgresql.py Cheers, M -- 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.
[sqlalchemy] Use regexp in like
Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Thanks, Michael -- 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.
