1

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?

Bob Wakefield
  • 3,739
  • 4
  • 20
  • 30
  • This is the output: `print(ctypes.windll.ODBCCP32.SQLInstallerError)`. It's a function object. You have to call it. But before going any further, you should check (and comply with) [\[SO\]: C function called from Python via ctypes returns incorrect value (@CristiFati's answer)](https://stackoverflow.com/questions/58610333/c-function-called-from-python-via-ctypes-returns-incorrect-value/58611011#58611011). Also you should add *SQLConfigDataSource*'s arguments into the question, to have a *MCVE*. – CristiFati Sep 17 '21 at 19:45
  • related: https://stackoverflow.com/a/57911646/2144390 – Gord Thompson Sep 17 '21 at 21:46
  • @CristiFati, I haven't yet fully chewed through your post, but I did want to ask a question about MCVE. This isn't an MCVE. This is a FULLY reproducible example where the parameters aren't part of the question. What are you using for guidelines on MCVEs? – Bob Wakefield Sep 19 '21 at 03:59
  • Sorry, I didn't pay enough attention. [\[SO\]: How to create a Minimal, Reproducible Example (reprex (mcve))](https://stackoverflow.com/help/minimal-reproducible-example). – CristiFati Sep 19 '21 at 05:29

1 Answers1

0

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.

Bob Wakefield
  • 3,739
  • 4
  • 20
  • 30