Search code examples
sql-serverpython-3.xwindowsdllodbc

How do you manage DSNs on Windows with Python 3?

I prefer to connect to databases with DSNs. I'm not a fan of putting user names and passwords in code or config files nor do I appreciate the trusted connection approach.

When I google how to MANAGE DSNs with Python, I wind up with some variation of the below.

import ctypes

ODBC_ADD_DSN = 1        # Add data source
ODBC_CONFIG_DSN = 2     # Configure (edit) data source
ODBC_REMOVE_DSN = 3     # Remove data source
ODBC_ADD_SYS_DSN = 4    # add a system DSN
ODBC_CONFIG_SYS_DSN = 5 # Configure a system DSN
ODBC_REMOVE_SYS_DSN = 6 # remove a system DSN

def create_sys_dsn(driver, **kw):
    """Create a  system DSN
    Parameters:
        driver - ODBC driver name
        kw - Driver attributes
    Returns:
        0 - DSN not created
        1 - DSN created
    """
    nul = chr(32)
    attributes = []
    for attr in kw.keys():
        attributes.append("%s=%s" % (attr, kw[attr]))


    if (ctypes.windll.ODBCCP32.SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, driver, nul.join(attributes))):
        return True
    else:
        print(ctypes.windll.ODBCCP32.SQLInstallerError)
        return False

if __name__ == "__main__":
    if create_sys_dsn("SQL Server",SERVER="server name", DESCRIPTION="SQL Server DSN", DSN="SQL SERVER DSN", Database="ODS", Trusted_Connection="Yes"):
        print ("DSN created")
    else:
        print ("DSN not created")

When you run this, I wind up with this as output:

<_FuncPtr object at 0x0000028E274C8930>

I have two issues.

  1. I'm not used to working with the OS through an API and I can't find much documentation or usage examples that aren't in C++. That said, the code runs, it just never returns true.
  2. I can't figure out how to get it to kick out error information so I can diagnose the problem. This code leaves no footprint in the logs so nothing shows up in event viewer as something being wrong.

How can I troubleshoot this? Am I even doing the right thing by taking this route? What exactly IS best practice for connecting to databases with Python?

Solution

  • There are actually TWO answers to this question.

    1. You can do it by calling PowerShell scripts from Python.

    2. You don't. You use trusted connections in your connection string.

    I went with option 2.