#Создать таблицу employee
#Заполнить одну строку значением dilbert
#Вывести одну строку таблицы

запрос1:
from sqlalchemy import create_engine
import os

if os.path.exists("some.db"):
    os.remove("some.db")
e = create_engine("sqlite:///some.db")
e.execute("""
    create table employee (
        emp_id integer primary key,
        emp_name varchar(30)
    )
""")

e.execute("""insert into employee(emp_name) values ('dilbert')""")

result = e.execute("select * from employee")
for row in result:
	print(row)

ответ:
vova@debian:~$ python laba5.py
(1, u'dilbert')


#Создать таблицы user и network
#Вывести на экран названия столбцов таблицы network
#При помощи inspect вывести на экран названия столбцов таблицы user
#При помощи inspect вывести на экран стобцы, содержащие поле story_id (т.к. таких не существует, то вывели пустое поле)
запрос2:
from sqlalchemy import MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String, DateTime
from sqlalchemy import create_engine
from sqlalchemy import inspect
from sqlalchemy import ForeignKey

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

metadata2 = MetaData()
net_table = Table('network', metadata2,
		Column('net_id', Integer, primary_key=True),
		Column('name', String(100), nullable=False),
		Column('create_at', DateTime, nullable=False),
		Column('owner_id', Integer, ForeignKey('user.id'))
		)

force = create_engine("sqlite:///some.db")
print(metadata.create_all(force))

net_reflected=Table('network', metadata2, autoload=True, autoload_with=force)
print(net_reflected.c)

inspector=inspect(force)
print(inspector.get_table_names())
print(inspector.get_columns('story_id'))

ответ:
vova@debian:~$ python laba5.py
None
['network.net_id', 'network.name', 'network.create_at', 'network.owner_id']
[u'employee', u'user']
[]

#Создать таблицу user
#Найти в таблице строки с нужными значениями
запрос3:
from sqlalchemy import MetaData, Table, Column, String, Integer
from sqlalchemy import create_engine
#from sqlalchemy

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


force = create_engine("sqlite:///some.db")
metadata.create_all(force)

str(user_table.c.fullname == 'ed')
print(user_table.c.fullname == 'ed')

str((user_table.c.fullname == 'ed') & (user_table.c.id>5))
print((user_table.c.fullname == 'ed') & (user_table.c.id>5))

str((user_table.c.username == 'edward') | ((user_table.c.fullname == 'ed') & (user_table.c.id > 5)))
print((user_table.c.username == 'edward') | ((user_table.c.fullname == 'ed') & (user_table.c.id > 5)))
ответ:
vova@debian:~$ python laba5.py
"user".fullname = :fullname_1
"user".fullname = :fullname_1 AND "user".id > :id_1
"user".username = :username_1 OR "user".fullname = :fullname_1 AND "user".id > :id_1

#Создать таблицу user
#При помощи insert добавить в таблицу строку
#При помощи select выбрать нужные строки из таблицы
запрос 4:
from sqlalchemy import MetaData, Table, Column, String, Integer
from sqlalchemy import create_engine
from sqlalchemy import select
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))
                )


force = create_engine("sqlite://")
metadata.create_all(force)

insert_s = user_table.insert().values(username='dilbert', fullname='Dilbert Jones')

conn = force.connect()
result = conn.execute(insert_s)
print(result)
print(result.inserted_primary_key)


select_s = select([user_table.c.username, user_table.c.fullname]).\
	where(
		or_(
			user_table.c.username == 'wendy',
			user_table.c.username == 'dilbert'
		)
	).\
	order_by(user_table.c.fullname)
answ = conn.execute(select_s)
for row in answ:
	print(row)

ответ:
vova@debian:~$ python laba5.py
<sqlalchemy.engine.result.ResultProxy object at 0xb6b266ec>
[1]
(u'dilbert', u'Dilbert Jones')

#Создать таблицы user и address
#При помощи join объединить их разными методами(условия - where, группировка - order_by)
запрос5:
from sqlalchemy import MetaData, Table, Column, String, Integer
from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy import and_, or_
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)
                )

force = create_engine("sqlite://")
metadata.create_all(force)

conn = force.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": "wendy@gmail.com"},
])


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

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

answ=conn.execute(select_stmt)
print(answ)

ответ:
vova@debian:~$ python laba5.py
"user" JOIN address ON "user".id = address.user_id
<sqlalchemy.engine.result.ResultProxy object at 0xb6bb7e2c>

#Создать таблицу user и address
#Заполнить таблицу address данными
#Обновить данные в таблице при помощи update
запрос6:
from sqlalchemy import MetaData, Table, Column, String, Integer
from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy import and_, or_
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)
                )

force = create_engine("sqlite://")
metadata.create_all(force)

conn = force.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": "wendy@gmail.com"},
])


update_s = user_table.update().\
		values(fullname = "Ed Jones").\
		where(user_table.c.username == "ed")

