select unique values not containing characters a-f from table column with sqlalchemy

Question

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!

Ernest Denisov · Accepted Answer · 2019-12-26 00:11:24Z

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

answered Dec 26, 2019 at 0:11

Ernest Denisov

363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select unique values not containing characters a-f from table column with sqlalchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related