def getMonthlyDataByWellProdMonthProduct(self, wellID, prodMonth, product):
for md in self.monthlyTable:
if md.WellID == wellID and md.ProdMonth == prodMonth and md.Product == product:
# if (md.WellId == wellId and md.ProdMonth == prodMonth and md.Product = product):
return md
raise AppError('Monthly Data not found for: ' + str(wellID) + ' ' +
str(prodMonth) + ' ' + product)
def insert_data(self, tableName, row, headerRow):
insert = 'INSERT INTO ' + tableName + ' VALUES ('
data = ""
i = 0
for h in headerRow:
cell = row[i]
if type(cell.value) is str:
data = data + "'" + cell.value + "',"
elif type(cell.value) is int:
data = data + str(cell.value) + ","
elif type(cell.value) is float:
data = data + str(cell.value) + ","
elif type(cell.value) is datetime.datetime:
data = data + "'" + str(cell.value) + "',"
# data = data + str(cell.value.strftime("%Y/%m/%d")) + ","
elif cell.value is None:
data = data + " Null,"
else:
raise AppError('*** Not Loaded', cell.value, type(cell.value))
i += 1
data = data + ')'
data = data.replace(',)', ')')
insert = insert + data
print(insert)
self.dbi.execute(insert)
def execute(self, stmt):
if self.debug_sql:
print('SqliteInstance.execute:', stmt)
try:
return self.cursor.execute(stmt)
except OperationalError as e:
raise AppError(str(e) + '--> ' + stmt)
def getCalcDataByWellProdMonthProduct(self, wellID, prodMonth, product):
for md in self.calc:
if md.WellID == wellID and md.ProdMonth == prodMonth:
# if (md.WellId == wellId and md.ProdMonth == prodMonth and md.Product = product):
return md
raise AppError('Calc Data not found for: ' + str(wellID) + ' ' +
str(prodMonth) + ' ' + product)
def __init__(self, worksheetName):
try:
self.worksheetName = worksheetName
self.newWorksheetName = worksheetName[:len(worksheetName) -
5] + ' new.xlsx'
self.wb = load_workbook(worksheetName)
self.wellTabName = 'Well'
self.royaltyMasterTabName = 'RoyaltyMaster'
self.leaseTabName = 'Lease'
self.monthlyTabName = 'Monthly'
self.calcTabName = 'Calc'
self.producingEntityTabName = 'ProducingEntity'
self.monthlyRecordId = 0
self.productClausesTabName = 'ProductClauses'
self.econOilDataTabName = 'ECONData'
self.calcDataTabName = 'Calc'
except FileNotFoundError:
raise AppError('The excel worksheet ' + worksheetName +
' is not found')
self.loadWellFromExcel()
self.loadRoyaltyMasterFromExcel()
self.loadLeaseFromExcel()
# self.loadProductClausesFromExcel()
self.loadMonthlyFromExcel()
# self.loadProducingEntityFromExcel()
self.loadECONOilDataFromExcel()
self.loadCalcDataFromExcel()
def getAllWells(self):
try:
wl = []
for w in self.well:
wl.append(self.well[w])
return wl
except:
raise AppError("Wells not found")
def select1(self, table, **kwargs):
result = self.select(table, **kwargs)
if len(result) != 1:
raise AppError(
"sqlite_database.select1 should have only found 1, but we found "
+ str(len(result)) + " in table: " + table +
". We are only looking for " + str(kwargs))
return result[0]
def getAllLeases(self):
al = []
try:
for l in self.lease:
al.append(self.lease[l])
# al.sort()
return al
except KeyError:
raise AppError('No leases found')
def updateRoyaltyCalc(self, rc):
try:
ws = self.wb[self.calcTabName]
headerRow = ws.rows[0]
tab = []
for cell in headerRow:
print(cell.value, getattr(rc, cell.value))
tab.append(getattr(rc, cell.value))
ws.append(tab)
print(headerRow)
except KeyError as e:
raise AppError('The excel worksheet ' + self.worksheetName +
" does not have tab: '" + self.calcTabName + "'")
raise e
except AttributeError as e:
raise AppError(
"database.updateRoyaltyCalc Royalty Calc Object has no value for attribute: '"
+ cell.value + "' correct worksheet header and continue.")
raise e
except Exception as e:
raise e
def to_db_value(self, value):
if type(value) is str:
return '"' + value + '"'
elif type(value) is int or type(value) is float:
return str(value)
elif type(value) is bool:
return '1' if (value) else '0'
elif not value:
return 'null'
raise AppError(
'sqlite_database.to_db_value can not handle update of: ' +
str(type(value)) + ':' + str(value))
def excelLoadWsTable(self, tabName):
try:
ws = self.wb[tabName]
stack = []
recordNo = 0
headerRow = None
for row in ws.rows:
if headerRow == None:
headerRow = row
else:
recordNo = recordNo + 1
ds = DataStructure()
stack.append(ds)
i = 0
for cell in headerRow:
setattr(ds, cell.value, row[i].value)
i = i + 1
setattr(ds, 'RecordNumber', recordNo)
setattr(ds, 'ExcelRow', row)
setattr(ds, 'HeaderRow', headerRow)
except KeyError:
raise AppError('The excel worksheet ' + self.worksheetName +
' does not have tab: ' + tabName)
except AttributeError as e:
print('Error Loading tab:', tabName, ' column:', i, 'Record:',
recordNo, 'Error:', e)
print(' cell.value:', cell.value)
print(' headerRow:', headerRow)
print(' row:', row)
raise e
except TypeError as e:
print('Error Loading tab:', tabName, ' column:', i, 'Record:',
recordNo, 'Error:', e)
print(' headerRow:', headerRow)
print(' row:', row)
raise e
return stack
def update(self, ds):
# Rule 1: all tables that can be updated must have an ID attrabute that is the primary key.
orig_ds = self.select(ds._table_name, ID=ds.ID)
if len(orig_ds) == 0:
raise AppError('sqlite_database.update can not find: ' +
str(ds.ID) + ' to update.')
orig_dict = orig_ds[0].__dict__
new_dict = ds.__dict__
to_update = ''
for attr in orig_dict:
if not attr.startswith('_'):
if orig_dict[attr] != new_dict[attr]:
#todo: Do that audit of the records right here.
# print('difference:',attr,orig_dict[attr])
if len(to_update) > 0:
to_update += ','
to_update += attr + '=' + self.to_db_value(new_dict[attr])
if len(to_update) > 0:
statement = 'UPDATE ' + ds._table_name + ' SET ' + to_update + ' where ID = ' + str(
ds.ID)
self.dbi.execute(statement)
self.dbi.commit()
def getLease(self, lease):
try:
return self.lease[lease]
except KeyError:
raise AppError('Lease not found for Lease: ' + lease)
def getRoyaltyMaster(self, lease):
try:
return self.royaltyMaster[lease]
except KeyError:
raise AppError('Royalty Master not found for Lease: ' + lease)
def getECONOilData(self, prodMonth):
try:
return self.econOilData[prodMonth]
except KeyError:
raise AppError('ECONOilData not found for: ' + str(prodMonth))
def getProducingEntity(self, lease):
try:
return self.producingEntity[lease]
except KeyError:
raise AppError('Producing Entity not found for Lease: ' + lease)
def getWell(self, wellID):
try:
return self.well[wellID]
except KeyError:
raise AppError('Well not found for wellID: ' + str(wellID))