使用 python 从 Azure 机器学习服务连接 Azure SQL 数据库时出错

Question

我正在尝试从Azure 机器学习服务连接Azure SQL 数据库，但出现以下错误。

请检查错误：-

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

请检查我用于数据库连接的以下代码：-

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()

有没有办法解决上述错误？请让我知道是否有任何方法。

Answer 1

我会考虑使用azureml.dataprepover pyodbc 来完成此任务（API 可能会更改，但上次我尝试过这工作）：import azureml.dataprep as dprepds = dprep.MSSQLDataSource(server_name=<server-name,port>,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;database_name=<database-name>,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;user_name=<username>,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;password=<password>)然后，您应该能够在 pandas 中收集 SQL 查询的结果，例如通过dataflow = dprep.read_sql(ds, "SELECT top 100 * FROM [dbo].[MYTABLE]")dataflow.to_pandas_dataframe()

Answer 2

或者，您可以创建 SQL 数据存储并从 SQL 数据存储创建数据集。了解如何： https ://docs.microsoft.com/en-us/azure/machine-learning/service/how-to-create-register-datasets#create-tabulardatasets示例代码：from azureml.core import Dataset, Datastore# create tabular dataset from a SQL database in datastoresql_datastore = Datastore.get(workspace, 'mssql')sql_ds = Dataset.Tabular.from_sql_query((sql_datastore, 'SELECT * FROM my_table'))@AkshayGodase您想使用pyodbc有什么特别的原因吗？