KhorAMus/Exercises 1 engine_usage

## Exercises 1 engine_usage
+-----------------------------------------------------------------------------------+
| *** Exercises ***                                                                 |
+-----------------------------------------------------------------------------------+
| Assuming this table:                                                              |
|                                                                                   |
|     CREATE TABLE employee (                                                       |
|         emp_id INTEGER PRIMARY KEY,                                               |
|         emp_name VARCHAR(30)                                                      |
|     }                                                                             |
|                                                                                   |
| And using the "engine.execute()" method to invoke a statement:                    |
|                                                                                   |
| 1. Execute an INSERT statement that will insert the row with emp_name='dilbert'.  |
|    The primary key column can be omitted so that it is generated automatically.   |
|                                                                                   |
| 2. SELECT all rows from the employee table.                                       |
+---------------------------------------------------------------------- (13 / 13) --+
сделано не в презентации
Программа на Python 3.4
from sqlalchemy import *
from sqlalchemy.engine.url import *

engine = create_engine("sqlite:///some.db")
# 1 punctum Executing an INSERT statement that will insert the row with emp_name='dilbert'
engine.execute("INSERT INTO employee(emp_name) VALUES (:emp_name)", emp_name="dilbert")
# 2 punctum SELECT all rows from the employee table.
result = engine.execute('SELECT * FROM employee')
for row in result:
    print(row)

Результат работы программы:
(1, 'ed')
(2, 'jack')
(3, 'fred')
(4, 'wendy')
(5, 'mary')
(6, 'dilbert')


## Exercises 2 metadata
+------------------------------------------------------------------+
| *** Exercises ***                                                |
+------------------------------------------------------------------+
| 1. Write a Table construct corresponding to this CREATE TABLE    |
|    statement.                                                    |
|                                                                  |
| CREATE TABLE network (                                           |
|      network_id INTEGER PRIMARY KEY,                             |
|      name VARCHAR(100) NOT NULL,                                 |
|      created_at DATETIME NOT NULL,                               |
|      owner_id INTEGER,                                           |
|      FOREIGN KEY owner_id REFERENCES user(id)                    |
| )                                                                |
|                                                                  |
| 2. Then emit metadata.create_all(), which will                   |
| emit CREATE TABLE for this table (it will skip                   |
| those that already exist).                                       |
|                                                                  |
| The necessary types are imported here:                           |
+----------------------------------------------------- (13 / 20) --+
Код для двух пунктов упражнения на 13-ом слайде (сделано в презентации)
#Write a Table construct corresponding to this CREATE TABLE
#statement.
network_table = Table('network', metadata, Column('network_id', Integer, primary_key=True),
                      Column('name', String(100), nullable=False),
                      Column('created_at', DateTime, nullable=False),
                      Column('owner_id', Integer, ForeignKey('user.id')))
#emiting CREATE TABLE
metadata.create_all(engine)

Результат:
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA table_info("story")
[SQL]: ()
[SQL]: PRAGMA table_info("fancy")
[SQL]: ()
[SQL]: PRAGMA table_info("published")
[SQL]: ()
[SQL]: PRAGMA table_info("address")
[SQL]: ()
[SQL]: PRAGMA table_info("network")
[SQL]: ()
[SQL]:
CREATE TABLE network (
        network_id INTEGER NOT NULL,
        name VARCHAR(100) NOT NULL,
        created_at DATETIME NOT NULL,
        owner_id INTEGER,
        PRIMARY KEY (network_id),
        FOREIGN KEY(owner_id) REFERENCES user (id)
)
[SQL]: ()
[SQL]: COMMIT


+---------------------------------------------------------------------+
| *** Exercises ***                                                   |
+---------------------------------------------------------------------+
|                                                                     |
| 1. Using 'metadata2', reflect the "network" table in the same way   |
|    we just did 'user', then display the columns (or bonus, display  |
|    just the column names)                                           |
|                                                                     |
| 2. Using "inspector", print a list of all table names that          |
|    include a column called "story_id"                               |
+-------------------------------------------------------- (20 / 20) --+
Код для двух пунктов упражнения на 20-ом слайде (сделано в презентации)

Получаем структуру таблицы network

