Check null values in data fetched from SQL database

Question

I want to store the data in numpy arrays fetched from the database. I want to make sure that no null value (None) go to the numpy array(throws an error anyway doing that). I have tried to do it the following way but it does not work. For some reason, NullValueCheck() always returns true How can I know about null values and do something about it?

import numpy as np
import pyodbc as odbc

cnxn = odbc.connect(conn_string)
cursor = cnxn.cursor()
cursor.execute("""SELECT ID, BuildingID, Title FROM Something"""")

rows = cursor.fetchall()
cnxn.close()

ID = [i[0] for i in rows]
buildingID = [i[1] for i in rows]
title = [i[2] for i in rows]

def NullValueCheck(rows):
     if (any(elem is None for elem in rows[0])):
         return True
     else:
         return False

if NullValueCheck(rows):
    ID_array = np.fromiter(ID, dtype= np.int32)

Edit: It turns out that I don't have to write all that code. I can achieve the same using pandas dataframe that I want to achieve from numpy array.

import pandas as pd
import pyodbc as odbc

cnxn = odbc.connect(conn_string)
df = pd.io.sql.read_sql("""SELECT ID, BuildingID, Title FROM Something""", cnxn)

FlipperPA · Accepted Answer · 2018-03-02 14:38:33Z

1

I've found this is easiest to address in your source SQL. COALESCE is helpful here:

df = pd.io.sql.read_sql("""SELECT ID, COALESCE(BuildingID, 0) AS BuildingID, Title FROM Something""", cnxn)

This will return 0 if the value of BuildingID is NULL. Different SQL databases have functions for specific NULL checking (ISNULL in SQL Server, IFNULL in MySQL, for example), but COALESCE is the most cross database compatible.

answered Mar 2, 2018 at 14:38

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check null values in data fetched from SQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related