Python与数据库[2] -> 关系对象映射/ORM[3] -> sqlalchemy 的声明层 ORM 访问方式

sqlalchemy的声明层ORM访问方式

sqlalchemy中可以利用声明层进行表格类的建立，并利用ORM对象进行数据库的操作及访问，另一种方式为显式的 ORM 访问方式。

主要的建立步骤包括:

1. 设置DSN连接网址;

2. 创建引擎，利用引擎对DSN进行连接;

3. 创建会话类并绑定到引擎，创建引擎实例;

4. 创建表类，由于使用声明层元数据;

5. 利用会话实例来进行数据库操作。

基本连接关系图:

下面是完整代码

 1 from sqlalchemy import create_engine, MetaData
 2 from sqlalchemy.ext.declarative import declarative_base
 3 from sqlalchemy.sql.schema import Table, Column
 4 from sqlalchemy.sql.sqltypes import Integer, VARCHAR
 5 from sqlalchemy.orm import sessionmaker
 6 
 7 Base = declarative_base()
 8 
 9 # Set DSN
10 dsn = 'mysql+mysqldb://root:root@localhost/test_db'
11 
12 # Create engine, get connection
13 eng = create_engine(dsn)
14 cnx = eng.connect()
15 
16 # Make a Session class binded with engine
17 Session = sessionmaker(bind=eng)
18 # Generate a session instance
19 session = Session()
20 
21 # Build up Table class
22 class Test_tb(Base):
23     __table__ = Table('test_tb', Base.metadata, 
24                         Column('id', Integer, primary_key=True),
25                         Column('name', VARCHAR(8))
26                         )
27 
28 
29 # Insert value
30 # session.add_all([Test_tb(id=6, name='Momo')])
31 session.add(Test_tb(id=6, name='Momo'))
32 session.commit()
33 
34 # Delete value
35 session.query(Test_tb).filter(Test_tb.id==6).delete()
36 session.commit()
37 
38 session.add_all([Test_tb(id=7, name='Momo'), Test_tb(id=8, name='Kitkat')])
39 session.commit()
40 # Update value
41 fr = session.query(Test_tb).filter_by(id=7).all()
42 for f in fr:
43     f.id = 9
44 session.query(Test_tb).filter(Test_tb.id==8).delete()
45 session.query(Test_tb).filter(Test_tb.id==9).delete()
46 session.commit()
47 
48 re = session.query(Test_tb).all()
49 # Show value: Each r is a row in table
50 for r in re:
51     print(r.__dict__)
52 
53 cnx.close()

View Code

分段分析

首先导入所需的模块，创建声明层基类

 1 from sqlalchemy import create_engine, MetaData
 2 from sqlalchemy.ext.declarative import declarative_base
 3 from sqlalchemy.sql.schema import Table, Column
 4 from sqlalchemy.sql.sqltypes import Integer, VARCHAR
 5 from sqlalchemy.orm import sessionmaker
 6 
 7 Base = declarative_base()
 8 
 9 # Set DSN
10 dsn = 'mysql+mysqldb://root:root@localhost/test_db'

创建引擎，以及会话实例

1 # Create engine, get connection
2 eng = create_engine(dsn)
3 cnx = eng.connect()
4 
5 # Make a Session class binded with engine
6 Session = sessionmaker(bind=eng)
7 # Generate a session instance
8 session = Session()

基于声明层创建表格类

1 # Build up Table class
2 class Test_tb(Base):
3     __table__ = Table('test_tb', Base.metadata, 
4                         Column('id', Integer, primary_key=True),
5                         Column('name', VARCHAR(8))
6                         )

最后利用会话实例对表格进行操作

 1 # Insert value
 2 # session.add_all([Test_tb(id=6, name='Momo')])
 3 session.add(Test_tb(id=6, name='Momo'))
 4 session.commit()
 5 
 6 # Delete value
 7 session.query(Test_tb).filter(Test_tb.id==6).delete()
 8 session.commit()
 9 
10 session.add_all([Test_tb(id=7, name='Momo'), Test_tb(id=8, name='Kitkat')])
11 session.commit()
12 # Update value
13 fr = session.query(Test_tb).filter_by(id=7).all()
14 for f in fr:
15     f.id = 9
16 session.query(Test_tb).filter(Test_tb.id==8).delete()
17 session.query(Test_tb).filter(Test_tb.id==9).delete()
18 session.commit()
19 
20 re = session.query(Test_tb).all()
21 # Show value: Each r is a row in table
22 for r in re:
23     print(r.__dict__)
24 
25 cnx.close()

最终运行结果

{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x000000000489F320>, 'id': 3, 'name': 'eric'}
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x000000000489F390>, 'id': 5, 'name': 'mock'}

Python与数据库[2] -> 关系对象映射/ORM[3] -> sqlalchemy 的声明层 ORM 访问方式

sqlalchemy的声明层ORM访问方式

相关阅读