Python SQL Select With Possible Null Values

Python SQL Select with possible NULL values

If you are using SQL Server then as long as you set ANSI_NULLS off for the session '= null' comparison will work.

SET ANSI_NULLS

Dynamically search for null in sqlite select query using python

Okay, after firing up a python REPL and playing around with it a bit, it's simpler than I thought. The Python sqlite bindings turn a Python None into a SQL NULL, not into a string 'None' like it sounded like from your question. In SQL, = doesn't match NULL values, but IS will. So...

Given a table foo looking like:

a     | b
--------------
NULL  | 1
Dog   | 2     

Doing:

c = conn.cursor()
c.execute('SELECT * FROM foo WHERE a IS ?', (None,))
print(c.fetchone())

will return the (NULL, 1) row, and

c.execute('SELECT * FROM foo WHERE a IS ?', ('Dog',))
print(c.fetchone())

will return the ('Dog', 2) row.

In other words, use IS not = in your query.

How can I replace NULL in a database query?

If you want to execute a second statement with input from the first statement, you can use a parameterized statement, and pass parameters from the first to the second in your python code.

For example, something like this:

cur = con.cursor()
cur.execute('select output1 from step1(null)')
result1 = cur.fetchone()

cur.execute('select output1, output2 from step2(?, ?, null, null)', (result1[0], result1[0]))
result2 = cur.fetchall()

Alternatively, you can join the stored procedures together to do this in one query. For example:

select s2.*
from step1(null) s1
cross join step2(s1.output1, s1.output1, null, null) s2

Contrary to normal tables, using a cross join with a stored procedure does not produce a cross-product, but instead behaves as a lateral join.

Create SQL command with a query parameter that checks for NULL but also for other values

Consider COALESCE to give NULL a default value. Below assumes z.code is a varchar or text. If a integer/numeric, change 'default' to a number value (e.g., 9999).

sql = """SELECT
               z.code as code
         FROM
               s_order as o
         LEFT JOIN
               s_code as z ON o.id = z.id
         WHERE
               COALESCE(z.code, 'default') = %(own_id)s;
      """

cursor.execute(sql, {'own_id': entry})    
cursor.execute(sql, {'own_id': 'default'})     # RETURNS NULLs IN z.code

Online Demo

SQL: SELECT where one of many columns contains 'x' and result is not NULL

cur=db.cursor()

data="123"
fields_to_check=["A","B","C","D","E","F","G"]
sub_query = ""

for field in fields_to_check:
    sub_query = sub_query + "or {}='{}' ".format(field,data)

if sub_query:
    query = "SELECT Value FROM table WHERE ("+ str(sub_query[2:]) +") and value IS NOT NULL;"

if query:
    cur.execute(query)
    rows = cur.fetchall()
    if rows:
      for row in rows:
        print(row)

Python in SQL Server: NULL values in INT columns get mapped to -2147483648 rather than None

The rxMissingValues document describes the pandas/numpy limitation of storing None values in integer columns. You can handle these by checking for missing value(rxMissingValues.int32()) as described in the document.

---

Related Topics