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

snakecharmerb · Accepted Answer · 2023-03-12T12:32:12.053

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).

thank you! is there a way for me to do something like `from models import Hero, Villains`, etc ? for me it seems more cleaner that way if I can just say from `models/` directory, import these classes. — joooet, Apr 16 '22 at 17:58
Yes: expose the names in `models.__init__`: for example `from hero import Hero` and then you can do `from models import Hero`. You definitely want to avoid your module and class names colliding in such a structure. — snakecharmerb, Apr 17 '22 at 06:59

score 1 · Answer 2 · answered Apr 24 '23 at 16:37

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

In conda: `conda install -c conda-forge 'sqlalchemy<2.0.0'` – Shovalt Aug 03 '23 at 06:47 — Shovalt, Aug 03 '23 at 06:47

score 0 · Answer 3 · answered Mar 15 '23 at 07:32

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

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

3 Answers3

Linked

Related