[ Python 常見問題 ] SQLAlchemy: how to filter date field?

Source From Here
Question
Here is model:

view plaincopy to clipboardprint?

1. class User(Base):  
2.     ...  
3.     birthday = Column(Date, index=True)   #in database it's like '1987-01-17'  
4.     ...  

I want to filter between two dates, for example to choose all users in interval 18-30 years. How to implement it with SQLAlchemy?

I think of:

view plaincopy to clipboardprint?

1. query = DBSession.query(User).filter(  
2.     and_(User.birthday >= '1988-01-17', User.birthday <= '1985-01-17')  
3. )   
4.   
5. # means age >= 24 and age <= 27  

I know this is not correct, but how to do correct?

How-To
In fact, your query is right except for the typo: your filter is excluding all records: you should change the <= for >= and vice versa:

view plaincopy to clipboardprint?

1. qry = DBSession.query(User).filter(  
2.         and_(User.birthday <= '1988-01-17', User.birthday >= '1985-01-17'))  
3. # or same:  
4. qry = DBSession.query(User).filter(User.birthday <= '1988-01-17').\  
5.         filter(User.birthday >= '1985-01-17')  

Also you can use between:

view plaincopy to clipboardprint?

1. qry = DBSession.query(User).filter(User.birthday.between('1985-01-17', '1988-01-17'))  

[ Python 常見問題 ] SQLAlchemy delete doesn't cascade

Source from here 
Question 
My User model has a relationship to the Address model. I've specified that the relationship should cascade the delete operation. However, when I query and delete a user, I get an error that the address row is still referenced. How do I delete the user and the addresses? 

view plaincopy to clipboardprint?

1. class User(db.Model):  
2.     id = db.Column(db.Integer, primary_key=True)  
3.     addresses = db.relationship('Address', cascade='all,delete', backref='user')  
4.   
5. class Address(db.Model):  
6.     id = db.Column(db.Integer, primary_key=True)  
7.     user_id = db.Column(db.Integer, db.ForeignKey(User.id))  

When I execute: 

view plaincopy to clipboardprint?

1. db.session.query(User).filter(User.my_id==1).delete()  

Got below error message: 

IntegrityError: (IntegrityError) update or delete on table "user" violates foreign key constraint "addresses_user_id_fkey" on table "address"
DETAIL: Key (my_id)=(1) is still referenced from table "address".
'DELETE FROM "user" WHERE "user".id = %(id_1)s' {'id_1': 1}

How-To 
You have the following: 

view plaincopy to clipboardprint?

1. db.session.query(User).filter(User.my_id==1).delete()  

Note that after "filter", you are still returned a Query object. Therefore, when you call delete(), you are calling delete() on the Query object (not the User object). This means you are doing a bulk delete (albeit probably with just a single row being deleted). The documentation for the Query.delete() method that you are using says: 

The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any foreign key references which require it, otherwise the database may emit an integrity violation if foreign key references are being enforced.

As it says, running delete in this manner will ignore the Python cascade rules that you've set up. You probably wanted to do something like: 

view plaincopy to clipboardprint?

1. user = db.session.query(User).filter(User.my_id==1).first()  
2. db.session.delete(user)  

Otherwise, you may wish to look at setting up the cascade for your database as well.

[ Python 常見問題 ] SQLAlchemy: What's the difference between flush() and commit()?

Source From Here 
Question 
What the difference is between flush() and commit() in SQLAlchemy? 

I'm particularly interested in their impact on memory usage. I'm loading some data into a database from a series of files (around 5 million rows in total) and my session is occasionally falling over - it's a large database and a machine with not much memory. 

I'm wondering if I'm using too many commit() and not enough flush() calls - but without really understanding what the difference is, it's hard to tell! 

How-To 
A Session object is basically an ongoing transaction of changes to a database (update, insert, delete). These operations aren't persisted to the database until they are committed (if your program aborts for some reason in mid-session transaction, any uncommitted changes within are lost). The session object registers transaction operations with session.add(), but doesn't yet communicate them to the database until session.flush() is called. 

session.flush() communicates a series of operations to the database (insert, update, delete). The database maintains them as pending operations in a transaction. The changes aren't persisted permanently to disk, or visible to other transactions until the database receives a COMMIT for the current transaction (which is what session.commit() does). 

commit() commits (persists) those changes to the database. flush() is always called as part of a call to commit() (1). 

When you use a Session object to query the database, the query will return results both from the database and from the flushed parts of the uncommitted transaction it holds. By default, Session objects autoflush their operations, but this can be disabled. Hopefully this example will make this clearer: 

view plaincopy to clipboardprint?

