def get_group_members(gid): conn = mysql.connect() cursor = conn.cursor() sql_string = 'select UserId from GroupMembers where GroupId=%s' cursor.execute(sql_string, (gid)) data = cursor.fetchall() return userids_to_object(data)
def customer_id_to_object(userid): conn = mysql.connect() cursor = conn.cursor() cursor.execute('select U.userid, U.registrationdate, U.username, U.password, U.firstname, U.lastname, u.address, u.city, u.state, u.zipcode, u.telephone, u.email, u.rating from UserData U where U.userid=%s', (userid)) d = cursor.fetchone() user = User(userid=d[0], registrationdate=d[1], username=d[2], password=d[3], firstname=d[4], lastname=d[5], address=d[6], city=d[7], state=d[8], zipcode=d[9], telephone=d[10], email=d[11], rating=d[12]) return user
def add_user(): try: _json = request.json _id = int(_json['id']) _firstname = _json['first_name'] _lastname = _json['last_name'] _emailaddress = _json['email_address'] _password = _json['password'] # validate the received values if _firstname and _lastname and _emailaddress and _password and request.method == 'POST': #do not save password as a plain text _hashed_password = generate_password_hash(_password) # save edits sql = "INSERT INTO users(id, first_name, last_name, email_address, password) VALUES(%s,%s, %s, %s, %s)" data = ( _id, _firstname, _lastname, _emailaddress, _hashed_password, ) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sql, data) conn.commit() resp = jsonify('User added successfully!') resp.status_code = 200 return resp else: return not_found() except Exception as e: print(e) finally: cursor.close() conn.close()
def get_company_name(companyid): conn = mysql.connect() cursor = conn.cursor() cursor.execute('select companyname from company where companyid=%s', (companyid)) data = cursor.fetchone() return data[0]
def record_transaction_controller(form): conn = mysql.connect() cursor = conn.cursor() #first, check if there are enough units to satisfy the transaction query = "SELECT Nunits FROM Advertisement WHERE AdId = %s" cursor.execute(query, (form.adid.data)) data = cursor.fetchone() nunits = data[0] if form.quantity.data > nunits: return False #now, insert into the sales database cursor.callproc('insertSales', args=(form.adid.data, form.employeeid.data, form.accountno.data, form.quantity.data)) conn.commit() #and subtract the units left for the ad nunits -= form.quantity.data query2 = "UPDATE Advertisement SET Nunits = %s WHERE AdId = %s" cursor.execute(query2, (nunits, form.adid.data)) conn.commit() return True
def employee_search(form): ssn, firstname, lastname = form.ssn.data, form.firstname.data, form.lastname.data if ssn == '': ssn = '%' if firstname == '': firstname = '%' if lastname == '': lastname = '%' # query parameters: conn = mysql.connect() cursor = conn.cursor() cursor.execute( 'select E.employeeid, E.startdate, E.ssn, E.firstname, E.lastname, E.address, E.city, E.state, E.zipcode, E.telephone, E.email from Employee E where E.ssn like %s and E.firstname like %s and E.lastname like %s', (ssn, firstname, lastname)) data = cursor.fetchall() employees = [] for d in data: employee = Employee(employeeid=d[0], startdate=d[1], ssn=d[2], firstname=d[3], lastname=d[4], address=d[5], city=d[6], state=d[7], zipcode=d[8], telephone=d[9], email=d[10]) employees.append(employee) return employees
def get_ad(adid): conn = mysql.connect() cursor = conn.cursor() query = "select adid, adtype, itemname from advertisement where adid=%s" cursor.execute(query, (adid)) data = cursor.fetchone() return Advertisement(data[0], adtype=data[1], itemname=data[2])
def post_on_page(pageid, userid, content): conn = mysql.connect() cursor = conn.cursor() sql_string = "INSERT INTO Posts(AuthorId, PageId, Content) VALUES (%s, %s, %s);" cursor.execute(sql_string, (userid, pageid, content)) conn.commit()
def index(): conn = mysql.connect() cursor = conn.cursor() cursor.execute("select * from UserData") data = cursor.fetchall() print(data) return render_template('home.html', data=data)
def inbox_controller(): conn = mysql.connect() cursor = conn.cursor() query = "SELECT U.username, M.Timesent, M.Subject, M.Content, M.messageid, U.userid FROM Messages M, UserData U WHERE M.Recipient = %s AND M.Sender = U.userid ORDER BY M.TimeSent DESC;" cursor.execute(query, (session['userid'])) data = cursor.fetchall() return data
def update_user(): try: _json = request.json _id = _json['id'] _name = _json['name'] _email = _json['email'] _password = _json['pwd'] # validate the received values if _name and _email and _password and _id and request.method == 'POST': #do not save password as a plain text _hashed_password = generate_password_hash(_password) # save edits sql = "UPDATE tbl_user SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s" data = ( _name, _email, _hashed_password, _id, ) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sql, data) conn.commit() resp = jsonify('User updated successfully!') resp.status_code = 200 return resp else: return not_found() except Exception as e: print(e) finally: cursor.close() conn.close()
def get_approved_videos(): conn = mysql.connect() cursor = conn.cursor() cursor.execute( '''SELECT * FROM category c INNER JOIN videos v ON c.id_category = v.id_category WHERE v.approved = 1 ORDER BY c.id_category''') data = cursor.fetchall() print (data) return data
def userids_to_object(userids): conn = mysql.connect() cursor = conn.cursor() users = [] for u in userids: user = userid_to_object(u) users.append(user) return users
def employees_to_object(employeeids): conn = mysql.connect() cursor = conn.cursor() employees = [] for e in employeeids: employee = employeeid_to_object(e) employees.append(employee) return employees
def get_friend_requests(): userid = session['userid'] conn = mysql.connect() cursor = conn.cursor() cursor.execute('SELECT F.RequesterId from FriendRequests F WHERE F.RequestedId=%s', (userid)) data = cursor.fetchall() users = userids_to_object(data) return users
def get_pageid_user(userid): conn = mysql.connect() cursor = conn.cursor() cursor.execute("select pageid from pagedata where ownerid=%s", (userid)) data = cursor.fetchone() if not data: return None return data[0]
def get_preferences(userid): conn = mysql.connect() cursor = conn.cursor() cursor.execute('select type from preferences where userid=%s', (userid)) data = cursor.fetchall() preferences = [] for d in data: preferences.append(d[0]) return preferences
def mailing_list_controller(): conn = mysql.connect() cursor = conn.cursor() cursor.execute('select email from userdata') data = cursor.fetchall() emails = [] for d in data: emails.append(d[0]) return emails
def top_customer(): #todo: Fixed query to be correct. Check project phase 2 doc for views necessary to execute query. conn = mysql.connect() cursor = conn.cursor() query = "select c.userid, c.userrevenue from customerrevenue c where not exists( select c1.userid, c1.userrevenue from customerrevenue c1 where c1.userid <> c.userid and c1.userrevenue > c.userrevenue);" cursor.execute(query) data = cursor.fetchone() userid, revenue = data[0], data[1] user = userid_to_object(userid) return [user, data]
def request_friend(userid): requester_userid = session['userid'] if userid != requester_userid: conn = mysql.connect() cursor = conn.cursor() cursor.execute('INSERT INTO FriendRequests(RequesterId, RequestedId) VALUES (%s,%s);', (requester_userid, userid) ) conn.commit() return True else: return False # can't add yourself as a friend, already a friend, etc
def get_group(gid): conn = mysql.connect() cursor = conn.cursor() cursor.execute( 'select G.GroupId, G.GroupName, G.GroupType, G.GroupOwner from Groups G where G.GroupId=%s', (gid)) data = cursor.fetchone() if not data: return None return Group(data[0], data[1], data[2], data[3])
def get_comment_likes(commentid): conn = mysql.connect() cursor = conn.cursor() sql_string = "select UserId from CommentLikes where CommentId=%s" cursor.execute(sql_string, (commentid)) data = cursor.fetchall() likes = set() for d in data: likes.add(d[0]) return likes
def insert_pending_group_member(gid, userid, awaiting_who): conn = mysql.connect() cursor = conn.cursor() if awaiting_who == 'user': status = 'waiting on user' else: status = 'waiting on group owner' sql_string = "INSERT INTO PendingGroupRequests VALUES (%s, %s, %s);" cursor.execute(sql_string, (gid, userid, status)) conn.commit()
def get_videos_by_category(id): conn = mysql.connect() cursor = conn.cursor() if id == '0': cursor.execute('''SELECT * FROM category c INNER JOIN videos v ON c.id_category = v.id_category WHERE v.approved = 0 ORDER BY c.id_category''') else: cursor.execute('''SELECT * FROM category c INNER JOIN videos v ON c.id_category = v.id_category WHERE c.id_category = %s AND v.approved = 0 ORDER BY c.id_category''', id) data = cursor.fetchall() # print (data) return data
def get_page(postid): conn = mysql.connect() cursor = conn.cursor() cursor.execute( "select p.pageid, p.ownerid, p.groupid, p.pagetype from pagedata p, posts t where t.pageid = p.pageid and t.postid=%s", (postid)) data = cursor.fetchone() if not data: return None return Page(data[0], data[3], ownerid=data[1], groupid=data[2])
def retrieve_customer_groups_controller(userid): conn = mysql.connect() cursor = conn.cursor() cursor.execute('select groupid from groupmembers where userid=%s', (userid)) group_ids = cursor.fetchall() groups = [] for group_id in group_ids: group = get_group(group_id) groups.append(group) return groups
def get_accounts(userid): conn = mysql.connect() cursor = conn.cursor() cursor.execute('select * from account where userid = %s', (userid)) data = cursor.fetchall() accounts = [] for d in data: account = Account(accountno=d[0], creationdate=d[1], creditcard=d[2], cardtype=d[3], userid=d[4]) accounts.append(account) return accounts
def in_group(userid, groupid): conn = mysql.connect() cursor = conn.cursor() cursor.execute( 'select userid, groupid from groupmembers where userid=%s and groupid=%s', (userid, groupid)) data = cursor.fetchone() print(data, userid, groupid) if data is None: return False return True
def get_comment(commentid): conn = mysql.connect() cursor = conn.cursor() cursor.execute( 'select commentid, authorid, postid, commentdate, content from comments where commentid=%s', (commentid)) data = cursor.fetchone() if not data: return None return Comment(data[0], data[1], data[2], data[3], data[4], userid_to_object(data[1]).username)
def edit_customer_controller(form): conn = mysql.connect() cursor = conn.cursor() # check that newly entered username is still unique: cursor.execute('select * from userdata where username=%s and userid <> %s', (form.username.data, form.userid.data)) data = cursor.fetchall() if data: return False # user w/ this username exists cursor.execute('update userdata set registrationdate=%s, lastname=%s, firstname=%s, address=%s, city=%s, state=%s, zipcode=%s, telephone=%s, email=%s, username=%s, password=%s, rating=%s where userid=%s', (form.registrationdate.data, form.lastname.data, form.firstname.data, form.address.data, form.city.data, form.state.data, form.zipcode.data, form.phonenumber.data, form.email.data, form.username.data, form.password.data, form.rating.data, form.userid.data)) conn.commit() return True
def save(self): conn = mysql.connect() try: with conn: cursor = conn.cursor() params = [self.email, self.access, self.authenticated, self.name, self.user_id] save_sql = 'UPDATE lead_track_users SET email = %s , access = %s , authenticated = %s , name = %s WHERE id = %s' print save_sql cursor.execute(save_sql,params) conn.close() return True except Exception,e: print e return False
def get(self,email): try: conn = mysql.connect() cursor = conn.cursor() cursor.execute('SELECT id,email,access,authenticated,name FROM lead_track_users WHERE email = %s',[email,]) user = cursor.fetchone() conn.close() self.email = user[1] self.user_id = user[0] self.authenticated = user[3] self.access = user[2] self.name = user[4] return True except: return False