Support pyformat and keyword args

[nhumrich]

It looks like for prepared statements, statements have to be in the form
'select $1 from $2 where baz=$3 with the arguments ('foo', 'bar', 'baz')

It is common in python database api's to support the pyformat int PEP 249. http://legacy.python.org/dev/peps/pep-0249/#paramstyle

Can this support at least one of the param styles in the pep? Preferably pyformat.

Ideally, the parameters could then be passed in as a dictionary instead of a ordered list.

conn.fetch(myquery, {bar='bar', foo='foo'})

[ludovic-gasc]

+1 for pyformat: we use massively this format for our queries, it's the most explicit, at least to me.

[1st1]

I'll let this issue stay open for a while to see if a lot of people is interested in this feature. Adding named parameters will require us to tokenize SQL, which is something I don't want to do unless it's absolutely necessary.

[gigatexal]

Will tokenizing hurt performance? I, too, prefer the dictionary vs ordered list approach.

[sethmlarson]

I can say that if this feature were added I would use it over ordered list.

[gglanzani]

@1st1 Interested here as well. It makes everything so much easier

[treycucco]

@1st1 I'd like to see this implemented as well. It would make maintaining queries easier. With just ordered parameters you have to be very careful when updating queries, and it's easy to get it wrong, especially if you have lots of parameters.

[bobcolner]

named keywords have proven more reliable for me as well. On brand with the python-zen 'explicit > implicit' credo as well.

[imbolc]

Why don't you just use little wrappers like this?

def insert(conn, table, fields=None, **kwargs):                                                                               
    fields = fields or {}                                                                                                     
    fields.update(kwargs)                                                                                                     
    items = list(fields.items())                                                                                              
    keys, values = [x[0] for x in items], [x[1] for x in items]                                                               
    placeholders = ['${}'.format(i) for i, _ in enumerate(values, 1)]                                                         
    query = 'INSERT INTO {} ({}) VALUES ({}) RETURNING id'.format(                                                            
        table, ', '.join(keys), ', '.join(placeholders))                                                                      
    return conn.fetchval(query, *values)

id = await insert(conn, 'users', {'email': 'foo@gmail.com', 'name': 'foo'})                                               
id = await insert(conn, 'users', email='bar@gmail.com', name='bar') 

[Daenyth]

I'd very much like to see named parameters as well - ordered ones have proven error prone to the point where we've made it a code style across the company to use only named parameters unless there's some reason it's impossible.

[thehesiod]

If tokenizing I'd suggest re-using any available code in libpq/psycopg2