>
> If you still want to store it as a string, I guess you'll need to try
> parsing it as a datetime and then fall back to parsing it as a date.
Exactly! That's my intention. I'm so excited that my idea has affirmed by a
pro now:)
You haven't said what database you are using
SQLite in Python 3.
But I'm still more inclined towards storing a long string of datetime/date
into a column, plus a suffix column indicating whether it's a date, a
datetime, or in future a time range. Is there a way to do this at SQL
expression level using hybrid_property?
I'm definitely going to check out TypeDecorator. It's another new thing but
sounds very interesting. Thanks for mentioning!
On Fri, Sep 16, 2016 at 2:28 AM, Simon King <si...@simonking.org.uk> wrote:
> Another option would be to have separate columns for date and time,
> and leave the time column NULL when it's not present.
>
> If you still want to store it as a string, I guess you'll need to try
> parsing it as a datetime and then fall back to parsing it as a date.
> You haven't said what database you are using, but for MySQL it might
> be something like this:
>
> IFNULL(STR_TO_DATE(timepoint, <your-datetime-format>),
> STR_TO_DATE(timepoint, <your-date-format>))
>
> If you're not actually going to filter on these columns, I don't think
> I'd do any of this at all. Instead, I'd use something like a
> TypeDecorator to convert the values when loading from and saving to
> the database.
>
> Simon
>
>
> On Fri, Sep 16, 2016 at 9:53 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> > 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/topic/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.
>
> --
> 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/
> topic/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.
