Inserting NULL as default in SQLAlchemy
Last Updated :
22 Jun, 2022
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()
engine = db.create_engine(
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 )
Base.metadata.create_all(engine,
tables = [Sales.__table__])
Session = sessionmaker(bind = engine)
session = Session()
new_record = Sales(product = 'Refrigerator' ,
quantity = 15 ,
description = 'The season is too hot!' )
session.add(new_record)
session.commit()
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
metadata_obj = db.MetaData()
engine = db.create_engine(
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 ),
)
metadata_obj.create_all(engine)
with engine.connect() as conn:
conn.execute(
db.insert(sales).values(
product = 'Televisions' ,
quantity = 25 ,
description = 'This value is inserted\
using SQLAlchemy Core!'
)
)
with engine.connect() as conn:
conn.execute(
db.insert(sales).values(
product = 'Laptops' ,
quantity = 31
)
)
|
Output:
Share your thoughts in the comments
Please Login to comment...