SQL Alchemy查询多个表
SQL Alchemy查询多个表
提问于 2017-07-03 14:46:31
我试图使用SQL炼金术进行查询，否则如果我不使用ORM，这将是非常简单的，所以我在想，肯定有一个简单的方法。关于这个话题，我已经回答了大部分的问题，但似乎没有回答我的问题。我有这两张桌子
class Artisan(Base):
__tablename__ = 'artisan'
name = Column(String(80), nullable=False)
skill = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
bio = Column(String(300))
category = Column(Integer, ForeignKey(Category.id))
user = Column(Integer, ForeignKey(Users.id))
id_no = Column(Integer, nullable=False)
users = relationship(Users)和
class Address(Base):
__tablename__ = 'address'
building = Column(String(80), nullable=False)
floor = Column(String(80), nullable=False)
house_no = Column(String(80), nullable=False)
telephone = Column(String(80), nullable=False)
kwetu_address = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
lat = Column(String(25))
lng = Column(String(25))
artisan = Column(Integer, ForeignKey(Artisan.id))
user = Column(Integer, ForeignKey(Users.id))
users = relationship(Users)我想按一个类别过滤艺人，然后通过过滤的工匠过滤地址，并以一种将工匠与各自地址相关联的方式呈现结果，希望能够得到sqlachemy来进行过滤。
我能想到的最好的方法包括两个查询和后置处理，我觉得这两个查询和后处理效率很低。
my_artisans = (session.query(Artisan).filter_by(category=cat_id))
my_addresses = (session.query(Address)
.join(Artisan, Artisan.id ==Address.artisan).filter_by(category=cat_id))
return jsonify(artisans =[art.serialize for art in my_artisans], addresses=[add.serialize for add in my_addresses])谢谢
添加所有相关类
import sys
from sqlalchemy import Column, ForeignKey, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
class Users(Base):
__tablename__ = 'users'
name = Column(String(80), nullable=False)
email = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
bio = Column(String(300))
picture = Column(String(80))
class Category(Base):
__tablename__ = 'category'
name = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
user = Column(Integer, ForeignKey(Users.id))
users = relationship(Users)
@property
def serialize(self):
return{
'id': self.id,
'name': self.name
}
class Artisan(Base):
__tablename__ = 'artisan'
name = Column(String(80), nullable=False)
skill = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
bio = Column(String(300))
category = Column(Integer, ForeignKey(Category.id))
user = Column(Integer, ForeignKey(Users.id))
id_no = Column(Integer, nullable=False)
users = relationship(Users)
@property
def serialize(self):
return{
'id': self.id,
'name': self.name,
'skill': self.skill,
'category': self.category,
'bio': self.bio,
'id_no': self.id_no
}
class Portfolio(Base):
__tablename__ = 'portfolio'
title = Column(String(80), nullable=False)
details = Column(String(300), nullable=False)
id = Column(Integer, primary_key=True)
artisan = Column(Integer, ForeignKey(Artisan.id))
user = Column(Integer, ForeignKey(Users.id))
users = relationship(Users)
@property
def serialize(self):
return{
'id': self.id,
'title': self.title,
'details': self.details
}
class Endorsements(Base):
__tablename__ = 'endorsements'
title = Column(String(80), nullable=False)
details = Column(String(300), nullable=False)
id = Column(Integer, primary_key=True)
artisan = Column(Integer, ForeignKey(Artisan.id))
user = Column(Integer, ForeignKey(Users.id))
users = relationship(Users)
@property
def serialize(self):
return{
'id': self.id,
'title': self.title,
'details': self.details
}
class Address(Base):
__tablename__ = 'address'
building = Column(String(80), nullable=False)
floor = Column(String(80), nullable=False)
house_no = Column(String(80), nullable=False)
telephone = Column(String(80), nullable=False)
kwetu_address = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
lat = Column(String(25))
lng = Column(String(25))
artisan = Column(Integer, ForeignKey(Artisan.id))
user = Column(Integer, ForeignKey(Users.id))
users = relationship(Users)
@property
def serialize(self):
return{
'id': self.id,
'lat': self.lat,
'lng': self.lng,
'kwetu_address': self.kwetu_address,
'artisan': self.artisan
}
engine = create_engine('sqlite:///mycatalog.db')
Base.metadata.create_all(engine)回答 1
Stack Overflow用户
回答已采纳
发布于 2017-07-04 08:21:12
这给了我在将我的工匠与各自的地址相关联方面所需的东西，尽管我仍然使用了两个查询。
my_artisans = (session.query(Artisan).filter_by(category=cat_id))
my_addresses = (session.query(Address)
.join(Artisan, Artisan.id ==Address.artisan).filter_by(category=cat_id))
these_addresses = []
for art in my_artisans:
art_id = art.id
for add in my_addresses:
if art_id == add.artisan:
grouped_address = {"Artisan Id" : art.id, "name" : art.name, "skill" : art.skill, "Lat" : add.lat, "lng" : add.lng}
these_addresses.append(grouped_address)
return jsonify({'Addresses': these_addresses }) 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44888512
复制相关文章
相似问题
腾讯云开发者
