Calling Argument in Python code

Question

I have the following code that demands a -p argument when calling. However, how do I call the -p argument in the SQL query? I am also looking to use this -p argument text in the output file name.

#!/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()

All help appreciated.

Thanks

Answer 1

First assign your argument to variable using dest argument to add_argument(). lets say we assign the input to the project_id variable. This way we can reference it in the code.

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))

Notice the use of execute(' ... %s ...', (args.project_id)) by doing this we interpolated the value referenced by project_id into the string.

• shahkalpesh: I suppose you wanted (args.project_id) instead of (project_id) as parameter to execute statement.
• user7822097: Thanks for your help!

Answer 2

After calling args = parser.parse_args() you can obtain the value of the arguments like this:

pid = args.project_id

Then you can use that value of pid in your code by using normal string substitution. However, it's better to use psycopg2's inbuilt method for passing parameters to SQL queries to prevent SQL injection.

Normal string substitution:

'hello world {]'.format(var_name)

psycopg2:

cur.execute('SELECT * from %s', (var_name))