pyodbc connect string

[Ben Finney]

Larry Martell <larry.martell at gmail.com> writes:

> I am having a problem building a connect string for pyodbc. It works
> when everything is hard coded, but if I build the connect string it
> fails.
>
> This works:
>
> pyodbc.connect('DRIVER=FreeTDS;' 'SERVER=xx.xx.xx.xx;' 'PORT=1433;'
> 'DATABASE=blah;' 'UID=foo;' 'PWD=bar;')

This calls the function with a single string,
"DRIVER=FreeTDS;SERVER=xx.xx.xx.xx;PORT=1433;DATABASE=blah;UID=foo;PWD=bar;".

Remember that consecutive, whitespace-separated, quote-delimited
fragments specify the construction of a single string literal::

    >>> 'foo'
    'foo'
    >>> 'foo' 'bar'
    'foobar'
    >>> 'foo' 'bar' 'baz'
    'foobarbaz'

See the reference for how this concatenation occurs
<URL:https://docs.python.org/3/reference/lexical_analysis.html#string-literal-concatenation>.

> But this does not:
>
> pyodbc.connect(conn_str)
>
> Where conn_str is:
>
> 'DRIVER=FreeTDS;' 'SERVER=xx.xx.xx.xx;' 'PORT=1433;' 'DATABASE=blah;'
> 'UID=foo;' 'PWD=bar;'

This string is different, because it contains a whole lot of quotation
marks and whitespace not in the string you show in the first example.

> conn_str is constructed with:
>
>  conn_str = "'DRIVER=%s;' 'SERVER=%s;' 'PORT=%s;' 'DATABASE=%s;'
> 'UID=%s;' 'PWD=%s;'" \
>                 % (RECIPE_DB['DRIVER'], RECIPE_DB['SERVER'],
> RECIPE_DB['PORT'], RECIPE_DB['DATABASE'],
>                    RECIPE_DB['USER'], RECIPE_DB['PASSWORD'])

Remove the extraneous quotes and whitespace, which were not in the
original string you showed above.

On a separate point: Since you have string keys for the mapping, you can
make the interpolation more readable::

    conn_str = (
            "DRIVER=%(DRIVER)s;SERVER=%(SERVER)s;PORT=%(PORT)s;"
            "DATABASE=%(DATABASE)s;UID=%(USER)s;PWD=%(PASSWORD)s;"
            ) % RECIPE_DB

since the named placeholders will be looked up by key in the ‘RECIPE_DB’
mapping.

There are other improvements to suggest, but I don't want to distract
from the main point of the post.

-- 
 \          “The fact that I have no remedy for all the sorrows of the |
  `\     world is no reason for my accepting yours. It simply supports |
_o__)  the strong probability that yours is a fake.” —Henry L. Mencken |
Ben Finney