Thx Kyle and Conor,
i finally dit that
s = session.query(Contact.IdContact, Contact.Civilite,
Contact.Nom, Contact.Prenom,
ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier)
# s = s.filter(ContactTel.Tel.like(NumeroApparu))
s =
s.filter(ContactTel.Tel.op("regexp")("(.)*".join(list(NumeroApparu))) )
i construct a regular expression from the telephone number and do the regexp
search sql
2009/10/14 Kyle Schaffrick <k...@raidi.us>
>
> On Mon, 12 Oct 2009 13:47:19 -0500
> Conor <conor.edward.da...@gmail.com> wrote:
>
> >
> > Christian Démolis wrote:
> > > Hi,
> > >
> > > The idea of creating another column is good but it will multiplicate
> > > the size of my table by 2 for nothing. Is it possible to use MYSQL
> > > regular expression search with sql alcmehy? If yes, what is the
> > > command?
> > >
> > MySQL supports RLIKE/REGEXP operators; you can see how to use them in
> > SQLAlchemy in this thread:
> >
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/7c6abe2ab9d5061
> >
> > Be aware that regexp matching cannot utilize indexes, so you may have
> > performance problems.
> >
> > If you want to get fancy, you can try creating an index on the
> > expression REPLACE(REPLACE(<tel_column>, ' ', ''), '.', '')
> > which should create an index on the normalized phone numbers. In
> > theory, as long as your queries use the exact same function sequence
> > as the index, the index will be scanned instead of the full table. I
> > don't know what limitations MySQL has in this regard. Even if it does
> > work, the only real advantage it provides over using another column
> > is the normalized form is "hidden" in the index instead of the table.
> >
> > Hope it helps,
> > -Conor
> >
>
> Indeed this problem is easily solved with expression indexes (a.k.a.
> "functional" or "calculated" indexes). Unfortunately MySQL does not
> appear to support them. Their suggested workaround is to add a column to
> store the precomputed expression, with a trigger to keep it up to date,
> and index that column. This also means the optimization is not
> transparent: you have to explicitly use the precomputed column in your
> query.
>
> It seems expression indexes have been MySQL's todo list since at least
> 2007 :(
>
> You can of course still use RLIKE/REGEXP instead of adding this
> redundant column, but you'll get a full table scan every time. A
> classic space/time performance tradeoff :)
>
> -Kyle
>
> > > 2009/10/10 Andre Stechert <stech...@gmail.com
> > > <mailto:stech...@gmail.com>>
> > >
> > >
> > > This is not really a sqlalchemy question, but the quick answer
> > > is that you need to convert both your indexed data and your queries
> > > to the same normal form. In your example, you appear to be
> > > correctly stripping spaces and periods in your query. If you
> > > haven't done that in the database, then you should do it there,
> > > too. If you need to preserve the original formatting of the
> > > telephone number column, then create another column that contains
> > > the stripped phone numbers. You probably also want to put an index
> > > on that column.
> > >
> > > Lastly, a minor note on the sample code: you appear to be
> > > missing a "%" operator in your LIKE query.
> > >
> > > Cheers,
> > > Andre
> > >
> > > On Fri, Oct 9, 2009 at 5:46 AM, Christian Démolis
> > > <christiandemo...@gmail.com <mailto:christiandemo...@gmail.com>>
> > > wrote:
> > > > Hi everybody,
> > > >
> > > > I m stuck with a query about telephone number : I want to find in
> > > > my database all the contact who have a telephone number. The
> > > > difficulty is that some number in the database can have space or .
> > > > between numbers
> > > > example : 06.06.50.44.11 or 45 87 12 45 65
> > > >
> > > > This my query with like but it s not what i want because i ignore
> > > > telephone number who have special chars
> > > >
> > > > NumeroApparu = "0645444444"
> > > > s = session.query(Contact.IdContact, Contact.Civilite,
> > > > Contact.Nom, Contact.Prenom,
> > > >
> > > >
> ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu))
> > > >
> > > > i saw class sqlalchemy.sql.expression.ColumnOperators¶ in the doc
> > > > but lack of explication...
> > > >
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
