def update_scheme(name, description, eligibility, category): #return "Update Function" c,conn = connection() ct = c.execute("select * from SCHEMES where name = (%s)",(esc(name),)) results = c.fetchall() for row in results: #n = row[1] d = row[2] e = row[3] ca = row[4] if description != "" and description is not None: d = description if eligibility != "" and eligibility is not None: e = eligibility if category != "" and category is not None: ca = category rt = c.execute (""" UPDATE SCHEMES SET description=%s, eligibility=%s, category=%s WHERE name=%s """, (d,e,ca,name)) conn.commit() c.close()
def submit_link(): if request.method == 'POST': # database stuff c, conn = connection() title = request.form['title'] newlink = request.form['newlink'] c.execute("INSERT INTO links (title,link) VALUES (%s,%s)", (esc(title),esc(newlink)) ) conn.commit() flash("Link Submitted!") c.close() conn.close() gc.collect() return redirect(url_for("main_page")) else: return render_template("submit.html")
def checkValid(): if request.method == 'POST': c, conn = connection() c.execute("select * from appn") results = c.fetchall() for rows in results: valid = request.form[str(rows[0])] print(valid) if valid == "Valid": print("True") c2, conn2 = connection() ct = c2.execute("select * from USER where id=" + str(rows[1])) userResults = c2.fetchall() for row in userResults: pres_scheme = row[10] pres_scheme = pres_scheme + "," + rows[11] if (ct > 0): c2.execute( "update user set schemes_applied=%s WHERE id=%s", (pres_scheme, rows[1])) conn2.commit() c2.close() else: c3, conn3 = connection() c3.execute( "insert into USER (id,id_type,name,gender,year_of_birth,father_or_spouse_name,address,pincode,income,schemes_applied) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);", (esc(rows[1]), esc(str(rows[2])), esc(rows[3]), esc(str(rows[4])), esc(str(rows[5])), esc( rows[6]), esc(rows[7]), esc(str( rows[8])), esc(str(rows[9])), esc(rows[11]))) conn3.commit() c3.close() c1, conn1 = connection() c1.execute("DELETE FROM APPN WHERE appn_id=" + str(rows[0])) conn1.commit() c1.close() else: print("False") c1, conn1 = connection() c1.execute("DELETE FROM APPN WHERE appn_id=" + str(rows[0])) conn1.commit() c1.close() return redirect(url_for('validation'))
def login(): error = '' try: cur, conn = connection() if request.method == 'POST': if request.form['submit'] == 'login': attempted_username = str(request.form['eid']) attempted_password = str(request.form['pass']) query = "SELECT * FROM users WHERE email = '" + attempted_username + "';" cur.execute(query) password = cur.fetchone()[4] if sha256_crypt.verify(attempted_password, password): session['logged_in'] = True session['username'] = attempted_username return redirect(url_for('profile')) else: error = 'Invalid credentials.' elif request.form['submit'] == 'signup': fname = str(request.form['fname']) sname = str(request.form['sname']) email = str(request.form['email']) phone = str(request.form['phone']) password = str(request.form['pass']) repass = str(request.form['repass']) if fname and sname and email and phone and password and repass and len(phone) == 10 and password == repass: password = sha256_crypt.encrypt(password) cur.execute("INSERT INTO users (fname, surname, email, phone, password) VALUES (%s, %s, %s, %s, %s)", (esc(fname), esc(sname), esc(email), esc(phone), esc(password))) conn.commit() session['logged_in'] = True session['username'] = attempted_username return redirect(url_for('profile')) else: error = 'Invalid.' conn.close() cur.close() gc.collect() return render_template("login.html", error=error) except Exception as e: return str(e)
def simple_where(data, field): if data: where.append(field + " like '%%" + esc(data) + "%%'")
def build_query(get): where = [] # holds where clauses, will be joined by and orderby = [] # holds order by clauses needed for hit relevance only. placeholders = {} # sql interpolated values def simple_where(data, field): if data: where.append(field + " like '%%" + esc(data) + "%%'") # title search if get.get('title'): title = esc(get['title']) orderby.append("title='%s' DESC" % title) orderby.append("title like ' %%{}%% ' DESC".format(title)) orderby.append("title like '%%{}%%' DESC".format(title)) # now search by word in title, alt, and cast for i, word in enumerate(title.split()): where.append("""(title like '%%{0}%%' or alttitle like '%%{0}%%' or cast like '%%{0}%%')""".format(esc(word))) # cast search simple_where(get.get('cast'), 'cast') # keywords in entry for word in get.get('keywords', '').split(): simple_where(word, 'entry') # genre query. some manual queries "?genre=action&genre2=adventure" to account for # i'm fetching all article ids and issuing an id in [...] query cause that's how php did it. genres = get.getlist('genre') + [get.get('genre2')] genres = filter(None, [Genre.name_to_id(genre) for genre in genres]) if genres: article_ids = [ str(row[0]) for row in Genre.objects.filter( id__in=genres).values_list('articles__id') ] where.append('id in (' + ', '.join(article_ids) + ')') # labels labels = filter(None, get.getlist('label')) if labels: article_ids = [ str(row[0]) for row in Tags.objects.filter( id__in=labels).values_list('articles__id') ] where.append('id in (' + ','.join(article_ids) + ')') # year tags year = get.get('year_from', '') if year and year.isdigit(): where.append('year >=%(year_from)s') placeholders['year_from'] = year year = get.get('year_to', '') if year and year.isdigit(): where.append('year <=%(year_to)s') placeholders['year_to'] = year ratings = dict(overall=['overall'], artistic=['stars'], moral=['moral', 'spiritual'], age=['age'], mpaa=['mpaa'], usccb=['usccb']) for field, tagnames in ratings.items(): if get.get(field): where.append( Ratings.sql(tagnames, get.get(field), get.get('%s_modifier' % field, '='))) if get.get('fletter'): letter = get['fletter'] where.append("""(%(fletter)s= LEFT(TRIM(leading "The " from TRIM(leading "A " from TRIM(leading "An " from TRIM(leading '"' from title) ) ) ),1))""") placeholders['fletter'] = letter # Manually selected ordering orderings = { 'title': """TRIM(leading "The " from TRIM(leading "A " from TRIM(leading "An " from TRIM(leading '"' from title)))) ASC""", 'date': ' dt_modified DESC', 'overall': Ratings.locate(['overall'], Ratings.overall, join=","), 'artistic': Ratings.locate(['artistic'], Ratings.artistic, join=","), 'year': "year DESC", } if get.get('order', '') in orderings: orderby = [orderings[get.get('order')]] if orderby: orderby = ' ORDER BY ' + ', '.join(orderby) else: orderby = '' where.append('exclude_from_search=0') where = ' AND '.join(filter(None, where)) sql = 'select * from blog where ' + where + orderby return sql, placeholders
def CreateLinks (self, cursor, fromObj, toObjs): for (objname, objid) in toObjs: query = "INSERT INTO Attributes (Name, ParentId, ChildId) VALUES ('" + esc(str(objname)) + "', " + str(fromObj) + ", " + str(objid) + ")" cursor.execute (query)
def simple_where(data, field): if data: where.append(field + " like '%%" + esc(data) + "%%'")
def build_query(get): where = [] # holds where clauses, will be joined by and orderby = [] # holds order by clauses needed for hit relevance only. placeholders = {} # sql interpolated values def simple_where(data, field): if data: where.append(field + " like '%%" + esc(data) + "%%'") # title search if get.get('title'): title = esc(get['title']) orderby.append("title='%s' DESC" % title) orderby.append("title like ' %%{}%% ' DESC".format(title)) orderby.append("title like '%%{}%%' DESC".format(title)) # now search by word in title, alt, and cast for i, word in enumerate(title.split()): where.append("""(title like '%%{0}%%' or alttitle like '%%{0}%%' or cast like '%%{0}%%')""".format(esc(word))) # cast search simple_where(get.get('cast'), 'cast') # keywords in entry for word in get.get('keywords', '').split(): simple_where(word, 'entry') # genre query. some manual queries "?genre=action&genre2=adventure" to account for # i'm fetching all article ids and issuing an id in [...] query cause that's how php did it. genres = get.getlist('genre') + [get.get('genre2')] genres = filter(None, [Genre.name_to_id(genre) for genre in genres]) if genres: article_ids = [str(row[0]) for row in Genre.objects.filter(id__in=genres).values_list('articles__id')] where.append('id in (' + ', '.join(article_ids) + ')') # labels labels = filter(None, get.getlist('label')) if labels: article_ids = [str(row[0]) for row in Tags.objects.filter(id__in=labels).values_list('articles__id')] where.append('id in (' + ','.join(article_ids) + ')') # year tags year = get.get('year_from', '') if year and year.isdigit(): where.append('year >=%(year_from)s') placeholders['year_from'] = year year = get.get('year_to', '') if year and year.isdigit(): where.append('year <=%(year_to)s') placeholders['year_to'] = year ratings = dict(overall=['overall'], artistic=['stars'], moral=['moral', 'spiritual'], age=['age'], mpaa=['mpaa'], usccb=['usccb'] ) for field, tagnames in ratings.items(): if get.get(field): where.append(Ratings.sql(tagnames, get.get(field), get.get('%s_modifier' % field, '='))) if get.get('fletter'): letter = get['fletter'] where.append("""(%(fletter)s= LEFT(TRIM(leading "The " from TRIM(leading "A " from TRIM(leading "An " from TRIM(leading '"' from title) ) ) ),1))""") placeholders['fletter'] = letter # Manually selected ordering orderings = { 'title': """TRIM(leading "The " from TRIM(leading "A " from TRIM(leading "An " from TRIM(leading '"' from title)))) ASC""", 'date': ' dt_modified DESC', 'overall': Ratings.locate(['overall'], Ratings.overall, join=","), 'artistic': Ratings.locate(['artistic'], Ratings.artistic, join=","), 'year': "year DESC", } if get.get('order','') in orderings: orderby = [orderings[get.get('order')]] if orderby: orderby = ' ORDER BY ' + ', '.join(orderby) else: orderby = '' where.append('exclude_from_search=0') where = ' AND '.join(filter(None, where)) sql = 'select * from blog where ' + where + orderby return sql, placeholders
def add_scheme(name, description, eligibility, category): c,conn = connection() #ins = c.execute("insert into SCHEMES values (%s,%s,%s,%s)",(esc(name),esc(description),esc(eligibility),esc(category))) ins = c.execute("insert into SCHEMES (name, description, eligibility, category) values (%s,%s,%s,%s);",(esc(name),esc(description),esc(eligibility),esc(category))) conn.commit() c.close()
def insert_appn(uid,id_type,name,gender,yob,care_of,address,pincode,income,phone,schemes_applied): c,conn=connection() scheme_id="" c.execute("select id from schemes where name='"+schemes_applied+"'") results=c.fetchall() for rows in results: scheme_id=rows[0] ins = c.execute("insert into APPN (id,id_type,name,gender,year_of_birth,father_or_spouse_name,address,pincode,income,phone,schemes_applied) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",(esc(uid),esc(str(id_type)),esc(name),esc(str(gender)),esc(str(yob)),esc(care_of),esc(address),esc(pincode),esc(income),esc(phone),esc(str(scheme_id)))) conn.commit() c.close()