Connecting to databases - mkleehammer/pyodbc GitHub Wiki

Connections to databases are made through the use of connection strings in the pyodbc.connect() function, but the most important thing to remember is that pyodbc does not even look at the connection string. It is passed directly to the database driver unmodified (through SQLDriverConnect). Connection strings are therefore driver-specific and all ODBC connection string documentation should be valid. Also, anything ODBC supports, pyodbc supports, including DSN-less connections and file DSNs.

General connection string information for most databases: http://www.connectionstrings.com

Important formatting rules: http://www.connectionstrings.com/formating-rules-for-connection-strings/

Note that although pyodbc does not modify the connection string, there are still a few layers of escaping which need to be taken into account, since the string passes through them before reaching the driver. This includes Python and the driver manager. Using correct escaping is particularly important for values in connection strings which are likely to contain special characters, such as passwords. This also means that the naive approach of concatenating the keys and values together in connection strings without taking into account the possibility of special characters will fail if such characters are present.

In Python, the usual string escaping rules apply: backslashes and quotes which are to appear literally in the connection string must be escaped, and { and } (the connection string quoting characters) also need to be doubled if they are to be used in a formatted string literal. See the Python documentation on this topic for more detailed information.

The driver manager, and thus ODBC drivers, interpret the connection string as a set of semicolon-delimited key-value pairs. The keys are either defined by the ODBC standard (such as DSN, DRIVER, and FILEDSN) or specific to the ODBC driver. Values may be "bare", i.e. specified directly, or "quoted", enclosed in { and }; bare values cannot contain leading/trailing spaces or the semicolon, while quoted values can. A quoted value which itself contains a } must have every occurrence of } doubled, i.e. } becomes }} (occurrences of { inside a quoted value can be as-is, since the end of a quoted value is indicated by an unescaped }.) For more detailed information on the ODBC connection string syntax, see the official Microsoft documentation on the subject. This syntax is also applicable to unixODBC.

Beyond Python and driver manager escaping rules, ODBC drivers may themselves define additional syntax in the connection string, such as for values which accept binary data. See the documentation of the ODBC driver you are using for the details of any such functionality.

When troubleshooting connectivity issues, especially when the error message hints at an incorrect key or value, printing out the connection string immediately before it is passed to pyodbc.connect() is a good way to determine if escaping (or the lack thereof) is at fault.