Donnerstag, 3. Juli 2014

Flask-SQLalchemy

So I am currently trying to set up a small web app using Flask. In order to connect to the MySQL database with some of the data that I want to administer  Flask-SQLAlchemy seemed to be a natural choice as I already had the SQLAlchemy ORM set up for some scripts and only needed to change a few imports.

If I understood correctly, the flask extension provides a few wrappers for querying the database that are supposed to be easier than using the session.

Now here's what I observed:
Depending on what filter-function you are using it is or is not possible to combine multiple filters using logical operators such as or_, and_ etc.

If I only wanted to query a single column in one of my tables called gene1 in may GeneToGene object, the following code does that beautifully:

results = GToG.query.filter_by(gene1 = genesymbol).all()                         

However, when trying to add a second column gene2 to the same filter (with a logical OR) I only get an error message

from flask.ext.sqlalchemy import SQLAlchemy                                      
from sqlalchemy import or_                                                       
results = GToG.query.filter_by(or_(gene1 = genesymbol, gene2 = genesymbol)).all()

TypeError: or_() got an unexpected keyword argument 'gene1'                      

Next try:



results = GToG.query.filter_by(or_(GToG.gene1 == genesymbol,                     
                                    GToG.gene2 == genesymbol)).all()             

resulted in the following error:


TypeError: filter_by() takes exactly 1 argument (2 given)                        

Turns out that only the function filter(), but not filter_by supports logical operators.
This is what finally worked and produced the output I was hoping for.

results = GToG.query.filter(or_(GToG.gene1 == genesymbol,                        
                                GToG.gene2 == genesymbol)).all()