Two database join query

[Artem]

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

[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.

[Artem]

My app require two database .

This why i post a question here ... 

   is no question to standard sqlite library , i think .

[黄祥]

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

[Artem]

Hi ,

Yes, tried

it rise error : OperationalError: no such table: table1 

[Manuele]

I think there's no way in a single query...

try making two different query and join results creating a brand new rows object

I hope it could help

    Manuele

[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

[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"

[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.