def login():
if session.get('logged_in', False):
return redirect(url_for('index'))
form = LoginForm()
if request.method == 'POST':
error = None
if form.validate_on_submit():
username = form.username.data
password = form.password.data
# h = hashlib.sha1()
# password = form.password.data
# h.update(password)
# password_hash = h.hexdigest()
if username and password:
sql_query = 'SELECT username FROM user_profile WHERE username="******" AND password="******"' % (
username, password)
cursor = con.cursor()
cursor.execute(sql_query)
data = cursor.fetchone()
if data is not None:
session['logged_in'] = True
session['username'] = username
return redirect(
request.args.get('next') or url_for('index'))
else:
error = "No user could be found"
error = "Invalid username/password combination"
flash(error)
return render_template('login.html', title='Sign In', form=form)
def getTable(category):
table = []
executeString = "SELECT * FROM {0}".format(category)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
while True:
row = cur.fetchone()
if row == None:
break
# for index in row.keys():
# entry[index] = copy.deepcopy(row[index])
#entry = json.dumps(row)
table.append(dict(row))
#print entry.keys()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def getTable(category):
table = []
executeString = "SELECT * FROM {0}".format(category)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
while True:
row = cur.fetchone()
if row == None:
break
# for index in row.keys():
# entry[index] = copy.deepcopy(row[index])
#entry = json.dumps(row)
table.append(dict(row))
#print entry.keys()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def executeQ(q, isFetchable=False):
with con:
with con.cursor() as cur:
cur.execute(str(q))
if isFetchable:
return cur.fetchall()
def dropTableColumn(category, columns):
executeString = "alter table {0} drop column %s".format(category)
## e.g., "alter table Tasks drop column %s"
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
for c in columns:
cur.execute(executeString, (AsIs(c),))
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def dropTableColumn(category, columns):
executeString = "alter table {0} drop column %s".format(category)
## e.g., "alter table Tasks drop column %s"
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
for c in columns:
cur.execute(executeString, (AsIs(c), ))
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def getTableEntryById(category, targetId):
executeString = "SELECT * FROM {0} WHERE id = {1}".format(category, targetId)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
row = cur.fetchone()
if row is not None:
return dict(row)
else:
return None
#for index in row.keys():
#entry[index] = copy.deepcopy(row[index])
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def addTableColumn(category, columns, typeString):
executeString = "alter table {0} add column %s {1}".format(category, typeString)
## e.g., "alter table Tasks add column %s char(40)"
## columns = ['add1', 'add2']
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
for c in columns:
cur.execute(executeString, (AsIs(c),))
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def getTableEntryById(category, targetId):
executeString = "SELECT * FROM {0} WHERE id = {1}".format(
category, targetId)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
row = cur.fetchone()
if row is not None:
return dict(row)
else:
return None
#for index in row.keys():
#entry[index] = copy.deepcopy(row[index])
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def create_task():
if not request.json or not 'title' in request.json or not 'id' in request.json:
abort(400)
task = {
'id': request.json['id'],
'title': request.json['title'],
'description': request.json.get('description', ""),
'done': request.json.get('done', "false"),
}
category = 'Tasks'
executeString = "SELECT * FROM {0}".format(category)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
# table = cur.fetchall()
# if len(table) == 0:
# abort(400)
#method 1:
keyString = ",".join(['"%s"' % k for k in task
]) # "description","done","id","title"
valueString = ",".join([
"%s",
] * len(task.keys())) # %s,%s,%s,%s
query = "insert into Tasks (%s) values (%s)" % (keyString, valueString)
cur.execute(query, tuple(task.values()))
#method 2:
#cur.execute("insert into Tasks VALUES(16, 'test', 'testt', 'test2')")
#method 3:
#s = "insert into Tasks VALUES({0},'{1}','{2}','{3}')".format(task['id'], task['title'], task['description'], task['done'])
#cur.execute(s)
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def addTableColumn(category, columns, typeString):
executeString = "alter table {0} add column %s {1}".format(
category, typeString)
## e.g., "alter table Tasks add column %s char(40)"
## columns = ['add1', 'add2']
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
for c in columns:
cur.execute(executeString, (AsIs(c), ))
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def delete_task(task_id):
category = 'Tasks'
executeSearchString = "SELECT FROM {0} WHERE id = {1}".format(category, task_id)
executeDeleteString = "DELETE FROM {0} WHERE id = {1} ".format(category, task_id)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeSearchString)
row = cur.fetchone()
if row is None:
abort(400)
cur.execute(executeDeleteString)
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def addTableColumnWithArrayType(category, columns):
typeString = "float[]"
executeString = "alter table {0} add column %s {1}".format(category, typeString)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
for c in columns:
cur.execute(executeString, (AsIs(c),))
#query = "UPDATE Tasks SET add6 = ARRAY{0} WHERE id = 55".format(table['feature'])
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def addTableColumnWithArrayType(category, columns):
typeString = "float[]"
executeString = "alter table {0} add column %s {1}".format(
category, typeString)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
for c in columns:
cur.execute(executeString, (AsIs(c), ))
#query = "UPDATE Tasks SET add6 = ARRAY{0} WHERE id = 55".format(table['feature'])
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def create_task():
if not request.json or not 'title' in request.json or not 'id' in request.json:
abort(400)
task = {
'id': request.json['id'],
'title': request.json['title'],
'description': request.json.get('description', ""),
'done': request.json.get('done', "false"),
}
category = 'Tasks'
executeString = "SELECT * FROM {0}".format(category)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeString)
# table = cur.fetchall()
# if len(table) == 0:
# abort(400)
#method 1:
keyString = ",".join(['"%s"' % k for k in task]) # "description","done","id","title"
valueString = ",".join(["%s",] * len(task.keys())) # %s,%s,%s,%s
query = "insert into Tasks (%s) values (%s)" % (keyString, valueString)
cur.execute(query, tuple(task.values()))
#method 2:
#cur.execute("insert into Tasks VALUES(16, 'test', 'testt', 'test2')")
#method 3:
#s = "insert into Tasks VALUES({0},'{1}','{2}','{3}')".format(task['id'], task['title'], task['description'], task['done'])
#cur.execute(s)
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def update_task():
if not request.json or not 'id' in request.json:
abort(400)
category = 'Tasks'
updateId = request.json['id']
task = getTableEntryById(category, updateId)
if not task:
abort(404)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
executePushStrings = []
for keyInTarget in task.keys():
if keyInTarget == 'id':
continue
requestValue = request.json.get(keyInTarget, "")
if requestValue is not "":
if isinstance(requestValue, basestring):
executePushString = "UPDATE {0} SET {1} = '{2}' WHERE id = {3}".format(
category, keyInTarget, requestValue, updateId)
print 'requestValue is string'
else:
executePushString = "UPDATE {0} SET {1} = {2} WHERE id = {3}".format(
category, keyInTarget, requestValue, updateId)
print 'requestValue is not string'
executePushStrings.append(executePushString)
#cur.execute("UPDATE Tasks SET done = true WHERE id = 15")
for executePushString in executePushStrings:
cur.execute(executePushString)
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def delete_task(task_id):
category = 'Tasks'
executeSearchString = "SELECT FROM {0} WHERE id = {1}".format(
category, task_id)
executeDeleteString = "DELETE FROM {0} WHERE id = {1} ".format(
category, task_id)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(executeSearchString)
row = cur.fetchone()
if row is None:
abort(400)
cur.execute(executeDeleteString)
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def update_task():
if not request.json or not 'id' in request.json:
abort(400)
category = 'Tasks'
updateId = request.json['id']
task = getTableEntryById(category, updateId)
if not task:
abort(404)
try:
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
executePushStrings = []
for keyInTarget in task.keys():
if keyInTarget == 'id':
continue
requestValue = request.json.get(keyInTarget, "")
if requestValue is not "":
if isinstance(requestValue, basestring):
executePushString = "UPDATE {0} SET {1} = '{2}' WHERE id = {3}".format(category, keyInTarget, requestValue, updateId)
print 'requestValue is string'
else:
executePushString = "UPDATE {0} SET {1} = {2} WHERE id = {3}".format(category, keyInTarget, requestValue, updateId)
print 'requestValue is not string'
executePushStrings.append(executePushString)
#cur.execute("UPDATE Tasks SET done = true WHERE id = 15")
for executePushString in executePushStrings:
cur.execute(executePushString)
con.commit()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
from app import app, con, api
base_url = '/api'
parser = reqparse.RequestParser()
cursor = con.cursor()
class UserAPI(Resource):
def get(self, id):
return {'hello': 'world'}
def put(self, id):
pass
def post(self, id):
pass
class LoginAPI(Resource):
def post(self):
args = parser.parse_args()
username =
sql_query = 'SELECT * FROM User WHERE '
class SignupAPI(Resource):
def post(self):
pass
api.add_resource(UserAPI, base_url + '/user/<int:id>', endpoint='user')
api.add_resource(LoginAPI, base_url + '/user/login', endpoint='login')
def dropAllTables():
with con:
with con.cursor() as cur:
cur.execute(sqlQueries.dropTables)
cur.execute(sqlQueries.dropSequences)
print("ALL TABLES WERE DELETED")