You might want to try turning on debug logging for your SQL statements by passing echo="debug" when creating your engine:
https://docs.sqlalchemy.org/en/13/core/engines.html#sqlalchemy.create_engine Amongst other things, this will show the values of parameters that you are passing to the database. I'm not sure what your problem is, but I notice that you are using "SELECT * FROM movies" to load your rows, and then you are assuming that the columns you get back are in a certain order. (You assume that the first column is title, the second is year, and the fifth is imdbrating). It's possible that the columns are coming back in a different order, and so the value you think is the title is actually something else. It would be better to explicitly list the columns you want, something like: SELECT title, year, imdbrating FROM movies WHERE "imdbID" = :imdb_id Alternatively, the rows that you get back from fetchall() actually know which columns they came from, so you could do this: # don't call list() here: check_for_api = check_for_api_unsplitted[0] # use dictionary-style access to get the individual values title = check_for_api["title"] Simon On Tue, Jul 2, 2019 at 3:33 PM Cravan <cravan...@gmail.com> wrote: > > Hi all, > > One of the other tasks for my movie review assignment is that > I have to create api access to my website, which should return a json > response including a review count. However, I have confirmed that I have > entered a review for a specific movie and it prompts out in my html, but > apparently when I try to print out the json I get 0 reviews for my review > count. Can someone help me troubleshoot? Sorry if it sounds unclear. > > ```` > > @app.route("/api/<imdb_id>") > > def api(imdb_id): > > check_for_api_statement = sqlalchemy.text('SELECT * FROM movies WHERE > "imdbID" = :imdb_id') > > check_for_api_unsplitted = engine.execute(check_for_api_statement, > imdb_id=imdb_id).fetchall() > > res = requests.get("http://www.omdbapi.com/";, params={"apikey": > "c2c76d64", "i": imdb_id, "plot": "full"}) > > omdb_data = res.json() > > check_for_api = list(check_for_api_unsplitted[0]) > > title_unsplitted = check_for_api[0] > > title = check_for_api[0] > > year = check_for_api[1] > > imdb_id = imdb_id > > imdbrating = check_for_api[4] > > director = omdb_data['Director'] > > actors = omdb_data['Actors'] > > check_for_reviews_statement = sqlalchemy.text('SELECT * FROM reviews > WHERE movie = :movie_title') > > check_for_reviews_unsplitted = > engine.execute(check_for_reviews_statement, movie_title=title).fetchall() > > review_count = len(check_for_reviews_unsplitted) > > print(check_for_reviews_unsplitted) #this returns a blank list for > some strange reason > > if len(check_for_reviews_unsplitted) != 0: > > check_for_reviews = check_for_reviews_unsplitted.split(',') > > score = float(check_for_reviews[4]) > > average_score = score/review_count > > else: > > average_score = "N.A" > > a = > > {"title":title,"year":year,"imdb_id":imdb_id,"director":director,"actors":actors,"imdb_rating":imdbrating,"review_count":review_count,"average_score":average_score} > > apijson = json.dumps(a, cls=CustomJsonEncoder) > > print (apijson) > > if len(check_for_api) != 0: > > return render_template("api.html", apijson=apijson) > > else: > > return render_template("error2.html", message="No such movie.") > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/7DB000C8-1EBC-48AF-A24F-202196C743CF%40gmail.com. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfVMZTVX6hKDvxgRFG0Wrc%3DhSfwU05XQGm%2B7gsRTtR_Cw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
