python sqlalchemy combine query from two tables

Question

I have two tables that are not related like this:

hurst = Table('Hurst', metadata,
    Column('symbol_id' , Integer, primary_key=True), 
    Column('Date'      , String(20), nullable=False),
    Column('symbol'    , String(40), nullable=False),
    Column('HurstExp'  , Float,      nullable=False),
)

fundamental = Table('Fundamental', metadata,
    Column('symbol_id' , Integer,    primary_key=True), 
    Column('Date'      , String(20), nullable=False),
    Column('symbol'    , String(40), nullable=False),
    Column('MarketCap' , Float,      nullable=False),
)

Each of the following queries works fine. How do I combine them so I can get only the hurst of companies that are worth more than 50,000,000,000?

# -*- coding: utf-8 -*-
"""
Created on Sun Dec 13 19:22:35 2015

@author: idf
"""

from sqlalchemy import *

def run(stmt):
    rs = stmt.execute()
    return rs

# Let's re-use the same database as before
dbh = create_engine('sqlite:///hurst.db')
dbf = create_engine('sqlite:///fundamental.db')

dbh.echo = True  # We want to see the SQL we're creating
dbf.echo = True  # We want to see the SQL we're creating

metadatah = MetaData(dbh)
metadataf = MetaData(dbf)

# The users table already exists, so no need to redefine it. Just
# load it from the database using the "autoload" feature.
hurst = Table('Hurst',       metadatah, autoload=True)
funda = Table('Fundamental', metadataf, autoload=True)

hurstQ = hurst.select(hurst.c.HurstExp < .5)
run(hurstQ)

fundaQ = funda.select(funda.c.MarketCap > 50000000000)
run(fundaQ)

If I try to use a join, I get an error:

j = join(hurst, funda, hurst.c.symbol == funda.c.symbol)
stmt = select([hurst]).select_from(j)
theJoin = run(stmt)


Traceback (most recent call last):
  File "/home/idf/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/idf/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)

   cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: Fundamental

I can't even do the easy version

# This will return more results than you are probably expecting.
s = select([hurst, funda])
run(s)

See the edited post. The tables are seperate. Maybe I need to relate them somehow but the two tables are created seperately. — Ivan, Dec 19 '15 at 04:29
You need to can use the `backref` feature of `sqlalchemy` and create a reference of the other table using a foreign key or relation. — Abhinav, Dec 19 '15 at 04:51

score 0 · Answer 1 · answered Dec 19 '15 at 04:54

0

Don't have an environment to test this in at the moment, but something along the lines of this should work:

j = join(husrt, funda, (hurst.c.symbol_id == funda.c.symbol_id) & (funda.c.MarketCap > 50000000000))
stmt = select([hurst]).select_from(j)
run(stmt)

Check out sqlalchemy.sql.expression.join on this SQL Alchemy docs page.

answered Dec 19 '15 at 04:54

Brad K.

188
1
6

See the edited post. Even something this simple doesn't appear to work. – Ivan Dec 19 '15 at 18:50

score 0 · Answer 2 · edited May 23 '17 at 12:07

I don't believe you can join between two databases.

One of your tables is in one database and the other is in another database:

dbh = create_engine('sqlite:///hurst.db')
dbf = create_engine('sqlite:///fundamental.db')

You should put all tables in the same database.db file and have one db = create_engine('sqlite:///database.db') .

Correction: You can do that: Cross database join in sqlalchemy

But do you really want to have each table in a separate database?

python sqlalchemy combine query from two tables

2 Answers2