To get all shots of an event, you could try
session.query(Shots).filter_by(event_id = event_id)
The event_id is the id of event you like to query. To store by user, you can try
from sqlalchemy.sql.expression
import desc, asc
session.query(Shots)\
.filter_by(event_id = event_id)\
.order_by(asc(Shots.user_id))Of course, you may want to sort by attribute of an user, you could join user table.
from sqlalchemy.sql.expression
import desc, asc
session.query(Shots)\
.filter_by(event_id = event_id)\
.join(User)\
.order_by(asc(User.name))Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table’s primary key. Moreover, classes mapping to the two tables have an attribute with a collection of objects of other association tables assigned as secondary attribute of relationship() function.,We now define a Link class. It is linked to link table and contains department_id and employee_id attributes respectively referencing to primary keys of department and employee table.,For this purpose, we shall create a SQLite database (mycollege.db) with two tables - department and employee. Here, we assume that an employee is a part of more than one department, and a department has more than one employee. This constitutes many-to-many relationship.,Here, we have to make a note that Department class has employees attribute related to Employee class. The relationship function’s secondary attribute is assigned a link as its value.
Definition of Employee and Department classes mapped to department and employee table is as follows −
from sqlalchemy
import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///mycollege.db', echo = True)
from sqlalchemy.ext.declarative
import declarative_base
Base = declarative_base()
from sqlalchemy.orm
import relationship
class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key = True)
name = Column(String)
employees = relationship('Employee', secondary = 'link')
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key = True)
name = Column(String)
departments = relationship(Department, secondary = 'link')We now define a Link class. It is linked to link table and contains department_id and employee_id attributes respectively referencing to primary keys of department and employee table.
class Link(Base):
__tablename__ = 'link'
department_id = Column(
Integer,
ForeignKey('department.id'),
primary_key = True)
employee_id = Column(
Integer,
ForeignKey('employee.id'),
primary_key = True)All these three tables are created when the following statement is executed −
Base.metadata.create_all(engine)
Next we create three objects of Department class and three objects of Employee class as shown below −
d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")
e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")Each table has a collection attribute having append() method. We can add Employee objects to Employees collection of Department object. Similarly, we can add Department objects to departments collection attribute of Employee objects.
e1.departments.append(d1) e2.departments.append(d3) d1.employees.append(e3) d2.employees.append(e2) d3.employees.append(e1) e3.departments.append(d2)
Although you can follow this tutorial independently, it is also a continuation of the How to Use One-to-Many Database Relationships with Flask-SQLAlchemy tutorial, in which you build a multi-table database with a one-to-many relationship between posts and comments in a blogging application.,In this step, you’ll use the Flask shell to add new posts to the database, add tags, and link between posts and tags. You’ll access posts with their tags, and you’ll see how to disassociate an item from another in Many-to-Many relationships.,Step 2 — Setting up Database Models for a Many-to-Many Relationship,(Optional) In Step 1, you’ll clone the blogging application you’ll be working on in this tutorial. However, you can optionally work through the tutorial How to Use One-to-Many Database Relationships with Flask-SQLAlchemy. You can access the final code from this page.
Clone the repository and rename it from flask-slqa-bloggy to flask_app with the following command:
git clone https: //github.com/do-community/flask-slqa-bloggy flask_appNavigate to flask_app:
cd flask_app
Then create a new virtual environment:
python - m venv env
Install Flask and Flask-SQLAlchemy:
pip install Flask Flask - SQLAlchemy
Next, set the following environment variables:
export FLASK_APP = app
export FLASK_ENV = developmentIf you want to use many-to-many relationships you will need to define a helper table that is used for the relationship. For this helper table it is strongly recommended to not use a model but an actual table:,The most common relationships are one-to-many relationships. Because relationships are declared before they are established you can use strings to refer to classes that are not created yet (for instance if Person defines a relationship to Address which is declared later in the file).,The baseclass for all your models is called db.Model. It’s stored on the SQLAlchemy instance you have to create. See Quickstart for more details.,Declaring Models Simple Example One-to-Many Relationships Many-to-Many Relationships
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
def __repr__(self):
return '<User %r>' % self.usernameclass Person(db.Model):
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(50), nullable = False)
addresses = db.relationship('Address', backref = 'person', lazy = True)
class Address(db.Model):
id = db.Column(db.Integer, primary_key = True)
email = db.Column(db.String(120), nullable = False)
person_id = db.Column(db.Integer, db.ForeignKey('person.id'),
nullable = False)class Person(db.Model):
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(50), nullable = False)
addresses = db.relationship('Address', lazy = 'select',
backref = db.backref('person', lazy = 'joined'))tags = db.Table('tags',
db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key = True),
db.Column('page_id', db.Integer, db.ForeignKey('page.id'), primary_key = True)
)
class Page(db.Model):
id = db.Column(db.Integer, primary_key = True)
tags = db.relationship('Tag', secondary = tags, lazy = 'subquery',
backref = db.backref('pages', lazy = True))
class Tag(db.Model):
id = db.Column(db.Integer, primary_key = True)Using our blog example, let's look at what a one-to-many relationship might look like for authors who have multiple posts or posts that have multiple comments:,Using the SQLAlchemy ORM to build data models with meaningful relationships. Create one-to-one, one-to-many, many-to-one, and many-to-many relationships.,When we perform a JOIN on SQLAlchemy models, we can utilize the relationship attribute of each record we fetch as though the attribute were an entire record in itself. It's probably easier just to show you what I mean.,All Python developers have something to gain from SQLAlchemy. Whether you're looking for a better way to manage database connections or build out an ORM data layer for your application, there's no reason for any of us to omit "pip install sqlalchemy" from our Python vocabulary.
"""Declare models and relationships."""
from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from database import engine
Base = declarative_base()
class User(Base):
"""User account."""
__tablename__ = "user"
id = Column(Integer, primary_key=True, autoincrement="auto")
username = Column(String(255), unique=True, nullable=False)
password = Column(Text, nullable=False)
email = Column(String(255), unique=True, nullable=False)
first_name = Column(String(255))
last_name = Column(String(255))
bio = Column(Text)
avatar_url = Column(Text)
role = Column(String(255))
last_seen = Column(DateTime)
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, onupdate=func.now())
def __repr__(self):
return f"<User {self.username}>"
class Comment(Base):
"""User-generated comment on a blog post."""
__tablename__ = "comment"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer)
post_id = Column(Integer, index=True)
body = Column(Text)
upvotes = Column(Integer, default=1)
removed = Column(Boolean, default=False)
created_at = Column(DateTime, server_default=func.now())
def __repr__(self):
return f"<Comment {self.id}>"
class Post(Base):
"""Blog post."""
__tablename__ = "post"
id = Column(Integer, primary_key=True, index=True)
author_id = Column(Integer)
slug = Column(String(255), nullable=False, unique=True)
title = Column(String(255), nullable=False)
summary = Column(String(400))
feature_image = Column(String(300))
body = Column(Text)
status = Column(String(255), nullable=False, default="unpublished")
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, server_default=func.now())
def __repr__(self):
return f"<Post {self.id}>"
Base.metadata.create_all(engine)...
from sqlalchemy.orm import relationship
class User(Base):
"""User account."""
__tablename__ = "user"
id = Column(Integer, primary_key=True, autoincrement="auto")
username = Column(String(255), unique=True, nullable=False)
password = Column(Text, nullable=False)
email = Column(String(255), unique=True, nullable=False)
first_name = Column(String(255))
last_name = Column(String(255))
bio = Column(Text)
avatar_url = Column(Text)
role = Column(String(255))
last_seen = Column(DateTime)
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, onupdate=func.now())
def __repr__(self):
return f"<User {self.username}>"
class Comment(Base):
"""User-generated comment on a blog post."""
__tablename__ = "comment"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("user.id")) # FK added
post_id = Column(Integer, ForeignKey("post.id"), index=True) # FK added
body = Column(Text)
upvotes = Column(Integer, default=1)
removed = Column(Boolean, default=False)
created_at = Column(DateTime, server_default=func.now())
# Relationships
user = relationship("User")
def __repr__(self):
return f"<Comment {self.id}>"
class Post(Base):
"""Blog post/article."""
__tablename__ = "post"
id = Column(Integer, primary_key=True, index=True)
author_id = Column(Integer, ForeignKey("user.id")) # FK added
slug = Column(String(255), nullable=False, unique=True)
title = Column(String(255), nullable=False)
summary = Column(String(400))
feature_image = Column(String(300))
body = Column(Text)
status = Column(String(255), nullable=False, default="unpublished")
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, server_default=func.now())
# Relationships
author = relationship("User")
comments = relationship("Comment")
def __repr__(self):
return f"<Post {self.id}>"...
author_id = Column(Integer, ForeignKey("user.id"))
......
author = relationship("User")
......
# Relationships
author = relationship("User", backref = "posts")
...from.models
import Post, User
admin_user = User(
username = "toddthebod",
password = "Password123lmao",
email = "todd@example.com",
first_name = "Todd",
last_name = "Birchard",
bio = "I write tutorials on the internet.",
avatar_url = "https://storage.googleapis.com/hackersandslackers-cdn/authors/todd_small@2x.jpg",
role = "admin",
)
post_1 = Post(
author_id = admin_user.id,
slug = "fake-post-slug",
title = "Fake Post Title",
status = "published",
summary = "A fake post to have some fake comments.",
feature_image = "https://cdn.hackersandslackers.com/2021/01/logo-smaller@2x.png",
body = "Cheese slices monterey jack cauliflower cheese dolcelatte cheese and wine fromage frais rubber cheese gouda. Rubber cheese cheese and wine cheeseburger cheesy grin paneer paneer taleggio caerphilly. Edam mozzarella.",
)
post_2 = Post(
author_id = admin_user.id,
slug = "an-additional-post",
title = "Yet Another Post Title",
status = "published",
summary = "An in-depth exploration into writing your second blog post.",
feature_image = "https://cdn.hackersandslackers.com/2021/01/logo-smaller@2x.png",
body = "Smelly cheese cheese slices fromage. Pepper jack taleggio monterey jack cheeseburger pepper jack swiss everyone loves. Cheeseburger say cheese brie fromage frais swiss when the cheese comes out everybody's happy babybel cheddar. Cheese and wine cheesy grin",
)