If I store the as DateTime values and with a second column to indicate
whether it's a date or datetime, it would look like this for a Date:
col1: "2016-09-16 00:00:00", col2: "date only"
It looks so messy to me:)
I'd prefer to have Date and DateTime distinctively written in my database.
I admit that it's one of my personal preference though:)
On Fri, Sep 16, 2016 at 1:49 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> Thanks for reply.
>
> The reason is simple. I plan in the future to accommodate datetime range
> into that column as well, so storing this logic as plain strings gives the
> most flexibility. This is a project that I'm learning by making. So I would
> like to try all the new features for later tasks.
>
> I'm fascinated by hybrid_property, but is stuck now when trying to
> dispatch functions at the SQL expression level.
>
> Jinghui
>
> On Fri, Sep 16, 2016 at 1:34 AM, Simon King <si...@simonking.org.uk>
> wrote:
>
>> Ignore SQLAlchemy for the moment and describe what you are trying to
>> achieve. It looks like you want to store dates and times as strings in
>> your database (rather than the appropriate type), and yet still be
>> able to perform date-related operations on them qhen querying. Is that
>> right? Is there a reason why you can't store them as proper DateTime
>> values (perhaps with a second column to indicate whether or not the
>> time part is valid)?
>>
>> Simon
>>
>> On Fri, Sep 16, 2016 at 4:39 AM, Jinghui Niu <niujing...@gmail.com>
>> wrote:
>> > I have the following code snippet, I marked my question in a comment
>> line
>> > inside the hybrid_property.expression part. As you can see, it is now
>> not
>> > implemented:
>> >
>> > from sqlalchemy.ext.declarative import declared_attr
>> > from sqlalchemy import Column, Integer, String, Unicode, UnicodeText
>> > from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
>> > import arrow
>> >
>> >
>> > datetimeString_format = {
>> > "UTC": "%Y-%m-%d %H:%M:%S+00:00",
>> > "local_with_timezoneMarker": "%Y-%m-%d %H:%M:%S %Z",
>> > "local_without_timezoneMarker": "%Y-%m-%d %H:%M:%S"
>> > }
>> >
>> >
>> > dateString_format = "%Y-%m-%d"
>> >
>> >
>> > class My_TimePoint_Mixin:
>> > # define output formats:
>> > datetimeString_inUTC_format = "%Y-%m-%d %H:%M:%S+00:00"
>> > datetimeString_naive_format = "%Y-%m-%d %H:%M:%S"
>> >
>> >
>> > # instrumented fields:
>> > _TimePoint_in_database = Column('timepoint', String, nullable=False)
>> > _TimePoint_in_database_suffix = Column(
>> > 'timepoint_suffix', String, nullable=False)
>> >
>> >
>> > @hybrid_property
>> > def timepoint(self):
>> > twoPossibleType_handlers = [
>> > self._report_ACCRT_DATE,
>> > self._report_ACCRT_DATETIME
>> > ]
>> > for handler in twoPossibleType_handlers:
>> > print("handler: ", handler)
>> > try:
>> > return handler(self)
>> > except (AssertionError, ValueError) as e:
>> > logging.warning("Try next handler!")
>> >
>> > @timepoint.expression
>> > def timepoint(cls):
>> > pass
>> > # How can I implement SQL expression for a equivalent function
>> > dispatch here? There seems to be no SQL equivalent structure for this?
>> >
>> >
>> > @timepoint.setter
>> > def timepoint(self, datetimepointOBJ):
>> > handlers_lookup = {
>> > datetime.datetime: self._set_ACCRT_DATETIME,
>> > datetime.date: self._set_ACCRT_DATE
>> > }
>> > this_time = type(datetimepointOBJ)
>> > this_handler = handlers_lookup[this_time]
>> > print("handler: ", this_handler)
>> > this_handler(datetimepointOBJ)
>> >
>> >
>> > def _report_ACCRT_DATE(self):
>> > """Accurate Date"""
>> > assert self._TimePoint_in_database_suffix == "ACCRT_DATE"
>> > date_string = self._TimePoint_in_database
>> > dateString_format = "%Y-%m-%d"
>> > # return a datetime.date
>> > return datetime.datetime.strptime(date_string,
>> > dateString_format).date()
>> >
>> >
>> > def _report_ACCRT_DATETIME(self):
>> > """Accurate DateTime"""
>> > assert self._TimePoint_in_database_suffix in
>> pytz.all_timezones_set
>> > datetimeString_inUTC = self._TimePoint_in_database
>> > utc_naive = datetime.datetime.strptime(
>> > datetimeString_inUTC, self.datetimeString_inUTC_format)
>> > utc_timepoint = arrow.get(utc_naive, "utc")
>> > # localize
>> > local_timepoint =
>> > utc_timepoint.to(self._TimePoint_in_database_suffix)
>> > # return a datetime.datetime
>> > return local_timepoint.datetime
>> >
>> >
>> > def _set_ACCRT_DATETIME(self, datetimeOBJ_aware):
>> > assert isinstance(datetimeOBJ_aware, datetime.datetime), "Must
>> be a
>> > valid datetime.datetime!"
>> > assert datetimeOBJ_aware.tzinfo is not None, "Must contain
>> tzinfo!"
>> > utctime_aware_arrow = arrow.get(datetimeOBJ_aware).to('utc')
>> > utctime_aware_datetime = utctime_aware_arrow.datetime
>> > store_datetime_string = utctime_aware_datetime.strftime(
>> > self.datetimeString_inUTC_format)
>> > self._TimePoint_in_database = store_datetime_string
>> >
>> >
>> > def _set_ACCRT_DATE(self, dateOBJ):
>> > store_date_string = dateOBJ.isoformat()
>> > self._TimePoint_in_database = store_date_string
>> >
>> > Could someone please point out a clue as to implement the
>> > hybrid_property.expression part? Thanks.
>> >
>> > I've also posted a similar issue on stackoverflow.com, for reference:
>> >
>> > http://stackoverflow.com/questions/39485440/calling-from-
>> the-same-class-why-is-one-treated-as-bound-method-while-the-other
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups
>> > "sqlalchemy" group.
>> > To unsubscribe from this group and stop receiving emails from it, send
>> an
>> > email to sqlalchemy+unsubscr...@googlegroups.com.
>> > To post to this group, send email to sqlalchemy@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> You received this message because you are subscribed to a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit https://groups.google.com/d/to
>> pic/sqlalchemy/i8k2u7bK5x4/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
