len wrote: > Hi all > > I am writing a python program that inserts records into a database on > XP using mxODBC. > > I need to write a section of code which will create the following SQL > command as an example; > > INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois') > > This statement will be built up using the following code; > > import mx.ODBC > import mx.ODBC.Windows > def insertFromDict(table, dict): > """Take dictionary object dict and produce sql for > inserting it into the named table""" > sql = 'INSERT INTO ' + table > sql += ' (' > sql += ', '.join(dict) > sql += ') VALUES (' > sql += ', '.join(map(dictValuePad, dict)) # ??? this code does > NOT format correctly > sql += ')' > return sql > > def dictValuePad(key): # ??? this code > does Not format correctly > return "'" + str(key) + "'" > > db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test') > c = db.cursor() > insert_dict = {'state':'IL', 'name':'Illinois'} > sql = insertFromDict("statecode", insert_dict) > print sql > c.execute(sql) >
The code below will do what you say that you want to do -- so long as all your columns are strings (varchar or whatever in SQL terms). Otherwise IMHO you would be much better off doing it this way: sql = "insert into policy (type, premium) values(?, ?)" data = ('building', 123.45) cursor.execute(sql, data) for two reasons: (1) let the ODBC kit worry about formatting dates, strings with embedded single quotes, etc (2) it can be more efficient; the sql is constant and needs to be parsed only once (3) [bonus extra reason] the way you are doing it is vulnerable to what's called an "SQL injection attack"; although you have no doubt eyeballed all the data, doing it that way is a bad habit to get into. You should be able to modify the supplied code very easily to produce the sql variety with "?" in it. HTH, John C:\junk>type sqlinsdict.py def sqlquote(astring): return "'" + astring.replace("'", "''") + "'" def insertFromDict(table, adict): """Take dictionary object dict and produce sql for inserting it into the named table. Sample input: insert_dict = {'state':'IL', 'name':'Illinois'} sql = insertFromDict("statecode", insert_dict) Required output: INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois') """ t = [ 'INSERT INTO ', table, ' (', ', '.join(adict.keys()), ') VALUES (', ', '.join(sqlquote(x) for x in adict.values()), ')', ] return ''.join(t) if __name__ == "__main__": tests = [ ('IL', 'Illinois'), ('OH', "O'Hara"), ] cols = ['state', 'name'] for test in tests: the_dict = dict(zip(cols, test)) print the_dict print insertFromDict('statecode', the_dict) C:\junk>sqlinsdict.py {'state': 'IL', 'name': 'Illinois'} INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois') {'state': 'OH', 'name': "O'Hara"} INSERT INTO statecode (state, name) VALUES ('OH', 'O''Hara') -- http://mail.python.org/mailman/listinfo/python-list