Sqlalchemy sintax for a query with "not like" operator

[pyArchInit ArcheoImagineers]

Hi to all,

I need to realize a query like this

select * from my_table where field not like "%value1%"  and field not like "%value2%" 

with a sqlalchemy sintax. I looked for around the documentation but it seems be impossible.

Am I wrong? It's possible that the only way could be to use the engine.execute method?

Thanks for any suggestion

Best regards

Luca

[Jonathan Vanasco]

i think this should work:

     query.filter( ~ table.column.contains('%value2%') )

[Ofir Herzas]

Each column has a  'like' method, so you should be able to filter by it: filter(~Table.field.like("%value1%"))

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[pyArchInit ArcheoImagineers]

Thanks a lot...sorry for my prevous post that I delete beacause I didn't undestand I must to use this  ~

def test_not_like(self):

Session = sessionmaker(bind=self.engine, autoflush=True, autocommit=True)

session = Session()

res = session.query(MAPPER).filter(and_(~MAPPER.field.contains('Value1'), ~US.field.contains('Value2'), ~US.field.contains('Value3'),~US.field.contains('Value4')))

return res

This appear to run properly now I test with my little dataset and I'll try to understand is what I must to find.

Thank you very much and sorry for the unnecessary my old replies.

Bye

Luca

[Ofir Herzas]

Hi Luca,
Keep in mind that contains will not map to 'like'. Hence if you want your query to have a 'like' clause, you'll have to use the 'like' method (the end result of 'contains' will be the same though as 'like' beginning and ending with a percent sign).

--

[pyArchInit ArcheoImagineers]

Hi to all.

I try to use the code in this way (query LIKE with OR operator)

res = session.query(MAPPER).filter(or_(MAPPER.field.contains('Value1'),MAPPER.field.contains('Value2'), MAPPER.field.contains('Value3'),MAPPER.field.contains('Value4')))

With a small dataset, I found the records I want, but with a larger dataset sqlalchemy return me an error:

more than 255 arguments.

To bypass this problem in the past I used the operator in_ in this way:

id_list = [1, 2, 3]

session.query(CLASS).filter(CLASS.field.in_(id_list)).all()

I've guessed that was possible to make something like this:

value_to_find = [CLASS.field.contains('value1'), CLASS.field.contains('value2')]

session.query(CLASS).filter(CLASS.field.in_(value_to_find)).all()

This script runs without error, but didn't return me any result.

Because is a OR type query, it's a good idea make single research for each value and storing the id of every record in a list?

Smothing like this:

res_list = []

for value in value_list:

  res = session.query(MAPPER).filter(MAPPER.field.contains(value))

  for r in res:

        res_list.append(res.id_record)

Or there is a more pythonic way?

Thanks a lot

Luca

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/_dpnK-WZzKQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

--
https://sites.google.com/site/pyarchinit/

pyarchinit - progetto per la gestione integrata dei dati di scavo su piattaforme GIS Free Open Suorce

[Michael Bayer]

On Sep 29, 2013, at 10:56 AM, pyArchInit ArcheoImagineers <pyarc...@gmail.com> wrote:

Hi to all.

I try to use the code in this way (query LIKE with OR operator)

res = session.query(MAPPER).filter(or_(MAPPER.field.contains('Value1'),MAPPER.field.contains('Value2'), MAPPER.field.contains('Value3'),MAPPER.field.contains('Value4')))

With a small dataset, I found the records I want, but with a larger dataset sqlalchemy return me an error:

more than 255 arguments.

To bypass this problem in the past I used the operator in_ in this way:

id_list = [1, 2, 3]

session.query(CLASS).filter(CLASS.field.in_(id_list)).all()

I've guessed that was possible to make something like this:

value_to_find = [CLASS.field.contains('value1'), CLASS.field.contains('value2')]

session.query(CLASS).filter(CLASS.field.in_(value_to_find)).all()

This script runs without error, but didn't return me any result.

Because is a OR type query, it's a good idea make single research for each value and storing the id of every record in a list?

Smothing like this:

res_list = []

for value in value_list:

  res = session.query(MAPPER).filter(MAPPER.field.contains(value))

  for r in res:

        res_list.append(res.id_record)

I'd batch them (also if you only need "id_record", query for that specifically):

res_list = []

while value_list:

    chunk = value_list[0:100]

    value_list = value_list[100:]

    res_list.extend(id_rec for id_rec, in session.query(M.id_record).filter(or_(*[M.field.contains(v) for v in chunk])))

[pyArchInit ArcheoImagineers]

Wow Michael, 

this syntax is very powerfull! 

Two questions before I can make mistakes:

1 - the number 100 is recovered by something like len(value_list)-1, where value_list contains alls the values I want to search?

2 - id_rec for id_rec: I must to pass this in the same way you wrote?

Thanks a lot and best regards

Luca