Python学习-day13 SqlAlchemy

 parameter is a newer version of a very common SQLAlchemy feature calledrelationship.backref. The relationship.backref parameter hasn’t gone anywhere and will always remain available! The relationship.back_populates is the same thing, except a little more verbose and easier to manipulate. For an overview of the entire topic, see the section Linking Relationships with Backref.　　

　　

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

?

1 2 3 4 5 6

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表

创建关联对象

?

1 2 3 4 5 6 7 8

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

 

 

常用查询语法

Common Filter Operators

Here’s a rundown of some of the most common operators used in filter():

• equals:

       query.filter(User.name == ‘ed‘)
  

• not equals:

       query.filter(User.name != ‘ed‘)
  

• LIKE:

  query.filter(User.name.like(‘%ed%‘))

• IN:

• NOT IN: query.filter(~User.name.in_([‘ed‘, ‘wendy‘, ‘jack‘]))

• IS NULL:

• IS NOT NULL:

• AND: 2.1. ObjectRelationalTutorial 17

query.filter(User.name.in_([‘ed‘, ‘wendy‘, ‘jack‘]))

# works with query objects too:

query.filter(User.name.in_( session.query(User.name).filter(User.name.like(‘%ed%‘))

))

query.filter(User.name == None)

# alternatively, if pep8/linters are a concern

query.filter(User.name.is_(None))

query.filter(User.name != None)

# alternatively, if pep8/linters are a concern

query.filter(User.name.isnot(None))

SQLAlchemy Documentation, Release 1.1.0b1

# use and_()

from sqlalchemy import and_ query.filter(and_(User.name == ‘ed‘, User.fullname == ‘Ed Jones‘))

# or send multiple expressions to .filter()

query.filter(User.name == ‘ed‘, User.fullname == ‘Ed Jones‘)

# or chain multiple filter()/filter_by() calls

query.filter(User.name == ‘ed‘).filter(User.fullname == ‘Ed Jones‘)

Note: Makesureyouuseand_()andnotthePythonandoperator! ? OR:

Note: Makesureyouuseor_()andnotthePythonoroperator! ? MATCH:

query.filter(User.name.match(‘wendy‘)) Note: match() uses a database-specific MATCH or CONTAINS f 

 

　　

4.多外键关联

One of the most common situations to deal with is when there are more than one foreign key path between two tables.

Consider a Customer class that contains two foreign keys to an Address class:

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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)

创建表结构是没有问题的，但你Address表中插入数据时会报下面的错

?

1 2 3 4 5 6

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.

解决办法如下

?

1 2 3 4 5 6 7 8 9 10

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. 一个作者可以写好几本书

此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为

当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了，太low b了，改书名还得都改。。。

 

那怎么办呢？ 此时，我们可以再搞出一张中间表，就可以了

这样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联

用orm如何表示呢？

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


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 多对多

 

接下来创建几本书和作者

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

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查一下数据

?

1 2 3 4 5 6 7 8 9

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

输出如下

?

1 2 3 4

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

牛逼了我的哥！！完善实现多对多　　

　　

多对多删除

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

通过书删除作者

?

1 2 3 4 5 6

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

直接删除作者　

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

?

1 2 3 4

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)

　

Python学习-day13 SqlAlchemy

标签：ora   anywhere   方式   height   led   org   tip   opera   border