Python自动化之sqlalchemy关联查询

时间:2023-12-24 20:17:19

外键关联

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String(32), nullable=False)
user_id = Column(Integer, ForeignKey('user.id')) user = relationship("User", backref="addresses") #这个nb,允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项 def __repr__(self):
return "<Address(email_address='%s')>" % self.email_address

user = relationship("User", backref="addresses")这里到底是什么意思呢?在大学最起码学过一门关系型数据库,就是数据库里面的表与表之间的关联关系。

表创建好后,我们可以这样反查试试

obj = Session.query(User).first()
for i in obj.addresses: #通过user对象反查关联的addresses记录
print(i)
addr_obj = Session.query(Address).first()
print(addr_obj.user.name) #在addr_obj里直接查关联的user表

如果没有关联的内容,那么就会打印出空的列表[ ]

添加关联项

obj = Session.query(User).filter(User.name=='rain').all()[0]
print(obj.addresses) obj.addresses = [Address(email_address="r1@126.com"), #添加关联对象
Address(email_address="r2@126.com")] Session.commit()

多外键关联

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship Base = declarative_base() class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
name = Column(String) billing_address_id = Column(Integer, ForeignKey("address.id"))
shipping_address_id = Column(Integer, ForeignKey("address.id")) billing_address = relationship("Address")
shipping_address = relationship("Address") class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
street = Column(String)
city = Column(String)
state = Column(String)

多对多关系

现在来设计一个能描述“图书”与“作者”的关系的表结构,需求是

  1. 一本书可以有好几个作者一起出版
  2. 一个作者可以写好几本书

一本书可以有多个作者,一个作者又可以出版多本书

from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker Base = declarative_base() book_m2m_author = Table('book_m2m_author', Base.metadata,
Column('book_id',Integer,ForeignKey('books.id')),
Column('author_id',Integer,ForeignKey('authors.id')),
) class Book(Base):
__tablename__ = 'books'
id = Column(Integer,primary_key=True)
name = Column(String(64))
pub_date = Column(DATE)
authors = relationship('Author',secondary=book_m2m_author,backref='books') def __repr__(self):
return self.name class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
name = Column(String(32)) def __repr__(self):
return self.name orm 多对多

创建书和作者

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
s = Session_class() #生成session实例 b1 = Book(name="跟Alex学Python")
b2 = Book(name="跟Alex学把妹")
b3 = Book(name="跟Alex学装逼")
b4 = Book(name="跟Alex学开车") a1 = Author(name="Alex")
a2 = Author(name="Jack")
a3 = Author(name="Rain") b1.authors = [a1,a2]
b2.authors = [a1,a2,a3] s.add_all([b1,b2,b3,b4,a1,a2,a3]) s.commit()

此时,手动连上mysql,分别查看这3张表,你会发现,book_m2m_author中自动创建了多条纪录用来连接book和author表

mysql> select * from books;
+----+------------------+----------+
| id | name | pub_date |
+----+------------------+----------+
| 1 | 跟Alex学Python | NULL |
| 2 | 跟Alex学把妹 | NULL |
| 3 | 跟Alex学装逼 | NULL |
| 4 | 跟Alex学开车 | NULL |
+----+------------------+----------+
4 rows in set (0.00 sec) mysql> select * from authors;
+----+------+
| id | name |
+----+------+
| 10 | Alex |
| 11 | Jack |
| 12 | Rain |
+----+------+
3 rows in set (0.00 sec) mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
| 2 | 10 |
| 2 | 11 |
| 2 | 12 |
| 1 | 10 |
| 1 | 11 |
+---------+-----------+
5 rows in set (0.00 sec

此时,我们去用orm查一下数据

print('--------通过书表查关联的作者---------')

book_obj = s.query(Book).filter_by(name="跟Alex学Python").first()
print(book_obj.name, book_obj.authors) print('--------通过作者表查关联的书---------')
author_obj =s.query(Author).filter_by(name="Alex").first()
print(author_obj.name , author_obj.books)
s.commit()

输出如下

--------通过书表查关联的作者---------
跟Alex学Python [Alex, Jack]
--------通过作者表查关联的书---------
Alex [跟Alex学把妹, 跟Alex学Python]