Re: RFC: Extend psycopg2.connect to accept all valid parameters?

On Wed, Nov 16, 2011 at 7:39 PM, Fabian Knittel
<fabian(dot)knittel(at)avona(dot)com> wrote:
> Hello Daniele,
>
> Am 16.11.2011 19:56, schrieb Daniele Varrazzo:
>> On Wed, Nov 16, 2011 at 5:56 PM, Fabian Knittel
>> <fabian(dot)knittel(at)avona(dot)com> wrote:
>>> I'm trying to pass the "sslrootcert" connection keyword parameter
>>> through sqlalchemy and psycopg2.
> [...]
>> os.environ['PGSSLROOTCERT'] is (should be) your friend. Also see
>> <http://www.postgresql.org/docs/9.1/static/libpq-envars.html>.
>
> True, this is another possible approach.  I personally don't see it as
> viable solution, because it's not easy to configure via a configuration
> file and you can't really configure multiple connections.

You are right, env variables are less than optimal. I forgot a better
option: if connect() is called with a single string parameter instead
of keyword arguments you can pass any libpq parameter:

In [16]: cnn = psycopg2.connect('host=localhost application_name=foo')
In [17]: cur = cnn.cursor()
In [19]: cur.execute("select application_name from pg_stat_activity")
In [20]: cur.fetchall()
Out[20]: [('foo',)]

I also see that the libpq checks if the keywords is really supported:

In [21]: cnn = psycopg2.connect('host=localhost bax=qux')
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)

/home/piro/dev/psycopg2/build/lib.2.7/<ipython console> in <module>()

OperationalError: invalid connection option "bax"

This is definitely a better solution than the env.

> I agree that option a) is probably the least desirable one.  But what do
> you think of option b)?  Effectively, it would be something along the
> lines of
>
>  dsn = ' '.join(
>      ['%s=%s' % (key, str(val)) for key, val in kwargs.iteritems()])
>
> translated to C (with a few additional translations, e.g. database to
> dbname).  All keyword arguments would be translated to a connection
> string, without explicit checking.  Any unknown keywords would then be
> detected by libpq the same way they are detected when the dsn is
> explicitly given.

Yes, I'd say having a pass-through **kwargs from the connect()
parameters to the PQconnect string is the best solution; psycopg
doesn't really need to know all of them and the error message raised
by the libpq in case of errors is perfectly explicit.

I don't see anything bad against the feature. But: do we really need
the keywords pass-through, given the fact that the connection string
already covers all the required cases? It looks more like syntactic
sugar, and if you have to fix a client to support a new keyword / all
the keywords, they can be fixed by generating a connection string
instead of forwarding the **kwargs. ... mmm... reading back the head
of the thread, as you describe it sqlalchemy would already work using
the **kwargs, whereas it should be patched to pass psycopg a
connection string instead. So it's a matter of patching us (to pass
**kwargs through) or them (to convert the url to a connection string
instead of a doct) it seems...

Thoughts? Patches?

-- Daniele