CODEWITHSUNDEEP
pythonmysqlsqlselectsqlalchemy
melchoir55
melchoir55

Reputation: 7276

SQLAlchemy select with where constraint

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.

Upvotes: 0

Views: 570

Answers (1)

davidism
davidism

Reputation: 127180

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.

Upvotes: 4

Related Questions