inner join on 加条件和where加条件_SQLAlchemy(8)Join查询

10220

16.join 查询

创建表

rom sqlalchemy import Column,String,Integer
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship


# 1.创建连接
engine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101/test')
# 2.创建 session池,并实例化
Session = sessionmaker(bind=engine)
session = Session()
# 3.创建基类
Base = declarative_base()

# 创建Model
class User1(Base):
   __tablename__ = 'user_1'

   id = Column(Integer, primary_key=True, nullable=False)
   name = Column(String(45))
   fullname = Column(String(45))
   nickname = Column(String(45))
   # 定义双向对应,连接的是 Model
   address = relationship('Address1', back_populates='user')

class Address1(Base):
   __tablename__ = 'addresses_1'

   id = Column(Integer, primary_key=True, nullable=False)
   email_address = Column(String(45))
   # 定义外键
   user_id = Column(Integer, ForeignKey('user_1.id'))
   # 定义双向对应
   user = relationship('User1', back_populates='address')
  
# 创建表
Base.metadate.create_all()

# 添加数据
Jack=User1(id=1,name='Jack', fullname="Jack Ning", nickname='Ningning')
Jack.address = [
Address1(id=1, email_address='xxx@xx1.com', user_id=1),
Address1(id=2, email_address='xxx@xx2.com', user_id=1),
Address1(id=3, email_address='xxx@xx3.com', user_id=1)
]

session.add(Jack)
session.commit()

执行join 查询,默认是内连接.

---

普通的交叉连接可以这样查询

q1 = session.query(User1, Address1).all()

通过日志查看,它相当查询了一个笛卡尔积.

[root@localhost ~]# tail -5 /mysql_data/localhost.log
SELECT user_1.id AS user_1_id, user_1.name AS user_1_name, user_1.fullname AS user_1_fullname, user_1.nickname AS user_1_nickname, addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id 
FROM user_1, addresses_1

使用Queue.join() 查询

>>> q2 = session.query(User1).join(Address1).filter(Address1.email_address == 'xxx@xx3.com')
>>> print(q2)
SELECT user_1.id AS user_1_id, user_1.name AS user_1_name, user_1.fullname AS user_1_fullname, user_1.nickname AS user_1_nickname 
FROM user_1 INNER JOIN addresses_1 ON user_1.id = addresses_1.user_id 
WHERE addresses_1.email_address = %(email_address_1)s

# 它相当于要执行如上语句,但是并没有去执行,调用 one(),get(),all() 去执行语句
>>> q2.all()

调用all() 之后,查看日志

[root@localhost ~]# tail -5 /mysql_data/localhost.log
...
SELECT user_1.id AS user_1_id, user_1.name AS user_1_name, user_1.fullname AS user_1_fullname, user_1.nickname AS user_1_nickname 
FROM user_1 INNER JOIN addresses_1 ON user_1.id = addresses_1.user_id 
WHERE addresses_1.email_address = 'xxx@xx3.com'

可以看到,如果join()直接写Address1 ,相当于一个内连接,filter 在这里扮演了where 子句的作用.

那么在SQL 语句中,join...on... 是有on子句做条件的.对应到sqlalchemy的join函数,这个函数会处理join...on 的判断条件.因为User1 和Address1 是通过外键连接的,也就是表user_1和addresses_1 是通过外键连接的,这里join(Address1) 虽然没有写条件,但是join()  函数会自动通过外键去连接这两张表.

User_1 和Address_1 他们的关系却是双向的,也就是说,除了Address_1 代表表addresses_1,也可以通过User_1.address 这个属性去代表address_1 这张表.

q4 = session.query(User1).join(User1.address)
print(q4)
"""
SELECT user_1.id AS user_1_id, user_1.name AS user_1_name, user_1.fullname AS user_1_fullname, user_1.nickname AS user_1_nickname 
FROM user_1 INNER JOIN addresses_1 ON user_1.id = addresses_1.user_id
"""

join()  函数还可以容纳多个on子句(表与表之间通过外键连接).比如:

class oders(Base):
 pass
class Items(Base):
 pass

q4 = session.query(User1).join(Address_1, Oders, Items)

# 通过属性连接,相当于
q4 = session.query(User1).join(User1.address, 
                         Address_1.oders,
                         Oders.items)
# 表与表之间是通过外键连接的

join() 函数还可以加入Table 对象.比如:

# 比如多对多中的Table对象,
# 新建一个Table对象,有外键连接
table_test = Table('table_test', Base.metadata,
             Column('c_id', Integer, ForeignKey('user_1.id'), nullable=False),
             )
# 内连接 外键是条件判断对象
q4 = session.query(User1).join(table_test)

join() 函数还可以显式的把on子句加入:

q5 = session.query(User1).join(Address1, User1.id == Address1.id)
print(q5)

"""
SELECT user_1.id AS user_1_id, user_1.name AS user_1_name, user_1.fullname AS user_1_fullname, user_1.nickname AS user_1_nickname 
FROM user_1 INNER JOIN addresses_1 ON user_1.id = addresses_1.id
"""

使用别名查询: 在多个表中查询时,如果同一个表需要多次应用,可以给表起个别名.比如

from sqlalchemy.orm import aliased

a1 = aliased(Address1)
a2 = aliased(Address1)

test1 = session.query(User1.name, a1.email_address, a2.email_address)
print(test1)
"""
SELECT user_1.name AS user_1_name, addresses_1_1.email_address AS addresses_1_1_email_address, addresses_1_2.email_address AS addresses_1_2_email_address 
FROM user_1, addresses_1 AS addresses_1_1, addresses_1 AS addresses_1_2
"""
test3=session.query(User1.name, a1.email_address, a2.email_address).join(a1, User1.id == a1.user_id).join(a2, User1.id == a2.user_id)
print(test3)
"""
SELECT user_1.name AS user_1_name, addresses_1_1.email_address AS addresses_1_1_email_address, addresses_1_2.email_address AS addresses_1_2_email_address 
FROM user_1 INNER JOIN addresses_1 AS addresses_1_1 ON user_1.id = addresses_1_1.user_id INNER JOIN addresses_1 AS addresses_1_2 ON user_1.id = addresses_1_2.user_id
"""
test4 = session.query(User1.name, a1.email_address, a2.email_address).join(a1, User1.id == a1.user_id).join(a2, User1.id == a2.user_id).filter(a1.email_address=='xxx@xx1.com').filter(a2.email_address=='xxx@xx2.com')
print(test4)
"""
SELECT user_1.name AS user_1_name, addresses_1_1.email_address AS addresses_1_1_email_address, addresses_1_2.email_address AS addresses_1_2_email_address 
FROM user_1 INNER JOIN addresses_1 AS addresses_1_1 ON user_1.id = addresses_1_1.user_id INNER JOIN addresses_1 AS addresses_1_2 ON user_1.id = addresses_1_2.user_id 
WHERE addresses_1_1.email_address = %(email_address_1)s AND addresses_1_2.email_address = %(email_address_2)s
"""

更多的查询阅读官网

https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.join
https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying-with-joins

17.outerjoin()

左连接,用法和join() 一样.

- END -