On Apr 10, 2010, at 12:28 PM, DrLongGhost wrote:
> I have a couple questions regarding the best way to do date math in
> SQLAlchemy with Postgresql and how I can write fixture-based unit
> tests for this without requiring an actual database in my test.
>
> There's 2 specific types of dates which I need to be retrieving.
> Written in SQL these would be:
> 1) CURRENT_TIMESTAMP
> 2) CURRENT_TIMESTAMP + interval 'X months'
>
> I know that I can use sqlalchemy.func.current_timestamp() to retrieve
> the first, but this breaks when I try to unit test it and there is no
> database session present. For example, consider the following code:
>
> @property
> def is_active(self):
> return ((not self.date_closed) or
> (self.date_closed >= sa.func.current_timestamp()))
the above code may return a python boolean, or may return a SQL expression. I
don't see how the above code actually accomplishes something useful -
"func.current_timestamp()" doesn't execute against the database in any case,
unless the return value of is_active() is used in a flush(), in which case its
only evaluated on the database side, and you only see its result if you then
re-SELECT the value (which the Session would do for you, when the expired
attribute is accessed). Your unit tests couldn't possibly rely upon flush()
and then re-fetching expired attributes if they also require no database to be
present. If you're actually trying to simulate full roundtrips to your
database and back without a database in use, that doesn't make much sense.
I'd just run my unit tests against a Postgresql database intended for tests, if
indeed you're looking for functionality that occurs via round trips through
flush and back - thats how this is normally done. The level of mocking needed
to "simulate" that would render your unit tests as not really testing what
actually occurs in your application.
> One idea my boss suggested was to use the Fudge
> module in my tests to overload sqlalchemy.func.current_timestamp()
> with a function that uses python's datetime module to return the
> current timestamp. Thus, when run in production, my code will use the
> database to get the current time, but when run in a unit test, we
> avoid a broken test by instead using python's datetime to get the
> current timestamp.
You could just use a function "my_current_timestamp" which your test suite can
mock to return a datetime instead of a SQL expression. But I don't see this
approach working for anything beyond the most small and trivial cases.
> Does anyone have any experience or examples of this type of approach?
>
> Also, while this seems like it will work for getting the current time,
> how would I use the same approach to get "CURRENT_TIMESTAMP + interval
> 'X months'"? I don't see a pre-written
> "sqlalchemy.func.current_timestamp_plus_interval('X months')
to get a SQL expression like that, call func.current_timestamp() +
datetime.interval(days=60) (note that you can't specify intervals in terms of
months since a "month" is not a fixed unit of time). Defintiely use 0.6 for
such expressions as many improvements have been made to PG date arithmetic in
0.6. If you were using "my_current_timestamp" as an abstraction from
func.current_timestamp(), it would work both as a SQL expression and as an
all-Python expression. See examples/derived_attributes/ in the distro for a
little exploration on that concept.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
