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)
>
> I copied this code off of ASP and I sure it worked for his particular
> circumstance but I need to format up the VALUE clause just a bit
> different.
>
ASP code frequently makes the mistake of bulding SQL statements that
way. I suspect this is because the ASP ADO model makes it difficult to
produce paramtereized queries. In Python, however, the position is very
different, and you should always try to separate the data from the
fieldnames.
> I will be working from a dictionary which will be continualy update in
> another part of the program and this code is working.
>
Well, assuming you would rather be free of SQL inhection errors you
would be much better advised to do something like this:
>>> def insertFromDict(table, d): vector
... """Return SQL statement and data vector for insertion into table."""
... fields = d.keys()
... sql = 'INSERT INTO %s (%s) VALUES(%s)' % (
... table, ",
... ".join(fields),
... ", ".join("?" for f in fields))
... return sql, d.values()
...
>>> sql, data = insertFromDict("statecode",
... {"state": "IL", "name": "Illinois"})
>>> sql
'INSERT INTO statecode (state, name) VALUES(?, ?)'
>>> data
['IL', 'Illinois']
>>>
Then you make the insertion into the database using
c.execute(sql, data)
The other principal advantage of this technique is that you don't need
to discriminate between numeric and string fields, since they are both
handled the same way. You also get better efficiency if you run with the
same fields many times, as the DBMS will (if it's sufficiently
advanced) use the already-prepared version of the statement rather than
recompiling it repeatedly.
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
--
http://mail.python.org/mailman/listinfo/python-list
