[Python] sqlalchemy.exc.ProgrammingError: Invalid object name 'users' while querying SQL...

I am running migration scripts via Alembic, and they are executing successfully. All the tables, including the users table, are created in the SQL Server database without issues. However, when I try to query the users table using SQLAlchemy and pyodbc, I get the following error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'users'. (208) [SQL: SELECT TOP 1 users.id FROM users WHERE users.email = ?]")
​

The users table exists in the csdd-persistance database. I'm using the following connection string:

"mssql+pyodbc://localhost\\MSSQLLocalDB/csdd-persistance?driver=ODBC+Driver+17+for+SQL+Server"


class User(BaseMSSQL):
__tablename__ = "users"
__table_args__ = {"schema": "dbo"}

id = Column(Integer, primary_key=True, index=True, autoincrement=True)
username = Column(String(255), unique=True, index=True, nullable=False)
email = Column(String(255), unique=True, index=True, nullable=False) # Fixed length to avoid indexing issue
password = Column(String(255), nullable=False)
is_active = Column(Boolean, default=True)
created_at = Column(DateTime, server_default=text("GETDATE()"), nullable=False)


Unable to run any api endpoint. For example :

@router.post("/register", response_model=dict)
def register_user(user: UserCreate, db: Session = Depends(lambda: next(get_db()))):
existing_user = db.query(User).filter(User.email == user.email).first()
if existing_user:
raise HTTPException(status_code=400, detail="Email already taken")
hashed_password = get_password_hash(user.password)
new_user = User(username=user.username, email=user.email, password=hashed_password)
db.add(new_user)
db.commit()
db.refresh(new_user)
return {"message": "User created successfully"}

Continue reading...