sqlalchemy Session object has no attribute add

Simple Solution

I ran into a similar issue in my own FastAPI project with SQLAlchemy. The issue stems from the gathering of the db session from a yield keyword. yield returns a generator rather than returning the sqlalchemy db session as you're expecting. You can read more about the yield expression here: https://docs.python.org/3/reference/expressions.html#yieldexpr

To fix the code you need to call the internal __next__ function in some form on the generator object returned from get_db. You can do this a variety of ways (some may be more stylistically accurate than my suggestion).

temp = schema.User(**filtered_dict)
insert_data=models.User(**temp.dict())
db_gen = get_db()
db = next(db_gen)
db.add(insert_data)
db.commit()
db.refresh()

EDIT with var reference to generator Thanks to @DustinMichels in the comments.

Adding the next function call should fix the issue.

Using Async Generators

Since we are not prepending the function get_db with async we get a normal generator. However, if we add async to this function definition, we can no longer use __next__ as a solution. Making the function asynchronous would also help making sure connections are closed correctly (see comments).

If a developer decides to add the async keyword then you have a couple options:

FastAPI's Depends system does work perfectly with asynchronous generators and can call the function for you, populating a nice parameter with your connection.

Alternatively, you can call asend(None) on an asynchronous generator to get the first value of the asynchronous generator. (There are also better ways to do this as well like using an async for loop or __anext__ function call). More to read here: https://www.python.org/dev/peps/pep-0525/#implementation-details

async def get_db():
    db_session = sessionlocal()
    try:
        yield db_session
    finally:
        db_session.close()

# ...

temp = schema.User(**filtered_dict)
insert_data=models.User(**temp.dict())
db = get_db().asend(None)
db.add(insert_data)
db.commit()
db.refresh()

What worked for me was simply calling the .begin() fx present in sessionmaker:

class MainDB:
    # create connection engine
    base_db_engine = create_engine(f'sqlite:///{get_project_root()}/db/base.db')

    # init tables
    Base = declarative_base()
    Base.metadata.create_all(base_db_engine)

class GenDBInterface:
    # create db session
    session: Session = sessionmaker(bind=MainDB.base_db_engine)

    def run(self):
        acc = Accounts(email='hello', password='hi', user_token='hello')
        with self.session.begin() as s:
            s.add(acc)

I'm not using this in a flask/FastAPI environment though. Might be different.

you should use the function get_db in "with" sentence(then the class type is correct):

@contextmanager
def get_db():
    db = SessionLocal()
    try:
        yield db
        db.commit()  
    except Exception as e:
        db.rollback() 
        raise e
    finally:
        db.close()

with get_db() as db:
    try:
        db.add(user)
        db.flush()
        db.refresh(user)
    except:
        pass