def dbbrowser(session_id): try: user_id = session.get_user_id(session_id) if user_id <> 7: print user_id return 'NO!', 500 params = json.loads(request.data) sql = params['sql'] c = create() cur = c.cursor() if sql.lower().find('update') > -1 or sql.lower().find('update') > -1: cur.execute(sql) cur.close() c.commit() c.close() return 'OK', 200 else: cur.execute(sql) rows = cur.fetchall() cur.close() c.commit() res = [dict(row) for row in rows] return jsonify(res) except BaseException as ex: return ex.message, 500
def get_user_by_email(email): sql = '''select * from people where EMAIL = ?''' connection = create() c = connection.cursor() res = c.execute(sql, [email]) rows = res.fetchall() return None if len(rows) == 0 else rows[0][0]
def delete_rule(id): connection = create() c = connection.cursor() sql = 'delete from rules where id = ?' c.execute(sql, [id]) c.close() connection.commit() connection.close()
def insert_table_company(id, name): connection = create() c = connection.cursor() c.execute('insert into company (ID,NAME) values (?,?)', (id, name)) last_id = c.lastrowid c.close() connection.close() return last_id
def insert_table_session(id, user_id): connection = create() c = connection.cursor() c.execute('insert into session (ID, USER_ID) values (?,?)', (id, user_id)) last_id = c.lastrowid c.close() connection.commit() connection.close()
def get_user_id(sid): sql = '''select * from session where ID = ?''' connection = create() c = connection.cursor() print sid, sql res = c.execute(sql, [sid]) rows = res.fetchall() return None if len(rows) == 0 else rows[0][1]
def get_brand_rules(id): connection = create() c = connection.cursor() sql = consts.SQL_GET_BRAND_USERS c.execute(sql, {"brand_id": id}) rows = c.fetchall() c.close() connection.close() return [dict(row) for row in rows]
def insert_table_template(name, brand_id, path, project=u"без кампании"): connection = create() c = connection.cursor() c.execute('insert into template (NAME,BRAND_ID,PATH, PROJECT) values (?,?,?,?)', (name, brand_id, path,project)) last_id = c.lastrowid c.close() connection.commit() connection.close() return last_id
def delete_template(id): connection = create() c = connection.cursor() sql = '' sql += 'delete from template where ID = ?' c.execute(sql, [id]) c.close() connection.commit() connection.close()
def send_invitation(email): sid = str(uuid.uuid4()) sql = "insert into invitation values(:sid,:email)" connection = create() cursor = connection.cursor() cursor.execute(sql, {'sid': sid, 'email': email}) # send email here cursor.close() connection.commit() connection.close()
def insert_table_brand(name, company_id): connection = create() c = connection.cursor() c.execute('insert into brand (NAME,COMPANY_ID) values (?,?)', (name, company_id)) last_id = c.lastrowid c.close() connection.commit() connection.close() return last_id
def get_user_tasks(user_id): c = create() cur = c.cursor() cur.execute( "select * from task where user_id = ? and create_date >= date('now')", [user_id]) rows = cur.fetchall() cur.close() c.close() return [dict(row) for row in rows]
def insert_table_rules(user_id, template_id, brand_id, company_id, role): connection = create() c = connection.cursor() c.execute( 'insert into rules (USER_ID,TEMPLATE_ID,BRAND_ID,COMPANY_ID,ROLE) values (?,?,?,?,?)', (user_id, template_id, brand_id, company_id, role)) last_id = c.lastrowid c.close() connection.commit() connection.close() return last_id
def update_table_rules(id, **kwargs): connection = create() c = connection.cursor() sql = '' sql += 'update rules set' sql += (',').join(k + ' = ?' for k, v in kwargs.iteritems()) sql += '\twhere id = ?' params = list(v for k, v in kwargs.iteritems()).append(id) c.execute(sql, params) c.close() connection.close()
def insert_table_people(name, email, password, country, company, phone, city, verified): connection = create() c = connection.cursor() c.execute( 'insert into people (NAME,EMAIL,PASSWORD,COUNTRY,COMPANY,PHONE,CITY,VERIFIED) values (?,?,?,?,?,?,?,?)', (name, email, password, country, company, phone, city, verified)) last_id = c.lastrowid c.close() connection.commit() connection.close() return last_id
def get_template_rules(id): connection = create() c = connection.cursor() sql = 'select p.*, r.ROLE, r.ID RULE_ID ' \ ' from rules r,' \ ' people p ' \ ' where r.TEMPLATE_ID = ?' \ ' and p.ID = USER_ID' c.execute(sql, [id]) rows = c.fetchall() c.close() connection.close() return [dict(row) for row in rows]
def get_user_items(sql, user_id, role, **kwargs): c = create() cur = c.cursor() user_role = None if role == None else role.upper() params = {'user_id': user_id, 'user_role': user_role} params.update(kwargs) # print sql, params cur.execute(sql, params) rows = cur.fetchall() cur.close() c.close() return [dict(row) for row in rows]
def login(email, password): if password == "": return None sql = '''select * from people where EMAIL = ? and PASSWORD = ?''' connection = create() c = connection.cursor() print email, password res = c.execute(sql, [email, password]) rows = res.fetchall() user_id = None if len(rows) == 0 else rows[0][0] if user_id == None: return None return create_session(user_id)
def register(email, password): connection = create() cur = connection.cursor() cur.execute("select * from people where EMAIL = ?", [email]) rows = cur.fetchall() if len(rows) == 0: user_id = insert_table_people("", email, password, "", "", "", "", 0) else: if not (rows[0]['PASSWORD'] is None or rows[0]['PASSWORD'] == ""): raise ValueError('User already exists') user_id = rows[0]["ID"] update_table_people(user_id, password=password) session_id = create_session(user_id) return session_id
def insert_table_task(task_id, user_id, info="", task_status='PENDING', result=""): connection = create() c = connection.cursor() c.execute( 'insert into task (task_id, user_id, status, info, result) values (?,?,?,?,?)', (task_id, user_id, task_status, info, result)) c.close() connection.commit() connection.close() return task_id
def update_table_task(task_id, **kwargs): connection = create() c = connection.cursor() sql = '' sql += 'update task set ' sql += (',').join(k + ' = ?' for k, v in kwargs.iteritems()) sql += '\twhere task_id = ?' params = list(v for k, v in kwargs.iteritems()) params.append(task_id) # params = list(v for k, v in kwargs.iteritems()).append(id) print sql print params c.execute(sql, params) c.close() connection.commit() connection.close()
def delete_task(task_id): c = create() cur = c.cursor() cur.execute("delete from task where task_id = ?", task_id) cur.close() c.commit()