>>> metadata3 = MetaData()
>>> network_table = Table('network', metadata3, autoload=True, autoload_with = engine)
[SQL]: PRAGMA table_info("network")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("network")
[SQL]: ()
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("user")
[SQL]: ()
[SQL]: PRAGMA index_list("user")
[SQL]: ()
[SQL]: PRAGMA index_list("network")
[SQL]: ()

Выводим имена колонок таблицы

>>> for column in network_table.c:
...     print(column)
...
network.network_id
network.name
network.created_at
network.owner_id

Получаем структуру БД

>>> inspector = inspect(engine)

Выводим имена таблиц в которых есть колонка с именем "story_id"

>>> for table_name in inspector.get_table_names():
...     for column in inspector.get_columns(table_name):
...             if column['name'] == 'story_id':
...                     print(table_name)
...
published
story

## Exercises 3 sql_expressions
+------------------------------------------------------------------------+
| *** Exercises ***                                                      |
+------------------------------------------------------------------------+
| Produce these expressions using "user_table.c.fullname",               |
| "user_table.c.id", and "user_table.c.username":                        |
|                                                                        |
| 1. user.fullname = 'ed'                                                |
|                                                                        |
| 2. user.fullname = 'ed' AND user.id > 5                                |
|                                                                        |
| 3. user.username = 'edward' OR (user.fullname = 'ed' AND user.id > 5)  |
+----------------------------------------------------------- (18 / 46) --+
Код и результат для трёх пунктов упражнения 18-ом слайде
user.fullname
# First punctum code
>>> user_table.c.fullname == 'ed'
# Result
<sqlalchemy.sql.expression.BinaryExpression object at 0x0000000000A17828>
#Second punctum code
>>> (user_table.c.fullname == 'ed') & (user_table.c.id > 5)
#Result
<sqlalchemy.sql.expression.BooleanClauseList object at 0x0000000000A17EF0>
#Third punctum code
>>> (user_table.c.username == 'edward') | (user_table.c.fullname == 'ed') & (user_table.c.id > 5)
#result
<sqlalchemy.sql.expression.BooleanClauseList object at 0x0000000000A471D0>


+----------------------------------------------------------------------------+
| *** Exercises ***                                                          |
+----------------------------------------------------------------------------+
| 1. use user_table.insert() and "r = conn.execute()" to emit this           |
| statement:                                                                 |
|                                                                            |
| INSERT INTO user (username, fullname) VALUES ('dilbert', 'Dilbert Jones')  |
|                                                                            |
| 2. What is the value of 'user.id' for the above INSERT statement?          |
|                                                                            |
| 3. Using "select([user_table])", execute this SELECT:                      |
|                                                                            |
| SELECT id, username, fullname FROM user WHERE username = 'wendy' OR        |
|   username = 'dilbert' ORDER BY fullname                                   |
+--------------------------------------------------------------- (27 / 46) --+
Код и результат выполнения для трёх пунктов упражнения на 27-ом слайде

# Punctum 1
# emiting INSERT INTO user (username, fullname) VALUES ('dilbert', 'Dilbert Jones')
>>> r = conn.execute(user_table.insert(), [{'username': 'dilbert', 'fullname': 'Dilbert Jones'}])

[SQL]: INSERT INTO user (username, fullname) VALUES (?, ?)
[SQL]: ('dilbert', 'Dilbert Jones')
[SQL]: COMMIT
# Punctum 2 Getting last note user.id
>>> r.inserted_primary_key
[4]

# Punctum 3 Executing SELECT id, username, fullname FROM user WHERE username = 'wendy' OR username = 'dilbert' ORDER BY fullname
>>> select_statement = select([user_table]).\
...     where((user_table.c.username == 'wendy') | (user_table.c.username == 'dilbert')).\
...     order_by(user_table.c.fullname)
>>> print(conn.execute(select_statement).fetchall())
[SQL]: SELECT user.id, user.username, user.fullname
FROM user
WHERE user.username = ? OR user.username = ? ORDER BY user.fullname
[SQL]: ('wendy', 'dilbert')
[(4, 'dilbert', 'Dilbert Jones'), (3, 'wendy', 'Wendy Weathersmith')]


