Getting "Column object 'editable_by' already assigned to Table 'mytable'" when I update lasted version of sqlmodel (0.0.14)

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]

[balocon]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

fromtypingimportOptional, ListfromsqlmodelimportField, SQLModel, Column, ARRAY, Stringfromdatetimeimportdatetime# This is a very basic model with more field and functions valid for all tables in this projectclassHeroBase(SQLModel):
id: Optional[int] =Field(default=None, primary_key=True)
name: str=Field(index=True)
secret_name: Optional[List[str]] =Field(sa_column=Column(ARRAY(String))) # <----age: Optional[int] =Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)classModel_A(HeroBase):
favorite_color: Optional[str] =Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)classModel_B(HeroBase):
favorite_car: Optional[int] =Field(default=None)
# This is an especific table of this especific projectclassFirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime=Field(default=datetime.utcnow())
# This is another especific table of this especific projectclassSecodGeneralHero(Model_B, table=True):
aField: str=Field(default='hello')

Description

Before I update to SQLModel 0.014, this kind of code run good. But now, when second table gonna be read, this error jump:

"sqlalchemy.exc.ArgumentError: Column object 'secret_name' already assigned to Table 'firstgeneralhero'"

I think the problem is over the field "secret_name", because it is an Array type and use sa_column. If you transform it into a string field, error disappear.
Error also disappear if you delete "table=True" en the second table.
I have been searching for help but ...

It is my fault or it could be a bug or similar?

Thanks!!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.014

Python Version

3.12.0

Additional Context

To produce de error, just create virtual environment and "pip3 install SQLModel".ç

pip3 list:
Package Version

---

annotated-types 0.6.0
pip 23.2.1
pydantic 2.5.2
pydantic_core 2.14.5
SQLAlchemy 2.0.23
sqlmodel 0.0.14
typing_extensions 4.9.0

[balocon]

Code run using "sa_type" instead "sa_column".

from typing import Optional, List, Type
from sqlmodel import Field, SQLModel, Column, ARRAY, String
from datetime import datetime
# This is a very basic model with more field and functions valid for all tables in this project
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: Optional[List[str]] = Field(sa_type=ARRAY(String)) # <----
age: Optional[int] = Field(default=None, index=True)
# This is a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_A(HeroBase):
favorite_color: Optional[str] = Field(max_length=12, default=None)
# This is also a first aproximation of one type of table in the project (still could be a model for more projects)
class Model_B(HeroBase):
favorite_car: Optional[int] = Field(default=None)
# This is an especific table of this especific project
class FirstGeneralHero(Model_A, table=True):
dateOfBorn: datetime = Field(default=datetime.utcnow())
# This is another especific table of this especific project
class SecodGeneralHero(Model_B, table=True):
aField: str = Field(default='hello')

[orenl]

Thanks, this works, and reads cleaner.
Note that one caveat is that mypy does not like it:

sample.py:13: error: No overload variant of "Field" matches argument type "ARRAY" [call-overload]

And the solution suggested in #955 is to, well, use sa_column instead of sa_type ... :(
For now I had to disable the mypy error when using sa_type.

[sihrc]

I'm hitting this error myself. In my opinion, the issue seems to be that the sqlalchemy Column instance is being reused instead of copied to a new table when a Column instance is specified through sa_column. There doesn't seem to be a "column factory" alternative.

[shaunpatterson]

Same issue. In my case I can't switch to just using sa_type

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("CURRENT_TIMESTAMP")))
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

Same issue with the same use case here. Did you find a way around this?

[0snug0]

I used this instead, and works

from datetime import datetime
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class Company(ModelBase, table=True):
name: str
class Account(ModelBase, table=True):
name: str

However I do have a similar issue as you do and need to keep sa_column

from sqlalchemy.dialects.postgresql import UUID as UUIDSA
from uuid import UUID
class ModelBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
updated_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
class UUIDModelBase(ModelBase):
uid: Optional[UUID] = Field(sa_column=Column(UUIDSA(as_uuid=True), server_default=text("gen_random_uuid()")), default=None)
class User(ModelBase, table=True):
__tablename__ = "users"
name: str
class Team(ModelBase, table=True):
__tablename__ = "teams"
name: str

And I get this error

sqlalchemy.exc.ArgumentError: Column object 'uid' already assigned to Table 'users'

[0snug0]

Figured it out

class UUIDModelBase(ModelBase):
"""
Base class for UUID-based models.
"""
uid: Optional[UUID] = Field(sa_type=UUIDSA(as_uuid=True), sa_column_kwargs={"server_default": text("gen_random_uuid()"),}, default=None)

So I guess @shaunpatterson
if you must change your code it should look like this

Didn't test it out, but here you go

class Base(SQLModel):
id: int = Field(primary_key=True)
created_at: datetime.datetime = Field(sa_type=TIMESTAMP(timezone=True),
sa_column_kwargs={
"server_default": text("CURRENT_TIMESTAMP"),
},
nullable=False)
class Company(Base, table=True):
name: str
class Account(Base, table=True):
name: str

[CodyManshack]

That does indeed work! Thank you :)

[sebastian-correa]

In newer versions of sqlmodel doing this gives a typing warning in sa_type, as it takes type[Any] (i.e., types, not instances) and TIMESTAMP(timezeon=True) is an instance. However, the code does work.

Maybe changing the type-hint for sa_type to be type[Any] | Any | UndefinedType? @tiangolo I can open a PR if you're able to offer some guide if this is the best approach :).

[struegamer]

Do you use alembic for migration scripts?
If so, double check the opposite: set something like

Field(sa_time=TIMESTAMP(timezone=False),...)

do alembic revision --autogenerate and it doesn't give you sa.Column('created_at', sa.TIMESTAMP(timezone=False), nullable=False),

TIMEZONE is defined in sqlalchemy with timezone=True by default, that's why timezone awareness is enabled by default, but this is not the way how it should work in a database, it should be UTC and that's without a timezone (or at least Z for Zulu time aka UTC and that's timezone=False)

(generated code: sa.Column('created_at', sa.TIMESTAMP(), nullable=False), so how can we fix this?

This PR looks promising: #1384
but it won't help when sa_type is set, because of

def get_sqlalchemy_type(field: Any) -> Any: if IS_PYDANTIC_V2: field_info = field else: field_info = field.field_info sa_type = getattr(field_info, "sa_type", Undefined) # noqa: B009 if sa_type is not Undefined: return sa_type [...]