def groupby_created(precision='month'):
conn = sa.connect()
month = func.date_trunc('month', users.c.created).label('month')
q = select([month,
func.count(users.c.id)]).group_by('month').order_by('month')
#return conn.execute(q).fetchall()
return [(dt.strftime('%b %Y'), num)
for (dt, num) in conn.execute(q).fetchall()]
def add_to_groups(uid, groups):
conn = sa.connect()
groups_existing = info(uid).groups
groups_new = set(groups_existing + groups)
q = users.update().values(groups=groups_new).where(users.c.id == uid)
conn.execute(q)
conn.execute(group_users.delete().where(group_users.c.user_id == uid))
conn.execute(group_users.insert(), [{'user_id': uid, 'group_name': name} for name in groups])
def info(email=None, uid=None):
conn = sa.connect()
_fields = [users.c.id, users.c.active, users.c.created, users.c.groups]
if email:
q = select(_fields).where(users.c.email == email.lower())
else:
q = select(_fields).where(users.c.id == uid)
res = conn.execute(q)
return dict(zip(res.keys(), res.fetchone()))
def edit(uid, mod_data):
conn = sa.connect()
editables = set(['email', 'password'])
if not editables.issuperset(mod_data.keys()):
raise SecurityViolation()
if 'password' in mod_data:
mod_data['password'] = encrypt(mod_data['password'], settings.SALT)
q = users.update().values(**mod_data).where(users.c.id == uid)
conn.execute(q)
return True
def authenticate(email, password):
"""
returns session if successful else returns None
"""
if not validate_email(email):
raise InvalidEmailError(email)
conn = sa.connect()
q = select([users.c.id, users.c.password, users.c.groups]).where(users.c.email == email.lower())
row = conn.execute(q).fetchone()
if not row:
raise EmailiDoesNotExistError(email)
uid, encpassword, groups = conn.execute(q).fetchone()
if encpassword == encrypt(password, settings.SALT):
return sessionslib.create(uid, groups)
def create(email, password, groups=[], connection=None):
validate_password(password)
if not validate_email(email):
raise InvalidEmailError(email)
email = email.lower()
if uid_by_email(email):
raise EmailExistsError(email)
conn = sa.connect()
encpassword = encrypt(password, settings.SALT)
created = datetime.datetime.now()
q = users.insert().values(email=email, password=encpassword, created=created, groups=groups)
conn.execute(q)
q = select([users.c.id]).where(users.c.email == email)
uid = conn.execute(q).fetchone()[0]
if groups:
conn.execute(group_users.insert(), [{'user_id': uid, 'group_name': name} for name in groups])
#user_created.send(uid, email)
if settings.SEND_WELCOME_EMAIL:
welcome(email)
return uid
def count():
conn = sa.connect()
q = select([func.count(users.c.id)])
return conn.execute(q).fetchone()[0]
def created_today():
conn = sa.connect()
today = datetime.date.today()
q = select([func.count(users.c.id)]).where(users.c.created > today)
return conn.execute(q).fetchone()[0]
def theapi(self, book_id):
conn = sa.connect()
q = self.books.insert().values(id=book_id, name='Another Book')
conn.execute(q)
return (book_id,
'Another Book') in list(conn.execute(self.books.select()))
def _test_insert(self):
conn = sa.connect()
q = self.books.insert().values(name='A Book')
conn.execute(q)
assert ((1, 'A Book') in list(conn.execute(self.books.select())))
def count():
conn = sa.connect()
q = select([func.count(users.c.id)])
return conn.execute(q).fetchone()[0]
def created_today():
conn = sa.connect()
today = datetime.date.today()
q = select([func.count(users.c.id)]).where(users.c.created > today)
return conn.execute(q).fetchone()[0]
def groupby_created(precision='month'):
conn = sa.connect()
month = func.date_trunc('month', users.c.created).label('month')
q = select([month, func.count(users.c.id)]).group_by('month').order_by('month')
#return conn.execute(q).fetchall()
return [(dt.strftime('%b %Y'), num) for (dt, num) in conn.execute(q).fetchall()]
def list_():
conn = sa.connect()
fields = [users.c.id, users.c.active, users.c.created, users.c.groups]
q = users.select(fields)
return conn.execute(q).fetchall()
def uid_by_email(email):
conn = sa.connect()
q = select([users.c.id]).where(users.c.email == email.lower())
row = conn.execute(q).fetchone()
return row and row[0] or None
def disable(uid):
conn = sa.connect()
q = users.update().values(active=False).where(users.c.id == uid)
conn.execute(q)