Load data to snowflake via python script

[VaranAwesomenow]

Install Python 3.7.4 : Download Python | Python.org

Install python by checking the checkbox -> set Path
Install 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,TYPE
LAB10,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

#!/usr/bin/env python

import sys

import snowflake.connector

userName = sys.argv[1]

passwordText = sys.argv[2]

accountName = sys.argv[3]

con = snowflake.connector.connect(

    user=userName,

   password=passwordText,

   account=accountName,

    session_parameters={

        '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")

referred to this to get understanding of how to parse REST response servicenow-client · PyPI

Script to read ServiceNow Data

#REST API to read ServiceNow data
#Need to install requests package for python
#easy_install requests
import requests
import json
def buildUrl (instanceName, api, table, query, limit):
url = 'https://'+ instanceName + '.service-now.com' + api + table + "?" + "sysparm_query=" + query + "&sysparm_limit=" + limit
return url

def 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=true^state=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']))

Output

 python servicenowRESTAPIv1.0.py
https://dev63486.service-now.com/api/now/table/incident?sysparm_query=active=true^state=2&sysparm_limit=100 admin Abcd123$
INC0006831
INC0006920
INC0006813
INC0006814
INC0006877
INC0006815
INC0006823
INC0006833
INC0006824
INC0006835
INC0006879
INC0006817
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