------------------presentation 01
--------------------------------------------
+-----------------------------------------------------------------------------------+
| *** 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) --+


---------------------1
>>> engine.execute("insert into employee(emp_name) values(:emp_name)",emp_name='dilbert')[SQL]: insert into employee(emp_name) values(:emp_name)
[SQL]: {'emp_name': 'dilbert'}
[SQL]: COMMIT
---------------------2
>>> print(engine.execute("select * from employee").fetchall())
[SQL]: select * from employee
[SQL]: ()
[(1, u'ed'), (2, u'jack'), (3, u'fred'), (4, u'wendy'), (5, u'mary'), (6, u'dilbert')]


-------------------presentation 02
--------------------------------------------
+------------------------------------------------------------------+
| *** 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) --+

>>> from sqlalchemy import Integer, String, DateTime

>>> 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')))
>>> metadata.create_all(engine)
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA table_info("fancy")
[SQL]: ()
[SQL]: PRAGMA table_info("story")
[SQL]: ()
[SQL]: PRAGMA table_info("address")
[SQL]: ()
[SQL]: PRAGMA table_info("published")
[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) --+

--------------------1
>>> network_reflected=Table('network', metadata2, autoload=True, autoload_with=engine)
[SQL]: PRAGMA table_info("network")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("network")
[SQL]: ()
[SQL]: PRAGMA index_list("network")
[SQL]: ()
>>> print(network_reflected.c)
['network.network_id', 'network.name', 'network.created_at', 'network.owner_id']
>>> print(inspector.get_columns('network'))
[SQL]: PRAGMA table_info("network")
[SQL]: ()
[{'primary_key': 1, 'nullable': False, 'default': None, 'autoincrement': True, 'type': INTEGER(), 'name': u'network_id'}, {'primary_key': 0, 'nullable': False, 'default': None, 'autoincrement': True, 'type': VARCHAR(length=100), 'name': u'name'}, {'primary_key': 0, 'nullable': False, 'default': None, 'autoincrement': True, 'type': DATETIME(), 'name': u'created_at'}, {'primary_key': 0, 'nullable': True, 'default': None, 'autoincrement': True, 'type': INTEGER(), 'name': u'owner_id'}]

---------------------------2
>>> columns_n=[name for name in inspector.get_table_names() for col in inspector.get_columns(name) if 'story_id'== col['name']]
>>> print(columns_n)
[u'published', u'story']





----------------------presentation 3
----------------------------------------
+------------------------------------------------------------------------+
| *** 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) --+


>>> engine.execute(user_table.select().where(user_table.c.fullname == "ed"))
[SQL]: SELECT user.id, user.username, user.fullname
FROM user
WHERE user.fullname = ?
[SQL]: ('ed',)
<sqlalchemy.engine.result.ResultProxy object at 0x7f6f2dc964d0>
>>> engine.execute(user_table.select().where(and_(user_table.c.fullname == "ed", user_table.c.id > 5)))
[SQL]: SELECT user.id, user.username, user.fullname
FROM user
WHERE user.fullname = ? AND user.id > ?
[SQL]: ('ed', 5)
<sqlalchemy.engine.result.ResultProxy object at 0x7f6f2dc96990>
>>> engine.execute(user_table.select().where(or_(user_table.c.username == "edward",and_(user_table.c.fullname=='ed',user_table.c.id > 5))))
[SQL]: SELECT user.id, user.username, user.fullname
FROM user
WHERE user.username = ? OR user.fullname = ? AND user.id > ?
[SQL]: ('edward', 'ed', 5)
<sqlalchemy.engine.result.ResultProxy object at 0x7f6f2dc96cd0>



+----------------------------------------------------------------------------+
| *** 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) --+


>>> result = conn.execute(user_table.insert().values(username='dilbert', fullname='Dilbert Jones'))
[SQL]: INSERT INTO user (username, fullname) VALUES (?, ?)
[SQL]: ('dilbert', 'Dilbert Jones')
[SQL]: COMMIT
>>> result.inserted_primary_key
[4]
>>>> result = conn.execute(select([user_table]).where(or_(user_table.c.username == 'wendy', user_table.c.username == 'dilbert')).order_by(user_table.c.fullname))
[SQL]: SELECT user.id, user.username, user.fullname
FROM user
WHERE user.username = ? OR user.username = ? ORDER BY user.fullname
[SQL]: ('wendy', 'dilbert')
>>> print([r for r in result])
(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) --+


>>> result = conn.execute(select([user_table.c.fullname, address_table.c.email_address]).select_from(user_table.join(address_table, user_table.c.id == address_table.c.user_id)). where(user_table.c.username == 'ed').order_by(address_table.c.email_address))
[SQL]: SELECT user.fullname, address.email_address
FROM user JOIN address ON user.id = address.user_id
WHERE user.username = ? ORDER BY address.email_address
[SQL]: ('ed',)
>>> for row in result:
...      print(row)
...
('Ed Jones', 'ed@ed.com')
('Ed Jones', 'ed@gmail.com')




+---------------------------------------------------------------------+
| *** 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) --+


