CODEWITHSUNDEEP
pythonsqlalchemy
alphanumeric
alphanumeric

Reputation: 19329

How to query multiple items using Flask SQLAlchemy

After importing the modules:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

and declaring app and db objects:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db' 
db = SQLAlchemy(app)

I go ahead and create two tables: User and Email:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    addresses = db.relationship('Email', backref='person', lazy='dynamic')

class Email(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50))
    person_id = db.Column(db.Integer, db.ForeignKey('user.id'))

With db.relationship in place I can now link some multiple emails to the same user. First I create two email addresses:

first_email = Email(email='[email protected]')
second_email = Email(email='[email protected]')

Then I am passing these two emails to User class at the time it is being created:

user = User(name='User Name', addresses = [first_email, second_email])

To see which user is linked to which email I can simply use:

print first_email.person
print user.addresses.all()

Now I want to add another third email to the same user. How do I append a new email to the list of the emails that have been already linked to the user?

Upvotes: 1

Views: 1093

Answers (2)

alphanumeric
alphanumeric

Reputation: 19329

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///inquestion.db' 
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    addresses = db.relationship('Email', backref='person', lazy='dynamic')

    def add_email(self, new_email):
        linked_emails = [email.email for email in self.addresses.all()] 
        if not new_email in linked_emails:
            linked_emails.append(new_email)

        self.addresses = [Email.find_or_create(email) for email in linked_emails]



class Email(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50))
    person_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    @staticmethod
    def find_or_create(email):
        try:
            return Email.query.filter_by(email=email).one()
        except:
            new_email = Email(email=email)
            db.session.add(new_email)
            db.session.commit()
            return new_email


first_email = Email(email='[email protected]')
second_email = Email(email='[email protected]')

user = User(name='User Name', addresses = [first_email, second_email])

db.drop_all()
db.create_all()


db.session.add(first_email)
db.session.add(second_email)
db.session.add(user)
db.session.commit()

# some extra queries
user.add_email('[email protected]')
print user.addresses.all()

print Email.find_or_create('[email protected]')
print Email.query.filter_by(email='[email protected]').one().email
print first_email.query.filter_by(email='[email protected]').one()

Upvotes: 0

Jake Conway
Jake Conway

Reputation: 909

new_email = Email(email='[email protected]')
user.addresses.append(new_email)
db.session.commit()

This will append the email address to the relationship.

Upvotes: 1

Related Questions