Newbie needs Help

[Steve Holden]

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