__________________________________________________________________1_____________________________________________________________________

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Numeric, func,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session,relationship
import os

metadata = MetaData()

engine = create_engine("sqlite:///baza.db")

Base = declarative_base()

class Network(Base):
    __tablename__ = 'network'

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

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

Base.metadata.create_all(engine)

session = Session(bind=engine)
session.add_all([
    Network(name='net1'),
    Network(name='net2')
])
session.commit()

_________________________________________________________________2___________________________________________________________________

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)

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

Base.metadata.create_all(engine)

session.add_all([
    User(name='wendy', fullname='Wendy Weathersmith'),
    User(name='mary', fullname='Mary Contrary'),
    User(name='fred', fullname='Fred Flinstone'),
    User(name='ed', fullname='Ed Jones')
])
session.commit()

query = session.query(User.fullname).order_by(User.fullname)
print(query.all())
query = query.filter(User.name.in_(['mary','ed']))
print(query.all())
print(query[1])

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)>" % self.email_address

Base.metadata.create_all(engine)

jack = User(name='jack', fullname='Jack Bean')
jack.addresses = [Address(email_address='jack@gmail.com'),
                  Address(email_address='j25@yahoo.com'),
                  Address(email_address='jack@hotmail.com')]
session.add(jack)
session.commit()

________________________________________________________________3______________________________________________________________________

class Account(Base):
    __tablename__ = 'account'

    id = Column(Integer, primary_key=True)
    owner = Column(String(50), nullable=False)
    balance = Column(Numeric, default=0)

    def __repr__(self):
        return "<Account(%r, %r)>" % (self.owner, self.balance)

class Transaction(Base):
    __tablename__ = 'transaction'

    id = Column(Integer, primary_key=True)
    amount = Column(Numeric, default=0)
    account_id = Column(Integer, ForeignKey('account.id'))
    account = relationship("Account", backref="transactions")

    def __repr__(self):
        return "Transaction: %r" % (self.amount)

Base.metadata.create_all(engine)

a1=Account(owner = "Jack Jones", balance = 5000)
a2=Account(owner="Ed Rendell", balance=10000)
session.add_all([
    a1,
    a2,
    Transaction(amount=500, account=a1),
    Transaction(amount=4500, account=a1),
    Transaction(amount=6000, account=a2),
    Transaction(amount=4000, account=a2)])

session.commit()

for account in session.query(Account).all():
    print(account.owner)
    print(account.balance)
    sum = 0
    for at in account.transactions:
        sum += at.amount
    print(sum)

______________________________________________________________4________________________________________________________________________

query = session.query(User.name, Address.email_address).join(Address).filter(Address.email_address=="j25@yahoo.com")
print(query)
print (query.all())

from sqlalchemy.orm import aliased

user_alias1 = aliased(User)
user_alias2 = aliased(User)

from sqlalchemy import select

query=engine.execute(select([user_alias1.name, user_alias2.name]).distinct().where(user_alias1.name < user_alias2.name))
print (query.fetchall())

session.close()