Задание 6
### title:: Exercises
# Produce this SELECT:
#
# SELECT fullname, email_address FROM user JOIN address
# ON user.id = address.user_id WHERE username='ed'
# ORDER BY email_address
#Запрос
from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String
from sqlalchemy import select
from sqlalchemy import ForeignKey
metadata = MetaData()

user_table = Table('user', metadata,
	Column('id', Integer, primary_key=True),
	Column('username', String(50)),
	Column('fullname', String(50))
)

address_table = Table("address", metadata,
	Column('id', Integer, primary_key=True),
	Column('user_id', Integer, ForeignKey('user.id'), nullable=False),
	Column('email_address', String(100), nullable=False)
)


e2 = create_engine("sqlite://")
metadata.create_all(e2)
conn = e2.connect()

conn.execute(address_table.insert(), [
	{"user_id": 1, "email_address": "ed@ed.com"},
	{"user_id": 1, "email_address": "ed@gmail.com"},
	{"user_id": 2, "email_address": "jack@yahoo.com"},
	{"user_id": 3, "email_address": "wandy@gmail.com"},
])

join_obj = user_table.join(address_table,
	user_table.c.id == address_table.c.user_id)

select_s = select([user_table, address_table]).select_from(join_obj).\
	where(user_table.c.username == 'ed').\
		order_by(address_table.c.email_address)

result = conn.execute(select_s)
print(result)

#Ответ
#marina@debian:~$ python lab53.py
#<sqlalchemy.engine.result.ResultProxy object at 0xb6a5b22c>