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)