Discussion of Python object-relational mapper SQLObject

C Vora | 5 Jan 23:23

Traversing a table

Hi

I'm somewhat new to Python and SQLObject (but have worked with ORM Wrappers before with Java). I have inherited a project which uses the above extensively. I have a somewhat novice question but couldn't find obvious answers so am posting here.

So the code I have basically does a .select() on a merge table and creates an output file (Comma separated say) with columns I'm interested in . I want to now additionally mark in this file -

a. duplicates that occur in succession only
b. objects that have certain values for a column

So for eg, if row X and row Y are duplicates, I want to have in the file a single row with the letter D at the end to mark Duplicate.

Whats the best way of doing this?

I thought of this pseudo-code:

for x in merge_table.select()
retrieve next row y ->> (how??)
if x==y //duplicate
add x to file & mark appropriately in file
else
add x and y to file
reposition cursor correctly to next unread row (if needed, how?)

I was trying to find a way to do this with SQLObject but couldn't easily figure out. I would like to avoid complicated cursor logic if I can help it.

Any pointers would be appreciated.

TIA,
CV

------------------------------------------------------------------------------

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

headers

Petr Jakeš | 6 Jan 00:10

Re: Traversing a table

Hi,
you are not telling us about columns. Are you trying to check if all collumns in the row are identical with some other row in the table? If yes, I would try to sort columns in the select. Than something like this can work (untested):

counter=0
lastRow=""
for actualRow in merge_table.select(orderBy = merge_table.q.firstColumn,orderBy = merge_table.q.secondColumn):
    if actualRow is lastRow:
        counter+=1
    else:
        if counter:
            write actual columns + counter+"D" to the file
            counter = 0
    lastRow=actualRow

HTH

Petr Jakeš

On Mon, Jan 5, 2009 at 11:23 PM, C Vora <to.chetanvora <at> gmail.com> wrote:

Hi

I'm somewhat new to Python and SQLObject (but have worked with ORM Wrappers before with Java). I have inherited a project which uses the above extensively. I have a somewhat novice question but couldn't find obvious answers so am posting here.

So the code I have basically does a .select() on a merge table and creates an output file (Comma separated say) with columns I'm interested in . I want to now additionally mark in this file -

a. duplicates that occur in succession only
b. objects that have certain values for a column

So for eg, if row X and row Y are duplicates, I want to have in the file a single row with the letter D at the end to mark Duplicate.

Whats the best way of doing this?

I thought of this pseudo-code:

for x in merge_table.select()
retrieve next row y ->> (how??)
if x==y //duplicate
add x to file & mark appropriately in file
else
add x and y to file
reposition cursor correctly to next unread row (if needed, how?)

I was trying to find a way to do this with SQLObject but couldn't easily figure out. I would like to avoid complicated cursor logic if I can help it.

Any pointers would be appreciated.

TIA,
CV
------------------------------------------------------------------------------

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

------------------------------------------------------------------------------

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

headers

Zoran Bošnjak | 7 Jan 09:40

table ralations

Hello,
I have 2 simple tables:
- block table (set of blocks)
- connection tables (connections between blocks)

I would like to setup a database to prevent creating connection to/from non-existant blocks.
For example: If I start with empty database, the script below should fail (but instead... the connection
between nonexistant blocks is created).
What's wrong?

If I delete a block, all connection related to this block should be deleted automaticaly. This works fine
with "cascade=True" option.

Thanks,
Zoran

------
#! /usr/bin/env python
# -*- coding: utf-8 -*-

import sys, os
from sqlobject import *

class Block(SQLObject):
    name = StringCol()

class Connection(SQLObject):
    src = ForeignKey('Block', cascade=True)
    dst = ForeignKey('Block', cascade=True)

db_filename = os.path.abspath(sys.argv[1]) print db_filename

# load database
connection_string = 'sqlite:' + db_filename connection = connectionForURI(connection_string)
sqlhub.processConnection = connection

# create tables
Block.createTable(ifNotExists=True)
Connection.createTable(ifNotExists=True)

# create dumb connection
# it should not work, because there is no blocks!!!
Connection(src=150, dst=250)

----

Dump database just created:

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE block (
    id INTEGER PRIMARY KEY,
    name TEXT
);
CREATE TABLE connection (
    id INTEGER PRIMARY KEY,
    src_id INT CONSTRAINT src_id_exists REFERENCES block(id) ON DELETE CASCADE,
    dst_id INT CONSTRAINT dst_id_exists REFERENCES block(id) ON DELETE CASCADE ); 
