Select NULL Values in SQLAlchemy

Last Updated : 29 Jan, 2022

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%[email protected]: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:

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)

SQLAlchemy Core - Multiple Tables

jssuriyakumar

Improve

Article Tags :

Practice Tags :

python

Select NULL Values in SQLAlchemy

Creating a table using SQLAlchemy in PostgreSQL:

Creating table for demonstration:

Selecting NULL values using SQLAlchemy in PostgreSQL

Similar Reads

Thank You!

What kind of Experience do you want to share?