Extract column values by substring by in SQLAlchemy
sqlalchemy.select([ Tablename.c.column_name, sqlalchemy.func.sum(Tablename.c.column_name) ]).group_by(Tablename.c.column_name)
Get the books to table from the Metadata object initialized while connecting to the database and pass the SQL query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
This SQL query returns the names of the book containing the substring “fiction” in the genre column.
Python3
# Get the `books` table from the Metadata object BOOKS = meta.tables[ 'books' ] # SQLAlchemy Query to pick # records containing substring fiction query = sqlalchemy.select([ BOOKS.c.book_name, ]). filter (BOOKS.c.genre.contains( "fiction" )) # Fetch all the records result = engine.execute(query).fetchall() # View the records for record in result: print ( "\n" , record) |
Output:
Python SQLAlchemy – Write a query where a column contains a substring
In this article, we discussed how to extract the column values containing substring in SQLAlchemy against a PostgreSQL database in python.
In SQLAlchemy, generic functions like SUM, MIN, MAX, are invoked like conventional SQL functions using the func attribute.
Some common functions used in SQLAlchemy are contains, count, cube, current_date, current_time, max, min, mode etc.
Usage: func.sum(). func.group_by(), func.sum()