SQLAlchemy select with where constraint

Question

I am trying to get a subset of a table from my database. The database is a MySql database.

Python code:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, VARCHAR, DATETIME, INT, TEXT, TIMESTAMP
from datetime import datetime
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class TrackablesTable(Base):
 __tablename__ = 'Trackables'
 trackableId = Column(INT, primary_key=True) #autogenerate
 productID = Column(TEXT)
 createdOn = Column(TIMESTAMP) #autogenerate
 urlTitle = Column(TEXT)
 humanTitle = Column(TEXT)
 userId = Column(VARCHAR(45))
 def __repr__(self):
 return "<MyTable(%s)>" % (self.asin)
 @staticmethod
 def getTrackableByProductId(productID, session):
 trackable = session.query(TrackablesTable).filter_by(productID=productID)
 return trackable

Note the method at the bottom. I was expecting this method to get me all the rows in the "Trackables" table with a "productID" column with the value of the productID variable. Instead, it seems to be returning a query which is malformed.

The query it returns is below:

SELECT "Trackables"."trackableId" AS "Trackables_trackableId", "Trackables"."productID" AS "Trackables_productID", "Trackables"."createdOn" AS "Trackables_createdOn", "Trackables"."urlTitle" AS "Trackables_urlTitle", "Trackables"."humanTitle" AS "Trackables_humanTitle", "Trackables"."userId" AS "Trackables_userId" 
FROM "Trackables" 
WHERE "Trackables"."productID" = :productID_1

MySQL workbench is telling me the query is malformed. Further, the value in the query of productID (":productID_1") is not the actual value of the variable referenced in the code.

• abarnert: The :productID_1 is not a value, it's a placeholder that will be bound to a parameter passed to the execute function. This is how you avoid Little Bobby Tables eating your database. If you turn up the debugging, you can see the value it passes to the execute and make sure it's the actual productID value.
• abarnert: Also, when you run your code, what actually happens? Does it raise an exception because it's a malformed query? Return the wrong results? Send threatening letters to Obama in your name?
• melchoir55: @abarnert I don't get an exception in python. I only say it is malformed because if I take the query it reports and dump it wholesale into mysql workbench, then the workbench reports it is malformed. In python I get a Query object returned from the end of the code block. However, that object doesn't contain any information from the database.
• abarnert: Well, you can't take a query with parameters and execute it without parameters (which is what the mysql command-line tool, workbench, etc. will all do with it).

Answer 1

You need to execute the query, not just return it. The query remains a query object until a method such as all(), first(), or scalar() is called on it, or it is iterated over.

Your method should look like this:

@staticmethod
def getTrackableByProductId(productID, session):
 q = session.query(TrackableTable).filter_by(productID=productID)
 return q.first()

When you print out the query, SQLAlchemy shows the query with format placeholders rather than actual values. The actual query is built by the dbapi (such as python-mysql) outside of SQLAlchemy's control.

---

Side note: Your code, both the use of staticmethod and the naming conventions, looks like you've tried to copy a Java class. Consider reading PEP8.