Order of declaring classes in SqlAlchemy

Question

I'm sory for this newby question, but I can't figure it out for a couple days and it is driving me nuts.

I'm building a small project in order to learn flask, SqlAlchemy and Postrges. I have major problems with declaring classes in SqlAlchemy. I have already simplified models by removing all many-to-many relationships. However, now I have new problems even with one-to many relationships, although I think I have tried all the possible options. Maybe there's a typo that I keep on overlooking, or I don't grasp something fundamental. Please let me know...

So, I have classes declared as follows in my models.py:

 @login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))


class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    date_registered = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    role = db.Column(db.Integer, nullable=False)
    role_exp_date = db.Column(db.DateTime)
    #o2o
    personal_datas = db.relationship('PersonalData', uselist=False, backref='user', lazy=True)  
    persons = db.relationship('Person', uselist=False, backref='user', lazy=True)       
    #o2m
    posts = db.relationship('Post', backref='author', lazy=True)
    comments = db.relationship('PostComment', backref='author', lazy=True)
    projects_owned = db.relationship('ConstrProject', backref='owner', lazy=True)
    attachments = db.relationship('Attachment', backref='author', lazy=True)

    def __repr__(self):
        return f"{self.username} ({self.email})"



class PersonalData(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    date_birth = db.Column(db.DateTime)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    interests =  db.Column(db.Text)
    experties =  db.Column(db.Text)                     #Потом сделать отдельную таблицу...
    #o2o
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    #o2m


class Person(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    first_name = db.Column(db.String(30), nullable=False)
    middle_name = db.Column(db.String(40), nullable=False)
    last_name = db.Column(db.String(60), nullable=False)
    email = db.Column(db.String(120))
    license = db.Column(db.String(120))
    address = db.Column(db.String(240))
    telephone = db.Column(db.String(30))
    #o2o
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    #o2m
    signers = db.relationship('Signer', backref='person', lazy=True)

    def __repr__(self):
        return f"{self.last_name.Capitalize} {self.first_name[0].Upper}. {self.middle_name[0].Upper}."


class ConstrProject(db.Model):
    __tablename__ = 'constrproject'
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(120), nullable=False, default='New Project')
    full_title = db.Column(db.Text, default='New Project')
    notes = db.Column(db.Text)
    public = db.Column(db.Boolean, default=True)                                    #? check expamples
    date_created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) 
    date_last_edit = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    document_template = db.Column(db.Integer, nullable=False, default=1)                        #later to m2m
    print_settings = db.Column(db.Integer, nullable=False, default=1)                           #later to m2m
    address = db.Column(db.String(240))
    #o2m
    documents = db.relationship('Document', backref='project', lazy=True)
    #m2o
    owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)          #+      #default = CurrentUser
    developer_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    main_contractor_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    architect_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    subcontractor_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    other_id = db.Column(db.Integer, db.ForeignKey('company.id'))


    developer = db.relationship('Company', foreign_keys=[developer_id], back_populates='constr_projects_developed')
    main_contractor = db.relationship('Company', foreign_keys=[main_contractor_id], back_populates='constr_projects_main_contracts')
    architect = db.relationship('Company', foreign_keys=[architect_id], back_populates='constr_projects_architect')
    subcontractor = db.relationship('Company', foreign_keys=[subcontractor_id], back_populates='constr_projects_subcontracts')
    other = db.relationship('Company', foreign_keys=[other_id], back_populates='constr_projects_other')


    tech_control_reps_id = db.Column(db.Integer, db.ForeignKey('signer.id'), nullable=False)
    main_contractor_reps_id = db.Column(db.Integer, db.ForeignKey('signer.id'), nullable=False)
    architect_reps_id = db.Column(db.Integer, db.ForeignKey('signer.id'), nullable=False)
    subcontractor_reps_id = db.Column(db.Integer, db.ForeignKey('signer.id'), nullable=False)
    other_reps_id = db.Column(db.Integer, db.ForeignKey('signer.id'), nullable=False)

    tech_control_reps = db.relationship('Signer', foreign_keys=[tech_control_reps_id], back_populates='tech_control_projects')
    main_contractor_reps = db.relationship('Signer', foreign_keys=[main_contractor_reps_id], back_populates='main_contractor_projects')
    architect_reps = db.relationship('Signer', foreign_keys=[architect_reps_id], back_populates='architect_projects')
    subcontractor_reps = db.relationship('Signer', foreign_keys=[subcontractor_reps_id], back_populates='subcontractor_projects')
    other_reps = db.relationship('Signer', foreign_keys=[other_reps_id], back_populates='others_projects')

    def __repr__(self):
        return f"Site: {self.name},  (id{self.id})"





