CODEWITHSUNDEEP
python
whoisearth
whoisearth

Reputation: 4160

python - sql query returns nothing

My json looks like this -

[
  {
    "Monitor Level":"1",
    "Estimate SLA (+30)":"214",
    "New Schedule":"",
    "Job Name":"\\Job1\\jobgroup",
    "Estimated Duration":"183",
    "Actual Duration":"184"
  },
  {
    "Monitor Level":"1",
    "Estimate SLA (+30)":"179",
    "New Schedule":"8:00 PM",
    "Job Name":"\\Job2\\jobgroup",
    "Estimated Duration":"1349",
    "Actual Duration":"1349"
  }
]

I run the following code - 

for o in json_object:
    # get jobid
    db = pyodbc.connect('DRIVER={SQL Server};SERVER=dvidbsql01\dev2008;DATABASE=Admiral;UID=Tidal;PWD=tidal97')
    cur = db.cursor()
    cur.execute("""select jobmst_id from jobmst where jobmst_prntname + '\\' + jobmst_name = ?""", o['Job Name'])
    r= cur.fetchall()
    print r

And r returns the value I want.

If I use the code I want to however - 

sql_jobid = """
    select jobmst_id 'Job ID' from jobmst where jobmst_prntname + '\\' + jobmst_name = ?
"""

## DEFINE ENVIRONMENT DATABASES
def db():
    if args.environment == 'DEV':
        return pyodbc.connect('DRIVER={SQL Server};SERVER=server\instance;DATABASE=db;UID=user;PWD=pass')

## DEFINE UPDATE
def query_db(query, args=(), one=False):
    cur = db().cursor()
    cur.execute(query, args)
    r = [dict((cur.description[i][0], value) \
               for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return (r[0] if r else None) if one else r

for o in json_object:
    # get jobid
    jobid = query_db(sql_jobid, (o['Job Name'][0]))
    print jobid

It is not printing the value I want even though it's doing the same thing. even replacing o['Job Name'][0] with 'Job1\jobgroup' still doesn't return anything so it's something with my more pythonic code that seems to not want to parse the Job Name.

Upvotes: 1

Views: 153

Answers (1)

falsetru
falsetru

Reputation: 368944

In the following line,

jobid = query_db(sql_jobid, (o['Job Name'][0]))

(o['Job Name'][0]) is not a tuple. If you want to pass a tuple, you need to append a trailing comma.

jobid = query_db(sql_jobid, (o['Job Name'][0],))
#                                            ^

Upvotes: 1

Related Questions