Thread: [SQLObject] PATCH: adds support for an interval timedelta column
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Matthew W. <ma...@tp...> - 2009-05-13 19:11:44
|
Hi -- I wrote some code to add a TimedeltaCol type. I only tested it in postgresql. I'll improve this code if you tell me what I should do. I find the SQLOjbect internals confusing. Matt Index: converters.py =================================================================== --- converters.py (revision 3881) +++ converters.py (working copy) @@ -187,6 +187,14 @@ registerConverter(Decimal, DecimalConverter) +def TimedeltaConverter(value, db): + + return """INTERVAL '%d days %d seconds'""" % \ + (value.days, value.seconds) + +registerConverter(datetime.timedelta, TimedeltaConverter) + + def sqlrepr(obj, db=None): try: reprFunc = obj.__sqlrepr__ Index: col.py =================================================================== --- col.py (revision 3881) +++ col.py (working copy) @@ -1517,6 +1517,32 @@ baseClass = SOPickleCol +class TimedeltaValidator(validators.Validator): + + def to_python(self, value, state): + return value + + def from_python(self, value, state): + return value + + +class SOTimedeltaCol(SOCol): + + def _postgresType(self): + return 'INTERVAL' + + def createValidators(self): + return [TimedeltaValidator(name=self.name)] + \ + super(SOTimedeltaCol, self).createValidators() + + + +class TimedeltaCol(Col): + baseClass = SOTimedeltaCol + + + + def pushKey(kw, name, value): if not kw.has_key(name): kw[name] = value |
From: Oleg B. <ph...@ph...> - 2009-05-13 19:39:06
|
On Wed, May 13, 2009 at 07:11:22PM +0000, Matthew Wilson wrote: > Hi -- I wrote some code to add a TimedeltaCol type. I only tested it in > postgresql. Thank you! > +def TimedeltaConverter(value, db): > + > + return """INTERVAL '%d days %d seconds'""" % \ > + (value.days, value.seconds) > + > +registerConverter(datetime.timedelta, TimedeltaConverter) The syntax is only valid in Postgres, so it has to be protected: def TimedeltaConverter(value, db): if db == 'postgres': return """INTERVAL '%d days %d seconds'""" % \ (value.days, value.seconds) else: raise ValueError('TimedeltaCol is only implemented for Postgres') > +class TimedeltaValidator(validators.Validator): > + def to_python(self, value, state): > + return value > + > + def from_python(self, value, state): > + return value Why empty validator? IWBN to have a test also. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Matthew W. <ma...@tp...> - 2009-05-13 20:05:50
|
On Wed 13 May 2009 03:39:01 PM EDT, Oleg Broytmann wrote: > On Wed, May 13, 2009 at 07:11:22PM +0000, Matthew Wilson wrote: >> Hi -- I wrote some code to add a TimedeltaCol type. I only tested it in >> postgresql. > > Thank you! > >> +def TimedeltaConverter(value, db): >> + >> + return """INTERVAL '%d days %d seconds'""" % \ >> + (value.days, value.seconds) >> + >> +registerConverter(datetime.timedelta, TimedeltaConverter) > > The syntax is only valid in Postgres, so it has to be protected: > > def TimedeltaConverter(value, db): > if db == 'postgres': > return """INTERVAL '%d days %d seconds'""" % \ > (value.days, value.seconds) > else: > raise ValueError('TimedeltaCol is only implemented for Postgres') > >> +class TimedeltaValidator(validators.Validator): >> + def to_python(self, value, state): >> + return value >> + >> + def from_python(self, value, state): >> + return value > > Why empty validator? > > IWBN to have a test also. I completely agree; can you point me to where in the code I should write the test? Also, are there any tests I can use as a guide? Later this week, I'll try to send in an updated patch with the code you added and a test. Matt |
From: Oleg B. <ph...@ph...> - 2009-05-13 20:36:03
|
On Wed, May 13, 2009 at 08:05:26PM +0000, Matthew Wilson wrote: > > IWBN to have a test also. > > I completely agree; can you point me to where in the code I should write > the test? Also, are there any tests I can use as a guide? Any test script would be ok - I'll convert it to the test suit myself. But of course a test in SQLObject style would be even better. SQLObject uses py.test as its testing environment. You can find a lot of tests in sqlobject/tests/ directory. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Matthew W. <ma...@tp...> - 2009-05-14 14:19:09
|
On Wed 13 May 2009 04:35:59 PM EDT, Oleg Broytmann wrote: > On Wed, May 13, 2009 at 08:05:26PM +0000, Matthew Wilson wrote: >> > IWBN to have a test also. >> >> I completely agree; can you point me to where in the code I should write >> the test? Also, are there any tests I can use as a guide? > > Any test script would be ok - I'll convert it to the test suit myself. > But of course a test in SQLObject style would be even better. > SQLObject uses py.test as its testing environment. You can find a lot of > tests in sqlobject/tests/ directory. I'm trying to write a test, but it seems like all the tests use a sqlite database, and sqlite doesn't support an interval datatype. Not sure what to do at this point. Here's the test (absolutely nothing fancy here): $ cat test_interval.py # vim: set expandtab ts=4 sw=4 filetype=python: from datetime import timedelta from sqlobject import * from sqlobject.tests.dbtest import * class Student(SQLObject): break_time = TimedeltaCol() def test_timedeltaCol(): setupClass(Student) td = timedelta(seconds=60*60) student = Student(break_time=td) assert student.break_time == td When I run py.test test_interval.py, I get an operational error that comes from sqlite rejecting the interval syntax. Ideas? Should I create a postgresql database in my setup method and then connect to that for my test? |
From: Oleg B. <ph...@ph...> - 2009-05-14 14:29:55
|
On Thu, May 14, 2009 at 02:18:47PM +0000, Matthew Wilson wrote: > I'm trying to write a test, but it seems like all the tests use a sqlite > database, and sqlite doesn't support an interval datatype. > > Not sure what to do at this point. Here's the test (absolutely > nothing fancy here): > > $ cat test_interval.py > # vim: set expandtab ts=4 sw=4 filetype=python: > > from datetime import timedelta > > from sqlobject import * > from sqlobject.tests.dbtest import * > > class Student(SQLObject): > break_time = TimedeltaCol() > > def test_timedeltaCol(): > setupClass(Student) > td = timedelta(seconds=60*60) > student = Student(break_time=td) > assert student.break_time == td > > When I run py.test test_interval.py, I get an operational error that > comes from sqlite rejecting the interval syntax. > > Ideas? Should I create a postgresql database in my setup method and > then connect to that for my test? It should be something like $ createdb test $ py.test test_interval.py -D postgres:/test $ dropdb test Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Matthew W. <ma...@tp...> - 2009-05-14 14:32:52
|
On Thu 14 May 2009 10:18:47 AM EDT, Matthew Wilson wrote: > On Wed 13 May 2009 04:35:59 PM EDT, Oleg Broytmann wrote: >> On Wed, May 13, 2009 at 08:05:26PM +0000, Matthew Wilson wrote: >>> > IWBN to have a test also. >>> >>> I completely agree; can you point me to where in the code I should write >>> the test? Also, are there any tests I can use as a guide? >> >> Any test script would be ok - I'll convert it to the test suit myself. >> But of course a test in SQLObject style would be even better. >> SQLObject uses py.test as its testing environment. You can find a lot of >> tests in sqlobject/tests/ directory. Here's another test, added to the bottom of test_converters.py: $ svn diff test_converters.py Index: test_converters.py =================================================================== --- test_converters.py (revision 3881) +++ test_converters.py (working copy) @@ -1,4 +1,5 @@ import sys +from datetime import timedelta from sqlobject.sqlbuilder import sqlrepr from sqlobject.sqlbuilder import SQLExpression, SQLObjectField, \ Select, Insert, Update, Delete, Replace, \ @@ -195,3 +196,8 @@ pass else: assert sqlrepr(Set([1])) == "(1)" + +def test_timedeltas(): + + assert sqlrepr(timedelta(seconds=30*60)) == \ + "INTERVAL '0 days 1800 seconds'" By the way, how do I write a converter that converts a python object one way for one database (sqlite) and another way for another database (postgres)? Are there any examples of this already? |
From: Oleg B. <ph...@ph...> - 2009-05-14 14:37:26
|
On Thu, May 14, 2009 at 02:32:31PM +0000, Matthew Wilson wrote: > Here's another test, added to the bottom of test_converters.py: Thanks! > By the way, how do I write a converter that converts a python object one > way for one database (sqlite) and another way for another database > (postgres)? Are there any examples of this already? Yes, see StringLikeConverter. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Daniel F. <fet...@go...> - 2009-05-15 17:09:07
|
>> Here's another test, added to the bottom of test_converters.py: > > Thanks! > >> By the way, how do I write a converter that converts a python object one >> way for one database (sqlite) and another way for another database >> (postgres)? Are there any examples of this already? > > Yes, see StringLikeConverter. Actually, I think TimedeltaCol could be implemented for sqlite as well. Although it doesn't have an INTERVAL data type it could use a plain TEXT instead and python would do the conversion. As far as I know sqlite only knows TEXT anyway so IntCol, StringCol, etc, all these things are implemented as TEXT and the data conversion is done by python. The TimedeltaCol could be similar. Cheers, Daniel -- Psss, psss, put it down! - http://www.cafepress.com/putitdown |
From: Oleg B. <ph...@ph...> - 2009-05-15 17:43:48
|
On Fri, May 15, 2009 at 10:09:01AM -0700, Daniel Fetchinson wrote: > Actually, I think TimedeltaCol could be implemented for sqlite as > well. Although it doesn't have an INTERVAL data type it could use a > plain TEXT instead and python would do the conversion. As far as I > know sqlite only knows TEXT anyway so IntCol, StringCol, etc, all > these things are implemented as TEXT and the data conversion is done > by python. The TimedeltaCol could be similar. Well, not exactly - SQLite understands and produces some data types - pysqlite passes them unmodified AFAIU. But anyway I did a similar trick with DecimalStringCol - it stores decimals as strings to work around SQLite's "type affinity" feature. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Daniel F. <fet...@go...> - 2009-05-18 21:31:02
|
>> Actually, I think TimedeltaCol could be implemented for sqlite as >> well. Although it doesn't have an INTERVAL data type it could use a >> plain TEXT instead and python would do the conversion. As far as I >> know sqlite only knows TEXT anyway so IntCol, StringCol, etc, all >> these things are implemented as TEXT and the data conversion is done >> by python. The TimedeltaCol could be similar. > > Well, not exactly - SQLite understands and produces some data types - > pysqlite passes them unmodified AFAIU. But anyway I did a similar trick > with DecimalStringCol - it stores decimals as strings to work around > SQLite's "type affinity" feature. I might be wrong, but from http://www.sqlite.org/datatype3.html I concluded that sqlite only knows about * TEXT * NUMERIC * INTEGER * REAL * NONE so things like date, datetime and anything else in sqlobject that is not one of the above 5 types will have to be stored as TEXT. So this trick of storing complicated python data types as TEXT is already happening in sqlobject (for example the one you mentioned, DecimalStringCol). The same thing can be done for the OP's proposed TimedeltaCol too. Cheers, Daniel -- Psss, psss, put it down! - http://www.cafepress.com/putitdown |
From: Oleg B. <ph...@ph...> - 2010-01-15 16:16:28
|
Hello! On Wed, May 13, 2009 at 07:11:22PM +0000, Matthew Wilson wrote: > Hi -- I wrote some code to add a TimedeltaCol type. I only tested it in > postgresql. I applied both your patches to the trunk, commit 4088. Thank you! Sorry it took so long. > By the way, how do I write a converter that converts a python object one > way for one database (sqlite) and another way for another database > (postgres)? Are there any examples of this already? > +def TimedeltaConverter(value, db): > + > + return """INTERVAL '%d days %d seconds'""" % \ > + (value.days, value.seconds) db is the name of the database. See StringLikeConverter for an example. Oleg. -- Oleg Broytman http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |