3

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?

xtian
  • 2,765
  • 7
  • 38
  • 65

1 Answers1

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Hmm. I was naively going in that direction until I ran across this param: `OLE DB Services` <-- has spaces. – xtian May 15 '20 at 00:26
  • 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? – Gord Thompson May 15 '20 at 12:37
  • 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. – xtian May 15 '20 at 17:13
  • 1
    Sure, but do you ever need to use an `OLE DB Services` value other than `-2`? – Gord Thompson May 15 '20 at 18:21
  • Thank you for your patience helping me see how to solve this, `pyodbc.connect('OpenMode=F;OLE DB Services=-2;', [kwargs])` – xtian May 15 '20 at 22:58