创建数据库与sqlalchemy
的映射
SQLAlchemy
提供automap_base
,实现数据库与sqlalchemy ORM
的映射
from sqlalchemy.ext.automap import automap_base
engine = create_engine("mysql+pymysql://user:password@ip/database_name?charset=utf8")
Base = automap_base()
Base.prepare(engine)
创建数据表反射的ORM
# 方式一:Base.classes.表名
# Person = Base.classes.account_capchem
# 方式二:Base.classes["表名"]
Person = Base.classes["account_capchem"]
扩展为什么可以使用这两种方式
分析源码
- 打印
Base.classes
类型,等到<class 'sqlalchemy.util._collections.Properties'>
- 查看
Properties
类源码
class Properties(Generic[_T]):
"""Provide a __getattr__/__setattr__ interface over a dict."""
__slots__ = ("_data",)
_data: Dict[str, _T]
def __init__(self, data: Dict[str, _T]):
object.__setattr__(self, "_data", data)
"""
此处省略后面的代码
Properties类的路径:from sqlalchemy.util import Properties
"""
- 从源码可知
Properties
类初始化需要一个字典对象,定义一个Test
类继承Properties
from typing import Dict
from sqlalchemy.util import Properties
class Test(Properties):
def __init__(self, data: Dict):
super().__init__(data)
if __name__ == '__main__':
test = Test(dict(name="test", aaa=100))
print(test.get("name")) # test
print(test.name) # test
- 总结:可知是这个类帮我们实现可以使用以上两种方式获取数据库中表与
ORM
类的映射
使用数据表映射出来的ORM
查询数据表
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
engine = create_engine("mysql+pymysql://user:password@ip/database_name?charset=utf8")
Base = automap_base()
Base.prepare(engine)
# Person = Base.classes.person
Person = Base.classes["person"]
print(Person, type(Person))
print(Base.classes.keys())
print(type(Base.classes))
session = Session(engine)
data = session.query(Person).all()
print([{"id": i.id, "name": i.name, "age": i.age, "sex": i.sex} for i in data])
发散思考
Base.classes
有哪些key
?这些key
与数据表有什么关系
ks = Base.classes.keys()
print(ks)
结果发现ks
是数据库所有表的名称的列表。不包含数据库的视图!