Adding a comments field to database with flask/SQLAlchemy?

Question

I'm working on a flask application that has a database that is set up like this and am using SQLAlchemy. (display_name was renamed to persona_name since that's what the steam api calls it.)

I'm still in the process of learning how work with databases, and what I have now works fine, I can track all the lists a user has made, and I can get suspects from all lists that one user has made. Also, removing a suspect from the suspect table also seems to remove the suspect from all lists it appears on.

This seems to work well, List and Suspect are classes that inherit from db.Model while suspect_list is an auxiliary table that is not itself a class.

The way it is set up I want to be able to have a suspect show up on multiple lists, but now I want to be able to add comments for individual suspects on individual lists.

I'm not sure how to go about this, but I have a few ideas and am not sure if they work or would have potential downsides.

1) Can I add a comment field to suspect_list?

2) Do I make a comment model as a class that inherits from db.Model and then add it to the auxiliary table instead?

3) Should I create a new id field for the suspect table, make it the primary key instead of steam_id , and then add a comment field so there can be duplicate suspects that have differing comments?

I probably could implement 3 but I don't think it is a good idea because duplicates of the same suspect probably is something that should be avoided.

As for 1 and 2 I don't know if they would work and if they did I'm not sure how to properly implement them.

This is the code that I have for my Models.py if it is needed

My question is how would I properly add comments to this database model I have set up?

Doobeh · Accepted Answer · 2014-10-25 04:05:34Z

1

Rather then an association table, you really need an association object. Here's a working example.

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.secret_key = 'MOO.'
app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite://'  # In memory.
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    def __repr__(self):
        return '<User {}>'.format(self.name)

class Suspect(db.Model):
    __tablename__ = 'suspect'
    id = db.Column(db.Integer, primary_key=True)
    steam_name = db.Column(db.String)

    def __repr__(self):
        return '<Suspect {}>'.format(self.steam_name)

class List(db.Model):
    __tablename__ = 'list'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('User', backref=db.backref('lists'))
    suspects = db.relationship('SuspectList', backref='list')
    def __repr__(self):
        return '<List {}>'.format(self.name)


class SuspectList(db.Model):
    __tablename__ = 'suspect_list'
    list_id = db.Column(db.Integer, db.ForeignKey('list.id'), primary_key=True)
    suspect_id = db.Column(db.Integer, db.ForeignKey('suspect.id'), primary_key=True)
    comment = db.Column(db.String)
    suspect = db.relationship('Suspect', backref=db.backref("suspect_list"))

    def __repr__(self):
        return '<SL: %s %s %s>' % (self.list, self.suspect, self.comment)

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
        hacker = Suspect(steam_name='Bob')
        cracker = Suspect(steam_name='Alice')
        carol = User(name='Carol')
        carols_list = List(name='Carols', user=carol)

        hacker_suspect = SuspectList(suspect=hacker, comment='RED HAIR')
        cracker_suspect = SuspectList(suspect=cracker, comment='RED EYES')

        carols_list.suspects.append(hacker_suspect)
        carols_list.suspects.append(cracker_suspect)

        db.session.add(carols_list)  # Trust SQLAlchemy to add all related.
        db.session.commit()

        ## Querying:

        my_list = List.query.filter(User.name == 'Carol').first()
        for record in my_list.suspects:
            print '{} reported {} with comment {}'.format(
                record.list.user.name,
                record.suspect.steam_name,
                record.comment
            )

        # Carol reported Bob with comment RED HAIR
        # Carol reported Alice with comment RED EYES

Two side-notes-- I found it a bit ugly to be use List as a class name, because when you want to query it, your default name would be list = List.query.fi.... which is a bit of a no-go :). Also, what program did you use to generate your ERD?

answered Oct 25, 2014 at 4:05

Doobeh

9,48042 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shadefinale Over a year ago

Thank you very much for the response, I will probably try to make some of these changes for my project. I do have some questions, however. 1) What is an ERD? Do you mean the diagram image I made? If so, this is what I used to make the image and plan out the database stuff in a way that would make sense. 2) does tablename essentially make the List/Suspect/SuspectList tables in the database have that name? 3) Do you suggest changing List to UserList or some other name that would make more sense? 4) When should I use an association object over a table

Doobeh Over a year ago

ERD = Entity Relationship Diagram, thanks-- hard to find non-ugly tools to create them. __tablename__ lets you specify what the name of table created in the database is, it's mostly for clarity, I think Flask-SQLAlchemy automatically names the tables, but sometimes it might do so in a way that's not quite what you expected-- this removes that risk. Yeah UserList is more informative to my mind.

Doobeh Over a year ago

You would use an association table when you are just using it for joining-- it essentially becomes a transparent two-way bridge between the two objects. That's very, very handy, but because it's transparent, when you want to do something like add the comment column in, it doesn't work because it's transparent to your objects and so there's no easy way to pull that comment columns data. That's when you'd migrate to an assocation object-- because you want it to become a real thing (an object) so you can look into it.

dpgaspar · Accepted Answer · 2014-10-24 18:42:05Z

0

if i correctly understand, i would just create a new model related to your List Model

class Comments(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    comment = db.Column(db.String(300))
    list_id = db.Column(db.Integer, db.ForeignKey('list.id'))

answered Oct 24, 2014 at 18:42

dpgaspar

1,2739 silver badges10 bronze badges

1 Comment

shadefinale Over a year ago

I essentially have a list that has multiple suspects. I'm attempting to allow someone who is using the application to add a comment about a suspect on a particular list, and that comment is specific to that suspect on that particular list. This looks like it would just be 1 comment attached to 1 list that doesn't have anything to do with a particular suspect. So like list 1 suspect 1 - "This guy was verbally abusive" list 2 suspect 1 (same suspect in suspect table) - "I think this guy was cheating"

Collectives™ on Stack Overflow

Adding a comments field to database with flask/SQLAlchemy?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related