1. #---  
2. s = Session()  
3.   
4. s.add(Foo('A')) # The Foo('A') object has been added to the session.  
5.                 # It has not been committed to the database yet,  
6.                 #   but is returned as part of a query.  
7. print 1, s.query(Foo).all()  
8. s.commit()  
9.   
10. #---  
11. s2 = Session()  
12. s2.autoflush = False  
13.   
14. s2.add(Foo('B'))  
15. print 2, s2.query(Foo).all() # The Foo('B') object is *not* returned  
16.                              #   as part of this query because it hasn't  
17.                              #   been flushed yet.  
18. s2.flush()                   # Now, Foo('B') is in the same state as  
19.                              #   Foo('A') was above.  
20. print 3, s2.query(Foo).all()   
21. s2.rollback()                # Foo('B') has not been committed, and rolling  
22.                              #   back the session's transaction removes it  
23.                              #   from the session.  
24. print 4, s2.query(Foo).all()  
25.   
26. #---  
27. Output:  
28. 1 ['A')>]  
29. 2 ['A')>]  
30. 3 ['A')>, 'B')>]  
31. 4 ['A')>]  

Supplement 
* FAQ - SQLAlchemy insert or update example

[ Python 文章收集 ] SQLAlchemy ORM - Eager Loading & Deleting Related Objects

Source From Here 
Eager Loading 
Eager load reduces the number of queries. SQLAlchemy offers eager loading functions invoked via query options which give additional instructions to the Query. These options determine how to load various attributes via the Query.options() method. 

Subquery Load 
We want that Customers.invoices should load eagerly. The orm.subqueryload() option gives a second SELECT statement that fully loads the collections associated with the results just loaded. The name “subquery” causes the SELECT statement to be constructed directly via the Query re-used and embedded as a subquery into a SELECT against the related table. 

view plaincopy to clipboardprint?

1. c1 = session.query(Customers).options(subqueryload(Customers.invoices)).filter_by(name='Gopal Krishna').one()  
2. print('Name of c1 is {}'.format(c1.name))  
3. print('Invoices of c1:')  
4. for iv in c1.invoices:  
5.     print('\t{} (amount={:,d})'.format(iv.invno, iv.amount))  

Output: 

Invoices of c1:
10 (amount=15,000)
14 (amount=3,850)

This emits following expression giving same output as above: 

view plaincopy to clipboardprint?

1. 2019-06-27 09:58:27,452 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email  
2. FROM customers  
3. WHERE customers.name = %(name_1)s  
4. 2019-06-27 09:58:27,452 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}  
5. 2019-06-27 09:58:27,454 INFO sqlalchemy.engine.base.Engine SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount, anon_1.customers_id AS anon_1_customers_id  
6. FROM (SELECT customers.id AS customers_id  
7. FROM customers  
8. WHERE customers.name = %(name_1)s) AS anon_1 JOIN invoices ON anon_1.customers_id = invoices.custid ORDER BY anon_1.customers_id, invoices.id  
9. 2019-06-27 09:58:27,454 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}  

Joined Load 
The other function is called orm.joinedload(). This emits a LEFT OUTER JOIN. Lead object as well as the related object or collection is loaded in one step: 

view plaincopy to clipboardprint?

1. # Joined Load  
2. c1 = session.query(Customers).options(joinedload(Customers.invoices)).filter_by(name='Gopal Krishna').one()  

This emits following expression giving same output as above: 

view plaincopy to clipboardprint?

1. 2019-06-27 10:00:21,443 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email, invoices_1.id AS invoices_1_id, invoices_1.custid AS invoices_1_custid, invoices_1.invno AS invoices_1_invno, invoices_1.amount AS invoices_1_amount  
2. FROM customers LEFT OUTER JOIN invoices AS invoices_1 ON customers.id = invoices_1.custid  
3. WHERE customers.name = %(name_1)s ORDER BY invoices_1.id  
4. 2019-06-27 10:00:21,443 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}  

The OUTER JOIN resulted in two rows, but it gives one instance of Customer back. This is because Query applies a “uniquing” strategy, based on object identity, to the returned entities. Joined eager loading can be applied without affecting the query results. 

The subqueryload() is more appropriate for loading related collections while joinedload() is better suited for many-to-one relationship. 

Deleting Related Objects 
It is easy to perform delete operation on a single table. All you have to do is to delete an object of the mapped class from a session and commit the action. However, delete operation on multiple related tables is little tricky. In our testdb database, Customers and Invoice classes are mapped to customer and invoice table with one to many type of relationship. We will try to delete Customers object and see the result. 

As a quick reference, below are the definitions of Customers and Invoice classes: 
- build_rel_ex1.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import Column, Integer, String, ForeignKey  
2. from sqlalchemy import create_engine  
3. from sqlalchemy.ext.declarative import declarative_base  
4. from sqlalchemy.orm.session import sessionmaker  
5. from sqlalchemy.orm import relationship  
6.   
7.   
8. db_string = "postgresql://postgres:john7810@localhost/testdb"  
9. engine = create_engine(db_string, echo=True)  
10. Base = declarative_base()  
11.   
12. class Customers(Base):  
13.     __tablename__ = 'customers'  
14.     id = Column(Integer, primary_key=True)  
15.     name = Column(String)  
16.     address = Column(String)  
17.     email = Column(String)  
18.   
19. class Invoice(Base):  
20.    __tablename__ = 'invoices'  
21.   
22.    id = Column(Integer, primary_key = True)  
23.    custid = Column(Integer, ForeignKey('customers.id'))  
24.    invno = Column(Integer)  
25.    amount = Column(Integer)  
26.    customer = relationship("Customers", back_populates = "invoices")  
27.   
28. Customers.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")  

