Python自动化之sqlalchemy关联查询

外键关联

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)

多对多关系

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

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

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

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]

巴特西

Python自动化之sqlalchemy关联查询

外键关联

最新文章

热门文章