sqla example

sqla_example.py

from pprint import pprint

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, ForeignKey, Sequence, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, sessionmaker


engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()


class UserAccountFullNumber(Base):
    __tablename__ = 'user_account_full_number'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    account_full_number_id = Column(Integer, ForeignKey('account_full_number.id'), primary_key=True)

    account_full_number = relationship(lambda: AccountFullNumber, backref="user_associations")
    user = relationship(lambda: User, backref="full_number_associations")
    extra_data = Column(String(50))

    def __repr__(self):
        return f'<{self.__class__.__name__}(user={self.user}, account={self.account_full_number})>'


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    account_full_numbers = relationship(lambda: AccountFullNumber,
                                        secondary='user_account_full_number',
                                        backref='users')

    def __repr__(self):
        return f"<{self.__class__.__name__}(name='{self.name}')>"


class AccountFullNumber(Base):
    __tablename__ = 'account_full_number'
    id = Column(Integer, primary_key=True)
    full_number = Column(String(15))
    # parents = relationship("Parent", secondary='user_account_full_number')

    def __repr__(self):
        return f"<{self.__class__.__name__}(full_number='{self.full_number}')>"


def main():
    Base.metadata.create_all(engine)

    user = User(name='Абонент')

    for full_number in ('006-123-456-789', '006-123-456-780'):
        account = AccountFullNumber(full_number=full_number)
        user.account_full_numbers.append(account)

    session.add_all([user])
    session.commit()

    obj = session.query(User).first()
    print(user, user.account_full_numbers)
    print(user.full_number_associations)


if __name__ == '__main__':
    main()