Error in connecting Azure SQL database from Azure Machine Learning Service using python

Question

I am trying to connect Azure SQL Database from Azure Machine Learning service, but I got the below error.

Please check Error: -

**('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')**

Please Check the below code that I have used for database connection: -

import pyodbc

class DbConnect:
    # This class is used for azure database connection using pyodbc
    def __init__(self):
        try:
            self.sql_db = pyodbc.connect(SERVER=<servername>;PORT=1433;DATABASE=<databasename>;UID=<username>;PWD=<password>')

            get_name_query = "select name from contacts"
            names = self.sql_db.execute(get_name_query)
            for name in names:
                print(name)

        except Exception as e:
            print("Error in azure sql server database connection : ", e)
            sys.exit()

if __name__ == "__main__":
    class_obj = DbConnect()

Is there any way to solve the above error? Please let me know if there is any way.

Davide Fiocco · Accepted Answer · 2019-09-22 10:05:09Z

1

I'd consider using azureml.dataprep over pyodbc for this task (the API may change, but this worked last time I tried):

import azureml.dataprep as dprep

ds = dprep.MSSQLDataSource(server_name=<server-name,port>,
                           database_name=<database-name>,
                           user_name=<username>,
                           password=<password>)

You should then be able to collect the result of an SQL query in pandas e.g. via

dataflow = dprep.read_sql(ds, "SELECT top 100 * FROM [dbo].[MYTABLE]")
dataflow.to_pandas_dataframe()

edited Sep 22, 2019 at 10:05

answered Sep 21, 2019 at 23:35

Davide Fiocco

6,0395 gold badges43 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akshay Godase Over a year ago

@Davide_Fiocco I tried above code before it's working fine, but want to access database using pyodbc. Is there any solution for it?

Davide Fiocco Over a year ago

@AkshayGodase this and similar questions may help stackoverflow.com/questions/46045834/… in that case!

Akshay Godase Over a year ago

@Davide_Fiocco I tried the below syntax on Debian :- import pyodbc print(pyodbc.drivers()) I got the output is :- [] There is no any driver available that's why i got the above error.

Sihui Hu · Accepted Answer · 2019-09-24 04:39:11Z

1

Alternatively you can create SQL datastore and create a dataset from the SQL datastore. Learn how: https://learn.microsoft.com/en-us/azure/machine-learning/service/how-to-create-register-datasets#create-tabulardatasets

Sample code:

from azureml.core import Dataset, Datastore

# create tabular dataset from a SQL database in datastore
sql_datastore = Datastore.get(workspace, 'mssql')
sql_ds = Dataset.Tabular.from_sql_query((sql_datastore, 'SELECT * FROM my_table'))

@AkshayGodase Any particular reason that you want to use pyodbc?

answered Sep 24, 2019 at 4:39

Sihui Hu

793 bronze badges

1 Comment

Akshay Godase Over a year ago

@Sihui_Hu I have already connected the azure sql database using azureml.datapreb library but I am not able to store data into database using azureml.datapreb library. That's why I want to connect the database using pyodbc library.

Collectives™ on Stack Overflow

Error in connecting Azure SQL database from Azure Machine Learning Service using python

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related