Inserting NULL as default in SQLAlchemy

In this article, we will see how to insert NULL as default in SQLAlchemy in Python.

The default value can be provided using the default parameter while defining the table (creating the table). We will be using mySQL local database for the examples in this article. Feel free to use any database you may like but you need to modify the connection string with the type of database you are working on and the credentials.

Example 1: Using SQLAlchemy ORM

In the above example, we have created a sales table in the database. We have defined 3 columns for the sales table, namely, product, quantity, and description. The product column is the primary key whereas for the description column we have provided a default=None parameter. This parameter is equal to providing a default value to a column in traditional SQL queries. When we first inserted the product ‘Refrigerator’, we provided a description along with it (visible in output as well). For the second entry, i.e., product ‘Washing Machine’ we did not provide any description parameter so it takes in the default value. The default value provided is None which is equivalent to NULL in SQL. In this example, we have used SQLAlchemy ORM.

Python

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")

# CREATE THE SALES TABLE MODEL
# TO USE IT FOR QUERYING
class Sales(Base):

    __tablename__ = 'sales'

    product = db.Column(db.String(50),
                        primary_key=True)
    quantity = db.Column(db.Integer)
    description = db.Column(db.String(100),
                            default=None)


# CREATE SALES TABLE IF IT
# DOES NOT EXIST ALREADY
Base.metadata.create_all(engine,
                         tables=[Sales.__table__])

# CREATE A SESSION OBJECT TO
# COMMIT SQL QUERIES TO DATABASE
Session = sessionmaker(bind=engine)
session = Session()

# INSERT NEW RECORD WITH A DESCRIPTION
new_record = Sales(product='Refrigerator',
                   quantity=15,
                   description='The season is too hot!')
session.add(new_record)
session.commit()

# INSERT NEW RECORD WITHOUT DESCRIPTION SO
# THAT IT TAKES DEFAULT NULL VALUE AS DEFINED
new_record = Sales(product='Washing Machine',
                   quantity=12)
session.add(new_record)
session.commit()

Output:

Example 2: Using SQLAlchemy Core

In the second example, we have inserted two new records for products ‘Televisions’ and ‘Laptops’. If we look at the code, the syntax for defining the default value for a column in a table is the same for both SQLAlchemy Core and ORM. The ‘Televisions’ record is provided with a description (visible in the output as well). For the second entry i.e., ‘Laptops’ , the description parameter is not provided so it by default takes in the value provided as the default parameter while creating or defining the table. Please note that the output consists of the previous two records as well seen in the SQLAlchemy ORM example.

Python

import sqlalchemy as db

# CREATE THE METADATA OBJECT
metadata_obj = db.MetaData()

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")

# CREATE THE SALES TABLE MODEL TO USE IT FOR QUERYING
sales = db.Table(
    'sales',
    metadata_obj,
    db.Column('product', db.String(50),
              primary_key=True),
    db.Column('quantity', db.String(100)),
    db.Column('description', db.Integer,
              default=None),
)

# CREATE SALES TABLE IF
# IT DOES NOT EXIST ALREADY
metadata_obj.create_all(engine)

# INSERT NEW RECORD WITH A DESCRIPTION
with engine.connect() as conn:
    conn.execute(
        db.insert(sales).values(
            product='Televisions',
            quantity=25,
            description='This value is inserted\
            using SQLAlchemy Core!'
        )
    )

# INSERT NEW RECORD WITHOUT DESCRIPTION SO
# THAT IT TAKES DEFAULT NULL VALUE AS DEFINED
with engine.connect() as conn:
    conn.execute(
        db.insert(sales).values(
            product='Laptops',
            quantity=31
        )
    )

Output: