best practice? null/None vs 'None' vs '' (in web/sql)
robin.thomas at starmedia.net
Fri Mar 30 18:58:15 CEST 2001
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
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
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
magic_function('"is this string quoted?"')
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.
if object is None: return ""
else: return str(object)
if not string: return None
# maybe do other conversions to int() or float()
return string # or use string unchanged
StarMedia Network, Inc.
robin.thomas at starmedia.net
More information about the Python-list