How to refactor columns that needs sa_solumn attribute across multiple SQLModel tables?

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"

[vinodkumars]

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

fromdatetimeimportdatetime, timezonefromtypingimportOptionalfromsqlmodelimportSQLModel, Field, create_engine, SessionfromsqlalchemyimportColumn, DateTimedefget_utc_now()->datetime:
returndatetime.now(timezone.utc)
classBaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))
classUser(BaseModel,table=True):
user_details:strclassProduct(BaseModel,table=True):
product_details:strengine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
defcreate_user():
withSession(engine) assession:
user=User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
defcreate_product():
withSession(engine) assession:
product=Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime
def get_utc_now()->datetime:
return datetime.now(timezone.utc)
class BaseModel(SQLModel):
id:int=Field(primary_key=True)
created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})
class User(BaseModel,table=True):
user_details:Optional[str]
class Product(BaseModel,table=True):
product_details:Optional[str]
engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)
def create_user():
with Session(engine) as session:
user = User(id=1)
session.add(user)
session.commit()
session.refresh(user)
print(user)
def update_user():
with Session(engine) as session:
user=session.get(User,1)
user.user_details="test_user_details"
session.add(user)
session.commit()
session.refresh(user)
print(user)
def create_product():
with Session(engine) as session:
product = Product(id=1)
session.add(product)
session.commit()
session.refresh(product)
print(product)
create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"