[web2py] Re: Two database join query

[Pbop]

In SQL Server, you can run queries across database tables following the 
syntax you mention if you have permissions to access both databases and 
they are on the same server. I presume the same is true on other (not all) 
database engines. If you have the SQL, then use the executesql feature and 
you are golden. 

If you need to supply query parameters at run time,  build a runner. 

In a module or controller...

SCORM_FIND = """
SELECT TOP 1
a.history_id,
a.highest_score,
a.history_status,
b.factorA,
b.factorB

FROM
DB1.SCORM a inner join DB2.SCORMFactor b on a.id = b.id
WHERE
a.registration_id = ?
"""


from gluon.contrib.pypyodbc import ProgrammingError
from gluon import *
import traceback

def run(sql, *params, **kw):
res = SQLResponse()
if sql == None:
res.setError('SQL was None')
return res
try:
kwargs = dict(placeholders=params, as_dict=True)
if 'no_return' in kw and kw['no_return'] == True:
kwargs['as_dict'] = False
res.rows = current.db.executesql(sql, **kwargs)
except Exception, e:
res.setError(str(e), traceback.format_exc(), current.db._lastsql)
return res
res.success = True
return res

class SQLResponse(object):
def __init__(self, rows=[], success=False, error=None):
self.rows = rows
self.success = success
self.error = dict(msg=None, trace=None)
self.setError(error)
def setError(self, error, rawStack=None, sql=None):
if error == None:
return
self.success = False
self.error['msg'] = error
if rawStack != None:
self.error['trace'] = rawStack.split("\n")
if sql != None:
self.error['sql'] = sql


In your controller:

lookupRes = sql.run(sql.SCORM_FIND, registrationId)

I did not write this so although it seems pretty strait forward. We use 
this quite often when we need to do things a little outside of the dal but 
still get all the honey in the dal. I suspect that since the SQL is 
declared, you get some marginal bumps at run time as well. 









On Monday, August 28, 2017 at 12:04:29 PM UTC-4, Artem wrote:
>
> Hello !
> Hope someone can help . Thanks in advance !
> I have two database :
> db1 = DAL('sqlite://first.sqlite')
> db2 = DAL('sqlite://second.sqlite')
> with tables :
> db1.define_table('table1',
> Field('id',requires=IS_NOT_EMPTY()),
> Field('pid',type='integer'),
> Field('title',type='string'),
> )
> and
> db2.define_table('table2',
> Field('id',requires=IS_NOT_EMPTY()),
> Field('pid',type='integer'),
> Field('data',type='string'),
> )
> How to execute sqlite join ,something like: 
> sql ="SELECT db1.id, db1.title,db2.data FROM db1.table1 INNER JOIN 
> db2.table2 ON db2.table2.pid == db1.table1.pid"
> db1.executesql(sql) doesn't work 
>
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Two database join query

[Massimo Di Pierro]

This is logically impossible. The role of a database it to store data and 
execute queries about the local data. If you have two, which one should 
execute the query? Each one of them can only search local data. databases 
do not talk to each other.

The only solution is not to do it in a query but do the join at the python 
level:

rows = db1(db1.table1).select()
ids = [row.pid for row in rows]
rows_tojoin = db2(db2.table2.pid.belongs(ids)).select()
maps = {row.pid: row for row in rows_tojoin}
joined = []
for row in rows:
 joined.append({'table1':row, 'table2':maps[row.pid]})
for row in joined:
 print row['table1'], row['table2']



sql ="SELECT db1.id, db1.title,db2.data FROM db1.table1 INNER JOIN 
db2.table2 ON db2.table2.pid == db1.table1.pid"




On Monday, 28 August 2017 11:04:29 UTC-5, Artem wrote:
>
> Hello !
> Hope someone can help . Thanks in advance !
> I have two database :
> db1 = DAL('sqlite://first.sqlite')
> db2 = DAL('sqlite://second.sqlite')
> with tables :
> db1.define_table('table1',
> Field('id',requires=IS_NOT_EMPTY()),
> Field('pid',type='integer'),
> Field('title',type='string'),
> )
> and
> db2.define_table('table2',
> Field('id',requires=IS_NOT_EMPTY()),
> Field('pid',type='integer'),
> Field('data',type='string'),
> )
> How to execute sqlite join ,something like: 
> sql ="SELECT db1.id, db1.title,db2.data FROM db1.table1 INNER JOIN 
> db2.table2 ON db2.table2.pid == db1.table1.pid"
> db1.executesql(sql) doesn't work 
>
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Two database join query

[Dave S]

On Tuesday, August 29, 2017 at 1:19:29 AM UTC-7, Artem wrote:
>
> Hi ,
> Yes, tried
> it rise error : OperationalError: no such table: table1 
>

I think the problem is the db() out front.  You're asking for an operation 
(method) on a database object that doesn't have your tables.  You have a 
db, as well as  db1 and db2?

I'm not doing any better than Manuele is coming up with a way of doing 
this.  You need a list of ids from both tables, and those have to come from 
separate databases.  You could try changing the db() to either db1() or 
db2(), but I'm not confidant that it would work, and I imagine you'd only 
get the entries from the one table, just filtered by the other.  

Niphlod and Massimo and Giovanni are probably the experts you need.

/dps


>
> On Tuesday, August 29, 2017 at 5:58:20 AM UTC+8, 黄祥 wrote:
>>
>> had you tried ?
>> *e.g. not tested*
>> rows = db(db1.table1.pid == db2.table2.pid).select()
>>
>> ref:
>>
>> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Inner-joins
>>
>> best regards,
>> stifan
>>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Two database join query

[Artem]

Hi ,
yes i'm tried .
it rise error : OperationalError: no such table: table1 

On Tuesday, August 29, 2017 at 5:58:20 AM UTC+8, 黄祥 wrote:
>
> had you tried ?
> *e.g. not tested*
> rows = db(db1.table1.pid == db2.table2.pid).select()
>
> ref:
>
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Inner-joins
>
> best regards,
> stifan
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Two database join query

[黄祥]

had you tried ?
*e.g. not tested*
rows = db(db1.table1.pid == db2.table2.pid).select()

ref:
http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Inner-joins

best regards,
stifan

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Two database join query

[Artem]

My app require two database .
This why i post a question here ... 

On Tuesday, August 29, 2017 at 1:55:09 AM UTC+8, Alfonso Serra wrote:
I guess this is the solution:
https:
//stackoverflow.com/questions/6824717/sqlite-how-do-you-join-tables-from-different-databases
 


If you want to write simpler sqls, the tables should be within the same 
database.

   is no question to standard sqlite library , i think .

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Two database join query

[Alfonso Serra]

I guess this is the solution:
https://stackoverflow.com/questions/6824717/sqlite-how-do-you-join-tables-from-different-databases

If you want to write simpler sqls, the tables should be within the same 
database.

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.