SQLAlchemy Data Types

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 that specifies the own 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 the datatype 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 and even target databases that can be used and called 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 both 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 will need to 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 inputs formats to the 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 the way for saving the colors from the color package with additional objects into 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 the 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 that have the sql standard kind but also provide the additional type of arguments.

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 like string versions, and it is rendered for table creation to satisfy all the sql functions that can be CAST the changes. The application wants to force the binary rendering datas 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 are used to insert the BLOB datas in the database through sqlalchemy import binary and compile libraries. It has n number of custom types satisfied with the XML and JSON type 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 some data types that help create the 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 datas 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

This is a guide to SQLAlchemy Data Types. Here we discuss the introduction and different SQLAlchemy data types, respectively. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. SQL EXCLUDE
4. MySQL InnoDB Cluster