October 18th, 2007, 08:38 AM
|
 | Moderator | | Join Date: Sep 2006 Location: Minden, Nevada, USA
Posts: 6,400
| |
Here are the latest versions of My (as in mine) SQL helper functions. Please feel free to rename them if you use them.
The SELECT helper: - def MySQLSelect(table, arglist=(), argdict={}, **kwargs):
-
"""Build an SQL SELECT command from the arguments:
-
Return a single string which can be 'execute'd.
-
arglist is a list of strings that are column names to get.
-
argdict and kwargs are two way to evaluate 'colName'=value
-
for the WHERE clause"""
-
# Allow NULL columns in the result set # pyodbc makes rows mutable so, use it!
-
a = ', '.join((arg, 'NULL')[arg is None] for arg in arglist)
-
args = argdict.copy()
-
args.update(kwargs)
-
for key, value in args.items():
-
args[key] = (str(value), repr(value))[isinstance(value, str)]
-
b = ''
-
if args:
-
b = 'WHERE %s' %' AND '.join(key + '=' + value
-
for key, value in args.items())
-
-
return ' '.join(['SELECT', (a or '*'), 'FROM', table, b])
The INSERT and UPDATE helpers now convert None to NULL: -
-
def MySQLInsert(table, argdict={}, **kwargs):
-
"""Build an SQL INSERT command from the arguments:
-
Return a single string which can be 'execute'd.
-
argdict is a dictionary of 'column_name':value items.
-
**kwargs is the same but passed in as column_name=value"""
-
args = argdict.copy() # don't modify caller dictionary!
-
args.update(kwargs)
-
keys = args.keys() # an ordered list #
-
argslist = []
-
for key in keys:
-
a = args[key] # argslist will match the order from above #
-
argslist.append(((str(a), repr(a))[isinstance(a, str)], "NULL")[a is None])
-
# wrap comma separated values in parens
-
a = '(%s)' %', '.join(field for field in keys)
-
b = '(%s)' %', '.join(argslist)
-
return ' '.join(['INSERT', table, a, 'VALUES', b])
-
-
-
def MySQLUpdate(table, valuedict, argdict={}, **kwargs):
-
"""Build an SQL SELECT command from the arguments:
-
Return a single string which can be 'execute'd.
-
valuedict is a dictionary of column_names:value to update.
-
argdict and kwargs are two way to evaluate 'colName'=value
-
for the WHERE clause."""
-
vargs = valuedict.copy()
-
for key, value in vargs.items():
-
vargs[key] = ((str(value), repr(value))[type(value) == str], "NULL")[value is None]
-
a = 'SET %s' % ', '.join(key + '=' + value
-
for key, value in vargs.items())
-
args = argdict.copy()
-
args.update(kwargs)
-
for key, value in args.items():
-
args[key] = (str(value), repr(value))[type(value) == str]
-
b = ''
-
if args:
-
b = 'WHERE %s' % ' AND '.join(key + '=' + value
-
for key, value in args.items())
-
-
return ' '.join(['UPDATE', table, a, b])
|
