When sharing a Python .exe with colleagues that uses PyODBC to query a database, is an ODBC Driver for SQL Server the only download required?

user_6366770 0
Viewed 883

I have a python script that I have made and developed an .exe with Pyinstaller that uses python's pyodbc library to query and looks like the fake code below. It works on my computer perfectly. I tried to share my .exe with a couple co-workers, who I have double-checked have access to query our databases within Excel after providing them login information. However, they are not able to access our database from within my python .exe.

After testing once, I know that they are unable to access, because the .exe I created outputted error (No suitable driver found. Cannot connect.) as you can see in the code below, because it could not find ODBC Driver 17 for SQL Server on the colleague's computer. I believe Excel uses PowerQuery, so it doesn't have a reliance on an ODBC Driver, but python will require the driver. My question is this -- should simply installing the ODBC Driver 17 for SQL Server from this link work? https://www.microsoft.com/en-us/download/details.aspx?id=56567

The user doesn't have admin rights to download the driver (and I can't test this myself on another computer) and I have limited time and and access to IT resources; otherwise, I would troubleshoot with the user myself, so I'm eager to hear if anyone has any experience with this or has found a similar post/documentation that could be helpful to me. Again, the code is below for reference, but I don't need assistance with that. I need to explain to IT what I need them to do for the users, which is why I was wondering if Microsoft® ODBC Driver 17 for SQL Server® - Windows will be enough.

import pandas as pd
import pyodbc

#Connection and credentials
driver_name = ''
driver_names = [x for x in pyodbc.drivers() if x.endswith(' for SQL Server')]
driver_names

if driver_names:
    driver_name = driver_names[-1]
if driver_name:
    conn_str = f'''DRIVER={driver_name};SERVER='''
else:
    print('(No suitable driver found. Cannot connect.)')

server = '111.111.11.111' 
database = 'database' 
username = 'username' 
password = 'password' 
cnxn = pyodbc.connect(conn_str+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

try:
    account_id_input = input("Select Account ID: ").strip()
    print("Confirming Account ID...")   
    #SQL Query
    df = pd.read_sql_query("""
    Select [Account ID], [Year-Month], [Revenue] from database.dbo.tblaccount
    """,
                            cnxn, params=[account_id_input]
                           )
    print("CONFIRMED")
else: print("Incorrect ID or database connection error")
df.to_csv(f'{account_id_input}_data.csv', index=False)
pandas pyodbc sql-server python
2 Answers

In short, the answer to this question turned out to be "Yes", but I am not deleting as this could be beneficial and useful for people who are researching how to share python/SQL .exe's with other users similar to how you might share an Excel Macro.

Assuming the user is on our company network, the only requirements were that the user:

1) downloads my python .exe file that utilizes the libraries: a) pyodbc and b) pandas

2) downloads an ODBC Driver for SQL Server, depending on your company's SQL Server setup. In my case, I had the user download ODBC Driver 17 for SQL Server: https://www.microsoft.com/en-us/download/details.aspx?id=56567

This means that they don't have to fully install SQL Server, just the driver!

user_63667700

In short, yes ODBC driver would be only additional download required for pyodbc.

PyODBC is an open-ended Python DB-API that accepts any compliant ODBC driver from any data source (Oracle, SQL Server, PostgreSQL, SalesForce, Quickbooks, etc.). ODBC drivers serve as a middle-layer between client (i.e., Python) and database server and, like any software, they can be developed by proprietary vendors (e.g., Oracle, Microsoft), open-source developers/groups, or third-parties. In addition, ODBC is a recognized technology among many platforms like Excel and languages.

Unlike other Python DB-APIs (pymysql, cxOracle, psycopg2), pyodbc as you recognize has one requirement: the specified ODBC driver in code must be installed beforehand on the client machine (not simply the Python library installed). By the way, this is very similar to JDBC drivers with Python's jaydebeapi and Microsoft does maintain JDBC drivers for SQL Server.

Your colleagues receive the following error:

No suitable driver found. Cannot connect.

because that specific ODBC driver is not installed on their machines but is installed on your machine. Have them check what versions are available to them with the command:

pyodbc.drivers()

As background Microsoft maintains multiple ODBC drivers (of varying versions that are backwards compatible) for Windows, Linux, and MacOS machines. Because MS Excel does not have any native database layer, it usually connects with ODBC or OLEDB but with a library like DAO or ADO (counterpart to Python's library, pydobc). Possibly, your other colleagues maintain other versions such as:

'ODBC Driver 11 for SQL Server'
'ODBC Driver 13 for SQL Server'
'SQL Server'
'SQL Native Client'
'SQL Server Native Client 11.0'

With that said, if you are going to circulate your code integrated into an executable to other users, do not hard-code any ODBC credentials since as you demonstrate, CPU environments vary considerably. Instead, consider below three solutions:

  1. Data Source Name: Create a Data Source Name (DSN) individually tailored to each user machine that can vary between different drivers, servers, username, passwords, etc.

    cnxn = pyodbc.connect(dsn="myDatabase")

  2. Configuration File: Use configuration files like yaml or json with all credentials listed in a secured folder and then integrated into connection. Many online tutorials on this.

    YAML

    db:
 driver: 'ODBC Driver Name'
 server: '111.111.11.111' 
 database: 'database' 
 username: 'username' 
 password: 'password'

    Python

    import yaml

with open('file.yaml') as f:
    db_creds = yaml.load(f)

...
cnxn = pyodbc.connect(driver=db_creds['driver'], host=db_creds['server'],
                      uid=db_creds['username'], pwd=db_creds['password'], 
                      database=db_creds['database'])

  3. Environment Variables: Use environment variables permanently or temporarily set on user's machine before connection.

    import os

...
cnxn = pyodbc.connect(driver = os.environ.get('MSSQL_DRIVER'),
                      host = os.environ.get('MSSQL_SERVER'),
                      uid = os.environ.get('MSSQL_USER'), 
                      pwd = os.environ.get('MSSQL_PWD'), 
                      database = os.environ.get('MSSQL_DB'))

Browse the web for documentation, tutorials, blogs, even other SO posts on how to troubleshoot. Above are simple examples that may need adjustment.

user_14224510
Related