CODEWITHSUNDEEP
python
Brady forcier
Brady forcier

Reputation: 1947

Using variables in Triple Quotes (Python)

I'm trying to input 2 variables into an sql query like so:

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)

When I run this script I get:

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

I was thinking maybe it's trying to interpret the % as a format character, but I tried using %% to surround the %s and I get the same error. Any ideas?

Thanks

Upvotes: 1

Views: 729

Answers (2)

user5547025
user5547025

Reputation:

In this

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'

I see two invalid formats:

TRF%
%CLINICAL%

Upvotes: 0

Bernhard
Bernhard

Reputation: 8831

You need to '%'-quote the LIKE expression, not the format character:

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

Upvotes: 2

Related Questions