[Zope] ZSQLMethods
Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
You want something along these lines: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in This would be more efficient than pulling out the whole of two tables. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
Hi Chris, Thanks, is this code still efficient if the tables relate to 1:1 and you need to show a list of all records in a tabulare table? eg key, NAME, ALIAS, where NAME is from database1 and ALIAS is from database2 The code below would impact that for every record in database1, I've to query database2 Is there another way to do this? (more efficient?, ie less database query) Tom. - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:45 PM Subject: Re: [Zope] ZSQLMethods You want something along these lines: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in This would be more efficient than pulling out the whole of two tables. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
Try something like: dtml-in selectFromTable1 dtml-in selectFromTable2 dtml-if idFromTable1 = idFromTable2 display parts of the two records /dtml-if /dtml-in /dtml-in This keeps it to two db queries and the join work is done by Zope. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi Chris, Thanks, is this code still efficient if the tables relate to 1:1 and you need to show a list of all records in a tabulare table? eg key, NAME, ALIAS, where NAME is from database1 and ALIAS is from database2 The code below would impact that for every record in database1, I've to query database2 Is there another way to do this? (more efficient?, ie less database query) Tom. - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:45 PM Subject: Re: [Zope] ZSQLMethods You want something along these lines: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in This would be more efficient than pulling out the whole of two tables. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
Just had a few additional points: Should be dtml-if idFromTable1 == idFromTable2 of course. You'll have problems if columns have the same name in the two tables. You may have to modify ZSQL methods from 'select column_name' to 'select column_name as distinct_name' to get around this. Chris On Mon, 4 Dec 2000, Chris Gray wrote: Try something like: dtml-in selectFromTable1 dtml-in selectFromTable2 dtml-if idFromTable1 = idFromTable2 display parts of the two records /dtml-if /dtml-in /dtml-in This keeps it to two db queries and the join work is done by Zope. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi Chris, Thanks, is this code still efficient if the tables relate to 1:1 and you need to show a list of all records in a tabulare table? eg key, NAME, ALIAS, where NAME is from database1 and ALIAS is from database2 The code below would impact that for every record in database1, I've to query database2 Is there another way to do this? (more efficient?, ie less database query) Tom. - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:45 PM Subject: Re: [Zope] ZSQLMethods You want something along these lines: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in This would be more efficient than pulling out the whole of two tables. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
Depending on the number of records in the tables this could get *very* slow *very* quickly. Surely the ideal would be to move/copy the tables into the same DB. Phil - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:21 PM Subject: Re: [Zope] ZSQLMethods Try something like: dtml-in selectFromTable1 dtml-in selectFromTable2 dtml-if idFromTable1 = idFromTable2 display parts of the two records /dtml-if /dtml-in /dtml-in This keeps it to two db queries and the join work is done by Zope. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi Chris, Thanks, is this code still efficient if the tables relate to 1:1 and you need to show a list of all records in a tabulare table? eg key, NAME, ALIAS, where NAME is from database1 and ALIAS is from database2 The code below would impact that for every record in database1, I've to query database2 Is there another way to do this? (more efficient?, ie less database query) Tom. - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:45 PM Subject: Re: [Zope] ZSQLMethods You want something along these lines: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in This would be more efficient than pulling out the whole of two tables. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
Yes, that's true, but then you're doubling the amount of data. And everytime you change the table in one database, you've to copy the table in the other database. What if this has to be done daily? So, I need to find a way from which we'll benefit the most. Tom. - Original Message - From: "Phil Harris" [EMAIL PROTECTED] To: "Chris Gray" [EMAIL PROTECTED]; "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 5:06 PM Subject: Re: [Zope] ZSQLMethods Depending on the number of records in the tables this could get *very* slow *very* quickly. Surely the ideal would be to move/copy the tables into the same DB. Phil - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:21 PM Subject: Re: [Zope] ZSQLMethods Try something like: dtml-in selectFromTable1 dtml-in selectFromTable2 dtml-if idFromTable1 = idFromTable2 display parts of the two records /dtml-if /dtml-in /dtml-in This keeps it to two db queries and the join work is done by Zope. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi Chris, Thanks, is this code still efficient if the tables relate to 1:1 and you need to show a list of all records in a tabulare table? eg key, NAME, ALIAS, where NAME is from database1 and ALIAS is from database2 The code below would impact that for every record in database1, I've to query database2 Is there another way to do this? (more efficient?, ie less database query) Tom. - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:45 PM Subject: Re: [Zope] ZSQLMethods You want something along these lines: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in This would be more efficient than pulling out the whole of two tables. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
Right, Phil. Mental lapse on my part. The nested dtml-ins will be very inefficient because the select on the whole of table2 in the inner one will be repeated once for each record in table1. I was thinking it would only get executed once. The first solution I offered is better especially if table2 is indexed on id, but you're still executing a repeated query on table2. dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in Chris On Mon, 4 Dec 2000, Phil Harris wrote: Depending on the number of records in the tables this could get *very* slow *very* quickly. Surely the ideal would be to move/copy the tables into the same DB. Phil - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:21 PM Subject: Re: [Zope] ZSQLMethods Try something like: dtml-in selectFromTable1 dtml-in selectFromTable2 dtml-if idFromTable1 = idFromTable2 display parts of the two records /dtml-if /dtml-in /dtml-in This keeps it to two db queries and the join work is done by Zope. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi Chris, Thanks, is this code still efficient if the tables relate to 1:1 and you need to show a list of all records in a tabulare table? eg key, NAME, ALIAS, where NAME is from database1 and ALIAS is from database2 The code below would impact that for every record in database1, I've to query database2 Is there another way to do this? (more efficient?, ie less database query) Tom. - Original Message - From: "Chris Gray" [EMAIL PROTECTED] To: "Tom Deprez" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 04, 2000 3:45 PM Subject: Re: [Zope] ZSQLMethods You want something along these lines: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in This would be more efficient than pulling out the whole of two tables. Chris On Mon, 4 Dec 2000, Tom Deprez wrote: Hi, I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Thanks, Tom. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
On Mon, 4 Dec 2000, Tom Deprez wrote: Thanks, is this code still efficient if the tables relate to 1:1 and you need to show a list of all records in a tabulare table? eg key, NAME, ALIAS, where NAME is from database1 and ALIAS is from database2 The code below would impact that for every record in database1, I've to query database2 Is there another way to do this? (more efficient?, ie less database query) This might not apply to your case, but with many RDBMS you can also use the dbname when qualifying columns. With MySQL for example, having 2 dbs on the same server you can say: select db1.table1.column1, db2.table2.column2 from db1.table1, db2.table2 ... HTH, Stefan ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQLMethods
Tom Deprez writes: I'm wondering how the following can be done. I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? Search the (seachable) list archive. There was recently a thread about getting data from two ODBC data sources and joining the results. Dieter ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] ZSQLMethods
I've 2 databases. I want to do a 'join' with 2 tables, both in a different database. Is it possible to retrieve data of both tables with a ZSQLMethod and then do a programatically join? What's the easiest python way? If you are using ODBC then the EasySoft SQL Engine might solve your problem: "Databases which can be accessed through the Engine include DB2, Postgres, MySQL or any ODBC driver. It will enhance the functionality of any existing ODBC driver to ODBC 3.5 and enable the seamless joining of data from two or more ODBC data sources." http://www.easysoft.com/products/sql_engine/main.phtml?SessionID=RefID= I have not tried it myself, but the EasySoft people seem OK - very responsive on queries and support, with a clear commitment to quality. Richard ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
