Discussion of Python object-relational mapper SQLObject

Petr Jakeš | 6 Aug 2008 13:05

timestamp - date select

Hi,
in my tables I am using timestamp field.
I would like to select all records from the table that have the same date (not the same timestamp!!), say all records with the date 31.12.2007. What is the best way to do it in the SQLObject?

Thanks for your suggestions.

Petr Jakes

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

headers

Oleg Broytmann | 6 Aug 2008 13:20

Re: timestamp - date select

On Wed, Aug 06, 2008 at 01:05:02PM +0200, Petr Jake?? wrote:
> in my tables I am using timestamp field.
> I would like to select all records from the table that have the same date
> (not the same timestamp!!), say all records with the date 31.12.2007. What
> is the best way to do it in the SQLObject?

   Using 'date()' function in SQL (not in SQLObject):

from sqlobject import *
from sqlobject.sqlbuilder import *

__connection__ = "sqlite:/:memory:?debug=1"

from datetime import datetime

class Test(SQLObject):
   ts = DateTimeCol()

Test.createTable()

Test(ts=datetime.strptime('2008-08-01 21:44:33', '%Y-%m-%d %H:%M:%S'))

print list(Test.select(func.date(Test.q.ts) == '2008-08-01'))

 2/QueryIns:  INSERT INTO test (ts) VALUES ('2008-08-01 21:44:33')
 2/QueryR  :  INSERT INTO test (ts) VALUES ('2008-08-01 21:44:33')
 3/QueryOne:  SELECT ts FROM test WHERE ((test.id) = (1))
 3/QueryR  :  SELECT ts FROM test WHERE ((test.id) = (1))
 4/Select  :  SELECT test.id, test.ts FROM test WHERE ((date(test.ts)) = ('2008-08-01'))
 4/QueryR  :  SELECT test.id, test.ts FROM test WHERE ((date(test.ts)) = ('2008-08-01'))
[<Test 1 ts='datetime.datetime...)'>]

   'func' is SQLBuilder's magic that passes its attribute to SQL unchanged.
Note 'date(test.ts)'! You should check that your backend actually
implements the function (for 'date' most do).

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

headers

Petr Jakeš | 6 Aug 2008 19:10

Re: timestamp - date select

from sqlobject import *
from sqlobject.sqlbuilder import *

__connection__ = "sqlite:/:memory:?debug=1"

from datetime import datetime

class Test(SQLObject):
ts = DateTimeCol()

Test.createTable()

Test(ts=datetime.strptime('2008-08-01 21:44:33', '%Y-%m-%d %H:%M:%S'))

print list(Test.select(func.date(Test.q.ts) == '2008-08-01'))

2/QueryIns: INSERT INTO test (ts) VALUES ('2008-08-01 21:44:33')
2/QueryR : INSERT INTO test (ts) VALUES ('2008-08-01 21:44:33')
3/QueryOne: SELECT ts FROM test WHERE ((test.id) = (1))
3/QueryR : SELECT ts FROM test WHERE ((test.id) = (1))
4/Select : SELECT test.id, test.ts FROM test WHERE ((date(test.ts)) = ('2008-08-01'))
4/QueryR : SELECT test.id, test.ts FROM test WHERE ((date(test.ts)) = ('2008-08-01'))
[<Test 1 ts='datetime.datetime...)'>]

'func' is SQLBuilder's magic that passes its attribute to SQL unchanged.
Note 'date(test.ts)'! You should check that your backend actually
implements the function (for 'date' most do).

I am on Firebird right now. AFIK Firebird does not support 'date'.
The syntax which works is:

cast(test.ts as date) = '2008-08-01'

But Python does not accept following construction:

print list(Test.select(func.cast(Test.q.ts as date) == '2008-08-01'))

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

headers

Matthew Wilson | 6 Aug 2008 19:23

How can I add extra columns to my select results?

This is the SQL I want to do:

    select employee.*, 1 + 1 as x
    from employee
    where employee.id = 44;

I know how to do this:

    User.select(User.q.id == 44)

But how can I get the extra column?

In my real-world example, the extra column is more complex.

Matt

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

headers

Oleg Broytmann | 6 Aug 2008 19:29

Re: timestamp - date select

On Wed, Aug 06, 2008 at 07:10:12PM +0200, Petr Jake?? wrote:
> I am on Firebird right now. AFIK Firebird does not support 'date'.
> The syntax which works is:
> 
> cast(test.ts as date) = '2008-08-01'
> 
> But Python does not accept following construction:
> 
> print list(Test.select(func.cast(Test.q.ts as date) == '2008-08-01'))

   Of course no. Not SQLObject fault, though. ;) You can overcome this by
using strings:

print list(Test.select("cast(ts as date) == '2008-08-01'"))

   or by implementing your own 'func'-like object (name it 'cast') so you
can pass it two parameters; something like this:

print list(Test.select(cast(Test.q.ts, 'date') == '2008-08-01'))

   See sqlobject/sqlbuilder.py on how 'func' is implemented.

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