+------------------------------------------------------------------+
| *** 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                                         |
+----------------------------------------------------- (38 / 46) --+
Код и результат выполнения для упражнения на 38-ом слайде

>>> result = select([user_table.c.fullname, address_table.c.email_address]).select_from(user_table.join(address_table)).
where(user_table.c.username=='ed').order_by(address_table.c.email_addres
s)
>>> print(result)

Результат работы:

SELECT "user".fullname, address.email_address
FROM "user" JOIN address ON "user".id = address.user_id
WHERE "user".username = :username_1 ORDER BY address.email_address


+---------------------------------------------------------------------+
| *** 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')                           |
+-------------------------------------------------------- (46 / 46) --+

Код и результат выполнения упражнения на 46-ом слайде

# 1 punctum. Executing UPDATE user SET fullname='Ed Jones' where username='ed'
>>> result = user_table.update().values(fullname = "Ed Jones").where(user_table.c.username=='ed')
>>> result = conn.execute(result)
[SQL]: UPDATE user SET fullname=? WHERE user.username = ?
[SQL]: ('Ed Jones', 'ed')
[SQL]: COMMIT
# 2 punctum. Get number of rows that affected.
>>> result.rowcount
1

# 3 punctum executing UPDATE user SET fullname=fullname ||
# (select email_address FROM address WHERE user_id=user.id) WHERE username IN ('jack', 'wendy')
>>> update_statement = user_table.update().values(fullname = user_table.c.fullname + user_email.as_scalar()).where(user_table.c.username.in_({'jack', 'wendy'}))
>>> conn.execute(update_statement)
[SQL]: UPDATE user SET fullname=(user.fullname || (SELECT address.email_address
FROM address
WHERE address.user_id = user.id)) WHERE user.username IN (?, ?)
[SQL]: ('jack', 'wendy')
[SQL]: COMMIT
<sqlalchemy.engine.result.ResultProxy object at 0x0000000000C84940>

## Exercises 4 ORM
+-------------------------------------------------------------------+
| *** Exercises - Basic Mapping ***                                 |
+-------------------------------------------------------------------+
|                                                                   |
| 1. Create a class/mapping for this table, call the class Network  |
|                                                                   |
| CREATE TABLE network (                                            |
|      network_id INTEGER PRIMARY KEY,                              |
|      name VARCHAR(100) NOT NULL,                                  |
| )                                                                 |
|                                                                   |
| 2. emit Base.metadata.create_all(engine) to create the table      |
|                                                                   |
| 3. commit a few Network objects to the database:                  |
|                                                                   |
| Network(name='net1'), Network(name='net2')                        |
+------------------------------------------------------ (25 / 72) --+
Исходный код для упражнений с 25-ого слайда. (сделан не в презентации)
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


# punctum 1 Creating a class that represent entities in table "network"
class Network(Base):
    __tablename__ = 'network'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    def __repr__(self):
        return "<Network(%r)>" % (self.name)
# punctum 2 Emiting Base.metadata.create_all(engine) to create the table
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
# punctum 3 adding few networks
from sqlalchemy.orm import Session
session = Session(bind = engine)
session.add(Network(name = 'net1'))
session.add(Network(name = 'net2'))
session.commit()
#check correctness
conn = engine.connect()
select_statement = select([Network.__table__])
result = conn.execute(select_statement)
print (result.fetchall())

Результат работы программы
[(1, 'net1'), (2, 'net2')]


+---------------------------------------------------------------------------+
| *** Exercises - ORM Querying ***                                          |
+---------------------------------------------------------------------------+
| 1. Produce a Query object representing the list of "fullname" values for  |
|    all User objects in alphabetical order.                                |
|                                                                           |
| 2. call .all() on the query to make sure it works!                        |
|                                                                           |
| 3. build a second Query object from the first that also selects           |
|    only User rows with the name "mary" or "ed".                           |
|                                                                           |
| 4. return only the second row of the Query from #3.                       |
+-------------------------------------------------------------- (43 / 72) --+
Исходный код для 43 слайда (сделан в презентации)

