Re: [sqlalchemy] Reviews query throwing up blank list

[Cravan]

I'm sorry if I have caused any inconvenience, but this project is an assignment 
for my school's computing course, and I checked with my teacher but she 
couldn't find the root of the problem. I have edited my code based on your 
suggestions, but am still facing the same problem.
Here is the code that is supposed to produce an individualised webpage for each 
movie, with any reviews to be on it(if there are)


@app.route("/movies/")
def movie(movie):
lol = movie.split(',')
movie_title = lol[0]
res = requests.get("http://www.omdbapi.com/";, params={"apikey": "c2c76d64", 
"t": movie_title, "plot": "full"})
omdb_data = res.json()
ratings_list = omdb_data['Ratings']
check_for_review_statement = sqlalchemy.text('SELECT * FROM reviews WHERE 
movie = :movie')
check_for_review = engine.execute(check_for_review_statement, 
movie=movie_title).fetchall()
print(check_for_review)
if res.status_code == 200:
if len(check_for_review) != 0:
return render_template("movie_individual.html", movie=lol, 
omdb_data=omdb_data, ratings_list=ratings_list, 
check_for_review=check_for_review)
else:
return render_template("movie_no_review.html", movie=lol, 
omdb_data=omdb_data, ratings_list=ratings_list)
else:
if len(check_for_review) != 0:
return render_template("movie_noomdbdata.html", movie=lol, 
check_for_review=check_for_review)
else:
return render_template("movie_gotnothing.html", movie=lol, 
res=res.json())

Below is now the new api code:

@app.route("/api/")
def api(imdb_id):
check_for_api_statement = sqlalchemy.text('SELECT title, year, "imdbRating" 
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 = check_for_api_unsplitted[0]
title = check_for_api["title"]
title_pattern =  "(" + title 
year = check_for_api[1]
imdb_id = imdb_id
imdbrating = check_for_api[2]
director = omdb_data['Director']
actors = omdb_data['Actors']
check_for_reviews_statement = sqlalchemy.text('SELECT * FROM reviews WHERE 
movie = :movie_title')
check_for_reviews = engine.execute(check_for_reviews_statement, 
movie_title=title_pattern).fetchall()
review_count = len(check_for_reviews)
print(check_for_reviews)
if len(check_for_reviews) != 0:
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)
if len(check_for_api) != 0:
return render_template("api.html", apijson=apijson)
else:
return render_template("error2.html", message="No such movie.")

Here are the logs for the api:

127.0.0.1 - - [05/Jul/2019 14:42:12] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [05/Jul/2019 14:42:14] "GET /favicon.ico HTTP/1.1" 404 -
2019-07-05 14:42:25,098 INFO sqlalchemy.engine.base.Engine select version()
2019-07-05 14:42:25,098 INFO sqlalchemy.engine.base.Engine {}
2019-07-05 14:42:26,020 DEBUG sqlalchemy.engine.base.Engine Col ('version',)
2019-07-05 14:42:26,020 DEBUG sqlalchemy.engine.base.Engine Row ('PostgreSQL 
11.4 (Ubuntu 11.4-1.pgdg16.04+1
) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 
5.4.0 20160609, 64-bit',)
2019-07-05 14:42:26,021 INFO sqlalchemy.engine.base.Engine select 
current_schema()
2019-07-05 14:42:26,021 INFO sqlalchemy.engine.base.Engine {}
2019-07-05 14:42:26,363 DEBUG sqlalchemy.engine.base.Engine Col 
('current_schema',)
2019-07-05 14:42:26,363 DEBUG sqlalchemy.engine.base.Engine Row ('public',)
2019-07-05 14:42:26,738 INFO sqlalchemy.engine.base.Engine SELECT CAST('test 
plain returns' AS VARCHAR(60))
AS anon_1
2019-07-05 14:42:26,738 INFO sqlalchemy.engine.base.Engine {}
2019-07-05 14:42:27,146 INFO sqlalchemy.engine.base.Engine SELECT CAST('test 
unicode returns' AS VARCHAR(60)
) AS anon_1
2019-07-05 14:42:27,146 INFO sqlalchemy.engine.base.Engine {}
2019-07-05 14:42:27,548 INFO sqlalchemy.engine.base.Engine show 
standard_conforming_strings
2019-07-05 14:42:27,548 INFO sqlalchemy.engine.base.Engine {}
2019-07-05 14:42:28,295 DEBUG sqlalchemy.engine.base.Engine Col 
('standard_conforming_strings',)
2019-07-05 14:42:28,296 DEBUG sqlalchemy.engine.base.Engine Row ('on',)
2019-07-05 14:42:29,400 INFO sqlalchemy.engine.base.Engine SELECT title, year, 
"imdbRating" FROM movies WHER
E "imdbID" = %(imdb_id)s
2019-07-05 14:42:29,400 INFO sqlalchemy.engine.base.Engine {'imdb_id': 
'tt1490017'}
2019-07-05 14:42:30,683 DEBUG sqlalchemy.engine.base.Engine Col ('title', 
'year', 'imdbRating')

Re: [sqlalchemy] Reviews query throwing up blank list

[Simon King]

I'm afraid I can't really help you debug your whole application like
this - I can only try to answer specific questions.

If you haven't already done so, I think it would help you to find a
program to explore the data in your database. Either the psql command
line tool that comes with postgresql, pgcli (https://www.pgcli.com/),
or one of the GUIs listed on
https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools.
Then you can test out the queries that you're trying to run from
SQLAlchemy. It would hopefully be much more obvious to you when you've
inserted incorrect data.

Who has given you this assignment? Is there anyone else that can help you?

Simon

On Thu, Jul 4, 2019 at 2:37 PM Cravan  wrote:
>
> Also, another problem is that multiple reviews do not print out.
>
> On 4/7/19, 5:25 PM, "Simon King"  si...@simonking.org.uk> wrote:
>
> The corrupted value is in the "reviews" table. Are you populating that
> from a CSV file as well? If so, please show the code.
>
> As for fixing the other problem, I don't really understand what the
> code is trying to do, so I can't give you an exact solution. But let's
> have a look in more detail:
>
>
>
> This part is supposed to check for all reviews and print it out
>
>
>
>
> check_for_review_statement = sqlalchemy.text('SELECT * FROM
> reviews WHERE movie = :movie')
> check_for_review = engine.execute(check_for_review_statement,
> movie=movie_title).fetchall()
> if res.status_code == 200:
> if len(check_for_review) != 0:
> check_for_review_splitted = str(check_for_review).split(',')
>
> So "check_for_review" is a list of rows. Each row is an SQLAlchemy
> RowProxy object that behaves a bit like a mix between a tuple and a
> dictionary. These objects are properly structured, so that you can
> access the rows and columns easily:
>
> row = check_for_review[0]
> rating = row["rating"]
>
> But you're calling "str(check_for_review)". That turns the nice
> structured set of objects into a single string, looking something like
> this:
>
> "[('Spider-Man', '10', 'peterp', 'Amazing, Incredible')]"
>
> Then you're turning that string back into a list by splitting it on
> commas. The individual items in that list will now be:
>
> "[('Spider-Man'"
> " '10'"
> " 'peterp'"
> " 'Amazing"
> " Incredible')]"
>
> As you can see, you've got unwanted brackets and quotes, and because
> the review itself contained a comma, you've split that up as well.
>
> Simon
>
> On Wed, Jul 3, 2019 at 3:48 PM Cravan  wrote:
> >
> > How do you suggest I change it then? I import my values are from a csv 
> sheet as per the task requirements. As for the other problem, how should I 
> change it? And may I know why will it not work/break badly?
> > Cravan
> >
> > On 3/7/19, 10:43 PM, "Simon King"  behalf of si...@simonking.org.uk> wrote:
> >
> > Look more closely here:
> >
> > 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine SELECT *
> > FROM reviews WHERE movie = %(movie)s
> > 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine {'movie':
> > "('The Lego Movie'"}
> > 2019-07-03 22:21:47,375 DEBUG sqlalchemy.engine.base.Engine Col
> > ('movie', 'rating', 'username', 'review')
> > 2019-07-03 22:21:47,376 DEBUG sqlalchemy.engine.base.Engine Row
> > ("('The Lego Movie'", Decimal('1'), 'sms', '
> > a very cool movie')
> >
> > The value in the "movie" column is literally:
> >
> > ('The Lego Movie'
> >
> > ie. "open parenthesis, single quote, The Lego Movie, single quote"
> >
> > Whatever you're doing to insert data into the database is broken.
> >
> > Things like this also raise red flags:
> >
> > check_for_review = engine.execute(check_for_review_statement,
> > movie=movie_title).fetchall()
> > check_for_review_splitted = str(check_for_review).split(',')
> >
> > You're getting a list of rows, converting them to a string, and then
> > splitting that string on commas. This will break badly in anything 
> but
> > the simplest of cases.
> >
> > Simon
> >
> > On Wed, Jul 3, 2019 at 3:23 PM Cravan  wrote:
> > >
> > > Yes, it is.
> > > 
> > > 2019-07-03 22:21:43,914 INFO sqlalchemy.engine.base.Engine select 
> version()
> > > 2019-07-03 22:21:43,915 INFO sqlalchemy.engine.base.Engine {}
> > > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Col 
> ('version',)
> > > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Row 
> ('PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg16.04+1
> > > ) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
> 5.4.0-6ubuntu1~16.04.11) 5.

Re: [sqlalchemy] "No such polymorphic_identity" when models in different modules

[natsjoo sodillepa]

@MIke
 

> SQLAlchemy has to know about all the classes before you do a query that's 
> going to refer to a remote class' polymorphic_identity, so somewhere you 
> have to make sure the module was imported.
>
>
I imported the subclass and indeed now "it works". At first this is counter 
intuitive, but I understand the need.
The point now is that I don't really want to know what subclasses there are 
(hence the polymorfism), so now
I've to find a way to import all relevant modules. I'll figure this out but 
any ideas are welcome.

Anyway: thanks both Mike & Simon for pointing this out.

Nacho

-- 
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/de8cd30f-f0b6-479d-a823-e65bb32a1c1b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] "No such polymorphic_identity" when models in different modules

[Mike Bayer]

On Thu, Jul 4, 2019, at 8:14 AM, natsjoo sodillepa wrote:
> @Simon.
> Interesting point. I create the DB and the instances in one script. The data 
> in the db seems to be correct.
> However the error occurs in a second script which almost is like this:
> 
> import ClassA2
> 
> ... create session ...
> 
> objectA2 = session.query(ClassA2).first()
> 
> for objectA1 in objectA2.list_objectA1:
>  objectA1.do_something()
> 
> So, no here I don't import ClassA1, but I shouldn't, should I (?).
> 
> The error occurs on entering the for loop.

SQLAlchemy has to know about all the classes before you do a query that's going 
to refer to a remote class' polymorphic_identity, so somewhere you have to make 
sure the module was imported. 


> 
> 
> 
> El jueves, 4 de julio de 2019, 13:16:12 (UTC+2), Simon King escribió:
>> SQLAlchemy doesn't care if your classes are defined in a single file 
>> or multiple files. 
>> 
>> When you got the error, is it possible that you hadn't imported 
>> moduleB? If you haven't imported it, SQLAlchemy will have no idea that 
>> ClassB exists. 
>> 
>> Simon 
>> 
>> On Thu, Jul 4, 2019 at 11:53 AM natsjoo sodillepa  wrote: 
>> > 
>> > Hi all, 
>> > 
>> > I got an "No such polymorphic_identity" error in the following situation: 
>> > - I use Declerative and joined table polymorfism style 
>> > - moduleA defines Base, and a lot of Classes, one of them ClassA1(Base) 
>> > and ClassA2(Base) 
>> > - moduleB contains a subclassed ClassB(ClassA1) 
>> > - ClassA2 contains a 1:n relation with ClassA1 
>> > 
>> > when I try to get the list of ClassA1 objects of ClassA2 I get a "No such 
>> > polymorphic_identity" 
>> > error. 
>> > 
>> > However, if I put everything in the same file things work fine. 
>> > 
>> > So my question is: can I put subclasses in different modules and if so: 
>> > how? 
>> > 
>> > Kind regards, 
>> > Nacho 
>> > 
>> > -- 
>> > 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 sqlal...@googlegroups.com. 
>> > To post to this group, send email to sqlal...@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/53a77b0e-b28c-4c1d-a54a-ffac09cfb5be%40googlegroups.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/a4af5e8f-d17a-4fd8-a0d4-5fb6a058b998%40googlegroups.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/032d8832-54e2-45cc-9cb4-0673c284017e%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to update jsonb blob subset values using sqlalchemy orm ?

[Mike Bayer]

it looks like Postgresql 9.5+ has a new function jsonb_set that does this, this 
can be used with func. If you aren't on pg9.5 you might have to update the 
whole value.

full POC below

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import type_coerce
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

Base = declarative_base()


class A(Base):
 __tablename__ = "a"

 id = Column(Integer, primary_key=True)
 data = Column(JSONB)


e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

data = {
 "preference": {
 "android": {"software_update": "true", "system_maintenance": "true"},
 "ios": {"software_update": "true", "system_maintenance": "true"},
 }
}

a1 = A(data=data)
s.add(a1)
s.commit()

s.query(A).update(
 {
 A.data: func.jsonb_set(
 A.data,
 "{preference,android}",
 type_coerce(
 {"software_update": "false", "system_maintenance": "false"},
 JSONB,
 ),
 )
 },
 synchronize_session="fetch",
)

assert a1.data["preference"]["android"] == {
 "software_update": "false",
 "system_maintenance": "false",
}




On Thu, Jul 4, 2019, at 7:44 AM, NanthaKumar Loganathan wrote:
> Hi , I have below jsonb blob which i wanted to update subset value.
> 
> {
>  "preference": {
>  "android": {
>  "software_update": "true",
>  "system_maintenance": "true"
>  },
>  "ios": {
>  "software_update": "true",
>  "system_maintenance": "true"
>  }
>  }
> }
> 
> how to i update only "android" blob which is inside "preference"
> can someone help here with sqlalchemy query?
> the following snippet doesnt worked for me
> 
> app_name = 'android'
> pref = {"software_update": "false", "system_maintenance": "false"}
> qu = session.query(SystemSubscription).filter(SystemSubscription.username == 
> 't...@gmail.com').update(
> {SystemSubscription.preference: cast(
> cast(SystemSubscription.preference[app_name], 
> JSONB).concat(func.jsonb_build_object(app_name, json.dumps(pref))),
> JSON)}, synchronize_session="fetch")
> 

> --
>  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/53f0dc96-7884-42b1-890b-26e20193708a%40googlegroups.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/4c442799-ad48-4195-9b0c-53fe72ce3d05%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Reviews query throwing up blank list

[Cravan]

Also, another problem is that multiple reviews do not print out.

On 4/7/19, 5:25 PM, "Simon King"  wrote:

The corrupted value is in the "reviews" table. Are you populating that
from a CSV file as well? If so, please show the code.

As for fixing the other problem, I don't really understand what the
code is trying to do, so I can't give you an exact solution. But let's
have a look in more detail:



This part is supposed to check for all reviews and print it out




check_for_review_statement = sqlalchemy.text('SELECT * FROM
reviews WHERE movie = :movie')
check_for_review = engine.execute(check_for_review_statement,
movie=movie_title).fetchall()
if res.status_code == 200:
if len(check_for_review) != 0:
check_for_review_splitted = str(check_for_review).split(',')

So "check_for_review" is a list of rows. Each row is an SQLAlchemy
RowProxy object that behaves a bit like a mix between a tuple and a
dictionary. These objects are properly structured, so that you can
access the rows and columns easily:

row = check_for_review[0]
rating = row["rating"]

But you're calling "str(check_for_review)". That turns the nice
structured set of objects into a single string, looking something like
this:

"[('Spider-Man', '10', 'peterp', 'Amazing, Incredible')]"

Then you're turning that string back into a list by splitting it on
commas. The individual items in that list will now be:

"[('Spider-Man'"
" '10'"
" 'peterp'"
" 'Amazing"
" Incredible')]"

As you can see, you've got unwanted brackets and quotes, and because
the review itself contained a comma, you've split that up as well.

Simon

On Wed, Jul 3, 2019 at 3:48 PM Cravan  wrote:
>
> How do you suggest I change it then? I import my values are from a csv 
sheet as per the task requirements. As for the other problem, how should I 
change it? And may I know why will it not work/break badly?
> Cravan
>
> On 3/7/19, 10:43 PM, "Simon King"  wrote:
>
> Look more closely here:
>
> 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine SELECT *
> FROM reviews WHERE movie = %(movie)s
> 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine {'movie':
> "('The Lego Movie'"}
> 2019-07-03 22:21:47,375 DEBUG sqlalchemy.engine.base.Engine Col
> ('movie', 'rating', 'username', 'review')
> 2019-07-03 22:21:47,376 DEBUG sqlalchemy.engine.base.Engine Row
> ("('The Lego Movie'", Decimal('1'), 'sms', '
> a very cool movie')
>
> The value in the "movie" column is literally:
>
> ('The Lego Movie'
>
> ie. "open parenthesis, single quote, The Lego Movie, single quote"
>
> Whatever you're doing to insert data into the database is broken.
>
> Things like this also raise red flags:
>
> check_for_review = engine.execute(check_for_review_statement,
> movie=movie_title).fetchall()
> check_for_review_splitted = str(check_for_review).split(',')
>
> You're getting a list of rows, converting them to a string, and then
> splitting that string on commas. This will break badly in anything but
> the simplest of cases.
>
> Simon
>
> On Wed, Jul 3, 2019 at 3:23 PM Cravan  wrote:
> >
> > Yes, it is.
> > 
> > 2019-07-03 22:21:43,914 INFO sqlalchemy.engine.base.Engine select 
version()
> > 2019-07-03 22:21:43,915 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Col 
('version',)
> > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Row 
('PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg16.04+1
> > ) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit',)
> > 2019-07-03 22:21:44,609 INFO sqlalchemy.engine.base.Engine select 
current_schema()
> > 2019-07-03 22:21:44,610 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:45,020 DEBUG sqlalchemy.engine.base.Engine Col 
('current_schema',)
> > 2019-07-03 22:21:45,021 DEBUG sqlalchemy.engine.base.Engine Row 
('public',)
> > 2019-07-03 22:21:45,346 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test plain returns' AS VARCHAR(60))
> > AS anon_1
> > 2019-07-03 22:21:45,346 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:45,670 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test unicode returns' AS VARCHAR(60)
> > ) AS anon_1
> > 2019-07-03 22:21:45,671 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:46,000 INFO sqlalchemy.engine.base.Engine show 
standard_conforming_strings
> > 2019-0

Re: [sqlalchemy] "No such polymorphic_identity" when models in different modules

[natsjoo sodillepa]

@Simon.
Interesting point. I create the DB and the instances in one script. The 
data in the db seems to be correct.
However the error occurs in a second script which almost is like this:

import ClassA2

... create session ...

objectA2 = session.query(ClassA2).first()

for objectA1 in objectA2.list_objectA1:
objectA1.do_something()

So, no here I don't import ClassA1, but I shouldn't, should I (?).

The error occurs on entering the for loop.


El jueves, 4 de julio de 2019, 13:16:12 (UTC+2), Simon King escribió:
>
> SQLAlchemy doesn't care if your classes are defined in a single file 
> or multiple files. 
>
> When you got the error, is it possible that you hadn't imported 
> moduleB? If you haven't imported it, SQLAlchemy will have no idea that 
> ClassB exists. 
>
> Simon 
>
> On Thu, Jul 4, 2019 at 11:53 AM natsjoo sodillepa  > wrote: 
> > 
> > Hi all, 
> > 
> > I got an "No such polymorphic_identity" error in the following 
> situation: 
> > - I use Declerative and joined table polymorfism style 
> > - moduleA defines Base, and a lot of Classes, one of them ClassA1(Base) 
> and ClassA2(Base) 
> > - moduleB contains a subclassed ClassB(ClassA1) 
> > - ClassA2 contains a 1:n relation with ClassA1 
> > 
> > when I try to get the list of ClassA1 objects of ClassA2 I get a "No 
> such polymorphic_identity" 
> > error. 
> > 
> > However, if I put everything in the same file things work fine. 
> > 
> > So my question is: can I put subclasses in different modules and if so: 
> how? 
> > 
> > Kind regards, 
> > Nacho 
> > 
> > -- 
> > 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 sqlal...@googlegroups.com . 
> > To post to this group, send email to sqlal...@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/53a77b0e-b28c-4c1d-a54a-ffac09cfb5be%40googlegroups.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/a4af5e8f-d17a-4fd8-a0d4-5fb6a058b998%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Reviews query throwing up blank list

[Cravan]

Here's my entire code. Some parts I messed up the get and post methods, will 
work on that later on. Erm... how do I split the list without breaking up the 
review then? Or in the first place, is check_for_review a list of dictionaries?
Cravan

On 4/7/19, 5:25 PM, "Simon King"  wrote:

The corrupted value is in the "reviews" table. Are you populating that
from a CSV file as well? If so, please show the code.

As for fixing the other problem, I don't really understand what the
code is trying to do, so I can't give you an exact solution. But let's
have a look in more detail:

check_for_review_statement = sqlalchemy.text('SELECT * FROM
reviews WHERE movie = :movie')
check_for_review = engine.execute(check_for_review_statement,
movie=movie_title).fetchall()
if res.status_code == 200:
if len(check_for_review) != 0:
check_for_review_splitted = str(check_for_review).split(',')

So "check_for_review" is a list of rows. Each row is an SQLAlchemy
RowProxy object that behaves a bit like a mix between a tuple and a
dictionary. These objects are properly structured, so that you can
access the rows and columns easily:

row = check_for_review[0]
rating = row["rating"]

But you're calling "str(check_for_review)". That turns the nice
structured set of objects into a single string, looking something like
this:

"[('Spider-Man', '10', 'peterp', 'Amazing, Incredible')]"

Then you're turning that string back into a list by splitting it on
commas. The individual items in that list will now be:

"[('Spider-Man'"
" '10'"
" 'peterp'"
" 'Amazing"
" Incredible')]"

As you can see, you've got unwanted brackets and quotes, and because
the review itself contained a comma, you've split that up as well.

Simon

On Wed, Jul 3, 2019 at 3:48 PM Cravan  wrote:
>
> How do you suggest I change it then? I import my values are from a csv 
sheet as per the task requirements. As for the other problem, how should I 
change it? And may I know why will it not work/break badly?
> Cravan
>
> On 3/7/19, 10:43 PM, "Simon King"  wrote:
>
> Look more closely here:
>
> 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine SELECT *
> FROM reviews WHERE movie = %(movie)s
> 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine {'movie':
> "('The Lego Movie'"}
> 2019-07-03 22:21:47,375 DEBUG sqlalchemy.engine.base.Engine Col
> ('movie', 'rating', 'username', 'review')
> 2019-07-03 22:21:47,376 DEBUG sqlalchemy.engine.base.Engine Row
> ("('The Lego Movie'", Decimal('1'), 'sms', '
> a very cool movie')
>
> The value in the "movie" column is literally:
>
> ('The Lego Movie'
>
> ie. "open parenthesis, single quote, The Lego Movie, single quote"
>
> Whatever you're doing to insert data into the database is broken.
>
> Things like this also raise red flags:
>
> check_for_review = engine.execute(check_for_review_statement,
> movie=movie_title).fetchall()
> check_for_review_splitted = str(check_for_review).split(',')
>
> You're getting a list of rows, converting them to a string, and then
> splitting that string on commas. This will break badly in anything but
> the simplest of cases.
>
> Simon
>
> On Wed, Jul 3, 2019 at 3:23 PM Cravan  wrote:
> >
> > Yes, it is.
> > 
> > 2019-07-03 22:21:43,914 INFO sqlalchemy.engine.base.Engine select 
version()
> > 2019-07-03 22:21:43,915 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Col 
('version',)
> > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Row 
('PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg16.04+1
> > ) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit',)
> > 2019-07-03 22:21:44,609 INFO sqlalchemy.engine.base.Engine select 
current_schema()
> > 2019-07-03 22:21:44,610 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:45,020 DEBUG sqlalchemy.engine.base.Engine Col 
('current_schema',)
> > 2019-07-03 22:21:45,021 DEBUG sqlalchemy.engine.base.Engine Row 
('public',)
> > 2019-07-03 22:21:45,346 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test plain returns' AS VARCHAR(60))
> > AS anon_1
> > 2019-07-03 22:21:45,346 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:45,670 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test unicode returns' AS VARCHAR(60)
> > ) AS anon_1
> > 2019-07-03 22:21:45,671 INFO sqlalchemy.engine.base.Engine {}
> > 20

[sqlalchemy] How to update jsonb blob subset values using sqlalchemy orm ?

[NanthaKumar Loganathan]

Hi ,  I have below jsonb blob which i wanted to update subset value.

{
  "preference": {
"android": {
  "software_update": "true",
  "system_maintenance": "true"
},
"ios": {
  "software_update": "true",
  "system_maintenance": "true"
}
  }
}

how to i update only "android" blob which is inside "preference"
can someone help here with sqlalchemy query?
the following snippet doesnt worked for me

app_name = 'android'
pref = {"software_update": "false", "system_maintenance": "false"}

qu = session.query(SystemSubscription).filter(SystemSubscription.username == 
't...@gmail.com').update(
{SystemSubscription.preference: cast(
cast(SystemSubscription.preference[app_name], 
JSONB).concat(func.jsonb_build_object(app_name, json.dumps(pref))),
JSON)}, synchronize_session="fetch")

-- 
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/53f0dc96-7884-42b1-890b-26e20193708a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] "No such polymorphic_identity" when models in different modules

[Simon King]

SQLAlchemy doesn't care if your classes are defined in a single file
or multiple files.

When you got the error, is it possible that you hadn't imported
moduleB? If you haven't imported it, SQLAlchemy will have no idea that
ClassB exists.

Simon

On Thu, Jul 4, 2019 at 11:53 AM natsjoo sodillepa  wrote:
>
> Hi all,
>
> I got an "No such polymorphic_identity" error in the following situation:
> - I use Declerative and joined table polymorfism style
> - moduleA defines Base, and a lot of Classes, one of them ClassA1(Base) and 
> ClassA2(Base)
> - moduleB contains a subclassed ClassB(ClassA1)
> - ClassA2 contains a 1:n relation with ClassA1
>
> when I try to get the list of ClassA1 objects of ClassA2 I get a "No such 
> polymorphic_identity"
> error.
>
> However, if I put everything in the same file things work fine.
>
> So my question is: can I put subclasses in different modules and if so: how?
>
> Kind regards,
> Nacho
>
> --
> 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/53a77b0e-b28c-4c1d-a54a-ffac09cfb5be%40googlegroups.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/CAFHwexdaHXfV5SBkzXmM2FLJNbfOBgti2Siqkh%2B-1y9RqNNmOA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] "No such polymorphic_identity" when models in different modules

[natsjoo sodillepa]

Hi all,

I got an "No such polymorphic_identity" error in the following situation:
- I use Declerative and joined table polymorfism style
- moduleA defines Base, and a lot of Classes, one of them ClassA1(Base) and 
ClassA2(Base)
- moduleB contains a subclassed ClassB(ClassA1)
- ClassA2 contains a 1:n relation with ClassA1

when I try to get the list of ClassA1 objects of ClassA2 I get a "No such 
polymorphic_identity"
error.

However, if I put everything in the same file things work fine.

So my question is: can I put subclasses in different modules and if so: how?

Kind regards,
Nacho

-- 
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/53a77b0e-b28c-4c1d-a54a-ffac09cfb5be%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] limit in context of entities not rows

[Simon King]

I would first figure out how you would do this in SQL, and then
translate that to SQLAlchemy. In this case, the EXISTS operator might
work:

SELECT *
FROM department
WHERE EXISTS (
SELECT 1
FROM employee
WHERE employee.department_id = department.id
AND employee.name IN (...)
)
LIMIT 2

The docs contain some examples of how to use EXISTS:

https://docs.sqlalchemy.org/en/13/orm/tutorial.html#using-exists

session.query(Department).filter(Department.employees.any(Employee.name.in_(...)))

I've no idea about the performance of this though. It seems like it's
going to have to evaluate the subquery for every row of the parent
table. If the parent table is huge, and you're only looking at a small
number of employees, that might be wasted effort. Under those
conditions, it would be better to find the employees first, then
select the matching departments:

SELECT *
FROM department
WHERE id in (
SELECT department_id
FROM employee
WHERE name IN (...)
)
LIMIT 2

employee_subquery =
session.query(Employee.department_id).filter(Employee.name.in_(...)).subquery()
departments = 
session.query(Department).filter(Department.id.in_(employee_subquery)).limit(2)

Simon

On Thu, Jul 4, 2019 at 10:27 AM Victor Olex
 wrote:
>
> Using ORM querying what is the best practice for limiting the output to a 
> given number of resulting entities?
>
> Consider this model:
>
> from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
> from sqlalchemy.orm import relationship, backref
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
>
> Base = declarative_base()
>
> class Department(Base):
> __tablename__ = 'department'
> id = Column(Integer, primary_key=True)
> name = Column(String)
>
> def __repr__(self):
> return f'Department({self.id}, {self.name})'
>
>
> class Employee(Base):
> __tablename__ = 'employee'
> id = Column(Integer, primary_key=True)
> name = Column(String)
> department_id = Column(Integer, ForeignKey('department.id'))
> # Use cascade='delete,all' to propagate the deletion of a Department onto 
> its Employees
> department = relationship(
> Department,
> backref=backref('employees', uselist=True,
>  cascade='delete,all'))
> engine = create_engine('sqlite://', echo=True)
> Base.metadata.create_all(engine)
>
> Session = sessionmaker(bind=engine)
> session = Session()
> session.add(Department(id=1, name='IT'))
> session.add(Department(id=2, name='Finance'))
> session.add(Department(id=3, name='Sales'))
> session.add(Employee(id=1, name='Victor', department_id=1))
> session.add(Employee(id=2, name='Michal', department_id=1))
> session.add(Employee(id=3, name='Kinga', department_id=2))
> session.add(Employee(id=4, name='Andy', department_id=3))
> session.commit()
>
> Now, let's query for the list of Departments given some criteria on the 
> Employee:
> session.query(Department).join(Employee).filter(Employee.name.in_(['Andy', 
> 'Kinga', 'Victor', 'Michal'])).all()
>
> As expected we get:
>
> [Department(1, IT), Department(2, Finance), Department(3, Sales)]
>
> Now suppose our intent is to limit the number of results, and we would prefer 
> to use a LIMIT clause to do the filtering on the database side:
>
> session.query(Department).join(Employee).filter(Employee.name.in_(['Andy', 
> 'Kinga', 'Victor', 'Michal'])).limit(2).all()
>
> This (in my case) resulted in:
>
> [Department(1, IT)]
>
> The reason for this is that the limit gets applied to the resulting rows from 
> the joined tables, which happen to begin with the two employees from IT (this 
> is non-deterministic unless order by is also used). Since both represent the 
> same entity, only one instance is returned.
>
> Other approaches (and their shortcomings) are:
>
> Using a DISTINCT clause prior to LIMIT - won't work if any field is include 
> non-comparable types like IMAGE, BLOB
> Using a subquery on the Department with LIMIT - this may filter out 
> departments, which would otherwise match
>
> What works is wrapping the entire query in a subquery, but only selecting a 
> DISTINCT Department.id field, and using that in the IN clause. It seems quite 
> convoluted and some databases might not do great to optimize this away.
>
> sq = 
> session.query(Department.id).join(Employee).filter(Employee.name.in_(['Andy', 
> 'Kinga', 'Victor', 'Michal'])).distinct().limit(2).subquery()
> session.query(Department).filter(Department.id.in_(sq)).all()
>
>
> --
> 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 rec

[sqlalchemy] limit in context of entities not rows

[Victor Olex]

Using ORM querying what is the best practice for limiting the output to a 
given number of resulting *entities*?

Consider this model:

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
  
Base = declarative_base()
 
class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key=True)
name = Column(String)

def __repr__(self):
return f'Department({self.id}, {self.name})'

 
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
department_id = Column(Integer, ForeignKey('department.id'))
# Use cascade='delete,all' to propagate the deletion of a Department 
onto its Employees
department = relationship(
Department,
backref=backref('employees', uselist=True,
 cascade='delete,all'))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()
session.add(Department(id=1, name='IT'))
session.add(Department(id=2, name='Finance'))
session.add(Department(id=3, name='Sales'))
session.add(Employee(id=1, name='Victor', department_id=1))
session.add(Employee(id=2, name='Michal', department_id=1))
session.add(Employee(id=3, name='Kinga', department_id=2))
session.add(Employee(id=4, name='Andy', department_id=3))
session.commit()

Now, let's query for the list of Departments given some criteria on the 
Employee: 
session.query(Department).join(Employee).filter(Employee.name.in_(['Andy', 
'Kinga', 'Victor', 'Michal'])).all()

As expected we get:

[Department(1, IT), Department(2, Finance), Department(3, Sales)]

Now suppose our intent is to limit the number of results, and we would 
prefer to use a LIMIT clause to do the filtering on the database side:

session.query(Department).join(Employee).filter(Employee.name.in_(['Andy', 
'Kinga', 'Victor', 'Michal'])).limit(2).all()

This (in my case) resulted in:

[Department(1, IT)]

The reason for this is that the limit gets applied to the resulting *rows* from 
the joined tables, which happen to begin with the two employees from IT 
(this is non-deterministic unless order by is also used). Since both 
represent the same entity, only one instance is returned.

Other approaches (and their shortcomings) are:

   1. Using a DISTINCT clause prior to LIMIT - won't work if any field is 
   include non-comparable types like IMAGE, BLOB
   2. Using a subquery on the Department with LIMIT - this may filter out 
   departments, which would otherwise match

What works is wrapping the entire query in a subquery, but only selecting a 
DISTINCT Department.id field, and using that in the IN clause. It seems 
quite convoluted and some databases might not do great to optimize this 
away.

sq = session.query(Department.id).join(Employee).filter(Employee.name.in_([
'Andy', 'Kinga', 'Victor', 'Michal'])).distinct().limit(2).subquery()
session.query(Department).filter(Department.id.in_(sq)).all()


-- 
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/d4e19b87-3442-4b5e-801a-3005fc4c433e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Reviews query throwing up blank list

[Simon King]

The corrupted value is in the "reviews" table. Are you populating that
from a CSV file as well? If so, please show the code.

As for fixing the other problem, I don't really understand what the
code is trying to do, so I can't give you an exact solution. But let's
have a look in more detail:

check_for_review_statement = sqlalchemy.text('SELECT * FROM
reviews WHERE movie = :movie')
check_for_review = engine.execute(check_for_review_statement,
movie=movie_title).fetchall()
if res.status_code == 200:
if len(check_for_review) != 0:
check_for_review_splitted = str(check_for_review).split(',')

So "check_for_review" is a list of rows. Each row is an SQLAlchemy
RowProxy object that behaves a bit like a mix between a tuple and a
dictionary. These objects are properly structured, so that you can
access the rows and columns easily:

row = check_for_review[0]
rating = row["rating"]

But you're calling "str(check_for_review)". That turns the nice
structured set of objects into a single string, looking something like
this:

"[('Spider-Man', '10', 'peterp', 'Amazing, Incredible')]"

Then you're turning that string back into a list by splitting it on
commas. The individual items in that list will now be:

"[('Spider-Man'"
" '10'"
" 'peterp'"
" 'Amazing"
" Incredible')]"

As you can see, you've got unwanted brackets and quotes, and because
the review itself contained a comma, you've split that up as well.

Simon

On Wed, Jul 3, 2019 at 3:48 PM Cravan  wrote:
>
> How do you suggest I change it then? I import my values are from a csv sheet 
> as per the task requirements. As for the other problem, how should I change 
> it? And may I know why will it not work/break badly?
> Cravan
>
> On 3/7/19, 10:43 PM, "Simon King"  si...@simonking.org.uk> wrote:
>
> Look more closely here:
>
> 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine SELECT *
> FROM reviews WHERE movie = %(movie)s
> 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine {'movie':
> "('The Lego Movie'"}
> 2019-07-03 22:21:47,375 DEBUG sqlalchemy.engine.base.Engine Col
> ('movie', 'rating', 'username', 'review')
> 2019-07-03 22:21:47,376 DEBUG sqlalchemy.engine.base.Engine Row
> ("('The Lego Movie'", Decimal('1'), 'sms', '
> a very cool movie')
>
> The value in the "movie" column is literally:
>
> ('The Lego Movie'
>
> ie. "open parenthesis, single quote, The Lego Movie, single quote"
>
> Whatever you're doing to insert data into the database is broken.
>
> Things like this also raise red flags:
>
> check_for_review = engine.execute(check_for_review_statement,
> movie=movie_title).fetchall()
> check_for_review_splitted = str(check_for_review).split(',')
>
> You're getting a list of rows, converting them to a string, and then
> splitting that string on commas. This will break badly in anything but
> the simplest of cases.
>
> Simon
>
> On Wed, Jul 3, 2019 at 3:23 PM Cravan  wrote:
> >
> > Yes, it is.
> > 
> > 2019-07-03 22:21:43,914 INFO sqlalchemy.engine.base.Engine select 
> version()
> > 2019-07-03 22:21:43,915 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Col 
> ('version',)
> > 2019-07-03 22:21:44,608 DEBUG sqlalchemy.engine.base.Engine Row 
> ('PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg16.04+1
> > ) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
> 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit',)
> > 2019-07-03 22:21:44,609 INFO sqlalchemy.engine.base.Engine select 
> current_schema()
> > 2019-07-03 22:21:44,610 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:45,020 DEBUG sqlalchemy.engine.base.Engine Col 
> ('current_schema',)
> > 2019-07-03 22:21:45,021 DEBUG sqlalchemy.engine.base.Engine Row 
> ('public',)
> > 2019-07-03 22:21:45,346 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test plain returns' AS VARCHAR(60))
> > AS anon_1
> > 2019-07-03 22:21:45,346 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:45,670 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test unicode returns' AS VARCHAR(60)
> > ) AS anon_1
> > 2019-07-03 22:21:45,671 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:46,000 INFO sqlalchemy.engine.base.Engine show 
> standard_conforming_strings
> > 2019-07-03 22:21:46,000 INFO sqlalchemy.engine.base.Engine {}
> > 2019-07-03 22:21:46,348 DEBUG sqlalchemy.engine.base.Engine Col 
> ('standard_conforming_strings',)
> > 2019-07-03 22:21:46,348 DEBUG sqlalchemy.engine.base.Engine Row ('on',)
> > 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine SELECT * 
> FROM reviews WHERE movie = %(movie)s
> > 2019-07-03 22:21:46,676 INFO sqlalchemy.engine.base.Engine {'movie': 
> "('The Lego Movie'"}
> > 2019-07-03 22:21:47,375 DEBUG sqlalchemy.engine.base.Engine Col 
> ('movie