INSERT INTO "connection" VALUES(1,150,250); COMMIT;
sqlite> 

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

headers

Petr Jakeš | 7 Jan 09:51

Re: table ralations

# create dumb connection
# it should not work, because there is no blocks!!!
Connection(src=150, dst=250)

Why do you think this should not work? AFIK above mentioned just insert one row (record) in your table Connection. Exactly as the SQL below describes.

INSERT INTO "connection" VALUES(1,150,250); COMMIT;
sqlite>

Petr

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

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

headers

Zoran Bošnjak | 7 Jan 10:17

Re: table ralations

Sorry for the confusion...

I don't want to have a "Connection" in a database unless the connection is between existing "Blocks".

Please correct my class definition for Block and Connection so, that it won't be even possible to create such connection.

Zoran

From: petr.jakes.tpc <at> gmail.com [mailto:petr.jakes.tpc <at> gmail.com] On Behalf Of Petr Jakeš
Sent: Wednesday, January 07, 2009 9:51 AM
To: Zoran Bošnjak
Cc: sqlobject-discuss <at> lists.sourceforge.net
Subject: Re: [SQLObject] table ralations

# create dumb connection
# it should not work, because there is no blocks!!!
Connection(src=150, dst=250)

Why do you think this should not work? AFIK above mentioned just insert one row (record) in your table Connection. Exactly as the SQL below describes.

INSERT INTO "connection" VALUES(1,150,250); COMMIT;
sqlite>

Petr

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

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

headers

GeorgeFLeCompte | 7 Jan 18:30

Please remove me from this mailing list

george <at> lecompte.org
GeorbeLeCompte <at> comcast.net

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

headers

inhahe | 7 Jan 18:32

how to get all records in a table?

hi, can somebody tell me how to select or get all records in a table?
i can't seem to find it in the documentation.  thanks

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

headers

Petr Jakeš | 7 Jan 19:41

Re: how to get all records in a table?

Can't believe you can not find it. Anyway.... HTH

from sqlobject import *
sqlhub.processConnection = connectionForURI('sqlite:/:memory:')
class Person(SQLObject):
fname = StringCol()
mi = StringCol(length=1, default=None)
lname = StringCol()

Person.createTable()
Person(fname="John", lname="Doe")
Person(fname="Inhahe", lname="Noname")

allRows = Person.select()
HTH.

Petr Jakeš

On Wed, Jan 7, 2009 at 6:32 PM, inhahe <inhahe <at> gmail.com> wrote:

hi, can somebody tell me how to select or get all records in a table?
i can't seem to find it in the documentation. thanks

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

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

headers

Petr Jakeš | 7 Jan 23:54

Re: Traversing a table

I don't think such a duplicity (to keep data in the database and some of rows in the text file) is the way to go.

Try to use SQL distinct commands (http://www.sqlobject.org/SelectResults.html#distinct) instead.

It will offer unique rows from the database to your audience.

Petr

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

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

headers

Petr Jakeš | 8 Jan 01:54

Re: how to get all records in a table?

On Thu, Jan 8, 2009 at 12:31 AM, inhahe <inhahe <at> gmail.com> wrote:

Oops, I thought I had tried that and that it didn't work (and I didn't
see the documentation explicitly mention calling select with no
parameters), but it just didn't work due to something I had missed
elsewhere. Thanks.

====== 8< ==========
from sqlobject import *
from sqlobject.sqlbuilder import *
connInMem = connectionForURI('sqlite:/:memory:')

sqlhub.processConnection = connectionForURI('sqlite:/:memory:')
class Person(SQLObject):
    fname = StringCol()
    mi = StringCol(length=1, default=None)
    lname = StringCol()

Person.createTable()
Person(fname="John", lname="Doe")
Person(fname="John", lname="Doe")
Person(fname="Inhahe", lname="Noname")
Person(fname="Inhahe", lname="Noname")

Person._connection.debug = True
returnedValues = connInMem.queryAll("SELECT DISTINCT person.fname, person.mi, person.lname from person")
print returnedValues
====== 8< ==========

Following code generates valid SQL query, but because id column is included, it returns all rows from the database (id is unique for each row).

for row in Person.select().distinct():
    print row

I do not know, if it is possible to force SQLObject not to include person.id column in to the select query, but I guess no (maybe Oleg can help).

Regards

Petr

------------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It is the best place to buy or sell services for
just about anything Open Source.
http://p.sf.net/sfu/Xq1LFB

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

5	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

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