Automatically Detect ODBC Driver using Pyodbc in Python 3

Question

Hi I'm currently using pyodbc in Python 3 and I'm trying to figure out a way to automatically detect ODBC Driver by not having to change manually when used by different computers. The reason is because my computer has ODBC Driver 13 and another friend's computer has ODBC Driver 11 so whenever we run the script from our side, we have to manually change the version first in order to execute the process.

Can anyone help to solve this problem? Below is my sample code.

Thank you

import os
import csv
import pyodbc
import datetime
from dateutil.relativedelta import relativedelta


conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 13 for SQL Server};'
    r'SERVER=****;'
    r'DATABASE=****;'
    r'Trusted_Connection=yes;'
    )

cursor = conn.cursor()

cursor.execute("Select * From Table1")
dData = cursor.fetchall()

Solution 1

You could use the pyodbc.drivers() method to retrieve the list of available drivers and then select the one you need, e.g.,

driver_name = ''
driver_names = [x for x in pyodbc.drivers() if x.endswith(' for SQL Server')]
if driver_names:
    driver_name = driver_names[0]
if driver_name:
    conn_str = 'DRIVER={}; ...'.format(driver_name)
    # then continue with ...
    # pyodbc.connect(conn_str)
    # ... etc.
else:
    print('(No suitable driver found. Cannot connect.)')

Solution 2

As @Adonis suggests, consider building a config file specific to each user and then read it in to connect to database. This also provides more security as code does not immediately expose credentials:

Build Config .ini file (to be run once specific to each user; adjust write path to needed folder)

import configparser
config = configparser.ConfigParser()
config['SQL_SERVER'] = {'DRIVER': 'ODBC Driver 13 for SQL Server',
                        'SERVER': '****',
                        'DATABASE': '****',
                        'Trusted_Connection': 'yes',
}

with open('db_connect.ini', 'w') as configfile:
    config.write(configfile)

Database Connection (regular connection, adjust read path to needed folder)

import configparser
import pyodbc

config = configparser.ConfigParser()    
config.read('db_connect.ini')

constr = 'DRIVER={{{drv}}};SERVER={srv};DATABASE={db};Trusted_Connection={tc};'\
              .format(drv=config['SQL_SERVER']['DRIVER'],
                      srv=config['SQL_SERVER']['SERVER'],
                      db=config['SQL_SERVER']['DATABASE'],
                      tc=config['SQL_SERVER']['Trusted_Connection'])

conn = pyodbc.connect(constr)