Also, it may be easier to use string interpolation, as in: return "INSERT INTO statecode (state, name) VALUES ('%(state)s', '%(name)s')" % insert_dict
...after all necessary escaping, of course. John Machin wrote: > 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