在三引号中使用变量（Python）

Question

我尝试在sql查询中输入2个变量，如下所示：

query = """
Select Distinct
    sp.NAME,
    sp.STUDY,
    sp.DISEASE_ONTOLOGY_TERM,
    sv.GENE,
    sv.CDS_EFFECT,
    sv.PROTEIN_EFFECT,
    rep.STATUS,
    sv.FRACTION_READS,
    sv.DEPTH,
    cvmship.REMOVED
  From
    SPECIMEN sp
    inner join CURATION_MANAGER cm on (cm.SPECIMEN_ID = sp.SPECIMEN_ID)
    inner join CURATION_VERSION cv on (cv.CURATION_VERSION_ID = cm.LATEST_VERSION_ID)
    inner join CURATION_VERSION_MEMBERSHIP cvmship on (cvmship.VERSION_ID = cv.CURATION_VERSION_ID)
    inner join CURATION_VERSION_MEMBER cvmer on (cvmer.CURATION_VERSION_MEMBER_ID = cvmship.MEMBER_ID)
    inner join REPORTABLE rep on (rep.CURATION_VERSION_MEMBER_ID = cvmer.CURATION_VERSION_MEMBER_ID)
    inner join SHORT_VARIANT sv on (sv.REPORTABLE_ID = rep.CURATION_VERSION_MEMBER_ID)
    inner join (
      Select
        sp.SPECIMEN_ID,
        cqr.STATUS
      From
        SPECIMEN sp
        inner join CURATION_MANAGER cm on (cm.SPECIMEN_ID = sp.SPECIMEN_ID)
        inner join CURATION_VERSION cv on (cv.CURATION_VERSION_ID = cm.LATEST_VERSION_ID)
        inner join CURATION_VERSION_MEMBERSHIP cvmship on (cvmship.VERSION_ID = cv.CURATION_VERSION_ID)
        inner join CURATION_VERSION_MEMBER cvmer on (cvmer.CURATION_VERSION_MEMBER_ID = cvmship.MEMBER_ID)
        inner join CURATION_QC_RESULT cqr on (cqr.CURATION_VERSION_MEMBER_ID = cvmer.CURATION_VERSION_MEMBER_ID)
    ) cqr on (cqr.SPECIMEN_ID = sp.SPECIMEN_ID)
  Where sp.ASSIGNED_INDEX is not null
    AND sp.NAME like 'TRF%'
    AND LENGTH(sp.NAME) = 12
    AND cv.STATUS = 'final'
    AND (cqr.STATUS = 'Pass' or cqr.STATUS = 'Qualified')
    AND sp.STUDY like '%CLINICAL%'
    AND sv.GENE = '%s'
    AND sv.PROTEIN_EFFECT = '%s'

    order by sp.name desc
""" % (gene, proEff)

当我运行这个脚本时，我得到：

^{pr2}$

我在想也许是想把%解释成一个格式字符，但我试着用%%包围%s，结果得到了同样的错误。有什么想法吗？在

谢谢

Answer 1

在这个

AND sp.NAME like 'TRF%'
AND LENGTH(sp.NAME) = 12
AND cv.STATUS = 'final'
AND (cqr.STATUS = 'Pass' or cqr.STATUS = 'Qualified')
AND sp.STUDY like '%CLINICAL%'
AND sv.GENE = '%s'
AND sv.PROTEIN_EFFECT = '%s'

我看到两种无效格式：

^{pr2}$

Answer 2

您需要“%”-引用LIKE表达式，而不是格式字符：

...AND sp.NAME like 'TRF%%'
...
...AND sp.STUDY like '%%CLINICAL%%'