In this article, we will see how to insert NULL values into a PostgreSQL database using SQLAlchemy in Python.
For demonstration purposes first, let us create a sample table using SQLAlchemy as shown below
Creating a table using SQLAlchmey in PostgreSQL:
- Import necessary functions from SQLAlchemy package.
- Establish connection with the PostgreSQL database using create_engine() function as shown below
- Create a table called book_publisher with columns publisher_id, publisher_name, and publisher_estd
- Insert record into the tables using insert() and values() function as shown.
Syntax: engine = create_engine(dialect+driver://username:password@host:port/database_name)
Python3
# import necessary packagesfrom sqlalchemy import create_engine,/MetaData, Table, Column, Integer, String# establish connectionengine = create_engine(# store engine objectsmeta = MetaData()# create a tablebook_publisher = Table( 'book_publisher', meta, Column('publisherId', Integer, primary_key=True), Column('publisherName', String), Column('publisherEstd', Integer),)# use create_all() function to create a# table using objects stored in meta.meta.create_all(engine)# insert valuesstatement1 = book_publisher.insert().values( publisherId=1, publisherName="Oxford", publisherEstd=1900)statement2 = book_publisher.insert().values( publisherId=2, publisherName='Stanford', publisherEstd=1910)statement3 = book_publisher.insert().values( publisherId=3, publisherName="MIT", publisherEstd=1920)statement4 = book_publisher.insert().values( publisherId=4, publisherName="Springer", publisherEstd=1930)statement5 = book_publisher.insert().values( publisherId=5, publisherName="Packt", publisherEstd=1940)engine.execute(statement1)engine.execute(statement2)engine.execute(statement3)engine.execute(statement4)engine.execute(statement5) |
Output:
Sample table using SQLAlchemy
Inserting NULL values using SQLAlchemy in PostgreSQL
Let us use the same insert() and values() function to insert NULL values. In python NULL equivalent is None. So, specify None for the records which you wish to insert as NULL values as shown below.
Python3
# specify null values as NONEstatement6 = book_publisher.insert().values( publisherId=6, publisherName=None, publisherEstd=None)# insert the null valuesengine.execute(statement6) |
Output:
You can see a new record inserted into row 6.
None values were inserted into a table
Fetching the above results using python shell
Here we are going to fetch the results using fetchall() methods.
Python3
# Get the `book_publisher` table from the# Metadata objectimport sqlalchemybook_publisher = meta.tables['book_publisher']# SQLAlchemy Query to fetch all recordsquery = sqlalchemy.select([ book_publisher])# Fetch all the recordsresult = engine.execute(query).fetchall()# View the recordsfor record in result: print("\n", record) |
Output:
The output of records having None values
