Install Python 3.7.4 : Download Python | Python.org

Install python by checking the checkbox -> set PathInstall latest snowflake connector using pip -> python -m pip install --upgrade pip

pip install snowflake-connector-python==2.3.8

If you receive error that says 'pip is not regonized cmdlet then navigate to /Scripts folder in python installation directory and run above command as .\pip install snowflake-connector-python==2.3.8

Python connector : Installing the Python Connector — Snowflake Documentation

Install ServiceNow Python library : servicenow · PyPI

pip install servicenow

once above library is installed execute below script to check if ServiceNow connector is working fine.

script to connect to snowflake via python.

Using the Python Connector — Snowflake Documentation

Code anilvaranasi/Python (github.com)

Setup on Snowflake side

Setup file format on snowflake at database & table level to handle data in csv format

Sql to create above file format

ALTER FILE FORMAT "IDRREPORTS"."PUBLIC".DATA SET COMPRESSION = 'AUTO' FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE' TRIM_SPACE = FALSE ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = '\134' DATE_FORMAT = 'AUTO' TIMESTAMP_FORMAT = 'AUTO' NULL_IF = ('\\N');

Create Data file contains following data

LAB,NAME,NUMBER,TYPELAB10,ACCOUNT10,CORPORATE,10

LAB11,ACCOUNT11,CORPORATE,11

Save the file in location and run below command

python .\insertDataFromFileParameterized.py USERNAME PASSWORD #####.us-east-2.aws

Output from snowflake DB

insertDataFromFileParameterized.py script

import snowflake.connector

passwordText = sys.argv[2]

accountName = sys.argv[3]

con = snowflake.connector.connect(

'QUERY_TAG': 'EndOfMonthFinancials',

con.cursor().execute("ALTER SESSION SET QUERY_TAG = 'EndOfMonthFinancials'")

con.cursor().execute("USE WAREHOUSE COMPUTE_WH")

con.cursor().execute("USE DATABASE IDRREPORTS")

con.cursor().execute("USE SCHEMA PUBLIC")

con.cursor().execute("PUT file://d:\data\data2.csv @%ACCOUNTS")

con.cursor().execute("COPY INTO ACCOUNTS")

# Putting Data for snowflake docs

con.cursor().execute("PUT file:///tmp/data/file* @%testtable")

put file://c:\data\data.csv @~/staged;

con.cursor().execute("PUT file:///loadersheet.csv* @%ACCOUNTS")

Script to read ServiceNow Data

REST API to read ServiceNow data#Need to install requests package for python#easy_install requestsimport requestsimport jsondef buildUrl (instanceName, api, table, query, limit): url = 'https://'+ instanceName + '.service-now.com' + api + table + "?" + "sysparm_query=" + query + "&sysparm_limit=" + limit return urldef readServiceNowData(url,username,password): # Set proper headers headers = {"Content-Type":"application/json","Accept":"application/json"} print (url + " " + username + " " + password) # Do the HTTP request response = requests.get(url, auth=(user, pwd), headers=headers ) # Check for HTTP codes other than 200 if response.status_code != 200: print('Status:', response.status_code, 'Headers:', response.headers, 'Error Response:',response.json()) exit() # Decode the JSON response into a dictionary and use the data #data = response.json() result = {} #responseJSON = response.json()['result'] responseJSON = response.json()['result'] return responseJSON# Set the request parameters#url = 'https://de.service-now.com/api/now/table/incident?sysparm\_limit=10'instanceName = 'dev'api = '/api/now/table/'table = 'incident'#query = 'active%3Dtrue%5Estate%3D2'query = 'active=truestate=2'limit = '100'url = buildUrl(instanceName,api,table,query,limit)#print(url)# Eg. User name="admin", Password="admin" for this code sample.user = 'admin'pwd = 'XXX'responseJSON = readServiceNowData(url,user,pwd)for item in responseJSON: print(str(item['number']))

python servicenowRESTAPIv1.0.pyhttps://dev63486.service-now.com/api/now/table/incident?sysparm_query=active=truestate=2&sysparm_limit=100 admin Abcd123$INC0006831INC0006920INC0006813INC0006814INC0006877INC0006815INC0006823INC0006833INC0006824INC0006835INC0006879INC0006817

INC0006826

Sometimes you may get an error message

requests.exceptions.SSLError: HTTPSConnectionPool(host='dev63486.service-now.com', port=443): Max retries exceeded with url: /api/now/table/incident?sysparm_query=active=true%5Estate=2&sysparm_limit=100 (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1076)')))

This is because ur machine is unable to access ServiceNow certificate

Labels: