#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2

    Interpreting a def function


    Hi there! I am working on a new project right now and I am having some trouble with this def.. It just seems a bit to messy for me ( I am a rookie in python ) to understand!

    Anyone mind commenting the steps of this def?

    like # here it does this
    # and now it does this

    Code:
    def getEmergingMarketPositions(uploadDate, countryCodes): #get profit center
        
        print "Fetching Emerging Markets Positions"
    
        portfolios = getEmergingMarketPortfolios() 
        
        sql_q = "SELECT c.countrycode,c.counterpartyid,i.isincusipcode,b.system,b.pos_type,"
        sql_q += "b.id_type,b.id,b.pf_id,b.portfolio,b.nominal,b.org_mv,b.currency,b.maturity "
        sql_q += "FROM [CDB].[dbo].[Arms_Bonds] b "
        sql_q += "LEFT JOIN [Prd_process].[dbo].issues i ON "
        sql_q += "b.id_id = i.isincusipcode "
        sql_q += "LEFT JOIN [Prd_process].[dbo].counterparty c ON "
        sql_q += "c.counterpartyid = i.bondissuerid "
        sql_q += "where credit_category <> 'SWAP' AND "
        sql_q += "id_type IS NOT NULL  "
        sql_q += " AND b.nominal <> 0 "
        sql_q += " AND c.countrycode in ('%s')" % "','".join(countryCodes)
        sql_q += " AND b.portfolio in ('%s')" % "','".join(portfolios)
        
        sf = database.connection("DSN=CDB")
        tbl = sf.select(sql_q)
        
        sql_q = "SELECT c.countrycode,c.counterpartyid,i.isincusipcode,b.system,b.pos_type,"
        sql_q += "'' as id_type,b.id,b.pf_id,b.portfolio,b.nominal,b.org_mv,b.currency,b.maturity "
        sql_q += "FROM [CDB].[dbo].[Arms_Frn] b "
        sql_q += "LEFT JOIN [Prd_process].[dbo].issues i ON "
        sql_q += "b.id_id = i.isincusipcode "
        sql_q += "LEFT JOIN [Prd_process].[dbo].counterparty c ON "
        sql_q += "c.counterpartyid = i.bondissuerid "
        sql_q += "where bond_category <> 'SWAP'  "
        sql_q += " AND b.nominal <> 0 "
        sql_q += " AND c.countrycode in ('%s')" % "','".join(countryCodes)
        sql_q += " AND b.portfolio in ('%s')" % "','".join(portfolios)
        
        tbl2 = sf.select(sql_q)
        
        tbl.extend(tbl2)
        
        sf.close()
        
        clean_tbl,counter = CleanTable(tbl)
    
        print "%s Positions read" % counter
    
        return clean_tbl
    Thankful for reply and taking your time!

    Regards
    Chris
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    python Code:
        tbl2 = sf.select(sql_q) # assigns results of a query; nearly always a list -- but you didn't include the part where we see what db or db bindings these are... (>o<)
        tbl.extend(tbl2) # extends whatever list tbl was with tbl2
        sf.close() # probably closing the db connection
        clean_tbl, counter = CleanTable(tbl) # This returns two values
        print "%s Positions read" % counter # coder detritus  
        return clean_tbl # meaningful, but somewhere else

    These are the only Python-relevant parts. The rest is a gigantic pile of an of SQL query of the sort typical in business applications where the DBA was told to clean his mouth out with a revolver. All that broken up query should at least be written as a triple-quote (sort of a Python way of making an inline here-document) instead of decorating the source file with huge string cat/reassign statements. Of course, it would be better if the DB were actually used like a DB and programmed to provide answers instead of a platform for mysterious bugs and security vulnerabilities... but I digress (I'm a db snob, btw).

    If you're asking what the giant SQL query does, I can help out with that, but later, when I'm sobered up.

    Comments on this post

    • ChristopherL agrees : Thank you!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2
    Originally Posted by zxq9
    python Code:
        tbl2 = sf.select(sql_q) # assigns results of a query; nearly always a list -- but you didn't include the part where we see what db or db bindings these are... (>o<)
        tbl.extend(tbl2) # extends whatever list tbl was with tbl2
        sf.close() # probably closing the db connection
        clean_tbl, counter = CleanTable(tbl) # This returns two values
        print "%s Positions read" % counter # coder detritus  
        return clean_tbl # meaningful, but somewhere else

    These are the only Python-relevant parts. The rest is a gigantic pile of an of SQL query of the sort typical in business applications where the DBA was told to clean his mouth out with a revolver. All that broken up query should at least be written as a triple-quote (sort of a Python way of making an inline here-document) instead of decorating the source file with huge string cat/reassign statements. Of course, it would be better if the DB were actually used like a DB and programmed to provide answers instead of a platform for mysterious bugs and security vulnerabilities... but I digress (I'm a db snob, btw).

    If you're asking what the giant SQL query does, I can help out with that, but later, when I'm sobered up.
    haha thank you I am having some concerns with the sql query but I think I can figuere it out! This is actually an ex worker who wrote the code and it's going to be re-written in another language so I am the one writing the specification! And I barely know any pytthon

    Really thankful for the python help though, for being a bit drunk you did an amazing job thank you!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    "A bit"? Man, I passed that a few hours ago. I'm about to get picked up for a stupid/awesome night of karaoke.

    If you've got permission to do it and this is a common query and your DB isn't retarded (lessee... DB2, Postgres, Oracle... the list is fairly short, actually...) I'd definitely rewrite this as a view or a materialized view so you can do more with it -- like ask it a question directly and keep all that crap out of the client code or create indexes over it, etc..

    Most Python db bindings return query results as lists, one element per line. Some might return a top line, sort of like how sometimes a csv has a "title" line, and sometimes not. Come to think of it, its really similar to reading in a csv, just more meaningful and far less error prone. So when you see an .extend() on a query result, its just appending more elements (in order) to a list. Which is stupid compared to (spoiler alert!) just doing a UNION ALL within the DB, but I digress...

    If I manage to wake up tomorrow maybe I'll work some more on RyuQ, come to think of it...
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    14
    Rep Power
    0
    Code:
    def getSensitivities(uploadDate,positionIds):
    
        sql_q  = "SELECT job_name,MAX(run_id) maxOfRunId FROM [ARMS].[dbo].[arms_run] "
        sql_q += "WHERE job_name IN('04.10 ATCM Delta1','04.11 ATCM FX Sensitivity','04.12 ATCM Greek') "
        sql_q += "AND upload_date = '%s' " % uploadDate.string('%Y-%m-%d')
        sql_q += "GROUP BY job_name"
    
        db = database.connection("DSN=armsrc.sebank.se")   #Sets the stuff in database.connection do variable db
        tbl = db.select(sql_q)   # sets the stuff in db.select(sql_q) to variable tb1
    
        runIds = []   # It creates an empty list
        for row in tbl:            # Loops for each of all the itens in tb1, that is equals to, db.select(sql_q)
                runIds.append(str(row['maxOfRunId'])) #This line ....
    #Will select the value related to 'maxOfRunid' in tb1, convert it to a string (might be a float or int) and appends that value
    #To runIds which is the empty list we've created before.
    
    
    
    
        sql_q  = "SELECT * FROM [ARMS].[dbo].[res_position_sensitivity_mb] "
        sql_q += "WHERE run_id IN(%s) and sens_test_set_name in ('04.12 ATCM Greek','Delta1 SEB') " % ",".join(runIds) #Adds runIds to the big string sql_q
    
        sql_q += "AND id IN('%s') " % "','".join(positionIds.keys()) #Adds positionIds.keys() (function argument) to sql_q
         
        tbl = db.select(sql_q)  #notice that sql_q is not updated
        db.close()  #Saves the whole thing
        
        return tbl  #And returns that monster string Lol
    
    #That's it :D

    Here's the one you pmed me, i can't pm people for lack of posts. Lol
    It's easier to read if you copy/past into a python highlighter...
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2
    Originally Posted by J Dihzy
    Code:
    def getSensitivities(uploadDate,positionIds):
    
        sql_q  = "SELECT job_name,MAX(run_id) maxOfRunId FROM [ARMS].[dbo].[arms_run] "
        sql_q += "WHERE job_name IN('04.10 ATCM Delta1','04.11 ATCM FX Sensitivity','04.12 ATCM Greek') "
        sql_q += "AND upload_date = '%s' " % uploadDate.string('%Y-%m-%d')
        sql_q += "GROUP BY job_name"
    
        db = database.connection("DSN=armsrc.sebank.se")   #Sets the stuff in database.connection do variable db
        tbl = db.select(sql_q)   # sets the stuff in db.select(sql_q) to variable tb1
    
        runIds = []   # It creates an empty list
        for row in tbl:            # Loops for each of all the itens in tb1, that is equals to, db.select(sql_q)
                runIds.append(str(row['maxOfRunId'])) #This line ....
    #Will select the value related to 'maxOfRunid' in tb1, convert it to a string (might be a float or int) and appends that value
    #To runIds which is the empty list we've created before.
    
    
    
    
        sql_q  = "SELECT * FROM [ARMS].[dbo].[res_position_sensitivity_mb] "
        sql_q += "WHERE run_id IN(%s) and sens_test_set_name in ('04.12 ATCM Greek','Delta1 SEB') " % ",".join(runIds) #Adds runIds to the big string sql_q
    
        sql_q += "AND id IN('%s') " % "','".join(positionIds.keys()) #Adds positionIds.keys() (function argument) to sql_q
         
        tbl = db.select(sql_q)  #notice that sql_q is not updated
        db.close()  #Saves the whole thing
        
        return tbl  #And returns that monster string Lol
    
    #That's it :D

    Here's the one you pmed me, i can't pm people for lack of posts. Lol
    It's easier to read if you copy/past into a python highlighter...
    Thank you! Amazing job, you're really good at this, im impressed! Think ill make a quite good first impression now to my boss haha
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    14
    Rep Power
    0
    Originally Posted by ChristopherL
    Thank you! Amazing job, you're really good at this, im impressed! Think ill make a quite good first impression now to my boss haha
    Lol i spotted a mistake:

    Code:
    tbl = db.select(sql_q)  #notice that sql_q is not updated
    I mistyped now for not. correct is #sql_q is NOW updated.


IMN logo majestic logo threadwatch logo seochat tools logo