python之ORM的使用（2）

4.多外键关联                                                                  

下表中，Customer表中有两个字段都关联了Address表

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)

创建表结构好像没什么问题，但是插入数据时，或出现错误：

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
condition between parent/child tables on relationship
Customer.billing_address - there are multiple foreign key
paths linking the tables.  Specify the 'foreign_keys' argument,
providing a list of those columns which should be
counted as containing a foreign key reference to the parent table.

因为添加的两个外键之间分不清到底那个是哪个了，所以要让他们能够区分开：

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", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

这样子sqlachemy就能分得清那个外键是哪个字段了

5.多对多关系                                                                                 

这种需要应用的场景是：设计一个能够描述“图书”与“作者”的关系表结构，需求是：

　　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()

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

我们再创建一张第三张表，用来指向需要用到多对多的两张表，把他们关联到第三张表

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')),

总体代码：

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


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 | 跟老三学Python   | NULL     |
|  2 | 跟老三学把妹     | 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]

多对对删除

删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

author_obj =s.query(Author).filter_by(name="Jack").first()
 
book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()
 
book_obj.authors.remove(author_obj) #从一本书里删除一个作者
s.commit()

直接删除作者　

删除作者时，会把这个作者跟所有书的关联关系数据也自动删除

author_obj =s.query(Author).filter_by(name="Alex").first()
# print(author_obj.name , author_obj.books)
s.delete(author_obj)
s.commit()

处理中文：

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)

---恢复内容结束---