Running Python script in Azure DevOps pipelines - module not found error

[brianmich]

Hello.  I have a simple Python script that updates a SQL database and I am running that script in my Azure DevOps release pipeline. I used the classic editor and created a Command Line Script step to install the pyodbc module needed for my Python script to run.  I have a second step that runs the Python inline script, but that step fails with this error:

ModuleNotFoundError: No module named 'pyodbc'

If I add "pip list" to the Command Line Script step, it does not show pyodbc installed.  I believe the issue is that when I install the module, it is installing it in the wrong location.  How do I specify where to install that module?  Am I going about this wrong? 

The command I have in step 1 which executes without error:

pip install pyodbc

Then the Python script step has this code which I know works outside of ADO:

import pyodbc  <<<<<THIS IS THE LINE THAT I GET THE ERROR ON
cnxn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=tcp:myserver,myport;Database=mydb;Uid=myuser;Pwd=mypwd;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')

cursor = cnxn.cursor()
cursor.execute("INSERT INTO Names (ID, Name) VALUES ('0019', 'TEST')")
cnxn.commit()

Thank you for your help!

[#2]

I was able to get past the error by adding this to my Command Line Script:

pip install -r $(System.DefaultWorkingDirectory)/my_working_directory/requirements.txt
requirements.txt file just has this:
pyodbc==4.0.35

But now I have a new error when the Python script executes:

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I added the IP address range for Azure DevOps to the Azure SQL firewall rules thinking that was the issue, but that did not help.

I thought it was a common task to use Python in Azure DevOps pipelines and this would work, but I may be wrong in that assumption. Anyone able to assist?

Thanks!

[#3]

I found that latest issue as well. Driver version 18 is apparently not supported in Azure DevOps agents so I changed it to 17 and it worked. Hope this solution helps someone else that needs to use Python in Azure DevOps pipelines to connect to an Azure SQL database. Maybe my approach was all wrong to begin with, I really don't know, but at least this does work.