[Python] SqlAlchemy Trying to convert Oracle Stored Procedure to Python/Postgres, issues with...

I'm working on a project where we are to convert some Oracle stored procedures into python via sqlalchemy. The issue is that we are also to use sqlite (We are unable to change this as those above us have made the decision for this structure) for testing and that causes issues with certain functions (in particular instr/position). I started off attempting to just do everything in the where clause with func.substr and func.strpos but strpos causes a failure in sqlite as it doesn't exist.

I then attempted to work it with a filter statement using python slicing and find to get what I needed but this returns an error that "getItem is not supported in this expression". I'm not quite sure what is causing that issue, and I'm starting to hit a wall as to how I should work these statements out so that they work both in prod and testing.

This is the original plsql statement:

UPDATE MC.SFCOBS SFC
SET SFC.WINDDIRECTION = NULL,
SFC.WINDSPEED = NULL,
SFC.WINDCONDITIONS = NULL,
SFC.WINDCONDITIONSQUALITYCODE = '5',
SFC.WINDSPEEDQUALITYCODE = CASE WHEN SFC.WINDSPEED = 0 THEN '5' ELSE NULL END
WHERE SFC.WINDCONDITIONS = 'C'
AND (SFC.WINDSPEED = 0 OR SFC.WINDSPEED IS NULL)
AND EXISTS (
SELECT 'X'
FROM MC.JOBS OBS
WHERE OBS.OBSERVATIONID = SFC.OBSERVATIONID
AND OBS.REPORTTYPECODE IN ('FM-12', 'FM-13', 'FM-14')
AND OBS.INSERTIONTIME >= startdatetime
AND OBS.INSERTIONTIME < enddatetime
AND OBS.OBSERVATIONID = SFC.OBSERVATIONID)
AND SFC.OBSERVATIONID = (
SELECT MSG.OBSERVATIONID
FROM MC.JMO_MESSAGE MSG
WHERE MSG.OBSERVATIONID = SFC.OBSERVATIONID
AND (
SUBSTR(MSG.ORIGINALMESSAGE, 16, 4) = '// 1'
OR SUBSTR(MSG.ORIGINALMESSAGE, 14, 5) = '//// '
OR SUBSTR(MSG.ORIGINALMESSAGE, INSTR (MSG.ORIGINALMESSAGE, ' 26/// ') + 10, 3) = '// '
AND INSTR(MSG.ORIGINALMESSAGE, ' 26/// ') > 3
OR SUBSTR(MSG.ORIGINALMESSAGE, INSTR (MSG.ORIGINALMESSAGE, ' 99') + 22, 3) = '// '
AND INSTR(MSG.ORIGINALMESSAGE, ' 99') BETWEEN 11 AND 20
OR SUBSTR (MSG.ORIGINALMESSAGE, 7, 6) IN ('45/// ', '46/// ')
AND SUBSTR (MSG.ORIGINALMESSAGE, 16, 3) = '// ')
);


the issue is in the very end where all of the substr and isntr are.

Here is the original where clause

.where(
JmoMessageTable.observationid == sfc.observationid
or_(
func.substr(JmoMessageTable.originalmessage, 16, 4) == "// 1",
func.substr(JmoMessageTable.originalmessage, 14, 5) == "//// ",
func.substr(JmoMessageTable.originalmessage, func.strpos(JmoMessageTable.originalmessage, " 26/// ") + 10, 3) == "// "
),
or_(
func.substr(JmoMessageTable.originalmessage, 16, 4) == "// 1",
func.substr(JmoMessageTable.originalmessage, func.strpos(JmoMessageTable.originalmessage, " 99") + 22, 3) == "// "
),
or_(
func.strpos(JmoMessageTable.originalmessage, " 99").between(11, 20),
func.substr(JmoMessageTable.originalmessage, 7, 6).in_(("45/// ", "46/// "))
),
func.substr(JmoMessageTable.originalmessage, 16, 3) == "// "
)


Here is the filter I attempted after

.filter(
or_(
JmoMessageTable.originalmessage[16:20] == "// 1",
JmoMessageTable.originalmessage[14:19] == "//// ",
JmoMessageTable.originalmessage[(JmoMessageTable.originalmessage.find(" 26/// ") + 10)(JmoMessageTable.originalmessage.find(" 26/// ") + 10) + 3)] == "// "
),
or_(
JmoMessageTable.originalmessage[16:20] == "// 1",
JmoMessageTable.originalmessage[(JmoMessageTable.originalmessage.find(" 99") + 22)(JmoMessageTable.originalmessage.find(" 99") + 22) + 3)] == "// ",
),
or_(
(JmoMessageTable.originalmessage.find(" 99") >= 11 & JmoMessageTable.originalmessage.find(" 99") <= 20),
(JmoMessageTable.originalmessage[7:13] == "45/// " | JmoMessageTable.originalmessage[7:13] == "46/// ")
),
JmoMessageTable.originalmessage[16:19] == "// "
)


Is there a way to work this as I have it? Or am I going to be stuck using ORM to attempt to pull and filter everything based on these parameters? Any help or input would be helpful! Thanks

Continue reading...