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

Запрос 1:

from sqlalchemy import create_engine
import os

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

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

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

Ответ:
(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'))
		)

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

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

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

Ответ:
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))
                )


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

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

Ответ:
"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))
                )


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

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

conn = move.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)

Ответ:
<sqlalchemy.engine.result.ResultProxy object at 0xb6b0f6ec>
[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)
                )

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

conn = move.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)

Ответ:
"user" JOIN address ON "user".id = address.user_id
<sqlalchemy.engine.result.ResultProxy object at 0xb6b57e6c>


//Создание таблиц 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)
                )

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

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

Ответ:
<sqlalchemy.engine.result.ResultProxy object at 0xb6bcaeec>
0
<sqlalchemy.engine.result.ResultProxy object at 0xb6bcaf2c>


//Создание таблицы 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
            )

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

session = Session(bind=move)

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)

Ответ:
(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
            )

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

session = Session(bind=move)
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])

Ответ:
[(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

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

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())

Ответ:
[('jack', 'j25@yahoo.com')]


//Создание класса под названием «Учетная запись», с таблицей «account»
//Создание класса под названием «Транзакция», с таблицей «транзакция»
//Добавление связи() в транзакцию с именем «учетная запись», которая относится к «Учетная запись», и имеет backref, называемый «transactions».
//Создание базы данных, создание таблиц, и после вставка этих объектов

Запрос 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
            )

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

session = Session(bind=move)

l1 = Account(owner='Jack Jones', balance=5000)
l2 = Account(owner='Ed Rendell', balance=10000)


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

session.commit()

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