Automates the creation of SQL INSERT statements for the "simple" attributes in a python object by creating a string of an object's attribute names and a corresponding string of that object's attribute values. Simple attributes are those that are one of the following types: string, int, long, float, boolean, None.
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 | import types
# Andrew Konstantaras
# konsta@speakeasy.org
# 12 May 2008
# Feel free to use with attribution where appropriate. If you find any errors or make any improvements, please make those freely available
# (again, where appropriate).
#
# These functions are designed to make SQL insert statements from user defined objects. An object is passed to the main
# function makeObjInsertStrings along with a tuple of the valid object names and the function will return a 3 value tuple.
# If the object is not an instance of the types listed in the passed tuple of valid object names, the 3 value tuple returned is None, None, None
# If a valid object is returned, the returned tuple contains:
#
# strCols a string of the the attribute names of the object that are of type Boolean, Int, Long,
# Float, StringType, StringTypes, None
# strVals a string of the corresponding values of the above object separated by commas (each string
# surrounded with dblquotes, numbers are not)
# lstExcludedAttrib a list of attributes that were excluded from the list because they were not of a valid type
#
# Current expects a tuple containing all the valid objects.
#
# If the default blnUseParens is set to False, the strCols and strVals will not be surrounded with parens, otherwise
# parens will be included
#
# ***To be implemented****
# I'd like to create code that will identify all the user defined objects that exist at the time this function is called, but
#I kept running into a snags, so I made the list passed parameter with a default of none to allow for the code to be added nicely.
#
# If the default blnGetAllAttrib is set to False, then only valid attributes with values (i.e., empty strings and None not included)
#will appear in the strCols and strVals
def makeObjInsertStrings( obj, tplObjects = None, blnUseParens=True, blnGetAllAttrib=True ):
# Returns a 3 val tuple, the first two of which are strings which can be dropped into a MySQL Insert statement for (1) column names and (2) values
if not tplObjects:
return None, None, None
if isinstance( obj, tplObjects ): #find out what got passed - valid objects must be included in tuple tplObjects
strDblQuote = '"'
lstCols = list()
lstVals = list()
lstExcludedAttrib = list()
dctObj = vars( obj )
lstObjVarNames = dctObj.keys()
if blnGetAllAttrib:
tplValidTypes = ( types.BooleanType, types.FloatType, types.IntType, types.LongType, types.StringType, types.StringTypes, types.NoneType )
for varName in lstObjVarNames:
val = dctObj[ varName ]
if isinstance( val, tplValidTypes ):
lstCols.append( varName )
if val or val == 0:
lstVals.append( dctObj[ varName ] )
else:
lstVals.append('')
else:
lstExcludedAttrib.append( varName )
if blnUseParens:
strCols = joinListItems( lstCols )
strVals = joinListItems( lstVals )
else:
strCols = joinListItems( lstCols, blnUseParens=False )
strCols = joinListItems( lstVals, blnUseParens=False )
strCols = strCols.replace('"', '')
return strCols, strVals, lstExcludedAttrib
else:
print 'No object passed.'
return None, None, None
def getValueStrings( val, blnUgly=True ):
#Used by joinWithComma function to join list items for SQL queries.
#Expects to receive 'valid' types, as this was designed specifically for joining object attributes and nonvalid attributes were pulled.
#If the default blnUgly is set to false, then the nonvalid types are ignored and the output will be pretty, but the SQL Insert statement will
#probably be wrong.
tplStrings = (types.StringType, types.StringTypes )
tplNums = ( types.FloatType, types.IntType, types.LongType, types.BooleanType )
if isinstance( val, tplNums ):
return '#num#'+ str( val ) + '#num#'
elif isinstance( val, tplStrings ):
strDblQuote = '"'
return strDblQuote + val + strDblQuote
else:
if blnUgly == True:
return "Error: nonconvertable value passed - value type: %s" % type(val )
else:
return None
def joinListItems( lstStart, strDelim = ',', strNumDelim='#num#', blnUseParens = True ):
#Replicates the join function associated with arrays in other languages, allowing for strings and numbers to be joined without converting the nums to strings
#Created specifically for SQL Insert Statement generation. Currently only allows for items to be separated by a comma.
#
# ***To be implemented:
# Allow for additional delimiters to join the list items.
if strDelim == ',':
strResult = reduce( joinWithComma, lstStart )
strResult = strResult.replace(strNumDelim+'"', '')
strResult = strResult.replace(strNumDelim, '')
strResult = '(' + strResult + ')'
strResult = strResult.replace('", ")', '", "")')
strResult = strResult.replace(', ",', ', "",')
strResult = strResult.replace('"", ",', '"", "",')
if strResult[0:3] == '(",':
strResult = '("",' + strResult[3:]
if not blnUseParens:
strResult = strResult[1:len(strResult)-1]
return strResult
def joinWithComma( x, y ):
strX = getValueStrings( x )
strY = getValueStrings( y )
if strX:
if strY:
strResult = strX + ', ' + strY
strResult = strResult.replace('""', '"')
else:
strResult = x
else:
if strY:
strResult = y
else:
strResult = ""
return strResult
####Main with simple test
if __name__ == '__main__':
class simple():
def __init__(self):
self.Name = ""
self.Codes = list()
self.NumCodes = 0
self.EntryType = None
self.OtherValue = 0
self.Comment = None
t = simple()
t.Name = "MyName"
t.Comment = ""
t.NumCodes = 1
t.OtherValue = 0
tplObjects = ( simple ) #I've only defined one class, so that's all I'm passing to the makeObjInsertStrings function
strCols, strVals, lstExcluded = makeObjInsertStrings( t, tplObjects )
print 'Columns: %s\nValues: %s' % (strCols, strVals )
print 'List of Attributes ignored:'
for attr in lstExcluded:
print " Name of attribute in t: ", attr, ' - which is of type ', type( eval("t."+attr) )
|
First, I'd like to thank everyone for sharing their recipes. It has made my process of learning Python much easier.
This is my first shared recipe, so I apologize in advance if it isn't up to snuff, but I thought someone else might find some value in it. I've identified places where I might extend it in the future.
Any comments or suggestions (including, but not limited to flames) are appreciated. I hope at least one person finds this helpful.
I created these functions to facilitate the creation of SQL Insert statements from objects. The function is designed to generate strings to be copied into a SQL statement of the form:
INSERT INTO SOMETABLE (colname1, colname2, . . .)
VALUES (col1value, col2value, . . . )
The first two values in the returned tuple ( strCols and StrVals ) can be pasted into the above statement, which, when assigned to a string variable, would look like:
strSQLStatement = "INSERT INTO SOMETABLE " + strCols + " Values " + strValues
(Note: by default, the function returns strCols and strVals already enclosed in parentheses.)
The resulting statment can then be passed to SQL via a cursor.execute() command. It does assume that there exists a db connection and that a database has been chosen that has a table named SOMETABLE that has column names that correspond exactly to the attribute names of the object passed to the function.
If an invalid object is passed, the function returns the tuple None, None, None.
The function currently requires at least two parameters, an instance of an object and a tuple of all objects that you want to store in a SQL table.
I hope to add a feature in the future that will allow the valid object tuple to be optional and the default would be all user-defined objects in the current namespace.
The third value in the tuple returned by the function is a list of the object attribute names that were skipped because they were not either a string, number, boolean or null value. Lists, dictionaries, tuples and other objects are excluded from both lists (as they won't make much sense to a normal SQL table).
The values in strVals will be separated by commas and string values will be surrounded with double quotes, numbers will not.
Please feel free to send comments/suggestions/problems to me directly (konsta@speakeasy.org) or just post them here.
---Andrew