headers

Oleg Broytmann | 6 Aug 2008 19:34

Re: How can I add extra columns to my select results?

On Wed, Aug 06, 2008 at 05:23:34PM +0000, Matthew Wilson wrote:
> This is the SQL I want to do:
> 
>     select employee.*, 1 + 1 as x
>     from employee
>     where employee.id = 44;
> 
> I know how to do this:
> 
>     User.select(User.q.id == 44)
> 
> But how can I get the extra column?

   You have to declared the column in the User class:

class MyCol(Col):
   ...implementation...

class User(SQLObject):
   extra = MyCol()
   ...other columns...

   Or you can use sqlbuilder.Select() - it allows to select arbitrary list
of columns.

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

headers

Luis Javier Peris | 12 Aug 2008 18:18

Inheritance and MultipleJoin

I'd like to know if there is a restriction using inheritance about multiplejoin function because I'm getting an error and I don't know why. I've the following code:

class Question(InheritableSQLObject):
    filename = StringCol(default=None, length=255, unique=True)
    title = StringCol(default=None)
    default_mark = FloatCol(default=0.0)
    wording = RelatedJoin('Content')
    term_questions = RelatedJoin('Term', joinColumn='term', otherColumn='question',
                                 intermediateTable='Term_questions')
    answers = MultipleJoin('Answer')
    questionref = MultipleJoin('QuestionRef')

class Answer(SQLObject):
    value = StringCol(default=None)
    calification = FloatCol(default=None)
    ide = StringCol(default=None)
    question = ForeignKey('Question')

class Ord(Question):
    _inheritable = False
    shuffle = BoolCol()
    orderanswers = MultipleJoin('OrderChoice')

class OrderChoice(SQLObject):
    value = StringCol(default=None)
    ord = IntCol(default=None)
    ide = StringCol(default=None)
    question = ForeignKey('Ord')

and when I've a Ord instance (called 'q') and I do 'q.orderanswers' I get the following error:

sqlobject.dberrors.OperationalError: Unknown column 'ord_id' in 'where clause'

Any idea?

Thanks!!

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

headers

Oleg Broytmann | 12 Aug 2008 18:38

Re: MultipleJoin

On Tue, Aug 12, 2008 at 06:18:39PM +0200, Luis Javier Peris wrote:
> I'd like to know if there is a restriction using inheritance about
> multiplejoin function because I'm getting an error and I don't know why.
> I've the following code:
> 
> class Question(InheritableSQLObject):
>     filename = StringCol(default=None, length=255, unique=True)
>     title = StringCol(default=None)
>     default_mark = FloatCol(default=0.0)
>     wording = RelatedJoin('Content')
>     term_questions = RelatedJoin('Term', joinColumn='term',
> otherColumn='question',
>                                  intermediateTable='Term_questions')
>     answers = MultipleJoin('Answer')
>     questionref = MultipleJoin('QuestionRef')
> 
> 
> class Answer(SQLObject):
>     value = StringCol(default=None)
>     calification = FloatCol(default=None)
>     ide = StringCol(default=None)
>     question = ForeignKey('Question')
> 
> 
> class Ord(Question):
>     _inheritable = False
>     shuffle = BoolCol()
>     orderanswers = MultipleJoin('OrderChoice')
> 
> 
> class OrderChoice(SQLObject):
>     value = StringCol(default=None)
>     ord = IntCol(default=None)
>     ide = StringCol(default=None)
>     question = ForeignKey('Ord')
> 
> and when I've a Ord instance (called 'q') and I do 'q.orderanswers' I get
> the following error:
> 
> sqlobject.dberrors.OperationalError: Unknown column 'ord_id' in 'where
> clause'

   It has nothing with inheritance. I simplified the program (you have to
do this yourself) to:

class Ord(SQLObject):
    orderanswers = MultipleJoin('OrderChoice')

class OrderChoice(SQLObject):
    question = ForeignKey('Ord')

Ord.createTable()
OrderChoice.createTable()

ord = Ord()
print ord.orderanswers

   and got the same error. Well, with this simple program and SQL it
generates it easy to see where is the problem:

 1/QueryR  :  CREATE TABLE ord (
    id INTEGER PRIMARY KEY
)
 2/QueryR  :  CREATE TABLE order_choice (
    id INTEGER PRIMARY KEY,
    question_id INT CONSTRAINT question_id_exists REFERENCES ord(id) 
)
 3/QueryR  :  INSERT INTO ord VALUES (NULL)
 4/QueryR  :  SELECT NULL FROM ord WHERE ((ord.id) = (1))
 5/QueryR  :  SELECT id FROM order_choice WHERE ord_id = (1)

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

headers

Luis Javier Peris | 12 Aug 2008 20:08

Re: MultipleJoin

Ok, it's solved using joinColumn.

Thanks.

Greetings.

2008/8/12 Oleg Broytmann <phd <at> phd.pp.ru>

