Adding relationship to tables already created on SQLAlchemy
On this post you could have a small idea how SQLAlchemy works. However, all my study on SQLAlchemy basics was due to a problem I was having that took me a lot of time to figure it out. Since the problem was more complex and didn’t actually fit on the last post, I decided to create a new one dedicated to it, so here it is :)
While I was creating the Native Authenticator I realized that I needed to store some information about my user that wasn’t available in the default
User table. Information such as email or password, for instance. The JupuyterHub
User class was like this:
# jupyterhub/orm.py class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(Unicode(255), unique=True)
I decided, then, to create a
UserInfo table that would store any other information that I wanted. My initial class was like this:
# nativeauthenticator/orm.py from sqlalchemy import Column, Integer, String from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class UserInfo(Base): __tablename__ = 'users_info' id = Column(Integer, primary_key=True, autoincrement=True) username = Column(String, nullable=False) password = Column(String, nullable=False)
Once I had the class ready, all I needed was to add this table to my database. So I added this creation on the
__init__ method of my authenticator class. Such as:
# nativeauthenticator/nativeauthenticator.py class NativeAuthenticator(Authenticator): def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) inspector = inspect(self.db.bind) if 'users_info' not in inspector.get_table_names(): UserInfo.__table__.create(self.db.bind)
Obs: self.db.bind is the SQLAlchemy engine.
I first checked to see if my table isn’t there already (otherwise I would get an error) and, if it isn’t, than I created the table. All was working so far :)
The problem started when I decided to add a relationship between
For some reason, I couldn’t make it work. The
UserInfo table would never find the
User table and I kept getting this error:
NoReferencedTableError: Foreign key associated with column 'product.user_id' could not find table 'user' with which to generate a foreign key to target column 'id'.
I spent some time going through Stack Overflow issues and after getting nowhere I wrote an issue asking for help.
That’s when André asked me if I were using the same
Base for both models and, guess what? I wasn’t.
So, the first thing you should do when creating two classes for the same
SQLAlchemy engine is to use the same
Base in all models. Seems trivial, but it isn’t. So, I fixed this:
# nativeauthenticator/orm.py from jupyterhub.orm import Base from sqlalchemy import Column, Integer, String class UserInfo(Base): __tablename__ = 'users_info' id = Column(Integer, primary_key=True, autoincrement=True) username = Column(String, nullable=False) password = Column(String, nullable=False)
After that, you can add the attributes for the relationship on your class
class UserInfo(Base): ... user_id = Column(Integer, ForeignKey('users.id')) user = relationship(User)
Now you can simply add the table and it will work!
However, this way you won’t be able to see which
UserInfo instances are connected to your
User, because only
User. Thus, you must add a relationship to the
User class. Since we are not adding it on the class creation (aka original file), we add it to the class in the moment that you need it:
User.info = relationship(UserInfo, backref='users')
See a full example on how you work with these two classes here:
Done! Everything worked!
Special thanks to André, Yuvi and Pastore for all the help ❤️!❤ Cheers! Letícia