Menu
I'm constructing a query using SQLAlchemy and SQLite3 in which I'd like to select rows in which a String column contains a particular substring. What is the best way to accomplish this?
812
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
Define an extension to the sqlalchemy. ext. declarative system which automatically generates mapped classes and relationships from a database schema, typically though not necessarily one which is reflected.
all() method. The Query object, when asked to return full entities, will deduplicate entries based on primary key, meaning if the same primary key value would appear in the results more than once, only one object of that primary key would be present.
all() will return all records which match our query as a list of objects.
You can filter using contains operator:
Model.query.filter(Model.columnName.contains('sub_string')) To negate in, use not_ operator with it:
Model.query.filter(not_(Model.columnName.contains('sub_string'))) Filter by db.table.column.like('%needle%'). There is also ilike for a case insensitive search.
For a fancier interface you can allow for the known "dir" wildcards.
if '*' in needle or '_' in needle: looking_for = needle.replace('_', '__')\ .replace('*', '%')\ .replace('?', '_') else: looking_for = '%{0}%'.format(needle) result = db.table.filter(db.table.column.ilike(looking_for)) Notes:
db.table.filter and db.table.column is for SQLSoup (SQLSoup is useful if the database was made by another application)select(column_list).where(table.c.column.ilike(expr)). This interface is the way to go when you want all the power from raw SQL without having to compose statements by hand using string interpolation (use it along SQLSoup for introspection, so you don't need to declare tables)Model.query.filter(Model.field.ilike(expr))
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
