NumesSanguis NumesSanguis - 2 months ago 9
Python Question

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:


  • 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: my code with thanks to @ApolloFortyNine



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'

#meanings = []

# TODO originkanji not working yet
result = session.query(TableKanji).join(TableMeaningEN).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)

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

session.close()

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


Question EDIT 4




  • 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 == '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?

  • 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: 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", backref="Kanji")
#originkanji = relationship("TableOriginKanji", back_populates="Kanji") # , foreign_keys=['kanji_id']


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)

# Not necessary because in TableKanji backref is used instead of back_populates
#kanji = relationship("Kanji", back_populates="MeaningEN")


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)

#kanji = relationship("TableKanji", foreign_keys=[kanji_id], back_populates="OriginKanji")

# sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|TableKanji|Kanji' has no property 'OriginKanji'
#kanji = relationship("TableKanji", foreign_keys=[kanji_id], back_populates="OriginKanji")

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.

Comments