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 packagesfrom sqlalchemy import create_engine, MetaData,/Table, Column, Integer, String# establish connectionengine = create_engine( "postgresql+psycopg2://\ postgres:Saibaba97%40@127.0.0.1:5432/test")# 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)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 Nones = book_publisher.select().where( book_publisher.c.publisherName == None)# output get stored in result objectresult = engine.execute(s)# iteratte through the result object# to get all rows of the outputfor 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)
