CODEWITHSUNDEEP
pythonmysql
David542
David542

Reputation: 110382

String formatting on SQL insert using dict

The following is how I usually add in params in MySQLdb:

cursor.execute('INSERT INTO main_territory (code, name) VALUES (%s, %s)', (item[0], item[1]))

Is there a way to make the string formatting more intuitive? For example, if I have 50 args in the INSERT. Something like:

cursor.execute('''INSET INTO main_territory (code, name) VALUES ({code}, {name})''',
                  {code=item[0], name=item[1}
               )

Upvotes: 0

Views: 1069

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123410

This depends on the database driver. Usually, if positional arguments (%s) are supported, then named arguments could be to, using the %(name)s format:

cursor.execute(
    '''INSERT INTO main_territory (code, name) VALUES (%(code)s, %(name)s)''',
    {'code': item[0], 'name': item[1]})

The parameter values then are passed in as a dictionary.

The most commonly used MySQL database adapter, MySQLdb supports this style.

Other database adapters use ? and :name as the positional and named arguments; you can query the style used with the paramstyle attribute on the module:

>>> import MySQLdb
>>> MySQLdb.paramstyle
'format'

but because most drivers support both positional and named styles, they usually just name one ('format' or 'qmark') while also supporting the named variants. Always consult the documentation to verify this.

Upvotes: 1

Related Questions