#通过实体类映射--创建索引
users = Table('t_role', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('roleKey', String(50)),
Column('description', String(250)),
Column('state', CHAR(1)), )
users.create()
##手写sql查询
result = engine.execute(text('select id_drg,id_mdc from current_data where id_drg = :id_drg'), {'id_drg': "J42x00"})
res=result.fetchall()
df=pd.DataFrame(res,columns=["id_drg","id_mdc"])
ss=df[["id_drg","id_mdc"]]
print(res)
#sql-expression 查询
# %% SQL-expressions in Python 方法
meta = MetaData(bind=engine, reflect=True)
table = meta.tables['current_data']
result2 = list(engine.execute(table.select(table.c.id_drg == "J42x00"))) # id_drg为current_data的一个列名
##ORM 方法查询---如果没有数据表没有与实体类做映射,则使用 .c 来使用数据库字段作为字段筛选。
# %% ORM 方法 表中要有主键
engine.echo = True # We want to see the SQL we're creating
metadata = MetaData(engine)
# The stock_basics table already exists, so no need to redefine it. Just
# load it from the database using the "autoload" feature.
# %% 将数据库中的对象映射到对象中
users = Table('current_data', metadata, autoload=True)
s = users.select(users.c.id_drg == 'J42x00')
rs = s.execute().fetchall()
s = users.select(and_(users.c.id_drg == 'J42x00', users.c.id_mdc == 'M10'))
s = users.select(or_(users.c.id_drg == 'J42x00', users.c.id_mdc == 'M10'))
s = users.select((users.c.id_drg == 'J42x00') & (users.c.id_mdc == 'M10'))
s = users.select((users.c.id_drg == 'J42x00') | (users.c.id_mdc == 'M10'))
s = users.select(users.c.id_mdc.startswith('M'))
s = users.select(users.c.id_mdc.like('%a%'))
s = users.select(users.c.id_mdc.endswith('n'))
#"in" and "between"
s = users.select(users.c.age.between(30, 39))
s = users.select(users.c.name.in_('Mary', 'Susan'))
#SQL function, use "func"
s = users.select(func.substr(users.c.name, 2, 1) == 'a')
#count()
s = select([func.count(users.c.user_id)])
#count(*)
s = select([func.count("*")], from_obj=[users])
rs = s.execute().fetchall()
##使用session查询
Session = sessionmaker(bind=engine)#绑定engine,生成全局session
session = Session()
#res=session.query(users).filter(users.c.name=='系统管理员').all()
res = session.query(users).first()
print(res)
##查询结果封装为实体类
class Role(object):
def __init__(self, id, name, state):
self.id = id
self.name = name
self.state = state
def __repr__(self):
return "<Role(name='%s', id='%s', state='%s')>" % (
self.name, self.id, self.state)
roles= Table('t_role', metadata, autoload=True)
mapper(Role, roles)
res=session.query(Role).filter_by(name='系统管理员').first()
#res=session.query(Role).filter_by(name='系统管理员').yield_per(1000)#当返回的数据量很大时,指定批量返回,不用一次生成足够的内存区域,提高数据提取性能。
Session 是一个直接实例化的常规的Python 类。然而, 为了标准会会话的配置和获取方式, sessionmaker 类通常用于创建顶级会话配置, 然后可以在整个应用程序中使用它, 而不是需要重复配置参数。