Selecting from DB where some data points can be NULL (psycopg2)

user_2253323 0
Viewed 31

I have the following query, where some of the values I am trying to select with can be empty and therefore default to None.

So I've come up with something like this:

   db.cursor.execute(
        '''
            SELECT
                s.prod_tires_size_id as size_id
                ,s.rim_mm
                ,s.rim_inch
                ,s.width_mm
                ,s.width_inch
                ,s.aspect_ratio
                ,s.diameter_inch
            FROM product_tires.sizes s
            WHERE 
                s.rim_mm %(rim_mm)s
            AND s.rim_inch %(rim_inch)s
            AND s.width_mm %(width_mm)s
            AND s.width_inch %(width_inch)s
            AND s.aspect_ratio %(aspect_ratio)s
            AND s.diameter_inch %(diameter_inch)s
        ''', {
            'rim_mm': data['RIM_MM'] or None,
            'rim_inch': data['RIM_INCH'] or None,
            'width_mm': data['WIDTH_MM'] or None,
            'width_inch': data['WIDTH_INCH'] or None,
            'aspect_ratio': data['ASPECT_RATIO'] or None,
            'diameter_inch': data['OVL_DIAMETER'] or None,
        }
    )

However, = NULL does not work.

If I use IS, then it will not match the values I am proving.

How can I solve this problem?

psycopg2 python-3.x postgresql sql python
1 Answers

Generate the query using python:

k = {'rim_mm':        'RIM_MM',
     'rim_inch':      'RIM_INCH',
     'width_mm':      'WIDTH_MM',
     'width_inch':    'WIDTH_INCH',
     'aspect_ratio':  'ASPECT_RATIO',
     'diameter_inch': 'OVL_DIAMETER',
}

where = []

for column, key in k.items():
    if data[key]:
        where.append("%s=%%(%s)s" % (column,key)")
    else:
        where.append("%s IS NULL" % column)

sql = "your select where " + " AND ".join(where)

cursor.execute( sql, data )
user_1444500
Related