python - 在三引号中使用变量 (Python)

Question

我正在尝试将 2 个变量输入到 SQL 查询中，如下所示:

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)

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

  File "fetchDEVDB.py", line 57, in <module>
    """ % (gene, proEff)
ValueError: unsupported format character ''' (0x27) at index 1481

我想也许它试图将 % 解释为格式字符，但我尝试使用 %% 包围 %s 并得到相同的错误。有什么想法吗？

谢谢

Answer 1

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

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