class Signer(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    decree = db.Column(db.String(120))
    job_title = db.Column(db.String(120))
    date_duty_start = db.Column(db.DateTime)
    date_duty_end = db.Column(db.DateTime)
    #o2m
    person_id = db.Column(db.Integer, db.ForeignKey('person.id'), nullable=False)
    company_id = db.Column(db.Integer, db.ForeignKey('company.id'), nullable=False)
    #m2o
    tech_control_projects = db.relationship('ConstrProject', back_populates='tech_control_reps')
    main_contractor_projects = db.relationship('ConstrProject', back_populates='main_contractor_reps')
    architect_projects = db.relationship('ConstrProject', back_populates='architect_reps')
    subcontractor_projects = db.relationship('ConstrProject', back_populates='subcontractor_reps')
    others_projects = db.relationship('ConstrProject', back_populates='other_reps')

    def __repr__(self):
        return f"{self.job_title} as per {self.decree}."       #название компании как подтянуть



class Company(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(60))
    full_title = db.Column(db.String(240))
    tin = db.Column(db.Integer)
    kpp = db.Column(db.Integer)
    ogrn = db.Column(db.Integer)
    email = db.Column(db.String(120))
    address = db.Column(db.String(240))
    telephone = db.Column(db.String(30))
    #o2m
    license_number = db.Column(db.String(40), nullable = False)
    license_date_issued = db.Column(db.DateTime) 
    license_category = db.Column(db.String(120), default = '2nd')
    license_issued_by = db.Column(db.String(120))
    license_issued_by_tin = db.Column(db.Integer)
    license_issued_by_kpp = db.Column(db.Integer)
    license_issued_by_ogrn = db.Column(db.Integer)

    signers = db.relationship('Signer', backref='company', lazy=True)
    constr_projects_developed = db.relationship('ConstrProject', back_populates='developer') 
    constr_projects_main_contracts = db.relationship('ConstrProject', back_populates='main_contractor')
    constr_projects_architect = db.relationship('ConstrProject', back_populates='architect')
    constr_projects_subcontracts = db.relationship('ConstrProject', back_populates='subcontractor')
    constr_projects_other = db.relationship('ConstrProject', back_populates='other')

    def __repr__(self):
        return f"{self.name}"



class Post(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    #o2m
    comments = db.relationship('PostComment', backref='Post', lazy=True)
    #m2o
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)


    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"



class PostComment(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    #m2o
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'), nullable=False)

    def __repr__(self):
        return f"Comment('{self.id}', '{self.date_posted}')"




class Document(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    type = db.Column(db.String(60), nullable=False, default='АОСР')
    date_last_edit = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    notes = db.Column(db.Text)
    public = db.Column(db.Boolean, default=True)
    number = db.Column(db.String(20), nullable=False)
    date = db.Column(db.DateTime, default=datetime.utcnow)
    job_name = db.Column(db.Text)                               #? обязательный? на каком этапе делать проверку?
    job_place = db.Column(db.String(200))
    date_job_start = db.Column(db.DateTime, default=datetime.utcnow)
    date_job_end = db.Column(db.DateTime, default=datetime.utcnow)
    regulations = db.Column(db.Text)
    next_job_allowed = db.Column(db.String(240))
    attachments_user_defined = db.Column(db.Text)
    #o2m
    attachments = db.relationship('Attachment', backref='document', lazy=True)
    #m2o
    project_id = db.Column(db.Integer, db.ForeignKey('constrproject.id'), nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    #m2m

    arch_docs  = db.Column(db.Text)
    building_materials  = db.Column(db.Text)
    work_drawings  = db.Column(db.Text)

    def __repr__(self):
        return f"АОСР ('{self.number}', '{self.job_name} {self.job_place}', '{self.project}' )"



class Attachment(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    type_of_document = db.Column(db.String(60), nullable=False, default="QAC")
    number = db.Column(db.String(50), nullable=False)
    date = db.Column(db.DateTime)
    date_valid_start = db.Column(db.DateTime)
    date_valid_end = db.Column(db.DateTime)
    contents = db.Column(db.Text)
    type_of_file = db.Column(db.String(10), nullable=False, default = 'jpg')
    image_file = db.Column(db.String(20), nullable=False)
    #m2o
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    document_id = db.Column(db.Integer, db.ForeignKey('document.id'), nullable=False)

    def __repr__(self):
        if self.text:
            return f'{self.text}'
        return f'+{self.type_of_document} id{self.id}  ({self.type_of_file})'

I cannot understand why, when I try to create an instance of a "Document" I get errors like this:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Signer.tech_control_projects - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

or

sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper|ConstrProject|constrproject, expression 'tech_control_reps_id' failed to locate a name ("name 'tech_control_reps_id' is not defined"). If this is a class name, consider adding this relationship() to the class after both dependent classes have been defined.

I haven't even tried to create these classes. Adding "foreign_keys" doesn't seem to help either. all relationships declared as strings. I tried to use lambda's also without success.

Nevertheless moving the order of class declearations alters the error messages i get...

I cannot find any good examples (resources) of more complicated databases with multiple many-to-many and one-to-many relationships in Each class. Usually examples are very basic and obvious. So I would really appreciate if you post links to such projects or tutorials.

score 0 · Answer 1 · answered Jan 12 '19 at 21:04

I wanted to learn some of it myself so I created a working example with one-to-one and multiple one-to-many relationships based on your code (User, Person, Post and Comment classes). I hope it will be a good (simple but not trivial) example for you.

'''SQLAlchemy one-to-one and one-to-many SSCCE'''

import sqlalchemy
import sqlalchemy.ext.declarative
from passlib.hash import pbkdf2_sha256

Base = sqlalchemy.ext.declarative.declarative_base()


class User(Base):
    __tablename__ = 'users'
    user_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    username = sqlalchemy.Column(sqlalchemy.String(20), unique=True, nullable=False)
    email = sqlalchemy.Column(sqlalchemy.String(120), unique=True, nullable=False)
    password = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
    registered = sqlalchemy.Column(
        sqlalchemy.DateTime(timezone=True),
        nullable=False,
        server_default=sqlalchemy.func.now()
    )
    #o2o
    person = sqlalchemy.orm.relationship(
        'Person',
        uselist=False,
        back_populates='user',
        lazy='joined'
    )
    #o2m
    posts = sqlalchemy.orm.relationship('Post', back_populates='user')
    comments = sqlalchemy.orm.relationship('Comment', back_populates='user')

    def __repr__(self):
        return f'{self.username} ({self.email})'


class Person(Base):
    __tablename__ = 'persons'
    person_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey('users.user_id'),
        primary_key=True
    )
    first_name = sqlalchemy.Column(sqlalchemy.String(30), nullable=False)
    middle_name = sqlalchemy.Column(sqlalchemy.String(40), nullable=False)
    last_name = sqlalchemy.Column(sqlalchemy.String(60), nullable=False)
    #o2o
    user = sqlalchemy.orm.relationship('User', back_populates='person', lazy='joined')

    def __repr__(self):
        return (
            f'{self.last_name.upper()}'
            f' {self.first_name[:1].upper()}.'
            f' {self.middle_name[:1].upper()}.'
        )


class Post(Base):
    __tablename__ = 'posts'
    post_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    title = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
    posted = sqlalchemy.Column(
        sqlalchemy.DateTime(timezone=True),
        nullable=False,
        server_default=sqlalchemy.func.now()
    )
    content = sqlalchemy.Column(sqlalchemy.Text, nullable=False)
    #o2m
    comments = sqlalchemy.orm.relationship('Comment', back_populates='post')
    #m2o
    user_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey('users.user_id'),
        nullable=False
    )
    user = sqlalchemy.orm.relationship('User', uselist=False, back_populates='posts', lazy='joined')

    def __repr__(self):
        return f'Post({self.title!r}, {self.posted!r})'


class Comment(Base):
    __tablename__ = 'comments'
    comment_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    posted = sqlalchemy.Column(
        sqlalchemy.DateTime(timezone=True),
        nullable=False,
        server_default=sqlalchemy.func.now()
    )
    content = sqlalchemy.Column(sqlalchemy.Text, nullable=False)
    #m2o
    user_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey('users.user_id'),
        nullable=False
    )
    user = sqlalchemy.orm.relationship(
        'User',
        uselist=False,
        back_populates='comments',
        lazy='joined'
    )
    post_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey('posts.post_id'),
        nullable=False
    )
    post = sqlalchemy.orm.relationship(
        'Post',
        uselist=False,
        back_populates='comments',
        lazy='joined'
    )

    def __repr__(self):
        return f'Comment({self.comment_id!r}, {self.posted!r})'


def main():
    engine = sqlalchemy.create_engine(
        'postgresql+psycopg2:///stack',
        echo=True,
        server_side_cursors=True,
        use_batch_mode=True
    )

    Base.metadata.create_all(engine)
    Session = sqlalchemy.orm.sessionmaker(bind=engine)

    session = Session()
    session.commit()

    try:
        the_user = session.query(User).filter(User.username == 'example').one()
    except sqlalchemy.orm.exc.NoResultFound:
        the_user = User(
            username='example',
            email='example@example.com',
            password=pbkdf2_sha256.hash('correct horse battery staple')
        )
        the_user.person = Person(first_name='Ex', middle_name='', last_name='Ample')
        session.add(the_user)

    print(the_user)
    print(the_user.person)

    if not the_user.posts:
        the_user.posts.append(Post(title='First post', content='Lorem ipsum'))
        session.commit()
    print(the_user.posts[0])

    if not the_user.posts[0].comments:
        the_user.posts[0].comments.append(Comment(content='Me too', user=the_user))
        session.commit()
    print(the_user.posts[0].comments[0])

if __name__ == '__main__':
    main()

Several comments:

I don't think it is easy to have foreign-keys both ways for a mandatory one-to-one relationship.
I made both User and Person use the same id numbers, as they are one-to-one.
I made the PostgreSQL now() as the default value for timestamps, instead of client-side utcnow.
I made the timestamps use "timestamp with timezone" type - the "timestamp" type (without timezone) is an abomination.
"user" is a bad name for a table, as this is also a keyword in PostgreSQL, so I changed it to "users". Other tables also changed to plural form for consistency.
A secure password storage is a must.
I used back_populates consistently, as it is more explicit and works better with static code analyzers than backref.

Order of declaring classes in SqlAlchemy

1 Answers1