Thread: [SQLObject] select from 2 tables
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Olo <jol...@o2...> - 2005-12-12 19:01:15
|
Hello I have a problem with select query. I have class Supplier(SQLObject): name ... class Delivery(SQLObject): date ... supplier = ForeignKey('Supplier') and I need query like: SELECT Supplier.*, Delivery.* FROM Supplier, Delivery where Supplier.id == Delivery.supplierID or something like that. I tried to do this with JOIN or LEFTJOINOn but it wasn't worked. |
From: Oleg B. <ph...@ma...> - 2005-12-12 19:39:59
|
On Mon, Dec 12, 2005 at 07:36:56PM +0100, Olo wrote: > class Supplier(SQLObject): > name ... > > class Delivery(SQLObject): > date ... > supplier = ForeignKey('Supplier') > > and I need query like: > > SELECT Supplier.*, Delivery.* SQLObject maps SQL to Python objects. "SELECT * FROM Supplier" is mapped to a list of Supplier instances. "SELECT * FROM Delivery" is mapped to a list of Delivery instances. To what objects are you expecting "SELECT Supplier.*, Delivery.*" would be mapped?! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Olo <jol...@o2...> - 2005-12-12 20:06:16
|
Oleg Broytmann napisał(a): > On Mon, Dec 12, 2005 at 07:36:56PM +0100, Olo wrote: > >>class Supplier(SQLObject): >> name ... >> >>class Delivery(SQLObject): >> date ... >> supplier = ForeignKey('Supplier') >> >>and I need query like: >> >>SELECT Supplier.*, Delivery.* > > > SQLObject maps SQL to Python objects. "SELECT * FROM Supplier" is mapped > to a list of Supplier instances. "SELECT * FROM Delivery" is mapped to a > list of Delivery instances. > To what objects are you expecting "SELECT Supplier.*, Delivery.*" would > be mapped?! > > Oleg. I don't know. I just need to get: Delivery.date, Delivery.supplierID, Supplier.name I think that is imposible to do this with SQLObject but if is posible the best for me will be a list. Olo |
From: David M. C. <da...@da...> - 2005-12-12 20:14:27
|
On Mon, Dec 12, 2005 at 10:39:40PM +0300, Oleg Broytmann wrote: > To what objects are you expecting "SELECT Supplier.*, Delivery.*" would > be mapped?! A tuple of Supplier and Delivery objects. Dave Cook |
From: Oleg B. <ph...@ma...> - 2005-12-12 20:25:07
|
On Mon, Dec 12, 2005 at 12:14:13PM -0800, David M. Cook wrote: > > To what objects are you expecting "SELECT Supplier.*, Delivery.*" would > > be mapped?! > > A tuple of Supplier and Delivery objects. SQLObject doesn't do that and probably never will. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Olo <jol...@o2...> - 2005-12-12 20:37:09
|
Oleg Broytmann napisał(a): > On Mon, Dec 12, 2005 at 12:14:13PM -0800, David M. Cook wrote: > >>> To what objects are you expecting "SELECT Supplier.*, Delivery.*" would >>>be mapped?! >> >>A tuple of Supplier and Delivery objects. > > > SQLObject doesn't do that and probably never will. > > Oleg. Ok, thanks So can I use simple SQL (select * from...) with SQLObject? Olo |
From: Oleg B. <ph...@ma...> - 2005-12-12 20:42:20
|
On Mon, Dec 12, 2005 at 09:34:00PM +0100, Olo wrote: > So can I use simple SQL (select * from...) with SQLObject? connection.query("select * from ...") from sqlobject.sqlbuilder import Select connection.query(connection.sqlrepr(Select(...))) But you'd better use ForeignKey and do Supplier.select() and then loop over the result dereferencing an every object... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Olo <jol...@o2...> - 2005-12-12 20:54:31
|
Oleg Broytmann napisał(a): > On Mon, Dec 12, 2005 at 09:34:00PM +0100, Olo wrote: > >>So can I use simple SQL (select * from...) with SQLObject? > > > connection.query("select * from ...") > > from sqlobject.sqlbuilder import Select > connection.query(connection.sqlrepr(Select(...))) > > But you'd better use ForeignKey and do Supplier.select() and then loop > over the result dereferencing an every object... > > Oleg. Ok Thank's |
From: Justin A. <JA...@ua...> - 2005-12-12 20:51:46
|
On Mon, 2005-12-12 at 23:24 +0300, Oleg Broytmann wrote: > On Mon, Dec 12, 2005 at 12:14:13PM -0800, David M. Cook wrote: > > > To what objects are you expecting "SELECT Supplier.*, Delivery.*" would > > > be mapped?! > > > > A tuple of Supplier and Delivery objects. > > SQLObject doesn't do that and probably never will. > > Oleg. Sure it does, see http://pythonpaste.org/archives/message/20051124.175649.ca2bde63.en.html this: >>> for s, d in flatjoin([Supplier,Delivery]): >>> print s.id, s.name, d.date would work. -- -- Justin Azoff |
From: Jorge G. <go...@ie...> - 2005-12-12 20:29:05
|
"David M. Cook" <da...@da...> writes: > On Mon, Dec 12, 2005 at 10:39:40PM +0300, Oleg Broytmann wrote: > > > To what objects are you expecting "SELECT Supplier.*, Delivery.*" would > > be mapped?! > > A tuple of Supplier and Delivery objects. Joined by which column? "zip"ed? Or just the two random result sets? -- Jorge Godoy <go...@ie...> |
From: David M. C. <da...@da...> - 2005-12-18 00:05:55
|
On Mon, Dec 12, 2005 at 06:21:38PM -0200, Jorge Godoy wrote: > "David M. Cook" <da...@da...> writes: > > > On Mon, Dec 12, 2005 at 10:39:40PM +0300, Oleg Broytmann wrote: > > > > > To what objects are you expecting "SELECT Supplier.*, Delivery.*" would > > > be mapped?! > > > > A tuple of Supplier and Delivery objects. > > Joined by which column? "zip"ed? Or just the two random result sets? Sorry, I was thinking of a left join. Hibernate can do it, and that's written in clunky ol' Java. Perhaps what Olo really needed was an "eager fetch", so that accessing attributes (e.g. employee.department) doesn't incur a select for each access. Dave Cook |
From: Olo <jol...@o2...> - 2005-12-12 20:29:08
|
David M. Cook napisał(a): > On Mon, Dec 12, 2005 at 10:39:40PM +0300, Oleg Broytmann wrote: > > >> To what objects are you expecting "SELECT Supplier.*, Delivery.*" would >>be mapped?! > > > A tuple of Supplier and Delivery objects. > > Dave Cook > Tuple will be OK too. Olo |
From: gary n. <li...@ga...> - 2005-12-13 05:00:47
|
One thing I would like to see in sqlobject is to turn an arbitary result set(from a SQL Select) into a SO class which can be loop through as an iterator. Sure, it is now possible to map individual raw tables to SQLObject then define foreignkeys/select rules and loop. But that is getting back to the dbase era and effectively turning a big buck super poweful Oracle(or whatever brand) RDBMS into an ISAM. I saw another post in this thread of implementing "join" in python(again building on top of the individual object and foreign key links), that IMO is a homebrew of the already exist SQL engine in whatever RDBMS backend SQLObject supports, only much limited in functionalities. "Oleg Broytmann" <ph...@ma...> wrote in message news:200...@ph...... > On Mon, Dec 12, 2005 at 09:34:00PM +0100, Olo wrote: > > So can I use simple SQL (select * from...) with SQLObject? > > connection.query("select * from ...") > > from sqlobject.sqlbuilder import Select > connection.query(connection.sqlrepr(Select(...))) > > But you'd better use ForeignKey and do Supplier.select() and then loop > over the result dereferencing an every object... > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click |
From: <jo...@co...> - 2005-12-13 07:53:07
|
Why not define a view of the join in your flavor of SQL and then let SQLO= bject query the view?=20 That would do the thing you are looking for if you are only trying to ret= rieve. The problem with this in most SQLs is that you probably can't update. An= d in most cases, it is EXTREMELY difficult to update through a query unless the semantics of the= underlying tables is simple and clear. The reason is that you could have duplicated values in= columns representing a foreign key table and you could update what is in essence the same row in= the underlying table from different rows in the "view" incorrectly. That is one of the nice things about SQLObject. It maps the underlying r= elationships so that proper updating takes place. If I update an underlying foreign key table= row from one row, the same update appears in the all the rest of the rows (as it should) withou= t any fuss or bother.=20 However, yes, I have to treat the tables independently, but that is what = you have to do anyway in most cases. This has always been one of the big issues with SQL databases. If you ju= st want to retrieve, then it is terrific. If you want to access and modify single tables, it is al= so very good. If you need to access and update lots of tables simultaneously, it has tr= ouble handlng all the nuances for you properly and so you are forced (as you put it) to turn yo= ur "big buck super powerful Oracle or whatever DBMS into an ISAM". Unfortunately, that is p= retty much what you have to do, whether you are using Python and SQLObject or Python alone, or Jav= a or for that matter, even the database language itself like PL/SQL. There is no silver bullet= in there that just does it all for you correctly. Jon > One thing I would like to see in sqlobject is to turn an arbitary resul= t > set(from a SQL Select) into a SO class which can be loop through as an > iterator. Sure, it is now possible to map individual raw tables to SQLO= bject > then define foreignkeys/select rules and loop. But that is getting back= to > the dbase era and effectively turning a big buck super poweful Oracle(o= r > whatever brand) RDBMS into an ISAM. > > I saw another post in this thread of implementing "join" in python(agai= n > building on top of the individual object and foreign key links), that I= MO is > a homebrew of the already exist SQL engine in whatever RDBMS backend > SQLObject supports, only much limited in functionalities. > > "Oleg Broytmann" <ph...@ma...> wrote in message > news:200...@ph...... >> On Mon, Dec 12, 2005 at 09:34:00PM +0100, Olo wrote: >> > So can I use simple SQL (select * from...) with SQLObject? >> >> connection.query("select * from ...") >> >> from sqlobject.sqlbuilder import Select >> connection.query(connection.sqlrepr(Select(...))) >> >> But you'd better use ForeignKey and do Supplier.select() and then l= oop >> over the result dereferencing an every object... >> >> Oleg. >> -- >> Oleg Broytmann http://phd.pp.ru/ phd@phd.pp= .ru >> Programmers don't die, they just GOSUB without RETURN. >> >> >> ------------------------------------------------------- >> This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files >> for problems? Stop! Download the new AJAX search engine that makes >> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK= ! >> http://ads.osdn.com/?ad_id=3D7637&alloc_id=3D16865&op=3Dclick > > > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log = files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://ads.osdn.com/?ad_id=3D7637&alloc_id=3D16865&op=3Dclick > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: gary n. <li...@ga...> - 2005-12-13 08:11:42
|
That is what I am doing now. However, it means I need to manually setup both the view and the SQLObject class. I don't mind the update, just treating the result set is iterable of objects for other processing, formatting for output etc. For multiple table updates, that is as you said, mostly interative loop process, no matter what language I would be using but that is fine. <jo...@co...> wrote in message news:295...@ma...... Why not define a view of the join in your flavor of SQL and then let SQLObject query the view? That would do the thing you are looking for if you are only trying to retrieve. The problem with this in most SQLs is that you probably can't update. And in most cases, it is EXTREMELY difficult to update through a query unless the semantics of the underlying tables is simple and clear. The reason is that you could have duplicated values in columns representing a foreign key table and you could update what is in essence the same row in the underlying table from different rows in the "view" incorrectly. That is one of the nice things about SQLObject. It maps the underlying relationships so that proper updating takes place. If I update an underlying foreign key table row from one row, the same update appears in the all the rest of the rows (as it should) without any fuss or bother. However, yes, I have to treat the tables independently, but that is what you have to do anyway in most cases. This has always been one of the big issues with SQL databases. If you just want to retrieve, then it is terrific. If you want to access and modify single tables, it is also very good. If you need to access and update lots of tables simultaneously, it has trouble handlng all the nuances for you properly and so you are forced (as you put it) to turn your "big buck super powerful Oracle or whatever DBMS into an ISAM". Unfortunately, that is pretty much what you have to do, whether you are using Python and SQLObject or Python alone, or Java or for that matter, even the database language itself like PL/SQL. There is no silver bullet in there that just does it all for you correctly. Jon > One thing I would like to see in sqlobject is to turn an arbitary result > set(from a SQL Select) into a SO class which can be loop through as an > iterator. Sure, it is now possible to map individual raw tables to SQLObject > then define foreignkeys/select rules and loop. But that is getting back to > the dbase era and effectively turning a big buck super poweful Oracle(or > whatever brand) RDBMS into an ISAM. > > I saw another post in this thread of implementing "join" in python(again > building on top of the individual object and foreign key links), that IMO is > a homebrew of the already exist SQL engine in whatever RDBMS backend > SQLObject supports, only much limited in functionalities. > > "Oleg Broytmann" <ph...@ma...> wrote in message > news:200...@ph...... >> On Mon, Dec 12, 2005 at 09:34:00PM +0100, Olo wrote: >> > So can I use simple SQL (select * from...) with SQLObject? >> >> connection.query("select * from ...") >> >> from sqlobject.sqlbuilder import Select >> connection.query(connection.sqlrepr(Select(...))) >> >> But you'd better use ForeignKey and do Supplier.select() and then loop >> over the result dereferencing an every object... >> >> Oleg. >> -- >> Oleg Broytmann http://phd.pp.ru/ ph...@ph... >> Programmers don't die, they just GOSUB without RETURN. >> >> >> ------------------------------------------------------- >> This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files >> for problems? Stop! Download the new AJAX search engine that makes >> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! >> http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > > > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://ads.osdn.com/?ad_idv37&alloc_id865&op=ick |
From: Justin A. <JA...@ua...> - 2005-12-13 17:35:40
|
On Tue, 2005-12-13 at 12:56 +0800, gary ng wrote: > One thing I would like to see in sqlobject is to turn an arbitary result > set(from a SQL Select) into a SO class which can be loop through as an > iterator. Sure, it is now possible to map individual raw tables to SQLObject > then define foreignkeys/select rules and loop. But that is getting back to > the dbase era and effectively turning a big buck super poweful Oracle(or > whatever brand) RDBMS into an ISAM. That is exactly what the functions I posted do. > I saw another post in this thread of implementing "join" in python(again > building on top of the individual object and foreign key links), that IMO is > a homebrew of the already exist SQL engine in whatever RDBMS backend > SQLObject supports, only much limited in functionalities. Is the post you are talking about mine? If so, that isn't what it does. The database does the join, all the python code does is make the various sqlobjects from the result set. -- -- Justin Azoff |
From: <jo...@co...> - 2005-12-12 23:16:57
|
I don't understand the various responses to this question. All this is (from my perspective) is a simple join which is EXACTLY what SQLObject does completely transparently. When you do a query with any kind of "q" criteria for the Delivery table, each object in Delivery will point to its appropriate supplier object, won't it? Consider this code: from sqlobject import * connectStr =3D """mysql://<connection string munged>""" connection =3D connectionForURI(connectStr) sqlhub.processConnection =3D connection # define table supplier class Supplier(SQLObject): supp =3D StringCol(length=3D16) # define table delivery class Delivery(SQLObject): dname =3D StringCol(length=3D16) dsupp =3D ForeignKey("Supplier") #create both tables Supplier.createTable() Delivery.createTable() #add a couple of Suppliers x =3D Supplier(supp=3D'Supplier X') y =3D Supplier(supp=3D'Supplier Y') #add a few Deliveries, 3 for supplier x, 1 for supplier y Delivery(dname=3D'rosen',dsupp=3Dx) Delivery(dname=3D'smith',dsupp=3Dx) Delivery(dname=3D'yates',dsupp=3Dy) Delivery(dname=3D'gates',dsupp=3Dx) #now, fetch the entire list of deliveries q =3D Delivery.select() #iterate through all the deliveries and print the data for item in q: print item.dname, item.dsupp.supp rosen Supplier X smith Supplier X yates Supplier Y gates Supplier X Note that you just pick up the Delivery.dsupp column and then reference the supp column in the Supplier table and the join retrieval is totally automatic. I don't know what can be simpler than that. If you really need to turn this into some kind of tuple, I suppose you can manually from the objects returned by SQLObject: qlist =3D [] for item in q: # double parens needed to construct tuples(??) qlist.append((item.dname,item.dsupp.supp)) print qlist [('rosen', 'Supplier X'), ('smith', 'Supplier X'), ('yates', 'Supplier Y'), ('gates', 'Supplier X')] Hope that helps (unless I am missing something really big! ;-) Jon Rosen > Hello > I have a problem with select query. > I have > class Supplier(SQLObject): > name ... > > class Delivery(SQLObject): > date ... > supplier =3D ForeignKey('Supplier') > > and I need query like: > > SELECT Supplier.*, Delivery.* > FROM Supplier, Delivery where Supplier.id =3D=3D Delivery.supplierID > > or something like that. > I tried to do this with JOIN or LEFTJOINOn but it wasn't worked. > > |