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/Beginner4Beginner" ) # 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/Beginner4Beginner" ) # 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:
Contact Us