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