sqlalchemy(二)高级用法

本文将介绍sqlalchemy的高级用法。

外键以及relationship

首先创建数据库,在这里一个user对应多个address,因此需要在address上增加user_id这个外键(一对多)。

#!/usr/bin/env python
# encoding: utf-8

from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    addresses = relationship("Address", order_by="Address.id", backref="user")

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(32), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    #user = relationship("User", backref=backref('addresses', order_by=id))

engine  = create_engine('mysql://root:root@localhost:3306/test', echo=True)
#Base.metadata.create_all(engine)

接下来,调用用户和地址以添加数据[en]Next, call user and address to add data

>>> jack = User(name='jack')
>>> jack.address
Traceback (most recent call last):
  File "", line 1, in
AttributeError: 'User' object has no attribute 'address'
>>> jack.addresses
[]
>>> jack.addresses = [Address(email_address='test@test.com'), Address(email_address='test1@test1.com')]
>>> jack.addresses
[, ]
>>> session.add(jack)
>>> session.commit()
2015-08-19 13:45:36,237 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2015-08-19 13:45:36,237 INFO sqlalchemy.engine.base.Engine ()
2015-08-19 13:45:36,238 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2015-08-19 13:45:36,238 INFO sqlalchemy.engine.base.Engine ()
2015-08-19 13:45:36,239 INFO sqlalchemy.engine.base.Engine show collation where Charset = 'utf8' and Collation = 'utf8_bin'
2015-08-19 13:45:36,239 INFO sqlalchemy.engine.base.Engine ()
2015-08-19 13:45:36,239 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2015-08-19 13:45:36,239 INFO sqlalchemy.engine.base.Engine ()
2015-08-19 13:45:36,240 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2015-08-19 13:45:36,240 INFO sqlalchemy.engine.base.Engine ()
2015-08-19 13:45:36,240 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2015-08-19 13:45:36,240 INFO sqlalchemy.engine.base.Engine ()
2015-08-19 13:45:36,241 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-08-19 13:45:36,242 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name) VALUES (%s)
2015-08-19 13:45:36,242 INFO sqlalchemy.engine.base.Engine ('jack',)
2015-08-19 13:45:36,243 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (email_address, user_id) VALUES (%s, %s)
2015-08-19 13:45:36,243 INFO sqlalchemy.engine.base.Engine ('test@test.com', 1L)
2015-08-19 13:45:36,243 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (email_address, user_id) VALUES (%s, %s)
2015-08-19 13:45:36,243 INFO sqlalchemy.engine.base.Engine ('test1@test1.com', 1L)
2015-08-19 13:45:36,244 INFO sqlalchemy.engine.base.Engine COMMIT
>>>

此时,查看数据库并获取您刚刚插入的数据[en]At this point, look at the database and get the data you just inserted

mysql> select * from users;
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+
1 row in set (0.00 sec)

mysql> select * from addresses;
+----+-----------------+---------+
| id | email_address   | user_id |
+----+-----------------+---------+
|  1 | test@test.com   |       1 |
|  2 | test1@test1.com |       1 |
+----+-----------------+---------+
2 rows in set (0.00 sec)

join查询

如果不使用连接,则可以直接连接表查询[en]If you do not use join, you can directly join table queries

>>> session.query(User.name, Address.email_address).filter(User.id==Address.user_id).filter(Address.email_address=='test@test.com').all()
2015-08-19 14:02:02,877 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name, addresses.email_address AS addresses_email_address
FROM users, addresses
WHERE users.id = addresses.user_id AND addresses.email_address = %s
2015-08-19 14:02:02,878 INFO sqlalchemy.engine.base.Engine ('test@test.com',)
[('jack', 'test@test.com')]

在sqlalchemy中提供了Queqy.join()函数,

>>> session.query(User).join(Address).filter(Address.email_address=='test@test.com').first()
2015-08-19 14:06:56,624 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name
FROM users INNER JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = %s
 LIMIT %s
