dictonary

[Bernard Yue]

Max M wrote:
> 
> Gerhard Häring wrote:
> 
> >>db = _mysql.connect()
> >>string = {
> >>      hello:'wow',
> >>      something:'nothing',
> >>      nothing : 'something',
> >>      number : 5,
> >>      name : 'daniel'}
> >>
> > This is one way to do it, I bet there's a better one:
> >
> > dict = {'name': 'Andy', 'age': 39}
> >
> > columns = ",".join(dict.keys())
> > # I hope using only %s works for MySQLdb. It does work like this for pyPgSQL.
> > placeholders = ",".join(['%(' + k + ')s' for k in dict.keys()])
> > sql = "insert into table(" + columns + ") values (" + placeholders + ")"
> 
> A bit more readable perhaps?
> 
> keys = dict.keys()
> vals = dict.vals()
> sql = "insert into table(%s) values (%s)" % (
>      ','.join(keys),
>      ','.join(list(len(keys)*'?'))
> )
> cursor.execute(sql, vals)
> 

Will this method cause problem when inserting numbers (strings are
quoted, but not for numbers)?  I remember it will (not 100% sure, it's
been a while).  Thats what I've done (with MySQLdb):

    import MySQLdb as DB

    _host, _port, _user, _passwd = 'host', 3306, 'me', 'passwd'
    _insertTemplate = 'INSERT INTO %s (%s) VALUES (%s)'

    _tableName = 'MyTable'
    _myData = {
        hello:'wow',
        something:'nothing',
        nothing : 'something',
        number : 5,
        name : 'daniel'}

    _valueTemplate = ('%s, ' * len( _myData))[:-2]
    _sqlTemplate = _insertTemplate % (_tableName,
            reduce( lambda x, y: '%s, %s' % (x,y) , _myData.keys()),
_valueTemplate)

    try:
        _dbconn = DB.connect( host=_host, port=_port, user=_user,
passwd=_passwd)
        _cursor = _dbconn.cursor()
        _cursor.execute( _sqlTemplate, _myData.values())
        _dbconn.commit()
    except DB.OperationalError, _msg:
	# connection failed
        print 'Warning: %s' % _msg
    except (DB.ProgrammingError, DB.IntegrityError), _msg:
        # insert failed
        print 'Warning: insert failed.\n' % _msg
        print 'sql = %s' % _sqlTemplate
        print 'data = %s' % _myData.values()
    else:
        _cursor.close()


Bernie

-- 
There are three schools of magic.  One:  State a tautology, then ring
the changes on its corollaries; that's philosophy.  Two:  Record many
facts.  Try to find a pattern.  Then make a wrong guess at the next
fact; that's science.  Three:  Be aware that you live in a malevolent
Universe controlled by Murphy's Law, sometimes offset by Brewster's
Factor; that's engineering.
                -- Robert A. Heinlein