Задание 7
### slide::
### title:: Exercises
# 1. Execute this UPDATE - keep the "result" that's returned
#
# UPDATE user SET fullname='Ed Jones' where username='ed'
#
# 2. how many rows did the above statement update?
#
# 3. Tricky bonus! Combine update() along with select().as_scalar()
# to execute this UPDATE:
#
# UPDATE user SET fullname=fullname ||
# (select email_address FROM address WHERE user_id=user.id)
# WHERE username IN ('jack', 'wendy')
#Запрос
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
from sqlalchemy import and_, or_
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"},
])

update_s = user_table.update().\
	values(fullname="Ed Jones").\
	where(user_table.c.username == "ed")
result = conn.execute(update_s)
print(result)
#Количество строчек, которые изменились
print(result.rowcount)

update_set = user_table.update().\
	values(fullname=user_table.c.username + " " + user_table.select(address_table.c.email_address).\
		where(address_table.c.user_id == user_table.c.id)).\
	where(
		or_(
			user_table.c.username == "jack",
			user_table.c.username == "wendy"
		)
	)

conn = e2.connect()
result1 = conn.execute(update_set)
print(result1)

#Ответ
<sqlalchemy.engine.result.ResultProxy object at 0xb6bb5e6c>
0