python - sql query returns nothing

Question

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.

falsetru · Accepted Answer · 2015-06-27 00:56:48Z

1

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],))
#                                            ^

answered Jun 27, 2015 at 0:56

falsetru

371k69 gold badges769 silver badges659 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

whoisearth Over a year ago

Thanks you pointed me in the right direction! This wasn't it completely. I didn't need the comma but I also didn't need the [0]!

falsetru Over a year ago

@whoisearth, because o['Job Name'] itself is a kind of sequence object?

whoisearth Over a year ago

o['Job Name'] returns a value in a json file.

Collectives™ on Stack Overflow

python - sql query returns nothing

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related