Newbie needs Help

[johnzenger at gmail.com]

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