Parameterize pyodbc connect string using keyword arguments from my qodbc API?

Question

Help me to understand if I can parameterize my connection string for a pyodbc connection to this qodbc interface for QuickBooks:

pyodbc.connect(r'DSN=qremote_dsn;IPAddress=192.168.0.50;Port=4500;RemoteDSN=login_dsn;OpenMode=F;OLE DB Services=-2;', autocommit=True)

I have several different DSNs, RemoteDSNs and servers which I'd like to loop over. Several SO posts (here and here) point to this code.google documentation suggesting I can use strings, keywords, or both with pyodbc's connect function.

pyodbc works with many different odbc APIs, so how do determine if any of the pyodbc keywords will map to my required qodbc keywords?

My search for the qodbc keywords in the pyodbc documentation returns no results. Must I conclude f-strings are my only option?

Gord Thompson · Accepted Answer · 2020-05-14 22:14:09Z

2

Must I conclude f-strings are my only option?

Not at all.

pyodbc is built to deal with any ODBC driver, so it does not identify which keywords are "legal" and which ones arent. As explained here there are a few keywords that are specified by the DBAPI spec, and a few keywords reserved for pyodbc internal use, and they "are not passed to the odbc driver" implying that other keywords are passed to the ODBC driver.

Example: When I use this connect call ...

cnxn = pyodbc.connect(
    driver="ODBC Driver 17 for SQL Server",
    server="192.168.0.179,49242",
    database="myDb",
    uid="sa", pwd="_whatever_",
    trusted_connection="no"
)

... the ODBC trace shows that this is the connection string passed to the driver

[ODBC][2404][1589493655.363466][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0xf7d9c0
            Window Hdl = (nil)
            Str In = [driver=ODBC Driver 17 for SQL Server;server=192.168.0.179,49242;database=myDb;uid=sa;pwd=_whatever_;trusted_connection=no;][length = 122 (SQL_NTS)]

Note that trusted_connection is specific to SQL Server.

answered May 14, 2020 at 22:14

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

xtian Over a year ago

Hmm. I was naively going in that direction until I ran across this param: OLE DB Services <-- has spaces.

Gord Thompson Over a year ago

Well, yeah, that's a "complication". ☺️ I use SQL Server ODBC quite a lot and I've never needed to use that connection string parameter. What do you use it for? Do you need to pass different values to it each time?

xtian Over a year ago

Ok. So looks like I might need to experiment with string + keywords; And answering your question, details in the OP, but each connection is to a different QuickBooks company file from which I need to obtain weekly reports.

Gord Thompson Over a year ago

Sure, but do you ever need to use an OLE DB Services value other than -2?

xtian Over a year ago

Thank you for your patience helping me see how to solve this, pyodbc.connect('OpenMode=F;OLE DB Services=-2;', [kwargs])

Collectives™ on Stack Overflow

Parameterize pyodbc connect string using keyword arguments from my qodbc API?

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related