pyodbc on Azure

Question

I have a python webapp in Azure which I would like to read from an SQLServer also in Azure in the same resource group. Following this example Connecting to Microsoft SQL server using Python, I have added pyodbc to my requirements.txt, the deployment to Azure fails complaining that it doesn't have the correct version of C++ redistributable (9.0) available. Is there anything that can be done about this, or is a different architecture required (and if so, which?)?

Is the requirements.txt file for pip? If so, and you simply added `pyodbc==4.0.21` then pip may not be recognizing the Azure environment and trying to install pyodbc from source. In that case perhaps you could upload the appropriate wheel file and have pip install that instead, e.g., `pip install --no-index --find-links=/local/wheels -r pyodbc_requirements.txt` — Gord Thompson, Mar 03 '18 at 18:58
Yes that sounds like it could work, however I'm not sure what platform the webapp is running in, which I believe is a requirement of knowing what the correct wheel file is? — Madden, Mar 03 '18 at 19:06
Can you have your web app (or a standalone script) display the result returned by `sys.version`? — Gord Thompson, Mar 03 '18 at 19:12
Sadly it doesn't appear so; the cli for the webapp won't even open a python shell — Madden, Mar 03 '18 at 19:28
The reference to C++ 9.0 suggests that you are running Python 2.7.x under Windows. So, you could take a guess and try the [64-bit wheel for that platform](https://pypi.python.org/packages/40/43/643870fdad2665671c2adc31a6477cae916e7856710bc0f7ffc1619e13d8/pyodbc-4.0.21-cp27-cp27m-win_amd64.whl#md5=6bc9c95c14304360f0a0b19c03000112) and if that doesn't work then you could try the [32-bit wheel](https://pypi.python.org/packages/27/a0/fc42f78ffaf94871f67de3e499158ff382eb7159a361fa4c7f7edfa71e70/pyodbc-4.0.21-cp27-cp27m-win32.whl#md5=5aff0d160a23894281e4b568874e7558). — Gord Thompson, Mar 04 '18 at 00:23

score 2 · Answer 1 · answered Mar 05 '18 at 07:55

I tried to access Azure SQL Database in my flask web app. You could refer to my working code.

view.py

from datetime import datetime
from flask import render_template
from jaygongflask import app
import pyodbc

@app.route('/database')
def database():
    """Renders the about page."""
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=***.database.windows.net;DATABASE=***;UID=***;PWD=***')
    cursor = cnxn.cursor()
    cursor.execute("select * from dbo.Student")
    row = cursor.fetchall()
    #for r in row:
     #   print r
    return render_template(
        'database.html',
        title='Database',
        year=datetime.now().year,
        message='Database query result.',
        queryResult = row
    )

web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="WSGI_HANDLER" value="jaygongflask.app"/>
    <add key="PYTHONPATH" value="D:\home\site\wwwroot"/>
    <add key="WSGI_LOG" value="D:\home\LogFiles\wfastcgi.log"/>
  </appSettings>
  <system.webServer>
    <handlers>
      <add name="PythonHandler" path="*" verb="*" modules="FastCgiModule" scriptProcessor="D:\home\Python361x64\python.exe|D:\home\Python361x64\wfastcgi.py" resourceType="Unspecified" requireAccess="Script"/>
    </handlers>
  </system.webServer>
</configuration>

Install pyodbc package

My web app works with python361x64 extension. Please refer to the steps I did as below:

Step 1 : Create azure web app and add Extensions(here is Python 3.6.1 x64)

Step 2 : Publish your flask project and add the web.config.

web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="WSGI_HANDLER" value="<Your Project Name>.app"/>
    <add key="PYTHONPATH" value="D:\home\site\wwwroot"/>
    <add key="WSGI_LOG" value="D:\home\LogFiles\wfastcgi.log"/>
  </appSettings>
  <system.webServer>
    <handlers>
      <add name="PythonHandler" path="*" verb="*" modules="FastCgiModule" scriptProcessor="D:\home\Python361x64\python.exe|D:\home\Python361x64\wfastcgi.py" resourceType="Unspecified" requireAccess="Script"/>
    </handlers>
  </system.webServer>
</configuration>

Step 3: Switch to the Kudu CMD and commands cd Python361x64 and touch get-pip.py and copy the content of the url https://bootstrap.pypa.io/get-pip.py into the get-pip.py via Edit button, then run python get-pip.py to install the pip tool.

Step 4 : Install pyodbc package or any packages you need via python -m pip install pyodbc

More deployment details , please refer to this tutorial.

Get query result

Access the url http://***.azurewebsites.net/database .

Hope it helps you.Any concern, please let me know.

pyodbc on Azure

1 Answers1

Linked