Why does my sqlite query not print all data?

Question

I've noticed that my query is getting all data from my joined tables but I can only read that data when I specifically try and access it.

My unedited file:

query = db.session.query(Rating, Song).filter(Rating.id==Song.id).all()
print(query) #<----- This prints [(4.75, MMMBop), (3.00, bombastic)]
for x in query:
    print(f"{x[1].title}:{x[1].artist}:{x[1].released}") #<-- This prints MMMBop:Hansons:1997\nbombastic:shaggy:1995

Why is this?

EDIT

I have added my model now. repr was the first thing I checked and I have run the code again after a reboot so there can't be any variables lurking. No repr is even including the artist and release.

from application import db

association_table = db.Table('association',
                             db.Column('songs_id', db.Integer,
                                       db.ForeignKey('songs.id')),
                             db.Column('genres_id', db.Integer,
                                       db.ForeignKey('genres.id'))
                             )


class Rating(db.Model):
    __tablename__ = 'songs_ratings'
    id = db.Column(db.Integer, primary_key=True)
    rating = db.Column(db.Numeric(precision=3, scale=2),
                       index=True, nullable=False)

    def __repr__(self):
        return '{}'.format(self.rating)


class Song(db.Model):
    __tablename__ = 'songs'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80), index=True, unique=True, nullable=False)
    artist = db.Column(db.String(30), primary_key=False,
                       unique=False, nullable=False)
    release = db.Column(db.Date, nullable=False)
    genres = db.relationship(
        "Genre", secondary=association_table, backref=db.backref('songs'))

    def __repr__(self):
        return '{}'.format(self.title)


class Genre(db.Model):
    __tablename__ = 'genres'
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(80), index=True,
                         unique=True, nullable=False)

    def __repr__(self):
        return '{}'.format(self.category)

What library are you using to connect to the database? Will you show your imports? — Code-Apprentice, May 03 '21 at 14:42
I am importing sqlalchemy and using it in my models from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy() — , May 03 '21 at 14:46
Looks like ["Understanding repr( ) function in Python"](https://stackoverflow.com/questions/7784148/understanding-repr-function-in-python) can help make sense of it. — Ilja Everilä, May 03 '21 at 16:43
Completely different question: why is `Rating` a separate table if it is just 1-1 relationship with `Song`? And even if you want to have it separately, why not creating a `1-1` actual `relationship` between the entities? — van, May 04 '21 at 07:55
That's a good point, I intend to use ratings for machine learning so I thought it would be best to seperate them. I tried 1-1 relationship between songs and rating but was unsuccessful because I find sqlalchemy to be hard. Even after reading all the tutorials I could find — , May 04 '21 at 08:16

score 0 · Answer 1 · answered May 03 '21 at 16:50

0

The output you see from print(query) is just a string representation of the query object. It does not give you any information about how to access the underlying data. I suggest you read more sqlalchemy tutorials and documentation to find out how to correctly use the results of db.session.query(). In this case, you need to fetch all the rows as a cursor:

for x in query.fetchall():

answered May 03 '21 at 16:50

Code-Apprentice

81,660
23
145
268

`Query` is not a cursor as such, and after having called `Query.all()` like in the question all you have is a `list`, which can be seen from the representation. – Ilja Everilä May 03 '21 at 16:58
@IljaEverilä Thanks for the clarification. Please edit my answer with the correct wording/terminology. I'm only passingly familiar with sqlalchemy, so corrections are welcome. – Code-Apprentice May 03 '21 at 17:08

score 0 · Accepted Answer · answered May 04 '21 at 07:47

Indeed, your query returns all the data (as you can probably see from the issued SQL statements).

The issue is not that you cannot "see" the data, but rather what you see when you use "print" statement.

query = db.session.query(Rating, Song).filter(Rating.id==Song.id).all()
print(query) #<----- This prints [(4.75, MMMBop), (3.00, bombastic)]

The reason above prints what you see is because both Rating and Song have __repr__ method, which prints only rating and title respectively.

But when you executed the below print statement, you are deciding what to print:

for x in query:
    print(f"{x[1].title}:{x[1].artist}:{x[1].released}") #<-- This prints MMMBop:Hansons:1997\nbombastic:shaggy:1995

My suggestion would be for you to implement a more verbose Song.__repr__ method to include all the columns you care about.

Why does my sqlite query not print all data?

2 Answers2