SQLAlchemy JSONB

Introduction to SQLAlchemy JSONB

SQLAlchemy JSONB datatype is supported in sqlalchemy; it will help both json and jsonb data types. It includes all the operations supported in json; it also works the same on indexing operations. It also adds the additional operator who is not present in the data type of json. It does not detect the changes which were seen by json data type.

What is SQLAlchemy JSONB?

In sqlalchemy jsonb, the custom deserializers and serializers are shared using the JSON class. It will serialize and deserialize by using the json_serializer and json_desrializer arguments. It will be specified by using the level of dialect using the create_engine method. It is a straightforward and convenient way of storing structured data in rows. Json and jsonb are similar; the only difference is that jsonb added more extra features on it. The json and jsonb both allow the nesting of all data types. Hstore data type only supports the data type of string and only allows one level of nesting, so using the jsonb data type is very useful.

Index operations will invoke by calling the expression using the bracket operator of python. It will return the expression object, which type is defaulted to the JSON. After defining the json type as default, the further jsonb instructions are called using the result type. We can say that index creation is more common when using sqlalchemy jsonb data type; it will return the scalar element, an integer type.

To provide access to data elements in the sqlalchemy jsonb data type, we are using the following data casters as follows:

• Comparator.as_string: This data caster will return the string element. This data caster is very important in it.
• Comparator.as_boolean: This data caster will return the Boolean element. This data caster is very useful in it.
• Comparator.as_float: This data casters will return the float element. This data caster is very important in it.
• Comparator.as_integer: This data caster will return the integer element. This data caster is very useful in it.

Above data, casters will be implemented by supporting the dialects to ensure that the data casters’ comparison will work as expected.

How to Create SQLAlchemy JSONB?

To create the sqlalchemy jsonb data type, we must first install the sqlalchemy package in our system. Without installing this package in our code, we cannot use the sqlalchemy jsonb data type. Unfortunately, the Sqlalchemy module is not coming when installing the python package in our system. Therefore, to use sqlalchemy, we need to install the sqlalchemy module using the pip command.

Below steps shows how to create sqlalchemy jsonb as follows:

1. In the first step, we install the sqlalchemy module using the pip command. We can install the sqlalchemy module in any operating system on which python is installed. In the below example, we are installing the sqlalchemy module as follows.

Code:

pip install sqlalchemy

Output:

2. After installing all the modules, we open the python shell using the python command.

Code:

python

Output:

3. After login into the python shell in this step, we are checking sqlalchemy package is installed in our system.

Code:

import sqlalchemy

Output:

4. After checking whether or not the sqlalchemy package is installed, we import the following modules using the sqlalchemy package. We are importing the MetaData, create_engine, Column, Table, Integer, text, JSONB, and string packages using the sqlalchemy package. We are importing all the modules while executing the separate command. We are importing all the modules by using the import keyword.

Code:

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Table
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Text
from sqlalchemy.dialects.postgresql import JSONB

Output:

5. After importing all the modules in this step, we are giving the sqluri of the database server, and also, we are calling the create_engine method to create the table into the database server. Also, we are defining the MetaData for the SQL engine.

Code:

py_uri = 'sqlite:///db.sqlite'
py_engine = create_engine (py_uri)
sql_meta = MetaData (py_engine)

Output:

6. After defining the sql_uri and calling the create_engine method in this step, we create multiple tables.

In the below example, we are creating a single table name as py_stud1.

Code:

sql_stud1 = Table ('EX1', py_meta,
Column ('id', Integer, primary_key=True),
Column ('name', JSONB))

Output:

7. After creating the table, we call the create_all method to create the table into the database server.

Code:

py_meta.create_all ()

Output:

Examples of SQLAlchemy JSONB

Different examples are mentioned below:

Example #1

In the below example, we create two tables with sqlalchemy jsonb data type. In the below example, we have imported the MetaData, create_engine, Column, Table, integer, jsonb, and string modules.

After importing the module, we provide the database url and call the create_engine method; then, we define the MetaData method. Then we create the two and give the column data type as jsonb.

Code:

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Table
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Text
from sqlalchemy.dialects.postgresql import JSONB
jsonb_uri = 'sqlite:///db.sqlite'
jsonb_engine = create_engine (jsonb_uri)
jsonb _meta = MetaData (jsonb_engine)
jsonb_stud1 = Table ('EX1', jsonb_meta,
Column ('stud_id', Integer, primary_key=True),
Column ('stud_name', JSONB))
Jsonb_stud2 = Table ('EX2', jsonb_meta,
Column ('stud_id', Integer, primary_key=True),
Column ('stud_addr', JSONB))
jsonb_meta.create_all ()

Output:

Example #2

In the below example, we create the single table by providing column data type as jsonb.

Code:

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Table
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Text
from sqlalchemy.dialects.postgresql import JSONB
jsonb_uri = 'sqlite:///db.sqlite'
jsonb_engine = create_engine (jsonb_uri)
jsonb_meta = MetaData (jsonb_engine)
jsonb_stud = Table('EX1', jsonb_meta,
Column ('id', Integer, primary_key=True),
Column ('name', JSONB))
jsonb_meta.create_all ()

Output:

Conclusion

The sqlalchemy jsonb will avoid creating new tables and their relationships; it will perform the use cases after setting up sqlalchemy jsonb properly. Furthermore, Sqlalchemy supports the jsonb data type while using the jsonb; we can add support for the same into the models of ORM.

Recommended Articles

This is a guide to SQLAlchemy JSONB. Here we discuss the introduction, how to create SQLAlchemy JSONB? and examples. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. PL/SQL NOT EQUAL
4. SQL NOT IN