Python学习-day13 SqlAlchemy
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Python学习-day13 SqlAlchemy，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8589字，纯文字阅读大概需要13分钟。
内容图文
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.多对多关系
现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
- 一本书可以有好几个作者一起出版
- 一个作者可以写好几本书
此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为
当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了，太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
接下来创建几本书和作者
?| 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
本文系统来源：http://www.cnblogs.com/Darksugar/p/6972476.html
内容总结
以上是互联网集市为您收集整理的Python学习-day13 SqlAlchemy全部内容，希望文章能够帮你解决Python学习-day13 SqlAlchemy所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。