CODEWITHSUNDEEP
pythonpython-2.7
Nicholas
Nicholas

Reputation: 125

How do I reference multiple variables in a for loop?

I am writing a script that submits multiple (30) SQL queries to Google BigQuery. What is the best way to loop through the queries? My code works but it doesn't feel very Pythonic.

I need to pass through the query name within the job_id and submit the query.

def run_query(query,job_id):
    try:
        query_job = client.query(query,job_id=job_id)
        polling = 1
        while query_job.done() is False:
            if "q1_" in job_id:
                time.sleep(20)
                print("Job State : {} - Polling : {}".format(query_job.state,polling))
                polling +=1 
                query_job.reload()
            else:
                time.sleep(1)
                print("Job State : {} - Polling : {}".format(query_job.state,polling))
                polling +=1 
                query_job.reload()                     
    except Conflict as err:
        print("Could not run Query.  System Message: \n{}".format(err))
        sys.exit()


q1 = """SELECT * FROM XYZ"""
q2 = """SELECT TOP 10 * FROM YZF"""
q3 = """select id from fjfj"""
q4 = """SELECT * FROM XYZ"""
q5 = """SELECT TOP 10 * FROM YZF"""
q6 = """select id from fjfj"""

query_jobs = [q1,q2,q3,q4,q5,q6]

q = 0

for query in query_jobs:
    randid = str(uuid.uuid4())
    q+=1
    queries = "q"+str(q)
    job_id = queries+"_"+randid
    run_query(query,job_id)
    print job_id

Upvotes: 0

Views: 73

Answers (3)

tarkmeper
tarkmeper

Reputation: 767

I'd suggest including the queries in a dictionary outlining what each is for.

QUERIES = {
   "q1_XYZ": """SELECT * FROM XYZ""",
   "q2_YZF": """SELECT TOP 10 * FROM YZF""",
   "q3_FJFJ": """select id from fjfj""",
   "q4_XYZ2": """SELECT * FROM XYZ""",
   "q5_YZF": """SELECT TOP 10 * FROM YZF""",
   "q6_FJFJ": """select id from fjfj"""
}


for job_id, query in query_jobs.items():
    run_query(query,job_id)

Depending on how complex this will become I'd suggest adding more attributes. The advantage like this is if you need more complex logic in run_query you can control that via attributes rather then the job_id of the query.

QUERIES = {
   "q1_XYZ": { 'query': """SELECT * FROM XYZ""", 'is_A': True, 'cost': 100 },
   << more samples >>
}

for job_id, details in query_jobs.items():
    run_query(details['query'],job_id)

Upvotes: 0

olinox14
olinox14

Reputation: 6672

First you could simplify your run_query method with something like:

time.sleep(20 if "q1_" in job_id else 1)

This would work in python 3+, I'm not sure it will wieht 2.7, but you got the idea.

Then you should take a look at python string formatting, which could really help you there.

At the end,you could have something like that:

query_jobs  = ["""SELECT * FROM XYZ""",
               # (...)
               """select id from fjfj"""]

for i, query in enumerate(query_jobs):
    job_id = "q%s_%s" % (i, uuid.uuid4())
    run_query(query,job_id)
    print job_id

Upvotes: 0

Nordle
Nordle

Reputation: 2981

Looks fine to me, you could improve upon this slightly by using enumerate in your loop instead of a counter:

for i, query in enumerate(query_jobs):
    randid = str(uuid.uuid4())
    queries = "q"+str(i)
    job_id = queries+"_"+randid
    run_query(query,job_id)
    print job_id

Upvotes: 1

Related Questions