On Tue, Aug 12, 2008 at 06:18:39PM +0200, Luis Javier Peris wrote:
> I'd like to know if there is a restriction using inheritance about
> multiplejoin function because I'm getting an error and I don't know why.
> I've the following code:
>
> class Question(InheritableSQLObject):
> filename = StringCol(default=None, length=255, unique=True)
> title = StringCol(default=None)
> default_mark = FloatCol(default=0.0)
> wording = RelatedJoin('Content')
> term_questions = RelatedJoin('Term', joinColumn='term',
> otherColumn='question',
> intermediateTable='Term_questions')
> answers = MultipleJoin('Answer')
> questionref = MultipleJoin('QuestionRef')
>
>
> class Answer(SQLObject):
> value = StringCol(default=None)
> calification = FloatCol(default=None)
> ide = StringCol(default=None)
> question = ForeignKey('Question')
>
>
> class Ord(Question):
> _inheritable = False
> shuffle = BoolCol()
> orderanswers = MultipleJoin('OrderChoice')
>
>
> class OrderChoice(SQLObject):
> value = StringCol(default=None)
> ord = IntCol(default=None)
> ide = StringCol(default=None)
> question = ForeignKey('Ord')
>
> and when I've a Ord instance (called 'q') and I do 'q.orderanswers' I get
> the following error:
>
> sqlobject.dberrors.OperationalError: Unknown column 'ord_id' in 'where
> clause'

It has nothing with inheritance. I simplified the program (you have to
do this yourself) to:

class Ord(SQLObject):
orderanswers = MultipleJoin('OrderChoice')

class OrderChoice(SQLObject):
question = ForeignKey('Ord')

Ord.createTable()
OrderChoice.createTable()

ord = Ord()
print ord.orderanswers

and got the same error. Well, with this simple program and SQL it
generates it easy to see where is the problem:

1/QueryR : CREATE TABLE ord (
id INTEGER PRIMARY KEY
)
2/QueryR : CREATE TABLE order_choice (
id INTEGER PRIMARY KEY,
question_id INT CONSTRAINT question_id_exists REFERENCES ord(id)
)
3/QueryR : INSERT INTO ord VALUES (NULL)
4/QueryR : SELECT NULL FROM ord WHERE ((ord.id) = (1))
5/QueryR : SELECT id FROM order_choice WHERE ord_id = (1)

Oleg.
--
Oleg Broytmann http://phd.pp.ru/ phd <at> phd.pp.ru
Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

headers

Sam's Lists | 17 Aug 2008 12:46

Having a modified column filled in automagically...

Hi...

I'd like to be able to create a timestamp column called "mtime" in some of my columns. This would hold a timestamp of when the row was last modified.

But I'd like the timestamp to be updated automatically whenever sqlobject updates anything else in the row.

How can I do this? I'd like a solution that is easy to add to any table....maybe so easy that the table only has to have a column called mtime and then everything happens without anything extra on my part.

Thanks

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Mon	Tue	Wed	Thu	Fri	Sat	Sun

				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

5	May 2013
4	April 2013
5	February 2013
4	January 2013
6	December 2012
1	October 2012
2	September 2012
2	August 2012
26	June 2012
10	May 2012
14	April 2012
23	March 2012
31	February 2012
12	January 2012
16	December 2011
17	November 2011
12	August 2011
29	July 2011
9	June 2011
27	May 2011
26	April 2011
36	March 2011
44	February 2011
15	January 2011
30	December 2010
11	November 2010
26	October 2010
12	September 2010
23	August 2010
37	July 2010
13	June 2010
14	May 2010
71	April 2010
23	March 2010
63	February 2010
24	January 2010
11	December 2009
10	November 2009
20	October 2009
38	September 2009
57	August 2009
36	July 2009
30	June 2009
38	May 2009
19	April 2009
22	March 2009
46	February 2009
69	January 2009
65	December 2008
41	November 2008
37	October 2008
21	September 2008
30	August 2008
66	July 2008
31	June 2008
82	May 2008
69	April 2008
55	March 2008
139	February 2008
82	January 2008
54	December 2007
137	November 2007
101	October 2007
31	September 2007
81	August 2007
118	July 2007
82	June 2007
129	May 2007
120	April 2007
115	March 2007
102	February 2007
152	January 2007
135	December 2006
128	November 2006
330	October 2006
142	September 2006
161	August 2006
94	July 2006
160	June 2006
254	May 2006
218	April 2006
228	March 2006
157	February 2006
235	January 2006
235	December 2005
315	November 2005
466	October 2005
303	September 2005
336	August 2005
265	July 2005
166	June 2005
193	May 2005
152	April 2005
145	March 2005
258	February 2005
264	January 2005
177	December 2004
169	November 2004
60	October 2004
78	September 2004
65	August 2004
52	July 2004
67	June 2004
65	May 2004
88	April 2004
123	March 2004
92	February 2004
38	January 2004
61	December 2003
95	November 2003
85	October 2003
88	September 2003
64	August 2003
129	July 2003
107	June 2003
206	May 2003
204	April 2003
43	March 2003
2	February 2003