35
loading...
How To Make a Field Unique With SQLModel
Creating unique fields in SQLmodel is very simple and I would like to make this post too very simple for you by showing you the codes first and then proceed to explaining what it does. I am assuming you know how to create database and tables, and how to populate them with data using sqlmodel already. Let's begin.
If you have not read about SQLmodel, don't worry.
SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust.
feel free to check it out here - SQLModel Documentation
SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust.
feel free to check it out here - SQLModel Documentation
There are two approaches in achieving this. Let's take a look at the model we will be working with.
from sqlmodel import SQLModel, Field
from typing import Optional
class User(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
email: strLet's make the email field unique in this model or table
from sqlmodel import SQLModel, Field
from sqlalchemy import UniqueConstraint
from typing import Optional
class User(SQLModel, table=True):
__table_args__ = (UniqueConstraint("email"),)
id: Optional[int] = Field(default=None, primary_key=True)
name: str
email: str...
CREATE TABLE user (
id INTEGER,
name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
PRIMARY KEY (id),
UNIQUE (email)
)
......
from sqlmodel import SQLModel, Field
from sqlalchemy import UniqueConstraint, Column, String
from typing import Optional
class User(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
email: str = Field(sa_column=Column("email", String, unique=True))
...Again, creating the database and tables we get the output below.
...
CREATE TABLE user (
id INTEGER,
name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
PRIMARY KEY (id),
UNIQUE (email)
)
...Now let's attempt to add two users with the same email to the user table
...
with Session(engine) as session:
user1 = User(name="Kyei Samuel", email="[email protected]")
user2 = User(name="Rex Osei", email="[email protected]")
session.add(user1)
session.add(user2)
session.commit()
...Notice the error we get in the terminal
...
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.email
[SQL: INSERT INTO user (email, name) VALUES (?, ?)]
[parameters: ('[email protected]', 'Rex Osei')]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
...This is an Integrity error and it occurred because of a failure of a unique constraint on the email field of the user model.
This shows that the unique constraint was successfully created.
This shows that the unique constraint was successfully created.
Now between these two methods, the one to choose depends on your preference. I personally do not know the effect of using any of these methods so feel free to choose one.😊
Note that SQLModel is based on both SQLAlchemy and Pydantic. This means that a SQLModel is actually a SQLAlchemy model and also a pydantic model. Therefore, if there is anything SQLModel is not providing directly, we can always access SQLAlchemy directly and implement that feature. That is why we were able to import "UniqueConstraint", "Column" as well as "String" from sqlalchemy to make the email field unique.
It is a class attribute in SQLModel(ofcourse SQLAlchemy) and it takes table arguments other than the name, metadata, and mapped
Column. This attribute accommodates both positional as well as keyword arguments that are normally sent to the Table constructor. The attribute can be specified in one of two forms, dictionary and tuple.
Column. This attribute accommodates both positional as well as keyword arguments that are normally sent to the Table constructor. The attribute can be specified in one of two forms, dictionary and tuple.
It is a parameter of the Field function in SQLModel and it helps us to define a field or column in the SQLAlchemy way using the Column class from SQLAlchemy. This actually means that sa_column takes an instance of the Column class as its value. With this, we are able to define a field while having access to all the parameters provided by SQLAlchemy Column class. That is why we were able to set the unique parameter of the email field to True.
Alright, that's it for this post, I hope you understood everything and also you were able to implement it in your code. This is my first post, kindly feel free to share your comments and your advices are warmly welcome😊. Thank you.
35