Python Tutorial

author(s) clark chen and kyle vessey, november 2022 developer prerequisites mtls certificate, client id and client secret key mtls & api key creation docid\ hro4lnngi79gn1ww2y ma python v3 9+ ( https //www python org/downloads/ https //www python org/downloads/ ) pip (latest version) https //pypi org/project/pip/ https //pypi org/project/pip/ vs code ide ( https //code visualstudio com/ https //code visualstudio com/ ) or equivalent ide internet connection data schema please read the data schema fundamentals docid\ irzm o cyhcgjw5athv1 document before using the api database crud operations are performed via the api and therefore it is important to think of each http rest operation (post/get/delete) as sql transaction (upsert/select/delete) visualizing data data inserted or modified using this tutorial can be visualized using the web portal application(s) at https //app onxecta com the portal contains a model quality docid\ jxhsila0ecxifrjb4g l6 tool where you can see what data is in the system, the validity of the data and when the data was last modified 1 create project and connect 1 1 project creation create new project in vs code create a new folder on your machine to begin building your project open the folder, and create a new python file open existing project in vs code 1 2 install xecta data apiclient download latest xecta data api client from pip package manager the xecta data api client is hosted as a python package in pypi https //pypi org/project/xecta data api client to install the api client, run the following command in your command prompt window pip install xecta data api client python3 # if you do not have python 2 7 installed, or you are on macos \# you may need to use the python3 syntax for pip pip3 install xecta data api client this will pull the compiled package from pypi and install it on your machine install and configure sdk to initialize and configure you project to include the sdk, follow this code sample python from xecta data client xecta api import xectaapi \# root url root url = "https //data sandbox onxecta com" \# path to signed csr certificate pem file signed cert = "{local path to pem}/xecta data api pem" \# path to private key private key = "{local path to private key}/xecta data api key" \# client id client id = '{your client id}' \# client secret client secret = '{your client secret}' \# initialize the xecta api with the root url, certificate and key xecta api = xectaapi(root url, signed cert, private key) \# authenticating will return you a xecta api client api client = xecta api authenticate(client id, client secret) error initializing certificate and key when calling the xecta api authenticate method, you must pass in the environment url, signed certificate ( xecta data api pem ), and your private key ( xecta data api key ) if you get an error message stating \[ssl certificate verify failed], try the following fix macos navigate to macintosh hd > applications > pythonx x folder (where x is the version of python you are using) > then double click on "install certificates command" file 1 3 pre requisite dependent python libraries installation a) required python libraries pandas and numpy pip install pandas pip install numpy b) optional python libraries tqdm (for displaying progress bar of data processing) pip install tqdm 1 4 import required python libraries and api client \## import required python libraries import pandas as pd import numpy as np \## import optional python libraries from tqdm import tqdm \## import required api client and api endpoints from openapi client import apiexception, apiclient from openapi client model daily production input import dailyproductioninput from openapi client model daily production import dailyproduction from openapi client model well input import wellinput from xecta data client xecta api import xectaapi from openapi client import apiclient, configuration from openapi client exceptions import apitypeerror, apivalueerror, apiexception from openapi client model deviation survey import deviationsurvey from openapi client model deviation survey input import deviationsurveyinput from openapi client model wellbore import wellbore from openapi client model wellbore input import wellboreinput from openapi client model casing import casing from openapi client model casing input import casinginput from openapi client model tubing import tubing from openapi client model tubing input import tubinginput from openapi client model formation import formation from openapi client model formation input import formationinput 2 well headers 2 1 insert or upsert a well header a) data preparation import well header from source data ( xlsx data demonstrated in this tutorial) \## import wellheader from source data well header = pd read excel(r' \exampledata xlsx',sheet name='well') b) create a function to convert field names in camel case to snake case this function will be used for other source data as well \## create a function to convert source data field names in camel case to snake case def filed name conversion(str) return '' join(\[' '+i lower() if i isupper() else i for i in str]) lstrip(' ') c) change field names and data type to be consistent with data schema \## convert source data field names in camel case to snake case for name in well header columns well header rename(columns={name filed name conversion(name)},inplace=true) \## convert nan to nonetype well header replace({np nan none},inplace=true) \## convert field 'uwi' and 'type' to str type well header\['uwi'] = well header\['uwi'] apply(str) well header\['type'] = well header\['type'] apply(str) \## convert wellheader data to list form well header input = well header to dict(orient = 'records') d) upsert wellheader data to api client try well header api = api client well header api() api response = well header api upsert wells(well header input) \## print api response when well header data was succesfully upserted print(api response) except apiexception as e \## print api response when error occured during upserting print("exception when calling wellapi >upsert wells %s\n" % e) potential error null values in non nullable fields example null in field 'lat' will return error message when upserting 2 2 get a well header record \## specify source id to be queried source id = 'specify your source id' \## get wellheader by source id try well header api = api client well header api() api response = well header api get well by source(source id) 	 \## print api response when well header data was fetched print(api response) except apiexception as e 	 \## print api response when error occured during fetching print("exception when calling wellapi >get well by source %s\n" % e) 2 3 get all well header records \## get all wellheaders try well header api = api client well header api() api response = well header api get wells() 	 \## print api response when well header data was fetched print(api response) except apiexception as e 	 \## print api response when error occured during fetching print("exception when calling wellapi >get wells() %s\n" % e) 2 4 delete a well header record \## specify source id to be deleted source id = 'specify your source id' \## delete wellheader by source id try well header api = api client well header api() api response = well header api delete well by source('source id') \## print api response when well header data was successfully deleted print(api response) except apiexception as e \## print api response when error occured during deleting print("exception when calling wellapi >delete well by source %s\n" % e) 3 daily production 3 1 insert or upsert single daily production record a) create daily production input (example data demonstrated) \## create example daily production record daily production = \[ dailyproductioninput( uwi = "well uwi", date=datetime datetime(2022, 9, 21, 20, 33, 4, 303441, tzinfo=tzutc()), oil rate=12 33, gas rate=12 33, water rate=12 33, tubing pressure=12 33, choke=1, gauge pressure=12 33, wellhead temperature=12 33, gauge temperature=12 33, casing pressure=12 33, gas lift injection rate=12 33, esp frequency=12 33, esp intake pressure=12 33, esp amps=12 33, esp volts=12 33, esp discharge pressure=12 33, esp motor temperature=12 33, esp vibration=12 33, srp strokes per min=12 33, downtime hours=12 33, downtime code="st 1", water injection rate=12 33, gas injection rate=12 33, )] b) upsert daily production to api client try daily production api = api client daily production api() response = daily production api upsert daily(daily production) \## print api response when daily production was successfully upserted print(response) except apiexception as e \## print api response when error occurred during upserting print("exception when calling dailyproductionapi >upsert daily %s\n" % e) api response when upserted successfully api response when error occurred (example uwi of upserted daily production doesn't exist in wellheader) api response when error occurred (example uwi of upserted daily production doesn't exist in wellheader) 3 2 insert a history of daily production (multiple records) 3 2 1 daily production input preparation a) import daily production from source data ( xlsx data demonstrated in this tutorial) \## import daily production from source data dailyproduction = pd read excel(r' \exampledata xlsx',sheet name='dailyproduction') b) input data preparation change field names and data type to be consistent with data schema \## convert nan to nonetype dailyproduction replace({np nan none},inplace=true) \## maximum 30 characters allowed for 'downtimecode' dailyproduction\['downtimecode'] = dailyproduction\['downtimecode'] str\[ 30] \## convert datetime to utc dailyproduction\['date'] = pd to datetime(dailyproduction\['date'],utc=true) \## convert 'uwi' to str type dailyproduction\['uwi'] = dailyproduction\['uwi'] apply(str) \## convert source data field names in camel case to snake case for name in dailyproduction columns dailyproduction rename(columns={name filed name conversion(name)},inplace=true) \## convert input data to list form daily production = dailyproduction to dict(orient = 'records') 3 2 2 basic method of upserting multiple records batch processing in order to reduce the payload, it is recommended to upsert the daily production of each well in batches \## create a function to divide the daily production into batches for each well def batch file(df,batch size) df len = len(df) batches = \[ df\[ix\ ix+batch size] for ix in range(0, df len, batch size)] return batches for uwi in tqdm(dailyproduction\['uwi'] unique()) uwi production = list(filter(lambda daily production daily production\['uwi'] == uwi, daily production)) batches = batch file(uwi production,1500) for batch in batches try daily production api = api client daily production api() response = daily production api upsert daily(batch) except apiexception as e print("exception when calling dailyproductionapi >upsert daily %s\n" % e) potential error api response will return 400 error code when uwi of daily production doesn't exist in database potential error api response will return 400 error code when uwi of daily production doesn't exist in database 3 3 get a daily production history \## get daily production try daily production api = api client daily production api() \## query daily production of uwi 'well uwi' response = daily production api get daily('well uwi') print(response) except apiexception as e print("exception when calling dailyproductionapi >get daily %s\n" % e) 3 4 remove a daily production record \## delete daily production try daily production api = api client daily production api() \## define datetime range for daily production (optional) start date = datetime datetime(1900, 9, 21, 20, 33, 4, 303441, tzinfo=tzutc()) end date = datetime datetime(2022, 9, 21, 20, 33, 4, 303441, tzinfo=tzutc()) response = daily production api delete daily(uwi,start date = start date,end date = end date) print(response) except apiexception as e print("exception when calling dailyproductionapi >delete daily %s\n" % e) if daily production has been successfully deleted, api response will return number of records which was just deleted (in this example 658 records were deleted) if uwi doesn't exist in database, api response will return 400 error code if uwi doesn't exist in database, api response will return 400 error code 4\ wellbore 4 1 upsert a wellbore record a) data preparation (data import and format conversion) \## import wellbore data wellbore = pd read excel(r' \dataplatformapitemplate by sourceid xlsx',sheet name='wellbore') \## convert source data field names in camel case to snake case for name in wellbore columns wellbore rename(columns={name filed name conversion(name)},inplace=true) \## replace nan with none wellbore replace({np nan none},inplace=true) \## convert wellbore data to list format wellbore input = wellbore to dict(orient='records') b) upsert wellbore record \# batch upsert batches = batch file(wellbore,100) for batch in tqdm(batches) wellbore input = batch to dict(orient='records') try wellbore api = api client wellbore api() response = wellbore api upsert wellbores(wellbore input) \#print(response) except apiexception as e print("exception when calling wellboreapi >upsert wellbores %s\n" % e) 4 2 get a wellbore record 4 2 1 get wellbore record by source well id \## define source well id to be queried source well id = 'testsourcewellid' \## get wellbore by well try wellbore api = api client wellbore api() response = wellbore api get wellbores by source well(source well id) print(response) except apiexception as e print("exception when calling wellboreapi >get wellbores by source well %s\n" % e) 4 2 2 get wellbore record by source well id and source wellbore id \## define source well id and source wellbore id to be queried source well id = 'testsourcewellid' source wellbore id = 'testsourcewellboreid' \## get wellbore by source try wellbore api = api client wellbore api() response = wellbore api get wellbor by source(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling wellboreapi >get wellbore by source %s\n" % e) 4 3 delete a wellbore record by source well id \## define source id to be queried source well id = 'testsourcewellid' \## delete wellbore try wellbore api = api client wellbore api() response = wellbore api delete wellbore by source well(source well id) print(response) except apiexception as e print("exception when calling wellboreapi >delete wellbore by source well %s\n" % e) 4 4 delete a wellbore record by source well id and source wellbore id \## define source well id and source wellbore id to be queried source well id = 'testsourcewellid' source wellbore id = 'testsourcewellboreid' \## delete wellbore try wellbore api = api client wellbore api() response = wellbore api delete wellbore by source(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling wellboreapi >delete wellbore by source %s\n" % e) 5 deviation survey 5 1 upsert deviation survey a) data preparation (data import and formation conversion) \## import deviation survey data deviationsurvey = pd read excel(r' \dataplatformapitemplate by sourceid xlsx',sheet name='wellboredeviationsurvey') \## convert source data field names in camel case to snake case for name in deviationsurvey columns deviationsurvey rename(columns={name filed name conversion(name)},inplace=true) \## replace nan with none deviationsurvey replace({np nan none},inplace=true) \## convert survey data to list deviationsurveyinput = deviationsurvey to dict(orient='records') b) upsert deviation survey \## upsert survey try deviation survey api = api client deviation survey api() response = deviation survey api upsert deviation surveys(deviationsurveyinput) print(response) except apiexception as e print("exception when calling deviationsurveyapi >upsert deviation surveys %s\n" % e) 5 2 get deviation survey 5 2 1 get deviation survey by source well id \## define source id to be queried source well id = 'testsourcesurveysourcewellid' \## get survey try deviation survey api = api client deviation survey api() response = deviation survey api get deviation survey by source well(source well id) print(response) except apiexception as e print("exception when calling deviationdurveyapi >get deviation survey by source well %s\n" % e) 5 2 2 get deviation survey by source well id and source wellbore id \## define source well id and source wellbore id to be queried source well id = 'testsourcesurveysourcewellid' source wellbore id = 'testsourcesurveysourcewellboreid' \## get survey try deviation survey api = api client deviation survey api() response = deviation survey api get deviation survey by source wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling deviationdurveyapi >get deviation survey by source wellbore %s\n" % e) 5 2 3 get deviation survey by source \## define source wellbore id to be queried source well id = 'testsourcesurveysourcewellid' source wellbore id = 'testsourcesurveysourcewellboreid' source id = 'testsourcesurveysourceid' \## get survey try deviation survey api = api client deviation survey api() response = deviation survey api get deviation survey by source(source well id,source wellbore id,source id) print(response) except apiexception as e print("exception when calling deviationdurveyapi >get deviation survey by source %s\n" % e) 5 3 delete deviation survey 5 3 1 delete deviation survey by source well id \## define source well id to be queried source well id = 'testsourcesurveysourcewellid' \## delete survey try deviation survey api = api client deviation survey api() response = deviation survey api delete deviation surveys by source well(source well id) print(response) except apiexception as e print("exception when calling deviationdurveyapi >delete deviation surveys by source well %s\n" % e) 5 3 2 delete deviation survey by source well id and source wellbore id \## define source well id and source wellbore id to be queried source well id = 'testsourcesurveysourcewellid' source wellbore id = 'testsourcesurveysourcewellboreid' \## delete survey try deviation survey api = api client deviation survey api() response = deviation survey api delete deviation surveys by source wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling deviationdurveyapi >delete deviation surveys by source wellbore %s\n" % e) 5 3 3 delete deviation survey by source \## define source well id and source wellbore id to be queried source well id = 'testsourcesurveysourcewellid' source wellbore id = 'testsourcesurveysourcewellboreid' source id = 'testsourcesurveysourceid' \## delete survey try deviation survey api = api client deviation survey api() response = deviation survey api delete deviation survey by source(source well id,source wellbore id,source id) print(response) except apiexception as e print("exception when calling deviationdurveyapi >delete deviation survey by source %s\n" % e) 6 casing 6 1 upsert casing records a) data preparation (data import and data format conversion) \## import casing data from local excel file casing = pd read excel(r' \dataplatformapitemplate by sourceid xlsx',sheet name='wellborecasing') \## convert source data field names in camel case to snake case for name in casing columns casing rename(columns={name filed name conversion(name)},inplace=true) \## replace nan with none casing replace({np nan none},inplace=true) \## convert run date data type casing\['run date'] = pd to datetime(casing\['run date'],utc=true) \## convert casing data to list casing input = casing to dict(orient='records') b) upsert casing data \## upsert casing try casing api = api client wellbore casing api() response = casing api upsert casing(casing input) print(response) except apiexception as e print("exception when calling casginapi >upsert casing %s\n" % e) 6 2 get casing record 6 2 1 get casing record by source well id \## define the source well id to be queried source well id = 'sourcewellid' \## get casing record by source id try casing api = api client wellbore casing api() response = casing api get casing by source well(source well id) print(response) except apiexception as e print("exception when calling casingapi >get casing by source well %s\n" % e) 6 2 2 get casing record by source well id and source wellbore id \## define the source id to be queried source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' \## get casing try casing api = api client wellbore casing api() response = casing api get casing by source wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling casingapi >get casing by source wellbore %s\n" % e) 6 2 3 get casing record by source \## define the source id to be queried source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' source id = 'sourceid' \## get casing try casing api = api client wellbore casing api() response = casing api get casing by source(source well id,source wellbore id,source id) print(response) except apiexception as e print("exception when calling casingapi >get casing by source %s\n" % e) 6 3 delete casing records 6 3 1 delete casing records by source well id \## define source id to be deleted source well id = 'sourcewellid' \## delete casing try casing api = api client wellbore casing api() response = casing api delete casing by source well(source well id) print(response) except apiexception as e print("exception when calling casingapi >delete casing by source well %s\n" % e) 6 3 2 delete casing records by source wellbore id \## define source id to be deleted source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' \## delete casing try casing api = api client wellbore casing api() response = casing api delete casing by source wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling casingapi >delete casing by source wellbore %s\n" % e) 6 3 2 delete casing records by source \## define source id to be deleted source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' source id = 'sourceid' \## delete casing try casing api = api client wellbore casing api() response = casing api delete casing by source(source well id,source wellbore id,source id) print(response) except apiexception as e print("exception when calling casingapi >delete casing by source %s\n" % e) 7 tubing 7 1 upsert tubing records a) data preparation (data import and data format conversion) \## import tubing data from local excel file tubing = pd read excel(r' \dataplatformapitemplate by sourceid xlsx',sheet name='wellboretubing') \## convert source data field names in camel case to snake case for name in tubing columns tubing rename(columns={name filed name conversion(name)},inplace=true) \## replace nan with none tubing replace({np nan none},inplace=true) \## convert tubing data to list tubing input = tubing to dict(orient='records') b) upsert tubing records \## upsert tubing try tubing api = api client wellbore tubing api() response = tubing api upsert tubing(tubing input) print(response) except apiexception as e print("exception when calling tubingapi >upsert tubing %s\n" % e) 7 2 get tubing records 7 2 1 get tubing records by source well id \## define the source id to be queried source well id = 'sourcewellid' \## get tubing record by source id try tubing api = api client wellbore tubing api() response = tubing api get tubing for source well(source well id) print(response) except apiexception as e print("exception when calling tubingapi >get tubing for source well %s\n" % e) 7 2 2 get tubing records by source well id and source wellbore id \## define the source well id to be queried source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' \## get tubing record by source well id try tubing api = api client wellbore tubing api() response = tubing api get tubing for source wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling tubingapi >get tubing for source wellbore %s\n" % e) 7 2 3 get tubing records by source \## define the source well id to be queried source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' source id = 'sourceid' \## get tubing record by source wellbore id try tubing api = api client wellbore tubing api() response = tubing api get tubing by source(source well id,source wellbore id,source id) print(response) except apiexception as e print("exception when calling tubingapi >get tubing by source %s\n" % e) 7 3 delete tubing records 7 3 1 delete tubing records by source well id \## specify source id to be deleted source well id = 'sourcewellid' \## delete tubing try wellbore tubing api = api client wellbore tubing api() response = wellbore tubing api delete tubing by source well(source well id) (source well id) print(response) except apiexception as e print("exception when calling dailyproductionapi >delete tubing by source well %s\n" % e) 7 3 2 delete tubing records by source well id and source wellbore id \## specify source id to be deleted source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' \## delete tubing try wellbore tubing api = api client wellbore tubing api() response = wellbore tubing api delete tubing by source wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling dailyproductionapi >delete tubing by source wellbore %s\n" % e) 7 3 3 delete tubing records by source \## specify source id to be deleted source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' source id = 'sourceid' \## delete tubing try wellbore tubing api = api client wellbore tubing api() response = wellbore tubing api delete tubing by source(source well id,source wellbore id,source id) print(response) except apiexception as e print("exception when calling dailyproductionapi >delete tubing by source %s\n" % e) 8 formation 8 1 upsert formation a) data preparation (data import and formation conversion) \## import formation data from excel file formation = pd read excel(r' \dataplatformapitemplate by sourceid xlsx',sheet name='wellboreformation') \## replace nan with none formation replace({np nan none},inplace=true) \## convert source data field names in camel case to snake case for name in formation columns formation rename(columns={name filed name conversion(name)},inplace=true) \## change attribute names formation rename(columns={"fluid comingled g o r" "fluid comingled gor","fluid molar frac c o2" "fluid molar frac co2"},inplace = true) \## convert formation data to list formation input = formation to dict(orient='records') b) upsert formation \## upsert formation try formation property api = api client wellbore formation api() response = formation property api upsert formations(formation input) print(response) except apiexception as e print("exception when calling formationapi >upsert formations %s\n" % e) 8 2 get formation 8 2 1 get formation by source well id \## specify source wellbore id source well id = 'sourcewellid' \## get formation try formation property api = api client wellbore formation api() response = formation property api get formations by well(source well id) print(response) except apiexception as e print("exception when calling formationapi >get formations by well %s\n" % e) 8 2 2 get formation by source wellbore id \## specify source wellbore id source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' \## get formation try formation property api = api client wellbore formation api() response = formation property api get formations by wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling formationapi >get formations by wellbore %s\n" % e) 8 3 delete formation 8 3 1 delete formation by source well id \## specify source wellbore id source well id = 'sourcewellid' \## delete formation try formation property api = api client wellbore formation api() response = formation property api delete formations by source well(source well id) print(response) except apiexception as e print("exception when calling formationapi >delete formations by source well %s\n" % e) 8 3 2 delete formation by source wellbore id \## specify source wellbore id source well id = 'sourcewellid' source wellbore id = 'sourcewellboreid' \## delete formation try formation property api = api client wellbore formation api() response = formation property api delete formation by source wellbore(source well id,source wellbore id) print(response) except apiexception as e print("exception when calling formationapi >delete formation by source wellbore %s\n" % e)