conn = force.connect()
result = conn.execute(update_s)
print(result)

print(result.rowcount)

update_st = 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 = force.connect()
answ = conn.execute(update_s)
print(answ)

ответ:
vova@debian:~$ python laba5.py
<sqlalchemy.engine.result.ResultProxy object at 0xb6b19ecc>
0
<sqlalchemy.engine.result.ResultProxy object at 0xb6b19f0c>

#Создать таблицу network в классе Network
#Используя Session добавить в нее данные
запрос7:
from sqlalchemy import MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String, DateTime
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


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.name
            )

force =create_engine("sqlite:///some.db")
Base.metadata.create_all(force)

session = Session(bind=force)

ed_user1 = Network(name='net1')
session.add(ed_user1)

ed_user2 = Network(name='net2')
session.add(ed_user2)

session.commit()
print(ed_user1.name, ed_user2.name)

ответ:
vova@debian:~$ python laba5.py
(u'net1', u'net2')


#Создать таблицу user и заполнить ее данными
#При помощи order_by упорядочить по алфавиту данные
#При помощи filter сделать выборку
запрос8:
from sqlalchemy import MetaData, and_, or_
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String, DateTime
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

    user_id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    fullname = Column(String(100), nullable=False)

    def __repr__(self):
        return "<User(%r, %r)>" % (
                self.name, self.fullname
            )

force =create_engine("sqlite:///some.db")
Base.metadata.create_all(force)

session = Session(bind=force)
session.add_all([
    User(name='wendy', fullname='Wendy Weathersmith'),
    User(name='mary', fullname='Mary Contrary'),
    User(name='fred', fullname='Fred Flinstone')
])
session.commit()

queury = session.query(User.fullname).order_by(User.fullname)
print(queury.all())

query2 = session.query(User.fullname).filter(
			or_(
				User.name=='mary',
				User.name=='ed'
			))
print(query2.all())
print(queury[2])
ответ:
vova@debian:~$ python laba5.py
[(u'Fred Flinstone',), (u'Mary Contrary',), (u'Wendy Weathersmith',)]
[(u'Mary Contrary',)]
(u'Wendy Weathersmith',)

#Создать таблицы user и address и заполняем их данными
#При помощи join объединить их
#При помощи filter выбрать нужные нам строки (сделать выборку)
запрос9:
from sqlalchemy import and_, or_
from sqlalchemy import Table, Column, MetaData
from sqlalchemy import Integer, String, DateTime
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    user_id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    fullname = Column(String(100), nullable=False)

    def __repr__(self):
        return "<User(%r, %r, %r)>" % (
                self.user_id, self.name, self.fullname
            )

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", backref="addresses")

    def __repr__(self):
        return "<Address(%r,%r)>" % self.id, self.email_address

force =create_engine("sqlite://")
Base.metadata.create_all(force)
q = Session(bind=force)

q.add_all([
    User(name='wendy', fullname='Wendy Weathersmith'),
    User(name='mary', fullname='Mary Contrary'),
    User(name='fred', fullname='Fred Flinstone')
])

jack = User(name='jack', fullname='Jack Bean')
jack.addresses

jack.addresses = [
                Address(email_address='jack@gmail.com'),
                Address(email_address='j25@yahoo.com'),
                Address(email_address='jack@hotmail.com'),
                ]


q.commit()

query = ession.query(User.name).join(Address.email_address).\
    filter(Address.email_address == 'j25@yahoo.com').all()

print(query.all())

ответ:
vova@debian:~$ python laba5.py
[('jack', 'j25@yahoo.com')]

#1 Создать класс под названием «Account» с таблицей «account»
#2 Создать класс "Transaction", с таблицей "transaction"
#3 Добавить связь в Transaction с именем «account», которая ссылается
# на «Account» и имеет ссылку на обратную ссылку «transactions».
#4 Создать базу данных, создать таблицы, затем вставить эти объекты
запрос10:
from sqlalchemy import func
from sqlalchemy import MetaData, and_, or_
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String, DateTime, Numeric
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
import os
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

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)
    account_id = Column(Integer, ForeignKey('account.id'))
    amount = Column(Numeric, default=0)

    account = relationship("Account", backref="transactions")

    def __repr__(self):
        return "<Transaction(%r, %r)>" % (
                self.owner, self.balance
            )

force =create_engine("sqlite:///some.db")
Base.metadata.create_all(force)

session = Session(bind=force)

q1 = Account(owner='Jack Jones', balance=5000)
q2 = Account(owner='Ed Rendell', balance=10000)


session.add_all([
	q1,
	q2,
        Transaction(amount=500, account=q1),
        Transaction(amount=4500, account=q1),
        Transaction(amount=6000, account=q2),
        Transaction(amount=4000, account=q2)
	])

session.commit()

query = session.query(
	Account.owner, Account.balance,
	func.sum(Transaction.amount).lable('summ')
).select_from(Account).join(Transaction.account).all()