CODEWITHSUNDEEP
pythonsqlalchemy
夏溪辰
夏溪辰

Reputation: 1991

why sqlachemy Table or class different？

I have a question for sqlalchemy;

Why the sqlalchemy orm Can't obj.select?

code on there. this orm：

from sqlalchemy import BigInteger, Boolean, Column, Date, Float, Index, Integer, JSON, MetaData, String, Table, Text, text
from sqlalchemy.dialects.postgresql import JSONB

metadata = MetaData()


t_clinicaltrials = Table(
    'clinicaltrials', metadata,
    Column('id', BigInteger, primary_key=True, server_default=text("nextval('shuimubio_auto_id.clinicaltrials_auto_id'::regclass)"), comment='数据库自增主键'),
    Column('nct_id', String, comment='Clinicaltrial的唯一ID'),
    Column('study_title', String, comment='标题'),
    Column('study_type', String, server_default=text("0"), comment="研究类型(现有四种'Expanded Access', 'Interventional', None, 'Observational')"),
    Column('phase_id_list', JSONB(astext_type=Text()), comment='研究阶段(逻辑外键表phase)'),
    Column('intervention_id_list', JSONB(astext_type=Text()), comment='介入方式(逻辑外键表 intervention_function 给药方式)'),
    Column('intervention_model', String, comment='干预模型'),
    Column('primary_purpose', String, comment='首要目标(主要的目的)'),
    Column('masking', String, comment='设盲方法'),
    Column('conditions_id_list', JSONB(astext_type=Text()), comment='适应症列表(逻辑外键表 condition)'),
    Column('number_enrolled', Integer, comment='入组人数'),
    Column('sex', String(16), comment='性别(逻辑外键表 sex)'),
    Column('study_age', JSONB(astext_type=Text()), comment='受试者年龄'),
    Column('study_start_date', Date, comment='试验开始时间'),
    Column('study_completion_date', Date, comment='试验结束时间'),
    Column('official_title', String, comment='官方标题'),
    Column('sponsor', String, comment='资助机构'),
    Column('collaborators', JSONB(astext_type=Text()), comment='合作者'),
    Column('result_first_posted', Date, comment='首次发布时间')
)

this metadata：

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata


class Clinicaltrial(Base):
    __tablename__ = 'clinicaltrials'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('shuimubio_auto_id.clinicaltrials_auto_id'::regclass)"), comment='数据库自增主键')
    nct_id = Column(String, comment='Clinicaltrial的唯一ID')
    study_title = Column(String, comment='标题')
    study_type = Column(String, server_default=text("0"), comment="研究类型(现有四种'Expanded Access', 'Interventional', None, 'Observational')")
    phase_id_list = Column(JSONB(astext_type=Text()), comment='研究阶段(逻辑外键表phase)')
    intervention_id_list = Column(JSONB(astext_type=Text()), comment='介入方式(逻辑外键表 intervention_function 给药方式)')
    intervention_model = Column(String, comment='干预模型')
    primary_purpose = Column(String, comment='首要目标(主要的目的)')
    masking = Column(String, comment='设盲方法')
    conditions_id_list = Column(JSONB(astext_type=Text()), comment='适应症列表(逻辑外键表 condition)')
    number_enrolled = Column(Integer, comment='入组人数')
    sex = Column(String(16), comment='性别(逻辑外键表 sex)')
    study_age = Column(JSONB(astext_type=Text()), comment='受试者年龄')
    study_start_date = Column(Date, comment='试验开始时间')
    study_completion_date = Column(Date, comment='试验结束时间')
    official_title = Column(String, comment='官方标题')
    sponsor = Column(String, comment='资助机构')
    collaborators = Column(JSONB(astext_type=Text()), comment='合作者')
    result_first_posted = Column(Date, comment='首次发布时间')

this code has error AttributeError: 'Query' object has no attribute 'filter_by'

Query(Clinicaltrial).filter_by(Uniprot.uniprot_id.in_(uniprot_Ids))

But this line of code will work。

select__filter = t_uniprot.select().filter(t_uniprot.columns["uniprot_id"].in_(uniprot_Ids))
return await database.fetch_all(select__filter)

What's the difference between the two and how to change this class orm Make it accessible for select attr

Upvotes: 0

Views: 62

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

According to the migration guide:

The Query object (as well as the BakedQuery and ShardedQuery extensions) become long term legacy objects, replaced by the direct usage of the select() construct in conjunction with the Session.execute() method.

So we can use select(…).where(…) for both ORM and Core (Table) queries:

from sqlalchemy import create_engine, Table, MetaData, Column, String,\
    BigInteger, select
from sqlalchemy.orm import declarative_base, Session

engine = create_engine("sqlite://")

Base = declarative_base()


class Clinicaltrial(Base):
    __tablename__ = 'clinicaltrials'

    id = Column(BigInteger, primary_key=True, autoincrement=False)
    nct_id = Column(String, comment='Clinicaltrial的唯一ID')

    def __repr__(self):
        return f"<Clinicaltrial(id={self.id})>"


Base.metadata.create_all(engine)

with engine.begin() as conn:
    conn.exec_driver_sql(
        "INSERT INTO clinicaltrials (id, nct_id) "
        "VALUES (1, 'test1'), (2, 'test2'), (3, 'test3')"
    )

with Session(engine) as sess:
    # ORM query
    q = select(Clinicaltrial).where(Clinicaltrial.id.in_([1, 3]))
    results = sess.execute(q).all()
    print(results)
    # [(<Clinicaltrial(id=1)>,), (<Clinicaltrial(id=3)>,)]

    # Core query
    ct = Table("clinicaltrials", MetaData(), autoload_with=engine)
    q = select(ct).where(ct.c.id.in_([1, 3]))
    results = sess.execute(q).all()
    print(results)
    # [(1, 'test1'), (3, 'test3')]

Upvotes: 1

Related Questions