Error while accessing google adwords API throught python client library on SQL Server (sp_execute_external_script)

0

I have strange problem and don't know what to check further.

Summary of the situation:

  • Working on the Windows Server 2019 as administrator, so should not have any access problems
  • Developed a Python Script that gets data from Google Adwords API and returns as a pandas dataframe
  • Script runs smothly locally, but I want to automatized as a job on a SQL Server
  • Installed SQL Machine Learning services and can execute sp_execute_external_script sucefully
  • Installed googleads through pip, according with Microsoft documentation
  • When I try to execute the following code on SQL Server, I get a error:
    EXECUTE sp_execute_external_script
  @language = N'Python',
  @script = N'
from googleads import adwords
from datetime import datetime, date, time, timedelta
from oauth2client.service_account import ServiceAccountCredentials
from google.oauth2 import service_account
import _locale

_locale._getdefaultlocale = (lambda *args: ["en_US", "UTF-8"])

# API version
version = "v201809"

# Authentication
googleads_client = adwords.AdWordsClient.LoadFromStorage("E:\Python support files\googleads.yaml")
credentials = service_account.Credentials.from_service_account_file("E:\Python support files\Google Ads API-79664b793f87.json")

report_downloader = googleads_client.GetReportDownloader(version=version)

'

This the error:

Msg 39004, Level 16, State 20, Line 0 A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 0 An external script error occurred:

Error in execution. Check the output for more information. Traceback (most recent call last): File "", line 5, in File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\0ED3B02F-A286-4566-88B0-93CADA623D84\sqlindb_0.py", line 61, in transform report_downloader = googleads_client.GetReportDownloader(version=version) File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\googleads\adwords.py", line 365, in GetReportDownloader return ReportDownloader(self, version, server) File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\googleads\adwords.py", line 1205, in init self.proxy_config, self._namespace, self._adwords_client.cache) File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\googleads\common.py", line 585, in init transport = _ZeepProxyTransport(timeout, proxy_config, cache)

Msg 39019, Level 16, State 2, Line 0 An external script error occurred: File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\googleads\common.py", line 513, in init cache = zeep.cache.SqliteCache() File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\zeep\cache.py", line 80, in init self._db_path = path if path else _get_default_cache_path() File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\zeep\cache.py", line 158, in _get_default_cache_path path = appdirs.user_cache_dir("zeep", False) File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\appdirs.py", line 293, in user_cache_dir path = os.path.normpath(_get_win_folder("CSIDL_LOCAL_APPDATA")) File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\appdirs.py", line 481, in _get_win_folder_with_pywin32 dir = shell.SHGetFolderPath(0, getattr(shellcon, csidl_name), 0, 0)

Msg 39019, Level 16, State 2, Line 0 An external script error occurred: pywintypes.com_error: (-2147024893, 'The system cannot find the path specified.', None, None)

SqlSatelliteCall error: Error in execution. Check the output for more information. STDOUT message(s) from external script: SqlSatelliteCall function failed. Please see the console output for more information. Traceback (most recent call last): File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call rx_native_call("SqlSatelliteCall", params) File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call ret = px_call(functionname, params)

RuntimeError: revoscalepy function failed.

python
sql-server
google-ads-api
sql-server-2019
asked on Stack Overflow Jun 16, 2020 by ggludwig

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0