In [2]:
from sqlalchemy import create_engine
engine = create_engine('sqlite:////vagrant/utils/db.sqlite')
In [3]:
from sqlalchemy import Table, Column, MetaData
metadata = MetaData()
connection = engine.connect()
In [15]:
user_table = Table('user', metadata, autoload=True, autoload_with=connection)
purchase_table = Table('purchase', metadata, autoload=True, autoload_with=connection)
In [5]:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
In [6]:
class User(object):
def __init__(self, title, first_name, surname):
self.title = title
self.first_name = first_name
self.surname = surname
def __repr__(self):
return "<User(id_user={}, {} {} {})>".format(self.id_user, self.title, self.first_name, self.surname)
class Purchase(object):
def __init__(self, id_user, category, item, date, price):
self.id_user = id_user
self.category = category
self.item = item
self.date = date
self.price = price
def __repr__(self):
return ("<Purchase(id_purchase={}, id_user={}, cat={}, item={}, date={}, price={})>"
.format(self.id_purchase, self.id_user, self.category, self.item, self.date, self.price))
Now map the items together.
In [7]:
from sqlalchemy.orm import mapper, relationship, backref
In [8]:
mapper(Purchase, purchase_table)
mapper(User, user_table, properties={
'purchases': relationship(Purchase, backref='user', order_by=purchase_table.c.id_purchase)
})
Out[8]:
In [9]:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
In [10]:
users = session.query(User).limit(10).all()
for u in users:
print(u)
In [11]:
u1 = users[0]
print(u1.purchases)
In [12]:
from sqlalchemy import func
In [13]:
results = (session.query(User, func.count(Purchase.id_purchase))
.join(Purchase)
.group_by(Purchase.id_user)
.having(func.count(Purchase.id_purchase) > 10)
.order_by(func.count(Purchase.id_purchase).desc())
.limit(10)
.all())
for (u, count) in results:
print("{}, Num Purchase={}".format(u, count))
In [ ]: