Title: Auto Generation of SQL Insert Statement Columns and Values from Object
Submitter: Andrew Konstantaras (other recipes)
Last Updated: 2008/05/12
Version no: 1.1
Category: Databases

Description:

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.

Source: Text Source

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) )

Discussion:

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