I am using postgresql database, want to define a data type of integer value of 2 byte(0-65535). if it is more than 2 byte(65535) value, it shouldn't insert the data into db and raise an error . Is there any data type to work with this scenario? or any check() functionality available for this ?
class Users(db.Model):
id = db.Column(db.Integer, primary_key=True)
You have three options for this. A TypeDecorator from SQLAlchemy, a DB-level "check-constraint" or a custom domain.
Simply apply the following on your column:
ALTER TABLE users ADD CONSTRAINT valid_2byte_int CHECK (id >= 0 AND id < 65535)
This has the advantage that it will work with whoever is connected to the DB. Whether it passes through application code or not. This (and the domain discussed below) are the safest options.
In SQLAlchemy you can then simply use the sqlalchemy.Integer type.
You can define your own column-types with restrictions as so called "domains" inside the database. This is very similar and (in my opinion) better than a simple check-domain as you will be able to reuse it on other tables/columns and keeps things more tidy in general. To do this, simply run the following DB query:
CREATE DOMAIN uint2 AS integer
CHECK(VALUE >= 0 AND VALUE < 65536);
You then can simply use the sqlalchemy.Integer type on you column and the DB will ensure it is valid, raising an appropriate error.
You can however also override the type compilation for more expressive code. I will leave this as an exercise to the reader ;)
You can use a type-decorator for this as follows:
from sqlalchemy.engine.interfaces import Dialect
from sqlalchemy.types import Integer, TypeDecorator
class TwoByteInt(TypeDecorator):
impl = Integer
def process_bind_param(self, value: int, dialect: Dialect) -> int:
if not (0 <= value < 65535):
raise ValueError("Value must be between 0 and 65535")
return value
def process_result_value(self, value: int, dialect: Dialect) -> int:
return value
And then use that type in your model definition:
class Users(db.Model):
id = db.Column(TwoByteInt, primary_key=True)
The process_bind_param function is called whenever a Python value needs to be converted to a DB value, and process_result_value does the inverse. Another way to think about it is: If the value travels from your application into the database, then it will first be processed by process_bind_param, and when it travels from the database to to your application, it will first be processed by process_result_value.
You can use these functions to do all kinds of checks & conversions in both directions. Here we're only interested in a simple value-check when the value is persisted to the DB, so we can leave the process_result_value dead-simple and pass the value transparently back to the caller. You could still do checks here if you really wanted to though.
Note that these functions are called before the SQL query is sent to the DB. So the database will never see these values. This may likely be what you want, but it's good to be aware that this will not be seen in any DB logs, and will also not trigger any DB-level constraint checks.
It might still be good to have a constraint-check on the DB though as discussed above if your SQLAlchemy-based application is not the only client on that DB.
See Custom Types for more information.
impl = Integer
Just wanted to share how did i fixed in my db model.
from sqlalchemy import CheckConstraint
class Users(db.Model):
id = db.Column(db.Integer, primary_key=True)
__table_args__ = (
CheckConstraint(id <= 65535, name='check_for_id'),
{})
alter table statement I posted above, or use a migration toolkit like alembic which will allow you to use the SA syntax to do the same.