Sqlalchemy select AttributeError: 'dict' object has no attribute

I am build fastapi + sqlalchemy app

My code

sql.py

form sqlalchemy import Table, String, BigInteger, MetaData

metadata = MetaData()

custom_datasets = Table(
    "custom_datasets",
    metadata,
    Column("project_id", String(500), primary_key=True),
    Column("id", BigInteger, primary_key=True),
    Column("name", String(500), unique=True),
    Column("path", String(500), unique=True),
    Column("feature_service", String(500), nullable=False),
    Column("last_updated_timestamp", BigInteger, nullable=False),
)

main.py

import os
from sqlalchemy.orm import Session
from sqlalchemy import select

def get_db():
    if os.getenv("REGISTRY_CONNECTION_STRING"):
        db = SessionLocal()
        yield db
    yield None


@app.get("/datasets/")
def get_project_registry(body: Dict[str, Any], db: Session = Depends(get_db)):
    print("XXX_ ", custom_datasets.c) # XXX_  ImmutableColumnCollection(custom_datasets.project_id, custom_datasets.id, 
    stmt = select(custom_datasets).where(
        custom_datasets.c.project_id in body.project_ids
    )
    res = db.execute().all()
    return res

I have an error

python3.8/site-packages/feast/feature_server.py", line 469, in get_project_registry
    custom_datasets.c.project_id in body.project_ids
AttributeError: 'dict' object has no attribute 'project_ids'

How to fix the error?

Update:

Filtering is not working for the request

GET http://127.0.0.1:8009/datasets
{
    "project_ids": ["proj_id1"]
}

Table looks like this:

testdb=# SELECT * FROM  custom_datasets;
 project_id | id | name  |   path    | feature_service | last_updated_timestamp 
------------+----+-------+-----------+-----------------+------------------------
 proj_id1   |  1 | name1 | path/path | service_name    |                      1

Updated code

    @app.get("/datasets/")
    def get_project_registry(body: Dict[str, Any], db: Session = Depends(get_db)):
        print("XXX_ ", body['project_ids'])  # prints XXX_  ['proj_id1']
        stmt = select(custom_datasets).where(
            custom_datasets.c.project_id in body['project_ids']
        )
        #stmt = select(custom_datasets)
        res = db.execute(stmt).all()
        return res

Solution

In python, if you want to access a value in a dictionary you need to do it with square bracket notation:

@app.get("/datasets/")
def get_project_registry(body: Dict[str, Any], db: Session = Depends(get_db)):
    print("XXX_ ", custom_datasets.c) # XXX_  ImmutableColumnCollection(custom_datasets.project_id, custom_datasets.id, 
    stmt = select(custom_datasets).where(
        custom_datasets.c.project_id in body['project_ids']
    )
    res = db.execute().all()
    return res

When you use dot notation, python doesn't actually look at the keys in the dictionary, but rather the properties of the dictionary object.

The where ... in filtering doesn't work because you can't use in directly like that in a SQLAlchemy select statement. SQLAlchemy provides a .in_ method for this purpose:

@app.get("/datasets/")
def get_project_registry(body: Dict[str, Any], db: Session = Depends(get_db)):
    stmt = select(custom_datasets).where(
        custom_datasets.c.project_id.in_(body['project_ids'])
    )
    res = db.execute(stmt).all()
    return res