CODEWITHSUNDEEP
pythonpyodbcazure-sql-databaseazure-machine-learning-servicecortana-intelligence
marnun
marnun

Reputation: 808

What is the name of the driver to connect to Azure SQL Database from pyodbc in Azure ML?

I'm trying to create a 'Reader' alternative to read data from Azure SQL Database using the 'Execute python script' module in Azure ML. While doing so, I'm trying to connect to Azure SQL using pyodbc library.

Here's my code:

def azureml_main(dataframe1 = None, dataframe2 = None):
    import pyodbc   
    import pandas as pd

    conn = pyodbc.connect('DRIVER={SQL Server}; SERVER=server.database.windows.net; DATABASE=db_name; UID=user; PWD=Password')
    SQLCommand = ('''select * from table1 ''')
    data_frame = pd.read_sql(SQLCommand, conn)
    return data_frame,

also tried to use a different driver name: {SQL Server Native Client 11.0}

Here is the error I'm getting:

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

Does anybody know which driver should I use?

Just to make sure, I tried {SQL Server}, {SQL Server Native Client 11.0} and {SQL Server Native Client 10.0} and got the same error

I also tried a different format:

conn = pyodbc.connect('DRIVER={SQL Server}; SERVER=server.database.windows.net; DATABASE=db_name; user=user@server; password=Password')

and

conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0}; SERVER=server.database.windows.net; DATABASE=db_name; user=user@server; password=Password')

Upvotes: 6

Views: 2623

Answers (5)

marnun
marnun

Reputation: 808

I got an answer from Azure support:

Currently it is not possible to access SQL Azure dbs from within an “execute python script” module. As you suspected this is due to missing odbc drivers in the execution environment. Suggested workarounds are to a) use reader module or b) export to blobs and use the Azure Python SDK for accessing those blobs http://blogs.msdn.com/b/bigdatasupport/archive/2015/10/02/using-azure-sdk-for-python.aspx

So currently it it impossible to connect to SQL server from “execute python script” module in Azure-ML. If you like to change it, please vote here

Upvotes: 2

meet-bhagdev
meet-bhagdev

Reputation: 2708

You will need the Microsoft ODBC Driver to use pyodbc. You can download it from here: https://www.microsoft.com/en-us/download/details.aspx?id=36434. Once you download that, try using the following connection string:

conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0}; SERVER=server.database.windows.net; DATABASE=db_name; user=user@server; password=Password')

If this string does no work, try this one:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=server.database.windows.net,1433', user='user@server', password='Password', database='db_name')

Upvotes: 2

Peter Pan
Peter Pan

Reputation: 24148

The SQL Database Driver name for Pyodbc should be {SQL Server Native Client 10.0} since some reasons below.

  1. The connection string for SQL Database shown on Azure portal, please see the Fig 1 & Fig 2 below.

Fig 1. On Azure old portal enter image description here

Fig 2. On Azure new portal (the version 11.0 later then v10.0) enter image description here

  1. According to the Pyodbc code comments, please see the Fig 3 below.

Fig 3. enter image description here

Upvotes: 1

jazz
jazz

Reputation: 2377

The ultimate source of truth :-) for connection strings is:

https://www.connectionstrings.com/sql-azure/

Use it as guide to construct yours.

Upvotes: 0

aneroid
aneroid

Reputation: 16057

According to this answer, the connection string should be:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yoursqlAzureServer.database.windows.net,1433', user='yourName@yoursqlAzureServer', password='Password', database='DBName')

Note the difference in the format: different params for user, password and database vs. all in one in the first string.

Also related, see this Azure page: Connect to SQL Database by using Python on Windows. It states using pymssql, with no mention of pyodbc.

Upvotes: 3

Related Questions