[Tutor] Need all values from while loop - only receiving one
Hi folks,
I'm trying to calculate the amount of rent per lease for the life of
the lease, by month. The following code provides the correct results
except for the monthnum and billper. Monthnum is intended to show the
month within the lease and the billper should list each date for rent
billing.
For example, if the lease commences May 1, 2018 for a 60 month term,
2018-05-01 is monthnum = 1 and the billper = 2018-05-01. The
following 59 rows needs to increment the monthnum and billper values
by 1.
The while loop (below) returns the first value correctly but stops
without continuing through the rest of the True matches.
Code (see below for results):
import xml.etree.ElementTree as ET
import pyodbc
import dateutil.relativedelta as rd
import dateutil.parser as pr
tree = ET.parse('DealData.xml')
root = tree.getroot()
for deal in root.findall("Deals"):
for dl in deal.findall("Deal"):
dealid = dl.get("DealID")
for dts in dl.findall("DealTerms/DealTerm"):
dtid = dts.get("ID")
dstart = pr.parse(dts.find("CommencementDate").text)
dterm = dts.find("LeaseTerm").text
darea = dts.find("RentableArea").text
for brrent in dts.findall("BaseRents/BaseRent"):
brid = brrent.get("ID")
begmo = int(brrent.find("BeginIn").text)
if brrent.find("Duration").text is not None:
duration = int(brrent.find("Duration").text)
else:
duration = 0
brentamt = brrent.find("Rent").text
brper = brrent.find("Period").text
perst = dstart + rd.relativedelta(months=begmo-1)
perend = perst + rd.relativedelta(months=duration-1)
billmocount = begmo
while billmocount < duration:
monthnum = billmocount
billmocount += 1
billmo = perst
while billmo < perend:
billper = billmo
billmo += rd.relativedelta(months=1)
if dealid == "706880":
print(dealid, dtid, brid, begmo, dstart, dterm,
darea, brentamt, brper, duration, perst, perend, \
monthnum, billper)
Results:
706880 4278580 45937180 1 2018-01-01 00:00:00 60 6200 15.0 rsf/year 36
2018-01-01 00:00:00 2020-12-01 00:00:00 35 2020-11-01 00:00:00
706880 4278580 45937181 37 2018-01-01 00:00:00 60 6200 18.0 rsf/year
24 2021-01-01 00:00:00 2022-12-01 00:00:00 35 2022-11-01 00:00:00
Any help is appreciated!
Thank you,
Daryl
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Need all values from while loop - only receiving one
Again thank you so much! I truly appreciate your direction!
The idea of a class is something I'd unsuccessfully tried but I'm up
for the challenge! I'm very comfortable in Transact SQL but OOP is
new to me. Here's my updated code with a sample XML. The full XML
file is over 600K rows.
I'd appreciate any feedback.
Daryl
import xml.etree.ElementTree as ET
import pyodbc
import dateutil.relativedelta as rd
import dateutil.parser as pr
from dateutil.rrule import rrule, MONTHLY
tree = ET.parse(r'\\DealData.xml')
root = tree.getroot()
class BillDate:
def __init__(self, begin, end):
self.current = begin
self.end = end
def __iter__(self):
return self
def __next__(self):
if self.current < self.end:
billmo = self.current
self.current += rd.relativedelta(months=1)
return billmo
else:
raise StopIteration
for deal in root.findall("Deals"):
for dl in deal.findall("Deal"):
dealid = dl.get("DealID")
for dts in dl.findall("DealTerms/DealTerm"):
dtid = dts.get("ID")
dstart = pr.parse(dts.find("CommencementDate").text)
dterm = int(dts.find("LeaseTerm").text)
darea = dts.find("RentableArea").text
for brrent in dts.findall("BaseRents/BaseRent"):
brid = brrent.get("ID")
begmo = int(brrent.find("BeginIn").text)
if brrent.find("Duration").text is not None:
duration = int(brrent.find("Duration").text)
else:
duration = 0
brentamt = brrent.find("Rent").text
brper = brrent.find("Period").text
perst = dstart + rd.relativedelta(months=begmo-1)
perend = perst + rd.relativedelta(months=duration-1)
months = [dt.strftime("%Y-%m-%d") for dt in rrule(MONTHLY,
dtstart=perst, until=perend)]
for billdt in BillDate(perst, perend):
billdt
if dealid == "706880":
print(dealid, dtid, brid, begmo, dstart, dterm, darea,
brentamt, brper, duration, perst, \
perend, billdt)
c03528f241fdc8e4
12345_Place - Bldg 1
2015-10-28T04:09:07Z
2018-04-24T21:56:25Z
SourceID
12345~12345
Calgary
AB
1234 5 Ave S.W.
T3P 0N7
Canada
800
11225
8
7
2016-04-20T13:47:13Z
2017-10-02T09:17:20Z
SourceID
800
new
2017-12-20T22:36:21Z
2017-06-06T15:09:35Z
dead_deal
0
2017-12-01
Office
Tenant Name
financial services
1013325
6000
7000
proposal
2017-06-06T15:09:35Z
2017-06-06T15:09:22Z
2017-06-06T15:09:22Z
2017-12-20
197
17699
Jessica
Rabbit
jessica.rab...@themovie.com
dead_deal
2017-12-20T22:36:01Z
2017-12-20T22:36:01Z
2017-12-20T22:36:01Z
2017-12-20
0
24934
Leigh
Vaughan
leigh.vaug...@emails.com
dead_deal
2017-12-20T22:36:21Z
2017-12-20T22:36:21Z
2017-12-20T22:36:21Z
169
price
24934
Leigh
Vaughan
leigh.vaug...@emails.com
landlord
0.08
6200
triple net
60
2018-01-01
2017-06-06T15:16:23Z
20714.1144670763
2017-06-06
7.45
103367.15936951
640876388.09096
642137720.0
502200.0
64480.0
1
true
1
15.0
rsf/year
36
37
18.0
rsf/year
24
landlord
Company1
2.9
rsf
landlord
Company2
7.5
rsf
186607
2017-06-06
ReimbursableExpenseCam
12.87
net
12.87
RealEstateTax
7.45
net
7.45
On Tue, Jul 3, 2018 at 3:42 AM Alan Gauld wrote:
>
> On 02/07/18 23:52, Daryl Heppner wrote:
> > The two While Loops are the part I'm struggling with. The goal is to
> > list each bill month number and date for each month in the term.
> When you need multiple values you need to use a collection
> type such as a list or tuple. Your problem is that you are using
> single valued variables to collect data but as you go round the
> loops (not just the while loops) you are throwing away the data
> you have already collected so that when the loop ends you only
> have the last set of values.
>
>
> > this example, I'm trying to return 36 rows, listed from 1-36 instead
> > of one row followed by 24 rows numbered 37 to 60.
>
> Personally I'd create a dictionary per row and store those
> dictionaries in a list. (You could use a class instead of a dictionary
> but I'm guessing that might be too big a step for a beginner.)
>
> I'd also put all the parsing code into a function so that you
> pass it the raw xml and get back the row dictionary.
>
> That should simplify the structure of your code significantly
> and make it easier to see what's going on. It should also be
> easier to test the function on its own with some sample xml.
>
> If you need more help with that let us know.
>
> --
>
Re: [Tutor] Need all values from while loop - only receiving one
Hi Alan,
Extremely valuable guidance - thank you! I will be adapting my script
once I get to the office but at a first glance, this appears to be
exactly what we need! I'll confirm once I've had a chance to
implement this.
If you have any suggestions for continued self-learning (books,
courses, etc.) I'd appreciate any tips.
A bit of context, in case you're curious - we're parsing the XML for
load into a T SQL database for reporting purposes which is why I've
parsed each value to a unique "cell". The print statement is merely a
QA step before ultimately making use of the pyodbc method.
For our Base Rent table, the final Python statement reads as:
x.execute("Insert into dealbaserents (dealid, dtermid, brentid, \
brbegin, brentamt, brentperiod, brentdur) values \
(?, ?, ?, ?, ?, ?, ?)", \
(dealid, dtid, brid, begmo, brentamt, brper, duration))
con.commit()
The need to extrapolate the full rent schedule is to address a few
reporting needs. Free Rent, where applicable, is not expressed as a
dollar value with a specific date. Rather, it is described as a
percentage of Base Rent with a reference to the month in which it
applies. A lease can have multiple Free Rent periods which may or may
not be consecutive and there are several examples where a Free Rent is
applied in the 2nd month and again in the 14th month which means that
the dollar value of the Free Rent could be different if a rent step
occurs at the end of the first year.
We will also be calculating Net Present Value which will be much
easier to deal with using normalized data. There are four scenarios
for calculating base rent - per square foot per month, per square foot
per year, monthly amount and annual amount.
Regards,
Daryl
On Tue, Jul 3, 2018 at 8:16 PM Alan Gauld via Tutor wrote:
>
> On 04/07/18 01:08, Alan Gauld via Tutor wrote:
>
> > # Now the main code just needs the outer loops:
> > tree = ET.parse(r'\\DealData.xml')
> > root = tree.getroot()
> > deals = []
> > for deal in root.findall("Deals"):
> > for dl in deal.findall("Deal"):
> > deals.append( Deal(dl) )
> >
> > if dl.id == "706880":
> >print(dl) #uses __str__() to create string
>
> Sorry, that last loop should be like this:
>
> for xml_dl in deal.findall("Deal"):
> theDeal = Deal(xml_dl)
> deals.append( theDeal )
>
> if theDeal.id == "706880":
> print(theDeal) #uses __str__() to create string
>
>
> --
> Alan G
> Author of the Learn to Program web site
> http://www.alan-g.me.uk/
> http://www.amazon.com/author/alan_gauld
> Follow my photo-blog on Flickr at:
> http://www.flickr.com/photos/alangauldphotos
>
>
> ___
> Tutor maillist - Tutor@python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Need all values from while loop - only receiving one
Hi Alan,
A lot of great guidance here!
I'm stepping back to simply return the DealID from the XML using the
concept of a class. My code results in exit code 0 but doesn't print
any results. Could you give me a hint where I'm missing something?
Thank you,
Daryl
root = tree.getroot()
class Deal:
def __init__(self, rt):
for deal in rt.findall("Deals/Deal"):
self.dealID = deal.get("DealID")
def __str__(self):
return self.dealID
def __iter__(self):
return self.dealID
def __next__(self):
if self.dealID is not None:
print(self.dealID)
else:
raise StopIteration
Deal(root)
On Wed, Jul 4, 2018 at 10:53 AM Alan Gauld via Tutor wrote:
>
> On 04/07/18 12:08, Daryl Heppner wrote:
>
> > If you have any suggestions for continued self-learning (books,
> > courses, etc.) I'd appreciate any tips.
>
> So far as OOP goes you can't go far wrong with the latest version
> of Grady Booch's OOAD book. I've read both the previous versions
> and the latest update is the best yet.
>
> > A bit of context, in case you're curious - we're parsing the XML for
> > load into a T SQL database for reporting purposes which is why I've
> > parsed each value to a unique "cell". The print statement is merely a
> > QA step before ultimately making use of the pyodbc method.
>
> I guessed as much.
>
> As for the database inserts I'd consider making them part of the
> class init method, so once you create the new class instance
> it automatically saves itself (or updates if the ID already
> exists?)
>
> > The need to extrapolate the full rent schedule is to address a few
> > reporting needs. Free Rent, where applicable, is not expressed as a
> > dollar value with a specific date. Rather, it is described as a
> > percentage of Base Rent with a reference to the month in which it
> > applies. A lease can have multiple Free Rent periods which may or may
> > not be consecutive and there are several examples where a Free Rent is
> > applied in the 2nd month and again in the 14th month which means that
> > the dollar value of the Free Rent could be different if a rent step
> > occurs at the end of the first year.
>
> Put the calculations as methods into the corresponding classes.
> If necessary break them up so each class does only its own bit
> of the calculation. Remember the adage that "objects do things to
> themselves". If an object owns a piece of data it should be the
> thing that operates (including doing calculations) on that data.
> Calculations are operations on objects not objects in their
> own right. (Unless you are building a calculation framework,
> like Matlab say...)
>
> One of the most common OOP anti-patterns is having some kind of
> calculator object that fetches data from all the surrounding
> objects, does a complex calculation then stores the result
> into some other object.
>
> Instead the calculation should commence in the target object and it
> should ask each of the supplier objects to do their own bits only
> returning to the target the end result of their subcalculation.
> The target object should, in turn, only be performing calculations
> on the results returned plus the data actually stored in the
> target itself. Of course it is not always that clean but if
> the object data is partitioned well it should be pretty close.
>
> But it takes most non-OOP programmers a little time to turn their
> thinking inside out like that. So don't sweat if it feels
> unnatural to start with.
>
> --
> Alan G
> Author of the Learn to Program web site
> http://www.alan-g.me.uk/
> http://www.amazon.com/author/alan_gauld
> Follow my photo-blog on Flickr at:
> http://www.flickr.com/photos/alangauldphotos
>
>
> ___
> Tutor maillist - Tutor@python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQL querying
Haven't worked with mySQL but MS SQL Server (and any other ODBC connection) should connect importing pyodbc. Good luck! Daryl On Thu, Aug 30, 2018 at 6:26 PM Rafael Knuth wrote: > > Can I do SQL querying in Python? > What packages do I need for that purpose? (specifically for mySQL) Thanks. > ___ > Tutor maillist - Tutor@python.org > To unsubscribe or change subscription options: > https://mail.python.org/mailman/listinfo/tutor ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
