johnzenger at gmail
Aug 16, 2006, 9:09 PM
Post #5 of 20
(700 views)
Permalink
|
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
|
