SQLModel: sqlalchemy.exc.ArgumentError: Column expression or FROM clause expected,

Question

I am using the SQLModel library to do a simple select() like described on their official website. However I am getting Column expression or FROM clause expected error message

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select

from models import Hero
    
sqrl = f"mysql+pymysql:///roo@asdf:localhost:3306/datab"

engine = create_engine(sqrl, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    select_heroes()


if __name__ == "__main__":
    main()

this is my models/Hero.py code:

from datetime import datetime, date, time
from typing import Optional
from sqlmodel import Field, SQLModel

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    created: datetime
    lastseen: time
     

when I run app.py I get the sqlalchemy.exc.ArgumentError: Column expression or FROM clause expected, got <module 'models.Hero' from '/Users/dev/test/models/Hero.py'>. message

Answer 1

The error message <Column expression or FROM clause expected, got module 'models.Hero' from '/Users/dev/test/models/Hero.py'> tells us:

• SQLAlchemy / SQLModel expected to receive a Column expression or FROM clause: in the ORM layer this often means a model class or collection of model's columns was expected
• SQLAlchemy / SQLModel unexpectedly received a module object named models.Hero
• that you have a module named Hero.py

The solution is to replace the object causing the error with something that the select or query function would expect to receive, such as an SQLAlchemy or SQLModel ORM model class.

The import statement from models import Hero only imports the module Hero. Either

• change the import to import the model^*

  from models.Hero import Hero
  

• change the code in select_heroes to reference the model^†

  statement = select(Hero.Hero)
  

---

* It's conventional to use all lowercase for module names; following this convention will help you distinguish between modules and models.

† This approach is preferable in my opinion: accessing the object via the module namespace eliminates the possibility of name collisions (of course it can be combined with lowercase module names).

Answer 2

I got this because I forgot to inherit Base class in my model.

my model was:

class WeatherData:
   __tablename__ = "weather_data"
   ...

Then I only change to

 Base = declarative_base()

 class WeatherData(Base):
       __tablename__ = "weather_data"

sqlalchemy explain this in: https://docs.sqlalchemy.org/en/20/orm/quickstart.html#declare-models

Answer 3

For me I got this error after importing the model class twice

from app.models.company import CompanyIndustryType
from uuid import UUID
...
from app.schemas.company import CompanyIndustryType

...

for item in data:
                query = select(CompanyIndustryType).where(
                    CompanyIndustryType.id == item.industry_id)
                results = await db.execute(query)
                item.industry = results.scalars().first()

...

so I got the error

Column expression or FROM clause expected, got <class 'app.schemas.company.CompanyIndustryType'>.

The solution was simply by removing the second import, and everything worked well

Answer 4

I got same error with below script

            from sqlalchemy.sql import select, text
            from dask.dataframe import read_sql_query

            con = "sqlite:///delete_me_test.sqlite"
            query = "SELECT * FROM ticks"
            query2 = " * FROM ticks"

            def _remove_leading_select_from_query(query):
                if query.startswith("SELECT "):
                    return query.replace("SELECT ", "", 1)
                else:
                    return query


            #sa_query = select(text(_remove_leading_select_from_query(query)))
            sa_query = select(text(_remove_leading_select_from_query(query)))
            print(sa_query)
            ddf = read_sql_query(sql=sa_query, con=con, index_col="index")

            print(ddf)
            print(ddf.head(3))    
      

and got it fixed by force downgrade sqlalchemy version

pip install --force-reinstall 'sqlalchemy<2.0.0'

ref