CODEWITHSUNDEEP
pythonsqlalchemy
NumesSanguis
NumesSanguis

Reputation: 6292

SQLalchemy find id and use it to lookup other information

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:

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

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

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

Upvotes: 0

Views: 976

Answers (1)

ApolloFortyNine
ApolloFortyNine

Reputation: 590

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.

Upvotes: 1

Related Questions