# 1. Run this SQL JOIN:
#
#    SELECT user.name, address.email_address FROM user
#    JOIN address ON user.id=address.user_id WHERE
#    address.email_address='j25@yahoo.com'
#
# 2. Tricky Bonus!  Select all pairs of distinct user names.
#    Hint: "... ON user_alias1.name < user_alias2.name"
#
from sqlalchemy import and_, or_
from sqlalchemy import Table, Column, MetaData
from sqlalchemy import Integer, String, DateTime
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    user_id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    fullname = Column(String(100), nullable=False)

    def __repr__(self):
        return "<User(%r, %r, %r)>" % (
                self.user_id, self.name, self.fullname
            )

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", backref="addresses")

    def __repr__(self):
        return "<Address(%r,%r)>" % self.id, self.email_address

e =create_engine("sqlite://")
Base.metadata.create_all(e)
s = Session(bind=e)

s.add_all([
    User(name='wendy', fullname='Wendy Weathersmith'),
    User(name='mary', fullname='Mary Contrary'),
    User(name='fred', fullname='Fred Flinstone')
])

jack = User(name='jack', fullname='Jack Bean')
jack.addresses

jack.addresses = [
                Address(email_address='jack@gmail.com'),
                Address(email_address='j25@yahoo.com'),
                Address(email_address='jack@hotmail.com'),
                ]


s.commit()

query = ession.query(User.name).join(Address.email_address).\
    filter(Address.email_address == 'j25@yahoo.com').all()

print(query.all())
#Ответ
[('jack', 'j25@yahoo.com')]