SQL帮助器函数更新
以下是My(和我的一样)SQL助手函数的最新版本。如果您使用它们,请随意重命名它们。SELECT助手:
选择 | 换行 | 行号
- 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])
INSERT和UPDATE助手现在将None转换为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])