2

The database table objects_tbl has a column object_id, that contains identifier strings that either consist of digits 0-9 only or that may additionally contain "hex-digits", i.e. the letters a-f. Using SqlAlchemy, I want to select all distinct values from this column which consist of the digits 0-9 only, so I added a condition on the column to not contain a or b or c or d or e or f:

col = objects_tbl.c.object_id
query = select([col]).where(not_(or_(col.contains('a'), col.contains('b'), col.contains('c'),
                            col.contains('d'), col.contains('e'), col.contains('f')))).distinct()

Within the faculties of SqlAlchemy, is there a more elegant/more concise way to accomplish this without having to list each of the six characters explicitly? The solution should be as little database-specific as possible, so using raw SQL with regular expressions is probably not a good option. Many thanks in advance!

ctenar
  • 718
  • 5
  • 24

1 Answers1

1

You can use generators inside OR operator like this:

or_(col.contains(s) for s in ('a', 'b', 'c', 'd', 'e', 'f'))

or:

or_(col.in_(['a', 'b', 'c', 'd', 'e', 'f']))

I've not tried these ways, but this advice is in Using OR in SQLAlchemy

Ernest Denisov
  • 36
  • 3