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

## My database structure

**Kanji** :

  * id
  * character (A kanji like 頑)
  * heisig6 (a number indicating the order of showing Kanji)
  * kanjiorigin (a number indicating the order of showing Kanji)

**MeaningEN** (1 kanji_id can have multiple entries with different meanings):

  * kanji_id (FOREIGN KEY(kanji_id) REFERENCES "Kanji" (id)
  * meaning

## User handling

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)

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

    
    
    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)
    

## Question EDIT 4+5

  * Is this an efficient query?: `result = session.query(TableKanji).join(TableMeaningEN).filter(getattr(s_table, s_method) == s_input).all()` (The .join statement is necessary, because otherwise e.g. `session.query(TableKanji).filter(TableMeaningEN.meaning == 'love').all()` returns all the meanings in my database for some reason? So is this either the right query or is my `relationship()` in my tableclass.py not properly defined?

  * **fixed** (see `lambda:` in tableclass.py) `kanji = relationship("TableKanji", foreign_keys=[kanji_id], back_populates="OriginKanji")` <\-- **what is wrong** about this? It gives the error:

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

sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|TableKanji|Kanji' has no
property 'OriginKanji'

## Edit 2: tableclass.py (EDIT 3+4+5: updated)

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


Answer: 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.

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.

    
    
    # Assuming kanji is a tc.tableMeaningEN.kanji_id object
    kanji_meaning = kanji.meanings
    

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

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

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

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.

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

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

    
    
    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
    

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

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