2015-08-19 14:06:56,624 INFO sqlalchemy.engine.base.Engine ('test@test.com', 1)
<demo.user object at 0x7f9a74139a10>
>>> session.query(User).join(Address).filter(Address.email_address=='test@test.com').first().name
2015-08-19 14:07:04,224 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name
FROM users INNER JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = %s
 LIMIT %s
2015-08-19 14:07:04,224 INFO sqlalchemy.engine.base.Engine ('test@test.com', 1)
'jack'
>>> session.query(User).join(Address).filter(Address.email_address=='test@test.com').first().addresses
2015-08-19 14:07:06,534 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name
FROM users INNER JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = %s
 LIMIT %s
2015-08-19 14:07:06,534 INFO sqlalchemy.engine.base.Engine ('test@test.com', 1)
2015-08-19 14:07:06,535 INFO sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses
WHERE %s = addresses.user_id ORDER BY addresses.id
2015-08-19 14:07:06,535 INFO sqlalchemy.engine.base.Engine (1L,)
[<demo.address object at 0x7f9a74139350>, <demo.address object at 0x7f9a741390d0>]
>>>
</demo.address></demo.address></demo.user>

请注意,上述用法的前提是,如果有外键,如果没有外键,则可以使用[en]Note that the premise of the above usage is that if there is a foreign key, if there is no foreign key, you can use the

query.join(Address, User.id==Address.user_id)    # explicit condition
query.join(User.addresses)                       # specify relationship from left to right
query.join(Address, User.addresses)              # same, with explicit target
query.join('addresses')

表的别名

>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address)

子查询

假设我们需要这样一个查询。[en]Suppose we need such a query.

mysql> SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
    ->     (SELECT user_id, count(*) AS address_count
    ->         FROM addresses GROUP BY user_id) AS adr_count
    ->     ON users.id=adr_count.user_id;
+----+------+---------------+
| id | name | address_count |
+----+------+---------------+
|  1 | jack |             2 |
+----+------+---------------+
1 row in set (0.00 sec)

#&#x3000;&#x751F;&#x6210;&#x5B50;&#x53E5;&#xFF0C;&#x7B49;&#x540C;&#x4E8E;&#xFF08;select user_id ... group_by user_id&#xFF09;
>>> sbq = session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()

# &#x8054;&#x63A5;&#x5B50;&#x53E5;&#xFF0C;&#x6CE8;&#x610F;&#x5B50;&#x53E5;&#x4E2D;&#x9700;&#x8981;&#x4F7F;&#x7528;c&#x6765;&#x8C03;&#x7528;&#x5B57;&#x6BB5;&#x5185;&#x5BB9;
>>> session.query(User.name, sbq.c.address_count).outerjoin(sbq, User.id==sbq.c.user_id).all()
2015-08-19 14:42:53,425 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name, anon_1.address_count AS anon_1_address_count
FROM users LEFT OUTER JOIN (SELECT addresses.user_id AS user_id, count(%s) AS address_count
FROM addresses GROUP BY addresses.user_id) AS anon_1 ON users.id = anon_1.user_id
2015-08-19 14:42:53,425 INFO sqlalchemy.engine.base.Engine ('*',)
[('jack', 2L)]
>>>

包含contains

query.filter(User.addresses.contains(someaddress))

数据删除delete

>>> session.delete(jack)
>>> session.query(User).filter_by(name='jack').count()
0

外键配置

在上面的示例中,删除了User-Jack,但不删除地址中的数据。[en]In the above example, the user-jack is deleted, but the data in the address is not deleted.

cascade字段用来

addresses = relationship("Address", backref='user',
    cascade="all, delete, delete-orphan")

Original: https://www.cnblogs.com/coder2012/p/4746941.html
Author: cococo点点
Title: sqlalchemy(二)高级用法

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/6038/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

发表回复

登录后才能评论
免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部