【机器学习 Azure Machine Learning】Azure Machine Learning 访问SQL Server 无法写入问题 (使用微软Python AML Core SDK）

pyodbc import itertools import sys from sqlalchemy import create_engine import urllib import scipy.stats as stats conn = pyodbc.connect(r‘DRIVER={SQL Server Native Client 11.0};SERVER=database.database.chinacloudapi.cn;DATABASE=db;UID=user;PWD=pwd‘) rmdf[[‘‘]].to_sql(‘xxxx_base‘,con = conn,index=False, if_exists=‘append‘, schema=‘ai‘)

错误截图：

 

详细日志

ActivityCompleted: Activity=to_pandas_dataframe, HowEnded=Failure, Duration=672.71 [ms], Info = <br />{<span style="color: #800000;">‘</span><span style="color: #800000;">activity_id</span><span style="color: #800000;">‘</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">e850f767-0c12-4864-8d01-d11dc5817ec9</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">activity_name</span><span style="color: #800000;">‘</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">to_pandas_dataframe</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">activity_type</span><span style="color: #800000;">‘</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">PublicApi</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">app_name</span><span style="color: #800000;">‘</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">TabularDataset</span><span style="color: #800000;">‘</span>, <br /><span style="color: #800000;">‘</span><span style="color: #800000;">source</span><span style="color: #800000;">‘</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">azureml.dataset</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">version</span><span style="color: #800000;">‘</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">1.0.76</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">completionStatus</span><span style="color: #800000;">‘</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">Success</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">durationMs</span><span style="color: #800000;">‘</span>: 6.05},<br /> Exception=DatasetExecutionError;<span style="background-color: #ffff00;"> Could <span style="color: #0000ff;">not</span> connect to specified database</span>.|session_id=f648402f-f619-469d-a6f4-aee7031bd438 <br />--------------------------------------------------------------------------- <br />ExecutionError Traceback (most recent call last) /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/data/dataset_error_handling.py <span style="color: #0000ff;">in</span> _try_execute(action, **kwargs) 82 <span style="color: #0000ff;">else</span>: <br />---> 83 <span style="color: #0000ff;">return</span> action() 84 <span style="color: #0000ff;">except</span> Exception as e: /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/dataprep/api/_loggerfactory.py <span style="color: #0000ff;">in</span> wrapper(*args, **kwargs) 130 <span style="color: #0000ff;">try</span>: <br />--> 131 <span style="color: #0000ff;">return</span> func(*args, **kwargs) 132 <span style="color: #0000ff;">except</span> Exception as e: /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/dataprep/api/dataflow.py <br /><span style="color: #0000ff;">in</span> to_pandas_dataframe(self, extended_types, nulls_as_nan) 676 self._engine_api.execute_anonymous_activity( <br />--> 677 ExecuteAnonymousActivityMessageArguments(anonymous_activity=Dataflow._dataflow_to_anonymous_activity_data(dataflow_to_execute))) <br />678 /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/dataprep/api/_aml_helper.py <span style="color: #0000ff;">in</span> wrapper(op_code, message, cancellation_token) <br />37 engine_api_func().update_environment_variable(changed) <br />---> 38 <span style="color: #0000ff;">return</span> send_message_func(op_code, message, cancellation_token) 39 /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/dataprep/api/engineapi/api.py <br /><span style="color: #0000ff;">in</span> execute_anonymous_activity(self, message_args, cancellation_token) 93 <br /><span style="color: #0000ff;">def</span> execute_anonymous_activity(self, message_args: typedefinitions.ExecuteAnonymousActivityMessageArguments, cancellation_token: CancellationToken = None) -> None: <br />---> 94 response = self._message_channel.send_message(<span style="color: #800000;">‘</span><span style="color: #800000;">Engine.ExecuteActivity</span><span style="color: #800000;">‘</span>, message_args, cancellation_token) <br />95 <span style="color: #0000ff;">return</span> response /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/dataprep/api/engineapi/engine.py <br /><span style="color: #0000ff;">in</span> send_message(self, op_code, message, cancellation_token) 118 <span style="color: #0000ff;">if</span> <span style="color: #800000;">‘</span><span style="color: #800000;">error</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">in</span> response: <br />--> 119 raise_engine_error(response[<span style="color: #800000;">‘</span><span style="color: #800000;">error</span><span style="color: #800000;">‘</span>]) 120 <span style="color: #0000ff;">elif</span> response.get(<span style="color: #800000;">‘</span><span style="color: #800000;">id</span><span style="color: #800000;">‘</span>) == message_id: /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/dataprep/api/errorhandlers.py <br /><span style="color: #0000ff;">in</span> raise_engine_error(error_response) 21 <span style="color: #0000ff;">if</span> <span style="color: #800000;">‘</span><span style="color: #800000;">ActivityExecutionFailed</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">in</span> error_code: <br />---> 22 <span style="color: #0000ff;">raise</span> ExecutionError(error_response) 23 <span style="color: #0000ff;">elif</span> <span style="color: #800000;">‘</span><span style="color: #800000;">UnableToPreviewDataSource</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">in</span> error_code: ExecutionError: Could <span style="color: #0000ff;">not</span> connect to specified database.<br />|session_id=f648402f-f619-469d-a6f4-aee7031bd438 During handling of the above exception, another exception occurred: <br />DatasetExecutionError Traceback (most recent call last) <ipython-input-7-7f54b930998f> <span style="color: #0000ff;">in</span> <module> <br />----> 1 dataset.to_pandas_dataframe() /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/data/_loggerfactory.py <span style="color: #0000ff;">in</span> wrapper(*args, **kwargs) 76<br /> with _LoggerFactory.track_activity(logger, func.<span style="color: #800080;">__name__</span>, activity_type, custom_dimensions) as al: 77 <span style="color: #0000ff;">try</span>: <br />---> 78 <span style="color: #0000ff;">return</span> func(*args, **kwargs) 79 <span style="color: #0000ff;">except</span> Exception as e: 80 <span style="color: #0000ff;">if</span> hasattr(al, <span style="color: #800000;">‘</span><span style="color: #800000;">activity_info</span><span style="color: #800000;">‘</span>) <br /><span style="color: #0000ff;">and</span> hasattr(e, <span style="color: #800000;">‘</span><span style="color: #800000;">error_code</span><span style="color: #800000;">‘</span>): /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/data/tabular_dataset.py <br /><span style="color: #0000ff;">in</span> to_pandas_dataframe(self) 138 <span style="color: #800000;">"""</span><span style="color: #800000;"> 139 dataflow = get_dataflow_for_execution(self._dataflow, ‘to_pandas_dataframe‘, ‘TabularDataset‘) <br />--> 140 df = _try_execute(dataflow.to_pandas_dataframe) 141 return df<br /> 142 /anaconda/envs/azureml_py36/lib/python3.6/site-packages/azureml/data/dataset_error_handling.py in _try_execute(action, **kwargs) <br />83 return action() 84 except Exception as e: <br />---> 85 raise DatasetExecutionError(str(e)) DatasetExecutionError: Could not connect to specified database.|session_id=f648402f-f619-469d-a6f4-aee7031bd438</span>

 

问题原因

根据代码判断，问题是在to_sql方法中使用的con对象的问题，此处需要使用的是由 sqlalchemy所创建的 create_engine对象，而不能使用 pyodbc的conn对象。 同时也必须根据环境选择正确的DB驱动。如Windows环境中，则可以使用‘Driver={SQL Server};‘，而在Linux中，则可以使用DRIVER={SQL Server Native Client 11.0};

错误的连接对象：

<span style="color: #0000ff;">import</span><span style="color: #000000;"> pyodbc
</span>
conn = pyodbc.connect(r<span style="color: #800000;">‘</span><span style="color: #800000;">DRIVER={SQL Server Native Client 11.0};SERVER=xxxx.database.chinacloudapi.cn;DATABASE=xx;UID=xx;PWD=</span><span style="color: #800000;">‘</span><span style="color: #000000;">)</span><span style="color: #008080;"><br /></span>

 正确的SQL连接对象：

<span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
 
engine </span>= create_engine(<span style="color: #800000;">‘</span><span style="color: #800000;">mssql+pyodbc://%s:%s@%s/%s?driver=SQL Server</span><span style="color: #800000;">‘</span> %<span style="color: #000000;"> (
</span><span style="color: #800000;">‘</span><span style="color: #800000;">user name</span><span style="color: #800000;">‘</span><span style="color: #000000;">,                      
</span><span style="color: #800000;">‘</span><span style="color: #800000;">pwd</span><span style="color: #800000;">‘</span><span style="color: #000000;">,                             
</span><span style="color: #800000;">‘</span><span style="color: #800000;"><service name>.database.chinacloudapi.cn</span><span style="color: #800000;">‘</span><span style="color: #000000;">,                             
</span><span style="color: #008000;">#</span><span style="color: #008000;">cf.ju_db_post,                             </span>
<span style="color: #800000;">‘</span><span style="color: #800000;">DB Name</span><span style="color: #800000;">‘</span><span style="color: #000000;">                                                         
),connect_args</span>={<span style="color: #800000;">‘</span><span style="color: #800000;">charset</span><span style="color: #800000;">‘</span>:<span style="color: #800000;">‘</span><span style="color: #800000;">utf8</span><span style="color: #800000;">‘</span>})

 

解决方案

使用Create_engine创建engine并且使用在to_sql方法中，具体代码如下图：

 

注意：如出现类似错误消息是“Error: (‘01000‘, "[01000] [unixODBC][Driver Manager]Can‘t open lib ‘SQL Server‘ : file not found (0) (SQLDriverConnect)")”，则需要检查当前VM中的ODBC Driver。

参考资料：

 pandas.DataFrame.to_sql:https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.DataFrame.to_sql.html

【机器学习 Azure Machine Learning】Azure Machine Learning 访问SQL Server 无法写入问题 (使用微软Python AML Core SDK）

标签：val   sch   unixodbc   error   nbsp   机器   cut   截图   ipy   

本文系统来源：https://www.cnblogs.com/lulight/p/13556004.html

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！