Select NULL Values in SQLAlchemy

In this article, we will see how to select NULL values into a PostgreSQL database using SQLAlchemy in Python.

For demonstration purposes first, let us create a sample table using SQLAlchemy in PostgreSQL as shown below

Creating a table using SQLAlchemy 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)

Parameters:

• dialect – Name of the DBMS. The dialect is the system SQLAlchemy uses to communicate with various types of DBAPIs and databases like PostgreSQL, MySQL, MS SQL, etc.
• driver – Name of the DB API that moves information between SQLAlchemy and the database.
• Username – Name of the admin
• Password – Password of the admin
• host – Name of the host in which the database is hosted
• port – port number through which the database can be accessed
• database_name– Name of the database

Creating table for demonstration:

Python3

# import necessary packages
from sqlalchemy import create_engine, MetaData,/
Table, Column, Integer, String

# establish connection
engine = create_engine(
    "postgresql+psycopg2://\
    postgres:Saibaba97%40@127.0.0.1:5432/test")

# store engine objects
meta = MetaData()

# create a table
book_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 values
statement1 = 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)

statement6 = book_publisher.insert().values(
    publisherId=6, publisherName=None, publisherEstd=None)

engine.execute(statement1)
engine.execute(statement2)
engine.execute(statement3)
engine.execute(statement4)
engine.execute(statement5)
engine.execute(statement6)

Output:

Sample table created using SQLAlchemy

Selecting NULL values using SQLAlchemy in PostgreSQL

Using select() and where function, we can query data from our database. The NULL equivalent in python is None.

The other things remain the same, like conventional SQL querying. The query is passed to a execute() statement and output is stored in a result object and rows can be iterated to fetch all the rows

Code:

Python3

# write a conventional SQL query
# with NULL equivalent as None
s = book_publisher.select().where(
  book_publisher.c.publisherName == None)

# output get stored in result object
result = engine.execute(s)

# iteratte through the result object
# to get all rows of the output
for row in result:
    print(row)

Output:

As we know from the output shown above, row 6 has None values and that is correctly returned in the below output

(6, None, None)