sqlalchemy 中 automap的使用
问题
- 有时候 我们可能希望映射数据库中已经有的表,怎么办呢?
sqlalchemy 给我们提供了这样的一个方法,方便我们映射 数据库中的已经存在的表, 用法 也比较简单 ,automap_base
这个对象,就可以完成我们想映射表的需求了。
- 来看一个小例子
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@Time : 2018/11/23 09:45
@File : myautomap.py
@Author : frank.chang@shoufuyou.com
"""
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.schema import Table
from datetime import datetime
from secure import XINYONGFEI_BI_URL
def to_dict(self):
return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}
Base = automap_base()
# engine, suppose it has two tables 'Bank' and 'BidOrder' set up
engine = create_engine(XINYONGFEI_BI_URL, pool_size=10, pool_recycle=7200,
pool_pre_ping=True, encoding='utf-8')
# reflect the tables
Base.prepare(engine, reflect=True)
# mapped classes are now created with names by default
# matching that of the table name.
# notice 注意这里 Bank 对应数据库中的表
Bank = Base.classes.Bank
session_factory = sessionmaker(bind=engine)
session = session_factory()
# 查询数据
query = session.query(Bank).filter_by(name='frank')
print(query)
m = query.first()
mydict = to_dict(m)
for k, v in mydict.items():
print(k, v)
if __name__ == '__main__':
data = {'id': 22, 'name': 'frank2', 'short_name': 'fk2', 'code': '10000', 'limit_daily': 3000,
'limit_single': 120000, 'status': 'valid', 'preset_id': 0, 'created_user': 2,
'created_time': datetime(2018, 9, 14, 15, 27, 57),
'updated_time': datetime(2018, 11, 22, 15, 29, 32)
}
bank = Bank(**data)
# 添加一条数据
session.add(bank)
session.commit()
XINYONGFEI_BI_URL 这个是接数据库的URL
具体可以参考官方给的链接:
database-urls
XINYONGFEI_BI_URL = ‘mysql+pymysql://xxxx:xxxxxxxx#@xxxxx:xxxxxxx/xinyongfei_db?charset=utf8’
结果如下:
SELECT `Bank`.id AS `Bank_id`, `Bank`.name AS `Bank_name`, `Bank`.short_name AS `Bank_short_name`, `Bank`.code AS `Bank_code`, `Bank`.limit_daily AS `Bank_limit_daily`, `Bank`.limit_single AS `Bank_limit_single`, `Bank`.status AS `Bank_status`, `Bank`.preset_id AS `Bank_preset_id`, `Bank`.created_user AS `Bank_created_user`, `Bank`.created_time AS `Bank_created_time`, `Bank`.updated_time AS `Bank_updated_time`
FROM `Bank`
WHERE `Bank`.name = %(name_1)s
id 10
name frank
short_name fd
code fsa
limit_daily 3000
limit_single 120000
status valid
preset_id 0
created_user 2
created_time 2018-09-14 15:27:57
updated_time 2018-11-22 15:29:32
这里会把 所有的表映射出来,engine 里面所有已经存在的表,都可以通过这种方式映射出来。
2.有的时候 我们可能只需要 映射部分表,也是有这个需求吧。
sqlalchemy 已经给我们提供了这样的接口
我们可以 自己生成一个 meta 对象 ,之后把这个 传给 auto_base() 就可以自定义 ,需要反射的表了。
- 来看下一个例子
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@Time : 2018/11/23 19:45
@File : myautomap.py
@Author : frank.chang@shoufuyou.com
"""
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy import create_engine, MetaData
from secure import XINYONGFEI_BI_URL
def to_dict(self):
return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}
metadata = MetaData()
# engine, suppose it has two tables 'Bank' and 'BidOrder' set up
engine = create_engine(XINYONGFEI_BI_URL, pool_size=10, pool_recycle=7200,
pool_pre_ping=True, encoding='utf-8')
session_factory = sessionmaker(bind=engine)
session = session_factory()
metadata.reflect(engine, only=['BidOrder', 'Book', 'TUser'])
Base = automap_base(metadata=metadata)
# calling prepare() just sets up mapped classes and relationships.
Base.prepare()
# mapped classes are now created with names by default
# matching that of the table name.
TUser = Base.classes.TUser
Book = Base.classes.Book
# 这里 没有Bank
# Bank = Base.classes.Bank
# print(Bank)
print(TUser)
print(Book)
print(type(TUser))
print(type(Book))
if __name__ == '__main__':
pass
结果如下:
Connected to pydev debugger (build 182.4505.26)
<class 'sqlalchemy.ext.automap.TUser'>
<class 'sqlalchemy.ext.automap.Book'>
<class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>
<class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>
这个only 参数 就可以指定要映射的表
metadata.reflect(engine, only=[‘BidOrder’, ‘Book’, ‘TUser’])
如果 这个时候取bank 就会报错了。 Bank = Base.classes.Bank