best practice? null/None vs 'None' vs '' (in web/sql)

[Robin Thomas]

At 04:12 PM 3/30/01 +0000, fluxent at yahoo.com wrote:
>(Python 2.x, Win2K, IIS5)
>
>I've got some simple pages for viewing and editing SQL records via
>Python in ASP.
>
>I would really like to distinguish between fields with null values
>and fields that might actually include the text string 'None'. Right
>now both cases come out from a SELECT as showing up as 'None', and
>then when I submit and edit they definitely get changed to 'None', so
>I end up with no nulls.
>
>I can see that the ODBC result set distinguishes them. But of course
>when things get rendered out to HTML you don't want to see single
>quotes around every string value, so 'None' and None become the same
>thing.

This about the limitations of "query arguments" to HTTP requests, not about 
any limitations of ODBC, Python, or ODBC/Python interfaces.

HTTP query arguments are of one type: STRING. There is no concept of a 
"null" value; the closest thing you have is an "empty" string like "". In 
most database engines, there is a clear difference between NULL and "" in 
any textual or character data column.

If your HTTP-based application wishes to consider some special string value 
as meaning "null", then you must choose which special string value will be 
considered "null". Then you must have your application transform data in 
two ways: transforming the special string to the null value (when 
processing HTTP request input), and transforming the null value to the 
special string value (on output of HTTP response).



>So, I guess this isn't a platform bug, it's a nasty area calling for
>some sort of interface standard. Is there a best practice I should
>adopt?

The best practice is consistency in your application. Pick a special value 
and go with it.

The "lack of standard" is instead a lack of (or lack of need for) as 
built-in string-to-null-value conversion function. Python has int() and 
float() and others to convert strings to numeric objects. But a null() 
function is overkill. You can write it yourself:

if a == "": a = None

If you instead wish for a magical standard "convert a string into the 
neatest possible object" function, the magical standard may not do what you 
ask. Consider:

magic_function("")
magic_function("None")
magic_function('"is this string quoted?"')
magic_function("[]")
magic_function("{'a': 5}")
magic_function("6L") # form submitter's apartment number, not a long integer
magic_function("11201") # zip code for ZIP_CODE VARCHAR column
magic_function("6K5 B1E") # Canadian zip code for ZIP_CODE VARCHAR column

Would these do the conversions you want in your application? In the case of 
"6L" as apartment number in an address form in HTML, definitely not.

Because the policies of string-to-object conversion are so 
application-dependent, Python does not impose a magical standard that may 
cuase you headaches. Instead, it gives you specific type-conversion 
function like int() and float(), and lets you do the rest.

>Maybe (a) transform the outputs of each SELECT to a different
>representation of None (either '' or 'null'?), then (b) transform all
>UPDATE query strings before executing them to change any ='' (or
>='null') to =NULL?

Yes. You've stated above that you do *not* wish to have "None" be the 
special string in your application. So you can pick something else. Many 
people choose the empty string "", which means that no input argument via 
HTTP can really be an empty string; it will always get converted to 
null/None by your application.

def output(object):
     if object is None: return ""
     else: return str(object)

def input(string):
     if not string: return None
     else:
         # maybe do other conversions to int() or float()
         return string # or use string unchanged


--
Robin Thomas
Engineering
StarMedia Network, Inc.
robin.thomas at starmedia.net