Insert twice with and without PK raises Duplicate primary key violates unique constraint #1267
-
First Check
Commit to Help
Example Codefrom typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = None
hero_1 = Hero(id=1, name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Deadpond", secret_name="Dive Wilson")
engine = create_engine("sqlite:///database.db")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2) # raise sqlalchemy.exc.IntegrityError: [...] DETAIL: Key (id)=(1) already exists.
session.commit()
session.refresh(hero_1)
session.refresh(hero_2) DescriptionHello, I am encountering an error while using SQLModel/SQLAlchemy. I am using PostrgreSQL database and when I try to insert a row, after specifying the id (primary key) attribute, This seems weird to me because sometimes I need to save data with the ID (like for my tests, initial data etc.). And not being able to specify an ID is quite annoying. Is the problem known? because I didn't find any open issues regarding this. Operating SystemLinux Operating System DetailsOS: Manjaro Linux x86_64 SQLModel Version0.0.22 Python VersionPython 3.13.0 Additional ContextNo response |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 12 replies
-
What if you use sqlalchemy? It's better to clarify which this issue is related to. |
Beta Was this translation helpful? Give feedback.
-
@monchin Actually, I tried with sqlalchemy, no problem. the following code, using sqlalchemy's from sqlalchemy import insert
from sqlmodel import Session, SQLModel, create_engine
engine = create_engine("sqlite:///database.db")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
# Using sqlalchemy's `insert` function
stmt = insert(Hero).values(id=1, name="username", secret_name="Full Username")
stmt2 = insert(Hero).values(name="username1", secret_name="Full Username2")
session.exec(stmt)
session.exec(stmt2) |
Beta Was this translation helpful? Give feedback.
-
Does using In SQLModel, you would write it like this : id: int | None = Field(default=None, primary_key=True, sa_column_kwargs={"autoincrement": True}) For reference, in SQLAlchemy : id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True) |
Beta Was this translation helpful? Give feedback.
Yes you're right, it crashed on psql. And I tried without sqlmodel with sync and async mode, with different drivers including psycopg, psycopg2, pg8000 and asyncpg, it crashed on every situation. So it can be concluded that this issue has no relationship with sqlmodel. I guess perhaps something different between psql and sqlite leads to this issue, but I have no idea to validate it.
Anyway, I suggest you not to explicitly specify the value of id. But if you must do that, you can open an issue on sqlalchemy and ask for some suggestions there.
Below is my sqlalchemy code: