Skip to content

Instantly share code, notes, and snippets.

@KhorAMus

KhorAMus/Exercises 1 engine_usage

Last active April 27, 2016 13:16
Show Gist options
  • Save KhorAMus/d440ec39942cacfb429e64ef213ccdaa to your computer and use it in GitHub Desktop.
Save KhorAMus/d440ec39942cacfb429e64ef213ccdaa to your computer and use it in GitHub Desktop.
Download ZIP
dbExercises
Raw
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')
Raw
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
Raw
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>
Raw
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='[email protected]' |
| |
| 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_(['[email protected]']))
>>> 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]: ('[email protected]',)
[('fred', '[email protected]')]
>>> 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment