Python session.query函数代码示例

def load_user_id():
    
    g.user_id = session.get('user_id')
    
    if g.user_id != None:
        g.user = db_session.query(User).filter_by(id=g.user_id)
        g.photos = db_session.query(Photo).filter_by(user_id=g.user_id).all()

def view_movie(id):
    #id = movie.id
    user_id = g.user_id
    if g.user_id:
        movie = Movie.search_movie(id)
        ratings = Rating.search_rating(movie.id, user_id.id)
        beratement = None
        if ratings == None:
            ratings = movie.ratings
            rating_nums = []
            user_rating =None
            for r in ratings:
                rating_nums.append(r.rating)
            avg_rating = float(sum(rating_nums))/len(rating_nums)
            prediction = User.predict_rating(movie.id, user_id.id)
            the_eye = db_session.query(User).filter_by(email="[email protected]").one()
            eye_rating = db_session.query(Rating).filter_by(user_id = the_eye.id, movie_id = id).first()
            if not eye_rating:
                eye_rating = User.predict_rating(movie.id, the_eye.id)
                print eye_rating
            else:
                eye_rating = eye_rating.rating
            if prediction:
                difference = abs(eye_rating - prediction)
                messages = [ "I suppose you don't have such bad taste after all.",
                "I regret every decision that I've ever made that has brought me to listen to your opinion.",
                "Words fail me, as your taste in movies has clearly failed you.",
                "That movie is great. For a clown to watch. Idiot.",]
                beratement = messages[int(difference)]
            return render_template("view_movie.html", movie=movie, average=avg_rating, user_rating=user_rating, prediction=prediction, ratings = ratings, beratement=beratement)
        else:
            return render_template("view_movie.html", movie = movie, ratings=ratings)
    return render_template("")

def movie(id):
    if not g.user_id:
        print 'no global user'
        movie = db_session.query(Movie).get(id)
        ratings = movie.ratings
        total = []
        for rating in ratings:
            total.append(rating.rating)
        avg_rating = float(sum(total))/len(total)
        return render_template('movie.html', movie=movie,
                        avg=avg_rating, user_rating=None, prediction=None)

    else:

        movie = db_session.query(Movie).get(id)
        ratings = movie.ratings 
        total = []
        user_rating = None
        for rating in ratings:     
            if rating.user_id == g.user_id:
                user_rating = rating
            total.append(rating.rating)
        avg_rating = float(sum(total))/len(total)

        # predict if no user rating
        prediction = None
        if not user_rating:
            user = db_session.query(User).get(g.user_id)
            prediction = user.predict_rating(movie)
            print "Prediction: ", prediction 

        return render_template('movie.html', movie=movie,
                                avg=avg_rating,
                                user_rating=user_rating,
                                prediction=prediction)

def sign_up_form():
		## input new user input into database
		email = request.form.get("email")
		password = request.form.get("password")
		username = request.form.get("username")
		first_name = request.form.get("first_name")
		last_name = request.form.get("last_name")
		gender = int(request.form.get("gender"))
		age = int(request.form.get("age"))
		zipcode = request.form.get("zipcode")
		
		hashed_password = hash_password(password, email)

		# create an instance of User with email, password, username, etc. as attributes
		user = User(email=email, password=hashed_password, username=username, first_name=first_name, 
			last_name=last_name, gender=gender, age=age, zipcode=zipcode)
		
		# check for email in db, if not there, add it to db
		if dbsession.query(User).filter_by(email = email).first():
			flash("This email address is already in use. Please try again.")
			return redirect("/sign_up")
		else:
			dbsession.add(user)
			dbsession.commit()
			created_user = dbsession.query(User).filter_by(email = email).first()
			session["login"] = created_user.id
			session["user"] = created_user
			return redirect("/pick_genres")

def load_rss():
	# query the db: how long is it? Use this number later to empty db of old stories
    exstories = db_session.query(Stories).all()
    last_id = exstories[-1].id
    sources = {"NPR News": 'http://www.npr.org/rss/rss.php?id=1001', "BBC": 'http://feeds.bbci.co.uk/news/rss.xml'}
    for source in sources:
        print source
        # use feedparser to grab & parse the rss feed
        parsed = feedparser.parse(sources[source])
        print "parsed"
        # go through each entry in the RSS feed to pull out elements for Stories
        for i in range(len(parsed.entries)):
            title = parsed.entries[i].title
            url = parsed.entries[i].link
            source = source
            # pull abstract, parse out extra crap that is sometimes included
            abstract = (parsed.entries[i].description.split('<'))[0]
            print abstract

            # connect with db
            story = db_session.Stories(title=title, url=url, abstract=abstract, source=source)
            print "connected with db model??"
            # add story to db
            db_session.add(story)
            print "added story to db"
            # commit
        db_session.commit()
        print "committed"
    # delete from db old stories
    for l in range(1,last_id+1):
        db_session.query(Stories).filter_by(id=l).delete()
    db_session.commit()    

def post_genres():
	# get logged in user and user genre picks from pick_genres.html
	user_obj = get_current_user()
	genre_ids = [int(gi) for gi in request.form.getlist('genres')]

	# modifying the collection that we're itering over is no bueno
	genres_in_db = dbsession.query(Genre).all()
	for g in genres_in_db:
		if g in user_obj.genres:
			user_obj.genres.remove(g)
	try:
		print "Committing..\n\n"
		dbsession.commit()
	except:
		print "\n\n\nRolling back uh oh\n\n\n"
		dbsession.rollback()

	for gi in genre_ids:
		genre_obj = dbsession.query(Genre).filter_by(id=gi).first()
		user_obj.genres.append(genre_obj)
	try:
		dbsession.commit()
		cache.set(session["user"].id, None, timeout=0)
	except:
		dbsession.rollback()
	return redirect("/wall")

def input_match_terms():
    """
    This allows the user to enter and edit the match percent and max
    salary percent match of their 401k match.
    """
    if g.logged_in is True:
        # If user selects that they do not have a 401k match, skip
        # all 401k match-related questions.
        match_401k = m_session.query(model.User).filter_by(
            id=g.user.id).first().company_match
        if match_401k == "Yes":
            if g.inputs is True:
                match_percent = m_session.query(model.User).filter_by(
                    id=g.user.id).first().match_percent
                match_salary = m_session.query(model.User).filter_by(
                    id=g.user.id).first().match_salary
            else:
                match_percent = match_salary = 0
            return render_template(
                "input_match_terms.html", match_percent=match_percent,
                match_salary=match_salary)
        else:
            match_percent = match_salary = 0
            update_user = m_session.query(model.User).filter_by(
                id=g.user.id).update({model.User.match_percent: match_percent,
                                      model.User.match_salary: match_salary})
            m_session.commit()
            return redirect("/input/risk_tolerance")
    else:
        return redirect("/login")

def view_categories():
	# get all categories of type "goods"
	goods = db_session.query(Category).filter_by(cat_type=1).all()
	#get all categories of type "services"
	services = db_session.query(Category).filter_by(cat_type=2).all()
	return render_template("view_categories.html",
							goods=goods, services=services)

def one_party():
    parties = session.query(Party).limit(5).all()
    guests = session.query(Guest).limit(5).all()
    q = {'parties': [party.as_dict() for party in parties]}, \
        {'guests': [guest.as_dict() for guest in guests]}
    # print "------------------------\n", q, "\n------------------------"
    return q

def load_globalcounts(list_of_wordcounts):
    """
    Adds wordcounts for all unique words. There should only be one row per unique word.
    """
    # i = 0

    for localcount_dict in list_of_wordcounts:
        # if i < 5:
        for word, count in localcount_dict.iteritems():
            item = session.query(GlobalCount).filter(GlobalCount.term == word).first()
            if item:
                print "%r is already in globalcounts. Updating count..." % word
                # update the global count for this word, because we have added new songs with more occurrences of this word
                q = session.query(LocalCount.term, func.sum(LocalCount.count))
                q = q.group_by(LocalCount.term)
                q = q.filter(LocalCount.term == word)
                results = q.all()

                # print "Current count for %r is %d" % (item.term, item.count)
                item.count = results[0][1]
                print "Updating %r's count to %d" % (item.term, item.count)
                session.commit()

            else:
                print "%r not in globalcounts table, creating new row" % word
                qq = session.query(LocalCount.term, func.sum(LocalCount.count))
                qq = qq.group_by(LocalCount.term)
                qq = qq.filter(LocalCount.term == word)
                resultsresults = qq.all()

                countcount = resultsresults[0][1]
                new_row = GlobalCount(term = word, count = countcount)
                session.add(new_row)
                # you must commit before you query the same word/item again!
                session.commit()

def add_page_pair_to_database(from_page, to_page, limit):

    with db_lock:
        cou = session.query(Page.id).filter(Page.url == from_page).scalar()
        cou1 = session.query(Page.id).filter(Page.url == to_page).scalar()

        if cou is None:
            new_page_from = Page(url=from_page, text="", rank=0)
            session.add(new_page_from)
            session.flush()
            id0 = new_page_from.id
        else:
            id0 = cou

        if cou1 is None:
            allowed = limit < 1 or limit > session.query(Page).count()
            if not allowed:
                return
            new_page_to = Page(url=to_page, text="", rank=0)
            session.add(new_page_to)
            session.flush()
            id1 = new_page_to.id
        else:
            id1 = cou1

        new_relation = Relation(page_id = id0, destination_id = id1)
        # print(new_relation.page_id.id)
        session.add(new_relation)
        session.commit()

def view_movie(id):
    #get is a function for a Query object
    movie = db_session.query(Movie).get(id)
    rating_objs = movie.ratings
    rating_values = []
    user_rating = None #user_rating will become user_rating object

    for r in rating_objs:
        if r.user_id == g.user_id:
            user_rating = r     
            # getting the whole rating object for the user's rating (will find out why later)
        rating_values.append(r.rating)

    # you want to make as few calls to the database as possible
    average_rating = float(sum(rating_values)/len(rating_values))

    # Prediction code: only predict if the user hasn't rated it.
    user = db_session.query(User).get(g.user_id)
    prediction = None
    if not user_rating:
        prediction = user.predict_rating(movie)
    # End prediction

    return render_template("movie.html", movie=movie, 
            average=average_rating, user_rating=user_rating,
            prediction=prediction)

    return render_template("movie.html", movie=movie, average=average_rating, user_rating=user_rating, prediction=prediction)

def join_carpool(trip, user):
    # Creates new TripPassenger object; returns appropriate message.

    carpool = db_session.query(model.Trip).filter_by(id=trip).first()
    driver = get_user_by_id(carpool.trip_driver)
    if user.id == carpool.trip_driver:
        return "You cannot join a carpool where you are the driver."

    if carpool.seats_available > 0:
        carpool_contact = driver.first_name + ", email: " + driver.email
        new_passenger = model.TripPassenger(trip_id=trip, passenger_id=user.id)
        seats_available = carpool.seats_available - 1
        db_session.query(model.Trip).filter_by(id=carpool.id).update({"seats_available": seats_available})
        db_session.add(new_passenger)
        db_session.commit()

        # Sends email to driver, confirming new passenger.
        message = (
            user.first_name
            + " "
            + user.last_name
            + " (email: "
            + user.email
            + ") has joined your carpool to "
            + carpool.event_name
            + " on "
            + carpool.departure_date
            + "."
        )
        send_email("inStep carpool addition", driver.email, "[email protected]", message)
        response = "You have been added to the carpool. The driver is " + carpool_contact
        return response
    else:
        return "That carpool is full."

def show_random_user_ratings():
    """return a list of movies and ratings given by this user"""
    our_range = dbsesh.query(model.User.id).all()
    # print our_range

    clean_list =[]

    for i in range(len(our_range)):
        clean_list.append(our_range[i][0])

    # print clean_list

    users_to_show = []

    for i in range(10):
        random_user = random.choice(clean_list)
        users_to_show.append(random_user)

    user_objects = []
    for uid in users_to_show:
        user_object = dbsesh.query(model.User).get(uid)
        user_objects.append(user_object)

    print len(user_objects)
    return (user_objects)

def movie_profile():
	user = get_current_user()
	id = request.args.get("id")
	if not id:
		return redirect("/wall")

	movie_info = dbsession.query(Media).options(joinedload('genres')).get(id)
	if not movie_info:
		# movie wasn't found
		flash("This movie does not exist. Please try again.")
		return redirect("/wall")

	# FIXME: fix this workaround for utf-8 decoding weirdness in flask/jinja
	if movie_info.director:
		movie_info.director = movie_info.director.decode('utf-8', 'ignore')
	if movie_info.actors:
		movie_info.actors = movie_info.actors.decode('utf-8', 'ignore')
	if movie_info.plot:
		movie_info.plot = movie_info.plot.decode('utf-8', 'ignore')

	ratings = dbsession.query(Rating).filter(Rating.rating !=0).options(joinedload('user')).filter_by(movie_id = id).all()
	avg_rating = average_rating(ratings)

	# get rating of currently logged in user
	current_user_rating = None
	for rating in ratings:
		if rating.user == user:
			current_user_rating = rating
			break

	return render_template("movie_prof.html", movie=movie_info, ratings=ratings, avg_rating = avg_rating, user=user, current_user_rating = current_user_rating)