Unable to make database connection using python adodbapi library

Question

I am trying to make SQL Server database connection using python library adodbapi and latest OLEDB provider using below code-

import adodbapi
import configparser

def try_connection():    
    _SERVER_NAME = "SERVERNAME"
    _DATABASE = "TESTDB"
    _USERNAME = "TERSTUSER"
    _PASSWORD = "TESTPWD"  

    conn = adodbapi.connect("PROVIDER=MSOLEDBSQL;Data Source={0};Database={1};UID={2};PWD={3};".format(_SERVER_NAME,_DATABASE,_USERNAME,_PASSWORD))
    cursor = conn.cursor()

print( try_connection() )

But, I am unable to make connection. I am getting below huge trace error which I am unable to figure out:

Traceback (most recent call last):
  File "C:\Program Files (x86)\Python 3.5\lib\site-packages\adodbapi\adodbapi.py", line 97, in make_COM_connecter
    c = Dispatch('ADODB.Connection') #connect _after_ CoIninialize v2.1.1 adamvan
NameError: name 'Dispatch' is not defined

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Program Files (x86)\Python 3.5\lib\site-packages\adodbapi\adodbapi.py", line 116, in connect
    co.connect(kwargs)
  File "C:\Program Files (x86)\Python 3.5\lib\site-packages\adodbapi\adodbapi.py", line 266, in connect
    self.connector = connection_maker()
  File "C:\Program Files (x86)\Python 3.5\lib\site-packages\adodbapi\adodbapi.py", line 99, in make_COM_connecter
    raise api.InterfaceError ("Windows COM Error: Dispatch('ADODB.Connection') failed.")
adodbapi.apibase.InterfaceError: Windows COM Error: Dispatch('ADODB.Connection') failed.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "TestAdodb.py", line 41, in <module>
    print( try_connection() )
  File "TestAdodb.py", line 18, in try_connection
    conn = adodbapi.connect("PROVIDER=MSOLEDBSQL;Data Source={0};Database={1};UID={2};PWD={3};".format(_SERVER_NAME,_DATABASE,_USERNAME,_PASSWORD))
  File "C:\Program Files (x86)\Python 3.5\lib\site-packages\adodbapi\adodbapi.py", line 120, in connect
    raise api.OperationalError(e, message)
adodbapi.apibase.OperationalError: (InterfaceError("Windows COM Error: Dispatch('ADODB.Connection') failed.",), 'Error opening connection to "PROVIDER=MSOLEDBSQL;Data Source=SERVERNAME;Database=TESTDB;UID=TERSTUSER;PWD=TESTPWD;"')

Please can anyone let me know what is wrong/missing in my connection string?

Answer 1

Quoting this from pypi,

Prerequisites:

• C Python 2.7 or 3.5 or higher and pywin32 (Mark Hammond's python for windows extensions.) or Iron Python 2.7 or higher. (works in IPy2.0 for all data types except BUFFER)

Installation:

• (C-Python on Windows): Install pywin32 ("pip install pywin32") which includes adodbapi.
• (IronPython on Windows): Download adodbapi from http://sf.net/projects/adodbapi. Unpack the zip. Open a command window as an administrator. CD to the folder containing the unzipped files. Run "setup.py install" using the IronPython of your choice.

Have you installed all the prerequisites for the adodbapi library ?

I was able to reproduce the error if I didn't have the pywin32 library installed.

Install pywin32 library using the command below :

pip install pywin32