SQL Queries In Python Where Value May Be None/Null

Question

I'm writing a Python script to analyse some data, that is sourced from several different SQL databases. Queries to the databases are based on the results of previous queries, to different databases.

The problem that I'm having is that some queries may return NULL.

So, if I have a query like

SELECT *
FROM table_name
WHERE fieldA = ?
AND fieldB = ?

And I have two values that have been returned from other queries; field_a = 'A' and field_b = None, when I try to execute the query in Python:

with pyodbc.connect('Trusted_connection=yes', driver='{SQL Server}', server=server_name, database=database_name, autocommit=True) as connection:
    ds = connection.execute(sql,[field_a, field_b])

nothing is returned. If I change the SQL query so that the None/Null value is hard-coded:

SELECT *
FROM table_name
WHERE field_a = ?
AND field_b IS NULL

I get the data that I expect.

Is there a better way to deal with this problem other than building the SQL query from substrings based on the field values?

What about using AND field_b IS ??

cdonts
– cdonts

2015-02-25 21:18:14 +00:00
Commented Feb 25, 2015 at 21:18 — cdonts
– cdonts, Commented Feb 25, 2015 at 21:18

VB1 · Accepted Answer · 2015-02-25 21:42:00Z

2

Try using OR

    SELECT *
    FROM table_name
    WHERE (field_a = ? AND field b IS NULL)
    OR (field_a IS NULL AND field b  = ?)
    OR (field_a = ? AND field b  = ?)

Hope this helps!

edited Feb 25, 2015 at 21:42

answered Feb 25, 2015 at 21:35

VB1

2141 silver badge7 bronze badges

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

3 Comments

Xedni Over a year ago

The only problem with this is that if you DO get a value from the parameter, you'd want it to only equate to those rows, and in this case you'd get the equating rows, plus all the nulls.

VB1 Over a year ago

Thanks and you re right. I edited to get results for Values and in case of nulls for either.

Batman Over a year ago

Thanks, I was making this problem more complicated than it needed to be.

Xedni · Accepted Answer · 2015-02-25 21:33:39Z

0

I'm guessing none gets translated to null when it's compiled into SQL. Since a null can never equal anything (not even another null) you'll have to use is null like you did when you hard coded the value. What if you want to be able to do both? The route I'd go would be to control for the possibility of a null with something like this:

and field_b = isnull(?, '')

Of course this has the loophole that if field_b actually IS an empty string, it will equate when you don't intend it to. Depending on whether that's a problem or not, you could also get creative with case statements or or conditions.

answered Feb 25, 2015 at 21:33

Xedni

4,7913 gold badges22 silver badges38 bronze badges

Collectives™ on Stack Overflow

SQL Queries In Python Where Value May Be None/Null

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related