Select NULL Values in SQLAlchemy

by

For SQLAlchemy 0.7.9 and newer
Please use the answer from @jsnow. !!!

For SQLAlchemy 0.7.8 and older

(as indicated by @augurar): Because sqlalchemy uses magic methods (operator overloading) to create SQL constructs, it can only handle operator such as != or ==, but is not able to work with is (which is a very valid Python construct).

Therefore, to make it work with sqlalchemy, you should use:

...filter(or_(people.marriage_status!='married', people.marriage_status == None))

, basically replace the is None with == None. In this case your query will be translated properly to the following SQL:

SELECT people.name AS people_name, people.marriage_status AS people_marriage_status 
FROM people 
WHERE people.marriage_status IS NULL OR people.marriage_status != ?

See IS NULL in the documentation.

More Related Contents:

  1. SQLAlchemy: how to filter date field?
  2. Getting random row through SQLAlchemy
  3. How to execute raw SQL in Flask-SQLAlchemy app
  4. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  5. sqlalchemy: how to join several tables by one query?
  6. SQLAlchemy: cascade delete
  7. Python, SQLAlchemy pass parameters in connection.execute
  8. Bulk insert with SQLAlchemy ORM
  9. How can I verify Column data types in the SQLAlchemy ORM?
  10. Complex foreign key constraint in SQLAlchemy
  11. How to see the raw SQL queries Django is running?
  12. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  13. How to execute “left outer join” in SqlAlchemy
  14. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  15. SQLAlchemy Obtain Primary Key With Autoincrement Before Commit
  16. VALUES clause in SQLAlchemy
  17. using sqlalchemy to load csv file into a database
  18. flask sqlalchemy query with keyword as variable
  19. Does SQLAlchemy have an equivalent of Django’s get_or_create?
  20. Pandas DENSE RANK
  21. SQLAlchemy ORDER BY DESCENDING?
  22. SqlAlchemy – Filtering by Relationship Attribute
  23. How do you join two tables on a foreign key field using django ORM?
  24. How to make/use a custom database function in Django
  25. sqlalchemy: create relations but without foreign key constraint in db?
  26. sqlite3.OperationalError: unable to open database file
  27. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  28. SQL join or R’s merge() function in NumPy?
  29. Python and SQLite: insert into table
  30. SQLAlchemy classes across files

Leave a Comment