[Python] Trying to nest func functions for sqlite errors out with operationalerror...

I am trying to write up a couple of custom functions for sqlite in sqlalchemy. The issue I'm running into is that one function will feed into the other, but the execute errors out when that happens, as if it can't hand the return of one function to another.

Here is the class I'm working with:

class InString(GenericFunction):
type = String() # noqa: A003
name = "INSTR"
identifier = "strpos"
inherit_cache = True

def ToChar(date, format: str) -> str:
convertedFormat = convertFormat(format)
if isinstance(date, int):
date = datetime.strptime(str(date), convertedFormat)
return date.strftime(convertedFormat)

def ToTimestamp(date: str, format: str):
convertedFormat = convertFormat(format)
return datetime.strptime(date, convertedFormat)

class UnitDatabase(Database):
def __init__(self, logger=logging.getLogger(__name__)):
metadata = Base.metadata
super().__init__("sqlite:///:memory:", logger)
self._session = Session(bind=self.engine)
for _, cur_table in metadata.tables.items():
for autoincrement_column in [
column for column in cur_table._columns if column.autoincrement is True
]:
if isinstance(autoincrement_column.type, BigInteger):
autoincrement_column.type = Integer()
if _.split(".")[0] not in cur_table.name:
cur_table.name = f"{cur_table.name}_{cur_table.schema}"
cur_table.schema = None
Base.metadata.create_all(self.engine)

@listens_for(Pool, 'connect')
def register_sqlite_functions(dbapi_conn, connection_record):
dbapi_conn.create_function("to_char", 2, ToChar)
dbapi_conn.create_function("to_timestamp", 2, ToTimestamp)

@property
def session(self):
return self._session

def convertFormat(format: str):
for key, value in FORMAT_DICT.items():
format = format.replace(key, value)
return format


We need the postgres version of tochar to work for sqlite, so I wrote the methods to do what is needed. But the issue comes when I use them in a statement. I have to_timestamp inside of of to_char, the to timestamp runs through when I debug and the value looks exactly as expected. But when it's time to run to_char, it errors somewhere (I can't seem to find where at exactly, it's not in the actual functions themselves). The error is:

(sqlite3.OperationalError) user-defined function raised exception


Here is the simplified query:

worker_stmt = (
select()
.select_from(obs, sfc, lim)
.where(
obs.observationid == sfc.observationid,
obs.observationid == lim.observationid,
func.to_char(func.to_timestamp(cast(SfcObsTable.month, String), "MM"), "MM") == func.to_char(func.to_timestamp(cast(obs.observationtime, String), "YYYYMMDDHH24MISS"), "MM"),
)
)


The error is the very last line with the functions. I'm at a bit of a loss, I could try to find a way to do all of that 1 function but it may cause issues with other statements in other classes that utilize just one or the other. Is there something about my code causing the issue? Or how the functions are being called in the statement? Or some way to try and debug this? I have tried

sqlite3.enable_callback_tracebacks(True)


but that has not offered up any extra traces to help or other error messages to explain what is happening.

Continue reading...