We setup a session and obtain a Customers object by querying it with primary ID using the below program: 

>>> from build_rel_ex1 import *
>>> x = session.query(Customers).get(2)
...
2019-07-21 16:59:31,348 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email
FROM customers
WHERE customers.id = %(param_1)s
2019-07-21 16:59:31,348 INFO sqlalchemy.engine.base.Engine {'param_1': 2}

>>> x.name
'Komal Pande'

In our sample table, x.name happens to be 'Gopal Krishna'. Let us delete this x from the session and count the occurrence of this name: 

>>> session.delete(x)
>>> session.query(Customers).filter_by(name = 'Komal Pande').count()
...
2019-07-21 17:02:29,587 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email
FROM customers
WHERE customers.name = %(name_1)s) AS anon_1
2019-07-21 17:02:29,588 INFO sqlalchemy.engine.base.Engine {'name_1': 'Komal Pande'}
0

However, the related Invoice objects of x are still there. It can be verified by the following: 

>>> session.query(Invoice).filter(Invoice.invno.in_([10, 14])).count()
2019-07-21 17:06:08,553 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount
FROM invoices
WHERE invoices.invno IN (%(invno_1)s, %(invno_2)s)) AS anon_1
2019-07-21 17:06:08,553 INFO sqlalchemy.engine.base.Engine {'invno_1': 10, 'invno_2': 14}
2

Here, 10 and 14 are invoice numbers belonging to customer Gopal Krishna. Result of the above query is 2, which means the related objects have not been deleted. This is because SQLAlchemy doesn’t assume the deletion of cascade; we have to give a command to delete it. 

To change the behavior, we configure cascade options on the Customers.invoices relationship. Let us close the ongoing session, use new declarative_base() and redeclare the User class, adding in the addresses relationship including the cascade configuration. 

The cascade attribute in relationship function is a comma-separated list of cascade rules which determines how Session operations should be “cascaded” from parent to child. By default, it is False, which means that it is "save-update, merge". The available cascades are as follows 

* save-update
* merge
* expunge
* delete
* delete-orphan
* refresh-expire

Often used option is "all, delete-orphan" to indicate that related objects should follow along with the parent object in all cases, and be deleted when de-associated. 

Hence redeclared Customer class is shown below: 

view plaincopy to clipboardprint?

1. Customers.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer", cascade = 'all, delete, delete-orphan')  

Let us delete the Customers with Gopal Krishna name using the below program and see the count of its related Invoice objects. First of all, let's check DB status: 

view plaincopy to clipboardprint?

1. testdb=# SELECT * FROM customers WHERE name='Gopal Krishna';  
2. id |     name      |        address        |    email  
3. ----+---------------+-----------------------+--------------  
4.   5 | Gopal Krishna | Bank Street Hydarebad | gk@gmail.com  
5. (1 row)  
6.   
7. testdb=# SELECT * FROM Invoices WHERE custid = 5;  
8. id | custid | invno | amount  
9. ----+--------+-------+--------  
10.   1 |      5 |    10 |  15000  
11.   2 |      5 |    14 |   3850  
12. (2 rows)  

Now below is the sample code to delete customer: 
- delete_gk.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python  
2. from  build_rel_ex1 import *  
3.   
4. x = session.query(Customers).get(5)  
5. print('Delete customer={} (id={})'.format(x.name, x.id))  
6. cust_name = x.name  
7. cust_id = x.id  
8. session.delete(x)  
9. num_cust = session.query(Customers).filter_by(name = cust_name).count()  
10. print('Now we have {:,d} customer with name as {}...'.format(num_cust, cust_name))  
11. num_invo = session.query(Invoice).filter(Invoice.custid == x.id).count()  
12. print('Now we have {:,d} invoice associated with customer id={}!'.format(num_invo, cust_id))  

Execution output: 

...
2019-07-21 17:27:09,972 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.emai l AS customers_email
FROM customers
WHERE customers.id = %(param_1)s
2019-07-21 17:27:09,972 INFO sqlalchemy.engine.base.Engine {'param_1': 5}
Delete customer=Gopal Krishna (id=5)
...
2019-07-21 17:27:09,978 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT customers.id AS customers_id, customers.name AS customers_name, customers.addre ss AS customers_address, customers.email AS customers_email
FROM customers
WHERE customers.name = %(name_1)s) AS anon_1
2019-07-21 17:27:09,978 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}
Now we have 0 customer with name as Gopal Krishna...
...
2019-07-21 17:28:35,931 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount
FROM invoices
WHERE invoices.custid = %(custid_1)s) AS anon_1
2019-07-21 17:28:35,932 INFO sqlalchemy.engine.base.Engine {'custid_1': 5}
Now we have 0 invoice associated with customer id=5!

Supplement 
* Prev - SQLAlchemy ORM - Working with Related Objects, Joins and common operators 
* Next - SQLAlchemy ORM - Many to Many Relationships 
* SQLAlchemy Doc - Query API 
* SQLAlchemy Doc - Relationship Loading Techniques