Python SQLAlchemy

本文為 Python SQLAlchemy ORM 一系列教學文：

刪除

學會如何查詢之後，就能夠進行後續的刪除、更新等操作。

同樣地，以幾個範例做為學習的捷徑。

1
2
3
4
5
6
7
8
9
user_1 = User('user1', 'username1', 'password_1')
user_2 = User('user2', 'username2', 'password_2')
session.add(user_1)
session.add(user_2)
affected_rows = session.query(User).filter_by(id=1).delete()
print('Affected rows:', affected_rows)
if session.query(User).filter_by(id=1).count() == 0:
    print('id 1 not found')
delete()
delete()

更新

update()
1
2
3
4
5
6
7
8
9
user_1 = User('user1', 'username1', 'password_1')
user_2 = User('user2', 'username2', 'password_2')
session.add(user_1)
session.add(user_2)
affected_rows = session.query(User).filter_by(id=1).update({'id':3, 'username': 'sqlalchemy'})
print('Affected rows:', affected_rows)
for r in session.query(User):
    print(r.id, r.username)

表格關聯(Relationship)

SQLAlchemy ORM 最大的特點就是能夠透過 Python 類別間關聯的建立，實作資料庫表格間的關聯，能夠讓程式開發者很方便的取得相關聯的資料。

而關聯的種類有：

  • One to Many
  • Many to one
  • One to one
  • Many to Many

分別代表一筆資料與另一個表格的資料間的關係。

如果有興趣了解的人可以詳閱 Basic Relationship Patterns 。

接下來同樣用一個範例了解 SQLAlchemy ORM 的表格關聯。範例中，除了原先已經定義過的 User 類別之外，還會再多定義一個 Address 類別，兩者間的關係為一對多，代表一個 user 允許有多個 address 。

一對多關聯

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
# -*- coding: utf-8 -*-
import hashlib
import sqlalchemy.orm
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import desc
from sqlalchemy.orm import aliased
from sqlalchemy import func
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
    name = Column(String)
    username = Column(String)
    password = Column(String)
def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()
def __repr__(self):
        return "User('{}','{}', '{}')".format(
            self.name,
            self.username,
            self.password
        )
class Address(Base):
__tablename__ = 'user_address'
id = Column(Integer, primary_key=True)
    address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship('User', backref=backref('address', order_by=id))
def __init__(self, address):
        self.address = address
def __repr__(self):
        return "Address('{}')".format(self.address)
if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:', echo=True)
    Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
    session = Session()
user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
    user_2 = User('user2', 'username2', 'password_2'.encode('utf-8'))
    user_1.address = [Address('臺灣凱達格蘭大道')]
    user_2.address = [Address('美國紐約時代廣場'), Address('美國華盛頓DC')]
    session.add(user_1)
    session.add(user_2)
for r in session.query(Address):
        print(r.id, r.address, r.user_id)
for r in session.query(User):
        for a in r.address:
            print(r.username, 'living at', a.address)

範例說明：

ForeignKeyrelationshipbackref
Foreign Key
relationship()backref('address', order_by=id)address
relationship('User')backrefbackref('address', order_by=id)

backref – indicates the string name of a property to be placed on the
related mapper’s class that will handle this relationship in the other
direction. The other property will be created automatically when the
mappers are configured. Can also be passed as a backref() object to
control the configuration of the new relationship.

user_1user_2
relationship('User', backref=backref('address', order_by=id))backref('address', order_by=id)

一對一關聯

user_2.address = [Address('美國紐約時代廣場'), Address('美國華盛頓DC')]
backref()uselist=False

例如前述範例的第 45 行可變更為：

1
backref('address', uselist=False, order_by=id)

那麼在第 62, 63 行就不需再以 list 的形式指定，同時在第 71 行也不需要再多一個迴圈將各別的 Address 取出。 多對一關聯 同樣以 User, Address 兩個類別作為例子，試想當多個使用者住在同一個地址時，就是多對一的情況。

relationship()backref

多對一關聯範例如下：

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
# -*- coding: utf-8 -*-
import hashlib
import sqlalchemy.orm
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import desc
from sqlalchemy.orm import aliased
from sqlalchemy import func
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
    name = Column(String)
    username = Column(String)
    password = Column(String)
    address_id = Column(Integer, ForeignKey('user_address.id'))
    address = relationship('Address')
def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()
def __repr__(self):
        return "User('{}','{}', '{}')".format(
            self.name,
            self.username,
            self.password
        )
class Address(Base):
__tablename__ = 'user_address'
id = Column(Integer, primary_key=True)
    address = Column(String, nullable=False)
def __init__(self, address):
        self.address = address
def __repr__(self):
        return "Address('{}')".format(self.address)
if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:', echo=True)
    Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
    session = Session()
user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
    user_2 = User('user2', 'username2', 'password_2'.encode('utf-8'))
    address_1 = Address('臺灣凱達格蘭大道')
    user_1.address = address_1
    user_2.address = address_1
    session.add(user_1)
    session.add(user_2)
for r in session.query(User):
        print(r.username, 'living at', r.address.address)
        print('Address_id:', r.address_id)

多對多關聯

association tableassociation table

接下來同樣用一個範例說明：

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# -*- coding: utf-8 -*-
import hashlib
import sqlalchemy.orm
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import desc
from sqlalchemy.orm import aliased
from sqlalchemy import func
from sqlalchemy import Table
Base = declarative_base()
association_table = Table(
    'association',
    Base.metadata,
    Column('table_a_id', Integer, ForeignKey('table_a.id')),
    Column('table_b_id', Integer, ForeignKey('table_b.id'))
)
class A(Base):
    __tablename__ = 'table_a'
    id = Column(Integer, primary_key=True)
    children = relationship('B', secondary=association_table)
class B(Base):
    __tablename__ = 'table_b'
    id = Column(Integer, primary_key=True)
if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
    session = Session()
b_list = [B(), B(), B()]
    a_1 = A()
    a_2 = A()
    a_1.children = b_list
    a_2.children = b_list
    session.add(a_1)
    session.add(a_2)
    session.commit()
for a in session.query(A):
        print('A:', a.id, 'has relationship with')
        for b in a.children:
            print('\tB:', b.id)

以下是執行結果：

1
2
3
4
5
6
7
8
A: 1 has relationship with
    B: 1
    B: 2
    B: 3
A: 2 has relationship with
    B: 1
    B: 2
    B: 3
sessionmaker()echo=True

如下所示：

1
2
2013-09-01 11:46:20,862 INFO sqlalchemy.engine.base.Engine INSERT INTO association (table_a_id, table_b_id) VALUES (?, ?)
2013-09-02 11:46:20,862 INFO sqlalchemy.engine.base.Engine ((1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3))

範例說明：

Table()Metadatadeclarative base classForeignKey()table_atable_b
relationship('B', secondary=association_table)secondary=association_tableassociation_table

最後在第 52 - 55 行將資料庫內的結果列印出來。

relationship()backref=backref('children')children

多對多關聯的刪除

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

結語

至此，已解說完大部份的 SQLAlchemy ORM 的功能。剩下的功能就得靠各位自行探索囉！

相关文章

最新内容
推荐内容

Copyright © 2008-2026 五速梦信息网 版权所有。

蜀ICP备2025158979号-1 | 川公网安备51010502015432号 网站地图

本站所有信息均来源于互联网搜集，并不代表本站观点，本站不对其真实合法性负责。如有信息侵犯了您的权益，请告知，本站将立刻删除。