# 1 punctum producing a query that representing fullnames in alphabet order
>>> alphabetical_fullnames = session.query(User.fullname).order_by(User.fullname)
# 2 punctum executing query
>>> alphabetical_fullnames.all()
[SQL]: SELECT user.fullname AS user_fullname
FROM user ORDER BY user.fullname
[SQL]: ()
[('Ed Jones',), ('Fred Flinstone',), ('Mary Contrary',), ('Wendy Weathersmith',)]
# 3 punctum
>>> mary_add_alphabetical_fullnames = alphabetical_fullnames.filter(User.name.in_(['mary', 'ed']))
# 4 punctum
>>> mary_add_alphabetical_fullnames.all()
[SQL]: SELECT user.fullname AS user_fullname
FROM user
WHERE user.name IN (?, ?) ORDER BY user.fullname
[SQL]: ('mary', 'ed')
[('Ed Jones',), ('Mary Contrary',)]
>>> mary_add_alphabetical_fullnames[1]
[SQL]: SELECT user.fullname AS user_fullname
FROM user
WHERE user.name IN (?, ?) ORDER BY user.fullname
 LIMIT ? OFFSET ?
[SQL]: ('mary', 'ed', 1, 1)
('Mary Contrary',)

+------------------------------------------------------------------+
| *** Exercises ***                                                |
+------------------------------------------------------------------+
| 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"            |
+----------------------------------------------------- (62 / 72) --+
Исходный код для 62 слайда (сделано в презентации)
>>> join_query = session.query(User.name, Address.email_address).join(Address).filter(Address.email_address.in_(['j25@yahoo.com']))
>>> join_query.all()
[SQL]: SELECT user.name AS user_name, address.email_address AS address_email_address
FROM user JOIN address ON user.id = address.user_id
WHERE address.email_address IN (?)
[SQL]: ('j25@yahoo.com',)
[('fred', 'j25@yahoo.com')]
>>> User1, User2 = aliased(User), aliased(User)
>>> pairs = session.query(User1.name, User2.name).outerjoin().filter(User1.name != User2.name)
>>> pairs.all()
[SQL]: SELECT user_1.name AS user_1_name, user_2.name AS user_2_name
FROM user AS user_1, user AS user_2
WHERE user_1.name != user_2.name
[SQL]: ()
[('ed', 'wendy'), ('ed', 'mary'), ('ed', 'fred'), ('ed', 'jack'), ('wendy', 'ed'), ('wendy', 'mary'), ('wendy', 'fred'),
('wendy', 'jack'), ('mary', 'ed'), ('mary', 'wendy'), ('mary', 'fred'), ('mary', 'jack'), ('fred', 'ed'), ('fred', 'wendy'),
('fred', 'mary'), ('fred', 'jack'), ('jack', 'ed'), ('jack', 'wendy'), ('jack', 'mary'), ('jack', 'fred')]


+----------------------------------------------------------------------------+
| *** Exercises - Final Exam ! ***                                           |
+----------------------------------------------------------------------------+
| 1. Create a class called 'Account', with table "account":                  |
|                                                                            |
|      id = Column(Integer, primary_key=True)                                |
|      owner = Column(String(50), nullable=False)                            |
|      balance = Column(Numeric, default=0)                                  |
|                                                                            |
| 2. Create a class "Transaction", with table "transaction":                 |
|      * Integer primary key                                                 |
|      * numeric "amount" column                                             |
|      * Integer "account_id" column with ForeignKey('account.id')           |
|                                                                            |
| 3. Add a relationship() on Transaction named "account", which refers       |
|    to "Account", and has a backref called "transactions".                  |
|                                                                            |
| 4. Create a database, create tables, then insert these objects:            |
|                                                                            |
|      a1 = Account(owner='Jack Jones', balance=5000)                        |
|      a2 = Account(owner='Ed Rendell', balance=10000)                       |
|      Transaction(amount=500, account=a1)                                   |
|      Transaction(amount=4500, account=a1)                                  |
|      Transaction(amount=6000, account=a2)                                  |
|      Transaction(amount=4000, account=a2)                                  |
|                                                                            |
| 5. Produce a report that shows:                                            |
|     * account owner                                                        |
|     * account balance                                                      |
|     * summation of transaction amounts per account (should match balance)  |
|       A column can be summed using func.sum(Transaction.amount)            |
+--------------------------------------------------------------- (72 / 72) --+
# исходный код для 72-ого слайда (сделан не в презентации)
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
# punctum 1: Creating a class Account and table account
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)

