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:[CODE=python]def MySQLSelect(tab le, 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(kwa rgs)
for key, value in args.items():
args[key] = (str(value), repr(value))[isinstance(valu e, 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])[/CODE]
The INSERT and UPDATE helpers now convert None to NULL:[CODE=python]
def MySQLInsert(tab le, 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':v alue items.
**kwargs is the same but passed in as column_name=val ue"""
args = argdict.copy() # don't modify caller dictionary!
args.update(kwa rgs)
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(tab le, 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:va lue 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(kwa rgs)
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])[/CODE]
The SELECT helper:[CODE=python]def MySQLSelect(tab le, 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(kwa rgs)
for key, value in args.items():
args[key] = (str(value), repr(value))[isinstance(valu e, 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])[/CODE]
The INSERT and UPDATE helpers now convert None to NULL:[CODE=python]
def MySQLInsert(tab le, 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':v alue items.
**kwargs is the same but passed in as column_name=val ue"""
args = argdict.copy() # don't modify caller dictionary!
args.update(kwa rgs)
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(tab le, 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:va lue 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(kwa rgs)
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])[/CODE]