def cast_omnitime(omnitime):
"""Function to construct a string-to-datetime case clause.
Many dates in the OmniCenterCPC01 database are stored as strings in the
format YYYYMMDDHHMMSSSS. Neither SQL Server nor SQLAlchemy have built-in
methods to convert these to datetime objects, so this method constructs
a SQL clause to cast the string first as a BigInteger and then as a native
SQL Server DateTime.
Args:
omnitime (str): The string representation of a datetime to be cast
as a SQL Server datetime object.
Returns:
case: A SQLAlchemy SQL expression clause object.
"""
return case([(
omnitime.notlike(""),
cast(
func.format(cast(omnitime, BigInteger),
'####-##-## ##:##:##"."##'),
DateTime,
),
)])
def invalid_query(self, session, **kw):
return session.query(
func.regexp_replace(Config.key, u'\D', '', u'g').label('id'),
func.nullif(Config.value, u'__gone__').label('target'))\
.filter(Config.key.like(u'__Source_%%__'))\
.filter(
session.query(orm.aliased(Config))
.filter_by(key=func.format(u'__Source_%s__', Config.value)).exists())\
.order_by('id', 'target')
def invalid_query(self, session, **kw):
return session.query(
func.regexp_replace(Config.key, u'\D', '', u'g').label('id'),
func.nullif(Config.value, u'__gone__').label('target'))\
.filter(Config.key.like(u'__Source_%%__'))\
.filter(
session.query(orm.aliased(Config))
.filter_by(key=func.format(u'__Source_%s__', Config.value)).exists())\
.order_by('id', 'target')