python - 在Python代码中调用参数

Question

我有以下代码，在调用时需要 -p 参数。但是，如何在 SQL 查询中调用 -p 参数呢？我还希望在输出文件名中使用此 -p 参数文本。

#!/usr/bin/python

import argparse
import psycopg2
import csv

parser = argparse.ArgumentParser(description='insert the project ID as an 
argument')
parser.add_argument('-p','--project_id', help='project_id to pull files from 
ERAPRO',required=True)
args = parser.parse_args()

conn = psycopg2.connect(database="XXX", user="XXX", password="XXX", 
host="XXX", port="5432")
cur = conn.cursor()

cur.execute("""SELECT project_analysis.project_accession, 
analysis.analysis_accession, file.filename, file.file_md5, file.file_location
    FROM project_analysis
    LEFT JOIN analysis on project_analysis.analysis_accession = analysis.analysis_accession
    LEFT JOIN analysis_file on analysis.analysis_accession = analysis_file.analysis_accession
    LEFT JOIN file on analysis_file.file_id = file.file_id
    WHERE project_accession = <INSERT -p ARGUMENT HERE> and analysis.hidden_in_eva = '0';""")

records = cur.fetchall()

with open ('/nfs/production3/eva/user/gary/evapro_ftp/<INSERT -p ARGUMENT 
HERE>.csv', 'w') as f:
writer = csv.writer (f, delimiter = ',')
for row in records:
    writer.writerow(row)

conn.close()

感谢所有帮助。

谢谢

Answer 1

首先使用 add_argument() 的 dest 参数将参数分配给变量。假设我们将输入分配给project_id 变量。这样我们就可以在代码中引用它了。

parser.add_argument('-p','--project_id', 
                     help='project_id to pull files from
                     ERAPRO',
                     required=True,
                     dest='project_id') # notice the dest argument

cur.execute("""SELECT project_analysis.project_accession, 
analysis.analysis_accession, file.filename, file.file_md5, file.file_location
    FROM project_analysis
    LEFT JOIN analysis on project_analysis.analysis_accession = analysis.analysis_accession
    LEFT JOIN analysis_file on analysis.analysis_accession = analysis_file.analysis_accession
    LEFT JOIN file on analysis_file.file_id = file.file_id
    WHERE project_accession = %s and analysis.hidden_in_eva = '0';""", (args.project_id))

请注意 execute(' ... %s ...', (args.project_id)) 的使用，通过这样做，我们插入了 project_id 引用的值到字符串中。