sqlalchemy Session object has no attribute add

user_13374511 0
Viewed 2598

I am trying to insert the data to mysql server, While doing so when i try to add the data into the SQLalchemy sesion i am getting the error "Generator object has no attribute add"

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

Session generator:

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

session creation


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "mysql+mysqlconnector://root:password@localhost:3305/line_api"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)
sessionlocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


Base = declarative_base()

please help...

fastapi sqlalchemy python
2 Answers
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()
user_77997980

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.

user_94206700
Related