>>> result = conn.execute(user_table.update().values(fullname='Ed Jones').where(user_table.c.username=='ed'))
[SQL]: UPDATE user SET fullname=? WHERE user.username = ?
[SQL]: ('Ed Jones', 'ed')
[SQL]: COMMIT
>>> result.rowcount
1
>>> result = conn.execute(user_table.update().values(fullname=user_table.c.fullname + 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')))
[SQL]: UPDATE user SET fullname=(user.fullname || (SELECT address.email_address
FROM address
WHERE address.user_id = user.id)) WHERE user.username = ? OR user.username = ?
[SQL]: ('jack', 'wendy')
[SQL]: COMMIT




---------------------------------presentation 4
---------------------------------------------------
+-------------------------------------------------------------------+
| *** 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) --+


>>> class Network(Base):
...      __tablename__ = 'network'
...      network_id = Column(Integer, primary_key = True)
...      name = Column(String(100), nullable = False)
...      def __repr__(self):
...          return "Network(%r, %r)" % (self.network_id, self.name)
...
>>> Base.metadata.create_all(engine)
[SQL]: PRAGMA table_info("network")
[SQL]: ()
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]:
CREATE TABLE network (
        network_id INTEGER NOT NULL,
        name VARCHAR(100) NOT NULL,
        PRIMARY KEY (network_id)
)


[SQL]: ()
[SQL]: COMMIT
>>> session.add_all([Network(name = 'net1'), Network(name = 'net2')])
>>> session.commit()
[SQL]: INSERT INTO network (name) VALUES (?)
[SQL]: ('net1',)
[SQL]: INSERT INTO network (name) VALUES (?)
[SQL]: ('net2',)
[SQL]: COMMIT


+---------------------------------------------------------------------------+
| *** 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) --+


>>> query = session.query(User.fullname).order_by(User.fullname)
>>> query.all()
[SQL]: SELECT user.fullname AS user_fullname
FROM user ORDER BY user.fullname
[SQL]: ()
[('Ed Jones',), ('Fred Flinstone',), ('Mary Contrary',), ('Wendy Weathersmith',)]
>>> query = session.query(User.fullname).filter(or_(User.name == 'mary', User.name == 'ed')).order_by(User.fullname)
>>> print(query[1])
[SQL]: SELECT user.fullname AS user_fullname
FROM user
WHERE user.name = ? OR user.name = ? 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) --+


>>> query = session.query(User.name, Address.email_address).join(Address, User.id == Address.user_id).filter(Address.email_address == 'j25@yahoo.com')
>>> 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 = ?
[SQL]: ('j25@yahoo.com',)
[('fred', 'j25@yahoo.com')]
>>> p1, p2 = aliased(User), aliased(User)
>>> query = session.query(p1.name, p2.name).join(p2, p1.name < p2.name)
>>> for q in query:
...      print(q)
...
[SQL]: SELECT user_1.name AS user_1_name, user_2.name AS user_2_name
FROM user AS user_1 JOIN user AS user_2 ON user_1.name < user_2.name
[SQL]: ()
('ed', 'wendy')
('ed', 'mary')
('ed', 'fred')
('ed', 'jack')
('mary', 'wendy')
('fred', 'wendy')
('fred', 'mary')
('fred', 'jack')
('jack', 'wendy')
('jack', 'mary')



+----------------------------------------------------------------------------+
| *** 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) --+

>>> from sqlalchemy import Integer, String, Numeric
>>> 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)
...      account_id = Column(Integer, ForeignKey('account.id'))
...      account = relationship('Account', backref='transatcions')
...      def __repr__(self):
...          return 'Transaction(%r, %r)' % (self.account_id, self.amount)
>>> Base.metadata.create_all(engine)
[SQL]: PRAGMA table_info("account")
[SQL]: ()
[SQL]: PRAGMA table_info("network")
[SQL]: ()
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA table_info("transaction")
[SQL]: ()
[SQL]: PRAGMA table_info("address")
[SQL]: ()
[SQL]:
CREATE TABLE account (
        id INTEGER NOT NULL,
        owner VARCHAR(50) NOT NULL,
        balance NUMERIC,
        PRIMARY KEY (id)
)
[SQL]: ()
[SQL]: COMMIT
[SQL]:
CREATE TABLE "transaction" (
        id INTEGER NOT NULL,
        amount NUMERIC,
        account_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(account_id) REFERENCES account (id)
)
[SQL]: ()
[SQL]: COMMIT
>>> 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()
[SQL]: BEGIN (implicit)
[SQL]: INSERT INTO account (owner, balance) VALUES (?, ?)
[SQL]: ('Jack Jones', 5000.0)
[SQL]: INSERT INTO account (owner, balance) VALUES (?, ?)
[SQL]: ('Ed Rendell', 10000.0)
[SQL]: INSERT INTO "transaction" (amount, account_id) VALUES (?, ?)
[SQL]: (500.0, 1)
[SQL]: INSERT INTO "transaction" (amount, account_id) VALUES (?, ?)
[SQL]: (4500.0, 1)
[SQL]: INSERT INTO "transaction" (amount, account_id) VALUES (?, ?)
[SQL]: (6000.0, 2)
[SQL]: INSERT INTO "transaction" (amount, account_id) VALUES (?, ?)
[SQL]: (4000.0, 2)
[SQL]: COMMIT
>>> query = session.query(Account.owner, Account.balance, func.sum(Transaction.amount)).join(Transaction).group_by(Transaction.account_id)
>>>print([q for q in query])
[SQL]: BEGIN (implicit)
[SQL]: SELECT account.owner AS account_owner, account.balance AS account_balance, sum("transaction".amount) AS sum_1
FROM account JOIN "transaction" ON account.id = "transaction".account_id GROUP BY "transaction".account_id
[SQL]: ()
/home/ubuntu/pycon2013_student_package/venv/local/lib/python2.7/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)
('Jack Jones', Decimal('5000.0000000000'), Decimal('5000.0000000000'))
('Ed Rendell', Decimal('10000.0000000000'), Decimal('10000.0000000000'))