# punctum 2, 3: Creating a class Transaction and table transaction
class Transaction(Base):
    __tablename__ = 'transaction'
    id = Column(Integer, primary_key=True)
    amount = Column(Numeric, nullable=False)
    account_id = Column(Integer, ForeignKey(Account.__tablename__ + '.id'), nullable=False)
    account = relationship('Account', backref="transactions")
    def __repr__(self):
        return "<Transaction(%r)>" % (self.amount)

# punctum 4 : Creating a database, create tables, then insert few objects
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
from sqlalchemy.orm import Session
session = Session(bind = engine)
a1 = Account(owner = "Jack Jones", balance = 5000)
a2 = Account(owner = "Ed Rendell", balance = 10000)
t1 = Transaction(amount = 500, account = a1)
t2 = Transaction(amount = 4500, account = a1)
t3 = Transaction(amount = 6000, account = a2)
t4 = Transaction(amount = 4000,  account = a2)
session.add_all([a1,a2,t1,t2,t3,t4])
session.commit()

# punctum 5 : Producing a report

for account in session.query(Account).all():
    account_owner = account.owner
    account_balance = account.balance
    spent_money = 0
    for account_transaction in account.transactions:
        spent_money += account_transaction.amount
    print("account_owner: " + str(account_owner) + '\t' +
          "account_balance: " + str(account_balance) + '\t' +  "money spent: " +str(spent_money))

