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()

Why not putting the connection string in a config.ini? Each of you can then have its own config file depending on your environment — Adonis, Apr 09 '18 at 07:30
@Adonis Thanks for the feedback, could you give me an example of it? It's my first time using this so would be awesome with examples! — DanLee, Apr 09 '18 at 07:36
I think the documentation on `configparser` is well explained: https://docs.python.org/3/library/configparser.html Let me know if you need more — Adonis, Apr 09 '18 at 07:55

score 24 · Answer 1 · answered Apr 09 '18 at 15:30

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.)')

score 3 · Answer 2 · answered Apr 09 '18 at 16:36

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)

score 0 · Answer 3 · answered Apr 09 '18 at 13:11

You could use a try / except block, trapping only pyodbc.Error:

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


try:
    conn = pyodbc.connect(
        r'DRIVER={ODBC Driver 13 for SQL Server};'
        r'SERVER=blah;'
        r'DATABASE=blah;'
        r'Trusted_Connection=yes;'
    )
except pyodbc.Error:
    conn = pyodbc.connect(
        r'DRIVER={ODBC Driver 11 for SQL Server};'
        r'SERVER=blah;'
        r'DATABASE=blah;'
        r'Trusted_Connection=yes;'
    )


cursor = conn.cursor()

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

Good luck!

Automatically Detect ODBC Driver using Pyodbc in Python 3

3 Answers3

Linked