SQLalchemy find id and use it to lookup other information
<p>I'm making a simple lookup application for Japanese characters (Kanji), where the user can search the database using any of the information available.</p>

<h2>My database structure</h2>

<p><strong>Kanji</strong>:</p>

<ul>
<li>id</li>
<li>character (A kanji like é )</li>
<li>heisig6 (a number indicating the order of showing Kanji)</li>
<li>kanjiorigin (a number indicating the order of showing Kanji)</li>
</ul>

<p><strong>MeaningEN</strong> (1 kanji_id can have multiple entries with different meanings):</p>

<ul>
<li>kanji_id (FOREIGN KEY(kanji_id) REFERENCES "Kanji" (id)</li>
<li>meaning</li>
</ul>

<h2>User handling</h2>

<p>The user can choose to search by 'id', 'character', 'heisig6', 'kanjiorigin' or 'meaning' and it should then return all information in all those fields. (All fields return only 1 result, except meanings, which can return multiple results)</p>

<h2>Code, EDIT 4+5: my code with thanks to @ApolloFortyNine and #sqlalchemy on IRC, EDIT 6: <code>join</code> --> <code>outerjoin</code> (otherwise won't find information that has no Origins)</h2>

<pre><code>import sqlalchemy as sqla
import sqlalchemy.orm as sqlo
from tableclass import TableKanji, TableMeaningEN, TableMisc, TableOriginKanji  # See tableclass.py

# Searches database with argument search method
class SearchDatabase():
    def __init__(self):
        #self.db_name = "sqlite:///Kanji_story.db"
        self.engine = sqla.create_engine("sqlite:///Kanji.db", echo=True)

        # Bind the engine to the metadata of the Base class so that the
        # declaratives can be accessed through a DBSession instance
        tc.sqla_base.metadata.bind = self.engine

        # For making sessions to connect to db
        self.db_session = sqlo.sessionmaker(bind=self.engine)

    def retrieve(self, s_input, s_method):
        # s_input: search input
        # s_method: search method
        print("\nRetrieving results with input: {} and method: {}".format(s_input, s_method))

        data = []  # Data to return

        # User searches on non-empty string
        if s_input:
            session = self.db_session()

            # Find id in other table than Kanji
            if s_method == 'meaning':
                s_table = TableMeaningEN  # 'MeaningEN'
            elif s_method == 'okanji':
                s_table = TableOriginKanji  # 'OriginKanji'
            else:
                s_table = TableKanji  # 'Kanji'

            result = session.query(TableKanji).outerjoin(TableMeaningEN).outerjoin(
                (TableOriginKanji, TableKanji.origin_kanji)
            ).filter(getattr(s_table, s_method) == s_input).all()
            print("result: {}".format(result))
            for r in result:
                print("r: {}".format(r))

                meanings = [m.meaning for m in r.meaning_en]
                print(meanings)
                # TODO transform into origin kanji's
                origins = [str(o.okanji_id) for o in r.okanji_id]
                print(origins)

                data.append({'character': r.character, 'meanings': meanings,
                             'indexes': [r.id, r.heisig6, r.kanjiorigin], 'origins': origins})

            session.close()

        if not data:
            data = [{'character': 'X', 'meanings': ['invalid', 'search', 'result']}]
        return(data)
</code></pre>

<h2>Question EDIT 4+5</h2>

<ul>
<li><p>Is this an efficient query?: <code>result = session.query(TableKanji).join(TableMeaningEN).filter(getattr(s_table, s_method) == s_input).all()</code> (The .join statement is necessary, because otherwise e.g. <code>session.query(TableKanji).filter(TableMeaningEN.meaning == 'love').all()</code> returns all the meanings in my database for some reason? So is this either the right query or is my <code>relationship()</code> in my tableclass.py not properly defined?</p></li>
<li><p><strong>fixed</strong> (see <code>lambda:</code> in tableclass.py) <code>kanji = relationship("TableKanji", foreign_keys=[kanji_id], back_populates="OriginKanji")</code> &lt;-- <strong>what is wrong</strong> about this? It gives the error:</p>

<p>File "/<em>path</em>/python3.5/site-packages/sqlalchemy/orm/mapper.py", line 1805, in get_property
 "Mapper '%s' has no property '%s'" % (self, key))</p>

<p>sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|TableKanji|Kanji' has no property 'OriginKanji'</p></li>
</ul>

<h2>Edit 2: tableclass.py (EDIT 3+4+5: updated)</h2>

<pre><code>import sqlalchemy as sqla
from sqlalchemy.orm import relationship
import sqlalchemy.ext.declarative as sqld

sqla_base = sqld.declarative_base()


class TableKanji(sqla_base):
    __tablename__ = 'Kanji'

    id = sqla.Column(sqla.Integer, primary_key=True)
    character = sqla.Column(sqla.String, nullable=False)
    radical = sqla.Column(sqla.Integer)  # Can be defined as Boolean
    heisig6 = sqla.Column(sqla.Integer, unique=True, nullable=True)
    kanjiorigin = sqla.Column(sqla.Integer, unique=True, nullable=True)
    cjk = sqla.Column(sqla.String, unique=True, nullable=True)

    meaning_en = relationship("TableMeaningEN", back_populates="kanji")  # backref="Kanji")
    okanji_id = relationship("TableOriginKanji", foreign_keys=lambda: TableOriginKanji.kanji_id, back_populates="kanji")

class TableMeaningEN(sqla_base):
    __tablename__ = 'MeaningEN'

    kanji_id = sqla.Column(sqla.Integer, sqla.ForeignKey('Kanji.id'), primary_key=True)
    meaning = sqla.Column(sqla.String, primary_key=True)

    kanji = relationship("TableKanji", back_populates="meaning_en")


class TableOriginKanji(sqla_base):
    __tablename__ = 'OriginKanji'

    kanji_id = sqla.Column(sqla.Integer, sqla.ForeignKey('Kanji.id'), primary_key=True)
    okanji_id = sqla.Column(sqla.Integer, sqla.ForeignKey('Kanji.id'), primary_key=True)
    order = sqla.Column(sqla.Integer)

    #okanji = relationship("TableKanji", foreign_keys=[kanji_id], backref="okanji")
    kanji = relationship("TableKanji", foreign_keys=[kanji_id], back_populates="okanji_id")
</code></pre>

<p>We would really have to be able to see your database schema to give real critique, but assuming no foreign keys, what you said is basically the best you can do.</p>

<p>SQLAlchemy really begins to shine when you have complicated relations going on however. For example, if you properly had foreign keys set, you could do something like the following.</p>

<pre><code># Assuming kanji is a tc.tableMeaningEN.kanji_id object
kanji_meaning = kanji.meanings
</code></pre>

<p>And that would return the meanings for the kanji as an array, without any further queries.</p>

<p>You can go quite deep with relationships, so I'm linking the documentation here. <a href="http://docs.sqlalchemy.org/en/latest/orm/relationships.html" rel="nofollow">http://docs.sqlalchemy.org/en/latest/orm/relationships.html</a></p>

<p>EDIT: Actually, you don't need to manually join at all, SQLAlchemy will do it for you.</p>

<p>The case is wrong on your classes, but I'm not sure if SQLAlchemy is case sensitive there or not. If it works, then just move on.</p>

<p>If you query the a table (self.session.query(User).filter(User.username == self.name).first()) you should have an object of the table type (User here).</p>

<p>So in your case, querying the TableKanji table alone will return an object of that type.</p>

<pre><code>kanji_obj = session.query(TableKanji).filter(TableKanji.id == id).first()
# This will return an array of all meaning_ens that match the foreign key
meaning_arr = kanji_obj.meaning_en
# This will return a single meeting, just to show each member of the arr is of type TableMeaningEn
meaning_arr[0].meaning
</code></pre>

<p>I have a project made use of some of these features, hope it helps:
<a href="https://github.com/ApolloFortyNine/SongSense" rel="nofollow">https://github.com/ApolloFortyNine/SongSense</a>
Database declaration (with relationships): <a href="https://github.com/ApolloFortyNine/SongSense/blob/master/songsense/database.py" rel="nofollow">https://github.com/ApolloFortyNine/SongSense/blob/master/songsense/database.py</a>
Automatic joins: <a href="https://github.com/ApolloFortyNine/SongSense/blob/master/songsense/getfriend.py#L134" rel="nofollow">https://github.com/ApolloFortyNine/SongSense/blob/master/songsense/getfriend.py#L134</a></p>

<p>I really like my database structure, but as for the rest it's pretty awful. Hope it still helps though.</p>

python sqlalchemy