Результат работы программы:
C:\Python34\lib\site-packages\sqlalchemy\types.py:307: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  d[coltype] = rp = d['impl'].result_processor(dialect, coltype)
account_owner: Jack Jones	account_balance: 5000.0000000000	spent money: 5000.0000000000
account_owner: Ed Rendell	account_balance: 10000.0000000000	spent money: 10000.0000000000
	+-----------------------------------------------------------------------------------+
	\| * Exercises * \|
	+-----------------------------------------------------------------------------------+
	\| Assuming this table: \|
	\| \|
	\| CREATE TABLE employee ( \|
	\| emp_id INTEGER PRIMARY KEY, \|
	\| emp_name VARCHAR(30) \|
	\| } \|
	\| \|
	\| And using the "engine.execute()" method to invoke a statement: \|
	\| \|
	\| 1. Execute an INSERT statement that will insert the row with emp_name='dilbert'. \|
	\| The primary key column can be omitted so that it is generated automatically. \|
	\| \|
	\| 2. SELECT all rows from the employee table. \|
	+---------------------------------------------------------------------- (13 / 13) --+
	сделано не в презентации
	Программа на Python 3.4
	from sqlalchemy import *
	from sqlalchemy.engine.url import *

	engine = create_engine("sqlite:///some.db")
	# 1 punctum Executing an INSERT statement that will insert the row with emp_name='dilbert'
	engine.execute("INSERT INTO employee(emp_name) VALUES (:emp_name)", emp_name="dilbert")
	# 2 punctum SELECT all rows from the employee table.
	result = engine.execute('SELECT * FROM employee')
	for row in result:
	print(row)

	Результат работы программы:
	(1, 'ed')
	(2, 'jack')
	(3, 'fred')
	(4, 'wendy')
	(5, 'mary')
	(6, 'dilbert')
	+-------------------------------------------------------------------+
	\| * Exercises - Basic Mapping * \|
	+-------------------------------------------------------------------+
	\| \|
	\| 1. Create a class/mapping for this table, call the class Network \|
	\| \|
	\| CREATE TABLE network ( \|
	\| network_id INTEGER PRIMARY KEY, \|
	\| name VARCHAR(100) NOT NULL, \|
	\| ) \|
	\| \|
	\| 2. emit Base.metadata.create_all(engine) to create the table \|
	\| \|
	\| 3. commit a few Network objects to the database: \|
	\| \|
	\| Network(name='net1'), Network(name='net2') \|
	+------------------------------------------------------ (25 / 72) --+
	Исходный код для упражнений с 25-ого слайда. (сделан не в презентации)
	from sqlalchemy import *
	from sqlalchemy.ext.declarative import declarative_base
	Base = declarative_base()


	# punctum 1 Creating a class that represent entities in table "network"
	class Network(Base):
	__tablename__ = 'network'
	id = Column(Integer, primary_key=True)
	name = Column(String(100), nullable=False)
	def __repr__(self):
	return "<Network(%r)>" % (self.name)
	# punctum 2 Emiting Base.metadata.create_all(engine) to create the table
	engine = create_engine('sqlite://')
	Base.metadata.create_all(engine)
	# punctum 3 adding few networks
	from sqlalchemy.orm import Session
	session = Session(bind = engine)
	session.add(Network(name = 'net1'))
	session.add(Network(name = 'net2'))
	session.commit()
	#check correctness
	conn = engine.connect()
	select_statement = select([Network.__table__])
	result = conn.execute(select_statement)
	print (result.fetchall())

	Результат работы программы
	[(1, 'net1'), (2, 'net2')]


	+---------------------------------------------------------------------------+
	\| * Exercises - ORM Querying * \|
	+---------------------------------------------------------------------------+
	\| 1. Produce a Query object representing the list of "fullname" values for \|
	\| all User objects in alphabetical order. \|
	\| \|
	\| 2. call .all() on the query to make sure it works! \|
	\| \|
	\| 3. build a second Query object from the first that also selects \|
	\| only User rows with the name "mary" or "ed". \|
	\| \|
	\| 4. return only the second row of the Query from #3. \|
	+-------------------------------------------------------------- (43 / 72) --+
	Исходный код для 43 слайда (сделан в презентации)

	# 1 punctum producing a query that representing fullnames in alphabet order
	>>> alphabetical_fullnames = session.query(User.fullname).order_by(User.fullname)
	# 2 punctum executing query
	>>> alphabetical_fullnames.all()
	[SQL]: SELECT user.fullname AS user_fullname
	FROM user ORDER BY user.fullname
	[SQL]: ()
	[('Ed Jones',), ('Fred Flinstone',), ('Mary Contrary',), ('Wendy Weathersmith',)]
	# 3 punctum
	>>> mary_add_alphabetical_fullnames = alphabetical_fullnames.filter(User.name.in_(['mary', 'ed']))
	# 4 punctum
	>>> mary_add_alphabetical_fullnames.all()
	[SQL]: SELECT user.fullname AS user_fullname
	FROM user
	WHERE user.name IN (?, ?) ORDER BY user.fullname
	[SQL]: ('mary', 'ed')
	[('Ed Jones',), ('Mary Contrary',)]
	>>> mary_add_alphabetical_fullnames[1]
	[SQL]: SELECT user.fullname AS user_fullname
	FROM user
	WHERE user.name IN (?, ?) ORDER BY user.fullname
	LIMIT ? OFFSET ?
	[SQL]: ('mary', 'ed', 1, 1)
	('Mary Contrary',)

	+------------------------------------------------------------------+
	\| * Exercises * \|
	+------------------------------------------------------------------+
	\| 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" \|
	+----------------------------------------------------- (62 / 72) --+
	Исходный код для 62 слайда (сделано в презентации)
	>>> join_query = session.query(User.name, Address.email_address).join(Address).filter(Address.email_address.in_(['j25@yahoo.com']))
	>>> join_query.all()
	[SQL]: SELECT user.name AS user_name, address.email_address AS address_email_address
	FROM user JOIN address ON user.id = address.user_id
	WHERE address.email_address IN (?)
	[SQL]: ('j25@yahoo.com',)
	[('fred', 'j25@yahoo.com')]
	>>> User1, User2 = aliased(User), aliased(User)
	>>> pairs = session.query(User1.name, User2.name).outerjoin().filter(User1.name != User2.name)
	>>> pairs.all()
	[SQL]: SELECT user_1.name AS user_1_name, user_2.name AS user_2_name
	FROM user AS user_1, user AS user_2
	WHERE user_1.name != user_2.name
	[SQL]: ()
	[('ed', 'wendy'), ('ed', 'mary'), ('ed', 'fred'), ('ed', 'jack'), ('wendy', 'ed'), ('wendy', 'mary'), ('wendy', 'fred'),
	('wendy', 'jack'), ('mary', 'ed'), ('mary', 'wendy'), ('mary', 'fred'), ('mary', 'jack'), ('fred', 'ed'), ('fred', 'wendy'),
	('fred', 'mary'), ('fred', 'jack'), ('jack', 'ed'), ('jack', 'wendy'), ('jack', 'mary'), ('jack', 'fred')]


	+----------------------------------------------------------------------------+
	\| * Exercises - Final Exam ! * \|
	+----------------------------------------------------------------------------+
	\| 1. Create a class called 'Account', with table "account": \|
	\| \|
	\| id = Column(Integer, primary_key=True) \|
	\| owner = Column(String(50), nullable=False) \|
	\| balance = Column(Numeric, default=0) \|
	\| \|
	\| 2. Create a class "Transaction", with table "transaction": \|
	\| * Integer primary key \|
	\| * numeric "amount" column \|
	\| * Integer "account_id" column with ForeignKey('account.id') \|
	\| \|
	\| 3. Add a relationship() on Transaction named "account", which refers \|
	\| to "Account", and has a backref called "transactions". \|
	\| \|
	\| 4. Create a database, create tables, then insert these objects: \|
	\| \|
	\| a1 = Account(owner='Jack Jones', balance=5000) \|
	\| a2 = Account(owner='Ed Rendell', balance=10000) \|
	\| Transaction(amount=500, account=a1) \|
	\| Transaction(amount=4500, account=a1) \|
	\| Transaction(amount=6000, account=a2) \|
	\| Transaction(amount=4000, account=a2) \|
	\| \|
	\| 5. Produce a report that shows: \|
	\| * account owner \|
	\| * account balance \|
	\| * summation of transaction amounts per account (should match balance) \|
	\| A column can be summed using func.sum(Transaction.amount) \|
	+--------------------------------------------------------------- (72 / 72) --+
	# исходный код для 72-ого слайда (сделан не в презентации)
	from sqlalchemy import *
	from sqlalchemy.ext.declarative import declarative_base
	from sqlalchemy.orm import relationship
	Base = declarative_base()
	# punctum 1: Creating a class Account and table account
	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)

	# punctum 2, 3: Creating a class Transaction and table transaction
	class Transaction(Base):
	__tablename__ = 'transaction'
	id = Column(Integer, primary_key=True)
	amount = Column(Numeric, nullable=False)
	account_id = Column(Integer, ForeignKey(Account.__tablename__ + '.id'), nullable=False)
	account = relationship('Account', backref="transactions")
	def __repr__(self):
	return "<Transaction(%r)>" % (self.amount)

	# punctum 4 : Creating a database, create tables, then insert few objects
	engine = create_engine("sqlite://")
	Base.metadata.create_all(engine)
	from sqlalchemy.orm import Session
	session = Session(bind = engine)
	a1 = Account(owner = "Jack Jones", balance = 5000)
	a2 = Account(owner = "Ed Rendell", balance = 10000)
	t1 = Transaction(amount = 500, account = a1)
	t2 = Transaction(amount = 4500, account = a1)
	t3 = Transaction(amount = 6000, account = a2)
	t4 = Transaction(amount = 4000, account = a2)
	session.add_all([a1,a2,t1,t2,t3,t4])
	session.commit()

	# punctum 5 : Producing a report

	for account in session.query(Account).all():
	account_owner = account.owner
	account_balance = account.balance
	spent_money = 0
	for account_transaction in account.transactions:
	spent_money += account_transaction.amount
	print("account_owner: " + str(account_owner) + '\t' +
	"account_balance: " + str(account_balance) + '\t' + "money spent: " +str(spent_money))

	Результат работы программы:
	C:\Python34\lib\site-packages\sqlalchemy\types.py:307: SAWarning: Dialect sqlite+pysqlite does not support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
	d[coltype] = rp = d['impl'].result_processor(dialect, coltype)
	account_owner: Jack Jones account_balance: 5000.0000000000 spent money: 5000.0000000000
	account_owner: Ed Rendell account_balance: 10000.0000000000 spent money: 10000.0000000000