Updated June 28, 2023
Introduction to SQLAlchemy Data Types
The SQLAlchemy data types are one of the utility models, and it provides various set of formats that includes numbers like integers, string, characters, float, and double; these data types will use automatic data coercion it will declare and use the functions and assigned automatically for all the classes which are is to be mapped with the coercion capable properties the SQLAlchemy mappers before initialized the data models.
Different SQLAlchemy Data Types
The SQLAlchemy provides the data abstractions for most common databases and the mechanism specifying custom data types. It includes the methods and attributes of every object type directly called to the object type. They are supplied to the database table definitions occasions for wherever the database driver will return as the incorrect datatypes error. The SQLAlchemy will use datatypes like Integer(10) and String(32) as the information type, which helps create the table with a set of back rows from the database.
1. Generic Type
The Generic is one of the datatype models, and it is specified through the column that can be stored the user data for reading and writing the data. It is also helpful for choosing the database column already available on the source. It targets usable databases and calls for issuing and creating tables on the database. It is also satisfied with the SQL standard and Multiple Vendor Types that can be of either SQL standard part, and it is potentially found with the subset of the database backends along with the generic types even the SQL standard with the multi-vendor types no guarantee records only the backends which explicitly called the support data.
Mainly the Array is the central part of the core support, which includes the standard SQL functions, which involves implicitly and explicitly array data being captured except the Postgresql backend and possible dialect like third-party tools with the built-in sqlalchemy functions.
2. Arrow Type
It provides a way for saving and retrieving the Arrow objects to create the database. It automatically changes the Arrow objects to specify the data objects like datetime to maintain the things on the way out of the querying database. The ArrowType must install the Arrow library; it expects all the arrow goodies. Arrow library mainly offers a sensible approach for creating, manipulating, formatting, and converting the user input formats to other data types like dates, times, and timestamps.
The above class is the main sqlalchemy utils in the arrow type with parameters and arguments passed in the method called ArrowType(args, kwargs).
3. Choice Type
The choice type offers multiple ways of fixing choices with the specified column type and the tuple collection key-value pairs list. Moreover, it will integrate with the standard library of the Python language for the compatible version automatically coerced to the choice objects tuple-based lists is passing to the constructor on the subclasses.
The choice type is more helpful in rendering the values on the user’s locale with additional changes on the Utils package.
4. Color Type
The color type will provide a way to save the colors from the color package with additional objects in the database. It will keep the color objects as the input strings and automatically convert them to the objects; when querying, the database will always return the same color objects.
The above class is the primary color type, and it will be imported the sqlalchemy utils with additional colors for passing the parameters like max_length=20 and arguments, kwargs. At the same time, it gives ideas with the same and different columns.
5. Composite Type
It helps to interact with the Postgresql database composite types automatically; this feature is available with easy access to the mixed type fields. And also supports the sqlalchemy type decorator types and the ability to include the composite types as part of the Postgresql arrays. The type creation and dropping of the mixed types for installing the DDL listeners before_create and after_drop the hybrid type of the database.
When creating the composite type with either pass of the tuple or any other data dictionaries.
6. Country Type
The country objects to passing and changing the objects to the string and other data type representation after changing back to the scalar coercible class.
This class will provide the county-type utils and pass the countries as the first and second arguments.
7. Vendor-Specific Type
The database-specific types are mainly available for importing databases for each dialect module. It is primarily referenced for the schema highlighted on the databases like Mysql, SQLite, PostgreSQL, etc. Generally, the integer and varchar datatypes are the primary ultimate for the sqlalchemy types. INET is one of the specific Postgresql dialect types with the sql standard kind but also provides an additional type of argument.
Code:
from sqlalchemy.dialects import mysql
tb = Table(news, metadata,
Column('id', mysql.BIGINT),
Column('validss',mysql.ENUM('p', 'e', 'r'))
)
The above codes are the basic vendor-specific types for the database to dialect the information from the users, which the database coder handles.
8. Custom Type
The sqlalchemy also uses the existing methods to redefine the current data types for the new ones. We can also override the type for compilation to enforce the datatype, such as string versions, and render it for table creation to satisfy all the SQL functions that can CAST the changes. The application wants to force the binary rendering data for all the platforms that included the BLOB data, which performed the large binary for the most preferred use cases. We can measure the control types on the compilation directive associated with any kind.
Code:
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import BINARY
@compiles(BINARY, "sqlite")
def news(type_, compiler, **kw):
return "BLOB"
The above codes utilize SQLAlchemy’s import binary and compile libraries to insert BLOB data into the database. It has n number of custom types satisfied with the XML and JSON data formats.
Examples of SQLAlchemy Data Types
Different examples are mentioned below:
Example #1
Code:
from sqlalchemy_utils import CountryType, Country
class firsts(Base):
__tablename__ = 'test4'
id = sa.Column(sa.Integer, autoincrement=True)
name = sa.Column(sa.Unicode(255))
country = sa.Column(CountryType)
ft = firsts()
ft.country = Country('FI')
session.add(ft)
session.commit()
ft.country
ft.country.name
print ft.country
Output:
Example #2
Code:
from colour import Color
from sqlalchemy_utils import ColorType
class seconds(Base):
__tablename__ = 'test3'
id = sa.Column(sa.Integer, autoincrement=True)
name = sa.Column(sa.Unicode(50))
background_color = sa.Column(ColorType)
sec = seconds()
document.background_color = Color('#blue')
session.commit()
Output:
Example #3
Code:
from sqlalchemy.dialects.postgresql import ARRAY
class third(Base):
__tablename__ = 'test4'
id = sa.Column(sa.Integer, primary_key=True)
bal = sa.Column(
ARRAY(
CompositeType(
'amnt',
[
sa.Column('ruppee', CurrencyType),
sa.Column('id', sa.Integer)
]
),
dmns=2
)
)
Output:
The above examples are data types that help create database columns at different stages. For example, when we want to add one more column to the existing tables, we can use an altered keyword and the query statement to add additional columns to the current table. Here we can create separate classes for each example, add the values to the required columns, and perform the data operations of the particular column type like country type, currency type, and color type. Some column types also handled the user input data to the databases.
Conclusion
The SQLAlchemy Data Types is one of the main features and concepts for implementing the user datas from the front end to the back end database schema. Then we need to add additional user input datas from the existing database table columns from the users based on the user requirement.
Recommended Articles
We hope that this EDUCBA information on “SQLAlchemy Data Types” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
