Newbie needs Help

[John Machin]

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