python 添加数据库表_通过时间表（Python库）简化复杂的计划

python 添加数据库表

by Maxim Mamaev

马克西姆·马马耶夫(Maxim Mamaev)

通过时间表(Python库)简化复杂的计划 (Make your complex scheduling simple with timeboard, a Python library)

timeboard is a Python library that creates schedules of work periods and performs calendar calculations over them. You can build standard business day calendars as well as a variety of other schedules, simple or complex.

timeboard是一个Python库，可创建工作周期时间表并对其进行日历计算。 您可以构建标准的工作日日历以及各种其他时间表，无论是简单的还是复杂的。

You can find the Documentation here.

您可以在此处找到文档。

Check out the GitHub repo here.

在此处查看GitHub存储库。

Find it on PyPI here.

在此处在PyPI上找到它。

故事 (The story)

It started with the headcount case. Our company introduced KPIs involving the revenue per employee, so we needed to know the average annual headcount of each team. I had already been writing Python scripts, so I was not intimidated.

它始于人数统计。 我们公司引入了涉及每个员工收入的KPI，因此我们需要了解每个团队的平均年度员工数。 我已经在写Python脚本了，所以没有被吓到。

To obtain a headcount I had to calculate the number of business days each employee has spent with the company within the year. Pandas would handle it in a sec, I thought. But it came out that Pandas could not.

为了获得员工人数，我必须计算每个员工在一年中在公司度过的工作日数。 我想，熊猫会在几秒钟内处理好它。 但是事实证明，熊猫不能。

The Russian business calendar is cumbersome. They swap weekdays with Saturdays or Sundays to fill gaps between holidays and weekends. For example, you have to come to work on a Saturday in February to be reimbursed with a free Monday preceding a holiday Tuesday somewhere in May.

俄罗斯的商务日历很麻烦。 他们将工作日与星期六或星期日互换，以填补假日和周末之间的空白。 例如，您必须在2月的一个星期六来上班，以便在5月某个星期二的假期之前获得免费的星期一报销。

The scheme for each year is unique. Pandas’s business day calendar supports only one-way amendments for holiday observations. So, I could turn a business day into a day off, but not the other way round.

每年的计划都是独特的。 熊猫的工作日日历仅支持对假期观察的单向修正。 因此，我可以将工作日改为休息日，但反之则不行。

Then there were operators in the call center, and my anxiety swung the other way. They work in shifts of varying length, and one shift in followed by three shifts out. To get the call center statistics, I did not need the business day calendar. Yet I had to count the number of particular operator’s shifts in a period of time.

然后呼叫中心里有话务员，而我的焦虑却反过来。 它们轮流工作，轮班时间各不相同，先移入后移三班。 要获取呼叫中心的统计信息，我不需要工作日日历。 但是我必须计算一段时间内特定操作员的轮班次数。

And finally, an offbeat problem. In my local Honda dealership, the mechanics work on alternate weekly schedules: Monday, Tuesday, Saturday and Sunday this week, and Wednesday through Friday the next week. I wanted to always be served by a particular mechanic, because the other one had once messed up the brakes. I wanted a simple way to determine the next shift of “my” mechanic.

最后，一个另类的问题。 在我的本地Honda经销商处，机械师按周交替进行工作：本周星期一，星期二，星期六和星期日，以及下周的星期三至星期五。 我一直希望由一位特殊的机械师服务，因为另一位机械师曾经弄坏了刹车。 我想要一种简单的方法来确定“我的”技工的下一个转变。

These cases have a common foundation. Their solutions would rely upon a schedule of “on-duty” and “off-duty” periods of time. We should be able to construct variously structured schedules suitable for different business cases. Queries and calculations run over the schedule must distinguish between “on-duty” and “off-duty” periods.

这些案例具有共同的基础。 他们的解决方案将取决于“上班”和“下班”时间段的时间表。 我们应该能够构建适合不同业务案例的各种结构的时间表。 计划表中的查询和计算必须区分“上班”和“下班”期间。

I could not find a Python package that provided the means for building and querying such schedules. As it happened, I had some free time to write it myself.

我找不到提供用于构建和查询此类计划的工具的Python包。 碰巧的是，我有一些空闲时间自己写。

这个概念 (The concept)

timeboard is a Python library that creates schedules of work periods and performs calendar calculations over them. These objects themselves are called timeboards.

timeboard是一个Python库，可创建工作周期时间表并对其进行日历计算。 这些对象本身称为时间表。

There are three major steps in reasoning about a timeboard.

制定时间表的过程主要分为三个步骤。

You start with an interval of time which sets the bounds of your calendar. Everything will be confined to this interval. It is called the (reference) frame. The frame consists of base units. A base unit is the smallest period of time you need for gauging your calendar. For example, if you reason in terms of business days, then the base unit is a day. Alternatively, if you build a schedule of multiple-hour shifts, then the base unit is one hour.

您从一个时间间隔开始，该时间间隔设置了日历的范围。 一切都将被限制在此间隔内。 这称为(参考)框架。 框架由基本单元组成。 基本单位是测量日历所需的最短时间。 例如，如果您根据工作日进行推理，则基本单位是一天。 或者，如果您建立了一个多小时轮班的时间表，则基本单位为一小时。

On the next step, you define the rules of marking up the frame into workshifts. Workshifts are periods of time that you care about. They make up your calendar. It is workshifts that you want to schedule or to count. In a standard business days calendar, workshift is a day (and base unit is a day too, so they coincide).

在下一步中，您将定义将框架标记为轮班的规则。 轮班是您关心的时间段。 它们组成了您的日历。 您要计划或计算的是轮班。 在标准工作日日历中，轮班是一天(基本单位也是一天，因此它们是一致的)。

In a call center, workshift is a period of several hours when a particular shift of operators is on duty. The base unit is likely to be one hour, and each workshift comprises a (probably varying) number of base units.

在呼叫中心，轮班是几个小时的时间，当接线员执行特定的轮班任务时。 基本单位可能为一小时，并且每个工作班次都包含(可能变化)数量的基本单位。

The sequence of workshifts filling the frame is called the timeline.

填充框架的轮班工作的顺序称为时间轴。

Finally, you create one or more schedules. A schedule is like a stencil laid over the timeline. Its purpose is to tell on-duty workshifts from off-duty ones.

最后，您创建一个或多个日程表。 时间表就像是在时间表上放置的模具。 其目的是告诉值班工作与非值班工作。

A schedule needs something to work with in order to declare a workshift on duty or off duty. This is why you provide a label for each workshift, or rather a rule for labeling them while the frame is marked up into the timeline. Each schedule defines a selector function which inspects the workshift’s label and returns True for the on-duty workshifts and False otherwise. Unless you override it, a timeline is accompanied by the default schedule whose selector returns the boolean value of the label.

时间表需要一些东西来宣布值班或下班的轮班。 这就是为什么要为每个工作班次提供标签，或者是在框架被标记到时间轴中时为它们贴标签的规则的原因。 每个计划表都定义一个选择器功能，该功能检查工作班次的标签并为当班工作班次返回True，否则返回False。 除非您覆盖它，否则时间轴将带有默认时间表，默认时间表的选择器将返回标签的布尔值。

Sometimes you want to define several schedules for the same timeline. For example, in a call center, there will be the schedule for the call center as a whole, and a separate schedule for each team of operators. The same workshift may be found on duty under some schedules and off duty under the others.

有时您想为同一时间线定义多个时间表。 例如，在呼叫中心，将为整个呼叫中心制定时间表，并为每个运营商团队制定单独的时间表。 在某些时间表中可能会发现相同的轮班值班，而在其他时间表中可能会发现下班。

Timeboard = timeline + schedules. More precisely, timeboard is a collection of work schedules based on a specific timeline of workshifts built upon a reference frame.

时间表=时间轴+时间表。 更准确地说， 时间表是基于建立在参考框架上的特定轮班 时间表的工作时间表的集合。

Once you have got a timeboard, you may carry out the useful work: do calendar calculations in order to solve the problems like those described in the prologue.

一旦有了时间表，您就可以进行有用的工作：进行日历计算，以解决序言中所述的问题。

Every computation performed with timeboard is duty-aware. The invoked method “sees” only workshifts with the specified duty and ignores the others. In order to reveal the duty of the workshifts, the method needs to be given a schedule. Therefore, each computation on the timeboard is parametrized with a duty and a schedule.

使用时间表执行的每项计算都具有责任意识。 被调用的方法仅“看到”具有指定职责的工作班次，而忽略其他工作。 为了揭示轮班的职责，需要给该方法一个时间表。 因此，时间表上的每个计算都带有职责和时间表。

By default, the duty is “on” and the schedule is the default schedule of the timeboard. For example, if you call count() without arguments on some interval of a timeboard, you will get the number of workshifts in the interval that are declared on duty under the default schedule. These defaults make life easier because in practice you will want to deal mostly with on-duty workshifts.

默认情况下，值班是“ on”，时间表是时间表的默认时间表。 例如，如果您在某个时间表的某个间隔上调用不带参数的count() ，则将获得在默认计划下宣布值班的间隔内的轮班数。 这些默认值使生活更轻松，因为在实践中，您将主要需要处理值班轮班。

API (The API)

The full timeboard documentation is available on Read the Docs.

完整的时间表文档可在“ 阅读文档”中找到 。

The package can be installed with the usual pip install timeboard.

该软件包可以按照通常的pip install timeboard 。

设置时间表 (Set up a timeboard)

The simplest way to get started is to use a preconfigured calendar which is shipped with the package. Let’s take a regular business day calendar for the United States.

最简单的入门方法是使用软件包随附的预配置日历。 让我们为美国制作一个正常的工作日日历。

>>> import timeboard.calendars.US as US >>> clnd = US.Weekly8x5()

clnd object is a timeboard (an instance of timeboard.Timeboard class). It has only one default schedule which selects weekdays as on-duty workshifts while weekends, as well as observations of US federal holidays, are declared off duty.

clnd对象是一个时间表( timeboard.Timeboard类的实例)。 它只有一个默认时间表，该时间表将工作日选择为工作日，而将周末以及对美国联邦假日的观察宣布为下班。

The tools for building your own timeboard will be briefly reviewed later on after we look at what you can do with a timeboard.

在研究您可以使用时间表后，稍后将简要回顾用于构建自己的时间表的工具。

轮班玩 (Play with workshifts)

Calling a timeboard instance clnd() with a single point in time retrieves the workshift that contains this point. How that you have a workshift you can query its duty:

用单个时间点调用时间表实例clnd()检索包含该时间点的工作clnd() 。 您如何进行轮班，可以查询其职责：

Is a certain date a business day?

某个日期是一个工作日？

>>> ws = clnd('27 May 2017')>>> ws.is_on_duty()False

Indeed, it was a Saturday.

确实，那是星期六。

You can also look into the future or in the past from the current workshift:

您还可以根据当前的轮班情况来展望未来或过去：

When was the next business day?

下一个工作日几点？

>>> ws.rollforward()Workshift(6359) of 'D' at 2017–05–30

The returned workshift has the sequence number of 6359 and represents the day of 30 May 2017, which, by the way, was the Tuesday after the Memorial Day holiday.

返回的轮班编号为6359，代表2017年5月30日，顺便说一下，这是阵亡将士纪念日假期后的星期二。

If we were to finish the project in 22 business days starting on 01 May 2017, when would be our deadline?

如果我们要从2017年5月1日开始的22个工作日内完成项目，那么我们的截止日期是什么时候？

>>> clnd('01 May 2017') + 22Workshift(6361) of 'D' at 2017–06–01

This is the same as:

这与以下内容相同：

>>> clnd('01 May 2017').rollforward(22)Workshift(6361) of 'D' at 2017–06–01

间隔播放 (Play with intervals)

Calling clnd() with a different set of parameters produces an object representing an interval on the calendar. The interval below contains all workshifts of the month of May 2017:

使用不同的参数集调用clnd()会生成一个对象，该对象表示日历上的间隔。 以下间隔包含2017年5月的所有工作班次：

>>> may2017 = clnd('May 2017', period='M')

How many business days were there in May?

五月份有几个工作日？

>>> may2017.count()22

How many days off?

多少天假？

>>> may2017.count(duty='off')9

How many working hours?

多少个工作时间？

>>> may2017.worktime()176

An employee was on the staff from April 3, 2017, to May 15, 2017. What portion of April’s salary did the company owe them?

从2017年4月3日到2017年5月15日，有一名员工在工作。 公司欠他们4月份工资的哪一部分？

Note that calling clnd() with a tuple of two points in time produces an interval containing all workshifts between these points, inclusively.

请注意，使用两个时间点的元组调用clnd()会产生一个间隔，其中包含这些点之间的所有工作班次。

>>> time_in_company = clnd(('03 Apr 2017','15 May 2017'))>>> time_in_company.what_portion_of(clnd('Apr 2017', period='M'))1.0

Indeed, the 1st and the 2nd of April in 2017 fell on the weekend, therefore, having started on the 3rd, the employee checked out all the working days in the month.

实际上，2017年4月1日和2月2日是在周末，因此，从3月3日开始，员工检查了该月的所有工作日。

And what portion of May’s?

梅的哪一部分？

>>> time_in_company.what_portion_of(may2017)0.5

How many days had the employee worked in May?

员工在五月份工作了几天？

The multiplication operator returns the intersection of two intervals.

乘法运算符返回两个间隔的交集。

>>> (time_in_company * may2017).count()11

How many hours?

多少个小时？

>>> (time_in_company * may2017).worktime()88

An employee was on the staff from 01 Jan 2016 to 15 Jul 2017. How many years had this person worked for the company?

从2016年1月1日到2017 年 7月15日，有一位员工在工作。 这个人在公司工作了多少年？

>>> clnd(('01 Jan 2016', '15 Jul 2017')).count_periods('A')1.5421686746987953

建立自己的时间表 (Build your own timeboard)

For the purpose of introduction, I will just plunge into two examples. If it seems too steep, please, find the thorough discussion of the construction tools in the project documentation.

为了介绍的目的，我将仅举两个例子。 如果看起来太陡峭，请在项目文档中找到有关施工工具的详尽讨论。

The import statement for this section:

本节的导入语句：

>>> import timeboard as tb

Let me return to a schedule of workshifts in the car dealership which I mentioned in the prologue. A mechanic works on Monday, Tuesday, Saturday, and Sunday this week, and on Wednesday, Thursday, and Friday next week; then the bi-weekly cycle repeats. The timeboard is created by the following code:

让我回到我在序言中提到的汽车经销店的轮班时间表。 技工在本周的周一，周二，周六和周日以及下周的周三，周四和周五工作； 然后每两周重复一次。 该时间表由以下代码创建：

>>> biweekly = tb.Organizer(marker='W',...     structure=[[1,1,0,0,0,1,1], [0,0,1,1,1,0,0]])>>> clnd = tb.Timeboard(base_unit_freq='D', ...     start='01 Oct 2017', end='31 Dec 2018', ...     layout=biweekly)

It makes sense to look into the last statement first. It creates a timeboard named clnd. The first three parameters define the frame to be a sequence of days (‘D’) from 01 Oct 2017 to 31 Dec 2018. The layout parameter tells how to organize the frame into the timeline of workshifts. This job is commissioned to an Organizer named biweekly.

首先查看最后一条语句是有意义的。 它创建一个名为clnd 。 前三个参数将框架定义为从2017年10月1日到2018年12月31日的天序列(' D ')。 layout参数告诉如何将框架组织到轮班时间轴中。 这项工作被委托给名为《 biweekly的Organizer 。

The first statement creates this Organizer which takes two parameters: marker and structure. We use amarker to place marks on the frame. The marks are kind of milestones which divide the frame into subframes, or “spans”. In the example marker=’W’ puts a mark at the beginning of each calendar week. Therefore, each span represents a week.

第一条语句创建此Organizer ，该Organizer带有两个参数： marker和structure 。 我们用一个marker放置在框架上的标记。 标记是一种里程碑，它将帧分为子帧或“跨度”。 在示例中， marker='W'在每个日历周的开始时放置一个标记。 因此，每个跨度代表一周。

The structure parameter tells how to create workshifts within each span. The first element of structure, the list [1,1,0,0,0,1,1], is applied to the first span (i.e. to the first week of our calendar). Each base unit (that is, each day) within the span becomes a workshift. The workshifts receive labels from the list, in order.

structure参数告诉您如何在每个跨度内创建轮班。 structure的第一个元素，列表[1,1,0,0,0,1,1]应用于第一个跨度(即，我们日历的第一周)。 跨度中的每个基本单位(即每天)成为一个轮班。 轮班从列表中按顺序接收标签。

The second element of structure, the list [0,0,1,1,1,0,0], is analogously applied to the second span (the second week). After this, since we’ve gotten no more elements, a structure is replayed in cycles. Hence, the third week is serviced by the first element of structure, the fourth week by the second, and so on.

structure的第二个元素，列表[0,0,1,1,1,0,0]类似地应用于第二个跨度(第二周)。 此后，由于我们没有更多的元素了，因此structure会循环播放。 因此，第三周由structure的第一个元素提供服务，第四周由第二个structure提供服务，依此类推。

As a result, our timeline becomes the sequence of days labeled with the number 1 when the mechanic is on duty and with the number 0 when he or she is not. We have not specified any schedule, because the schedule which is built by default suits us fine. The default schedule considers the boolean value of the label, so 1 translates into ‘on duty’, and zero into ‘off duty’.

结果，我们的时间轴变成了一天的顺序，当技工上班时以数字1标记，而当他或她不上班时则以数字0标记。 我们没有指定任何时间表，因为默认情况下构建的时间表适合我们。 默认计划会考虑标签的布尔值，因此1 “值班”，零表示“值班”。

With this timeboard, we can do any type of calculations that we have done earlier with the business calendar. For example, if a person was employed to this schedule from November 4, 2017, and salary is paid monthly, what portion of November’s salary has the employee earned?

使用此时间表，我们可以执行我们之前使用业务日历进行的任何类型的计算。 例如，如果某个人从2017年11月4日开始按此时间表工作，并且每月支付薪水，那么该员工赚取了11月薪水的哪一部分？

>>> time_in_company = clnd(('4 Nov 2017', None))>>> nov2017 = clnd('Nov 2017', period='M')>>> time_in_company.what_portion_of(nov2017)0.8125

In the second example we will build a timeboard for a call center. The call center operates round-the-clock in shifts of varying length: 08:00 to 18:00 (10 hours), 18:00 to 02:00 (8 hours), and 02:00 to 08:00 (6 hours). An operator’s schedule consists of one on-duty shift followed by three off-duty shifts. Hence, four teams of operators are needed. They are designated as ‘A’, ‘B’, ‘C’, and ‘D’.

在第二个示例中，我们将为呼叫中心构建时间表。 呼叫中心全天候运行，轮班时间不定：08：00至18:00(10小时)，18：00至02:00(8小时)和02:00至08:00(6小时) )。 操作员的时间表包括一个值班班，然后是三个值班班。 因此，需要四个操作员团队。 它们被指定为“ A”，“ B”，“ C”和“ D”。

>>> day_parts = tb.Marker(each='D', ...     at=[{'hours':2}, {'hours':8}, {'hours':18}])>>> shifts = tb.Organizer(marker=day_parts, ...     structure=['A', 'B', 'C', 'D'])>>> clnd = tb.Timeboard(base_unit_freq='H', ...     start='01 Jan 2009 02:00', end='01 Jan 2019 01:59',...     layout=shifts)>>> clnd.add_schedule(name='team_A', ...    selector=lambda label: label=='A')

There are four key differences from the dealership case. We will examine them one by one.

与经销商案例有四个主要区别。 我们将逐一检查它们。

First, the frame’s base unit is now a one-hour period (base_unit_freq='H') instead of a one-day period of the dealership’s calendar.

首先，框架的基本单位现在是一个小时周期( base_unit_freq='H' )，而不是经销商日历的一天周期。

Second, the value of the marker parameter of the Organizer is now a complex object instead of a single calendar frequency it was before. This object is an instance of Marker class. It is used to define rules for placing marks on the frame when the simple division of the frame into uniform calendar units is not sufficient. The signature of the Marker above is almost readable — it says: place a mark on each day (‘D’) at 02:00 hours, 08:00 hours, and 18:00 hours.

其次，管理器的marker参数的值现在是一个复杂的对象，而不是以前的单个日历频率。 该对象是Marker类的实例。 当框架简单地划分为统一的日历单位不足时，它用于定义在框架上放置标记的规则。 上面的标记的签名几乎是可读的-它说：在每天的02：00、08：00和18:00标记一个标记('D') 。

Third, the value of the structure is now simpler: it is a one-level list of teams’ labels. When an element of the structure is not an iterable of labels but just one label, its application to a span produces a single workshift which, literally, spans the span.

第三， structure的价值现在更简单：它是团队标签的一个单级列表。 当structure的元素不是标签的可迭代变量而是仅一个标签时，将其应用于跨度将产生一个单独的轮班，从字面上看，跨度。

In our example, the very first span comprises six one-hour base units starting at 2, 3, 4 … 7 o’clock in the morning of 01 Jan 2009. All these base units are combined into the single workshift with label ‘A’. The second span comprises ten one-hour base units starting at 8, 9, 10 … 17 o’clock. These base units are combined into the single workshift with label ‘B’, and so on. When all labels have been taken, the structure is replayed, so the fifth span (08:00:00–17:59:59 on 01 Jan 2009) becomes a workshift with label ‘A’.

在我们的示例中，第一个跨度包括六个1小时基本单位，从2009年1月1日上午的2、3、4…7点开始。所有这些基本单位合并为带有标签“ A”的单个轮班。 第二个跨度包括从8点，9点，10点…17点开始的十个一小时基本单元。 这些基本单元被组合到带有标签“ B”的单个工作班次中，依此类推。 取完所有标签后，将重播该结构，因此第五个跨度(2009年1月1日，08：00：00–17：59：59)成为带有标签“ A”的轮班工作。

To recap, if an element of structure is a list of labels, each base unit of the span becomes a workshift and receives a label from the list. If an element of structure is a single label, all base units of the span are combined to form a single workshift which receives this label.

概括地说，如果structure的元素是标签列表，则范围的每个基本单位将变为工作班次并从列表中接收标签。 如果structure的元素是单个标签，则跨度的所有基本单位将合并以形成一个接收此标签的单个工作班次。

And finally, we explicitly created a schedule for team A. The default schedule does not serve our purpose as it returns “always on duty”. This is true for the call center as a whole but not so for a particular team. For the new schedule, we supply the name and the selector function which returns True for all workshifts labeled with ‘A’. For the practical use, you will want to create the schedules for the other teams as well.

最后，我们为A团队明确创建了一个时间表。默认时间表无法达到我们的目的，因为它返回“总是值班”。 整个呼叫中心都是这样，但特定团队却不是。 对于新的时间表，我们提供名称和选择器功能，该功能对于标有“ A”的所有轮班返回True。 对于实际使用，您还需要为其他团队创建时间表。

This timeboard is as good to work with as any other. However, this time we will have to explicitly specify the schedule we want to use.

此时间表与其他时间表一样好。 但是，这一次我们将必须明确指定要使用的时间表。

>>> schedule_A = clnd.schedules['team_A']

How many shifts did the operators of team A sit in November 2017?

A团队的运营商在2017年11月坐了多少班？

>>> nov2017 = clnd('Nov 2017', period='M', schedule=schedule_A)>>> nov2017.count()22

And how many hours were there in total?

总共有几个小时？

>>> nov2017.worktime()176

A person was employed as an operator in team A from November 4, 2017. Salary is paid monthly. What portion of November’s salary has the employee earned?

自2017年11月4日起，一个人被聘为A队的操作员。月薪。 雇员赚了11月工资的哪一部分？

>>> time_in_company = clnd(('4 Nov 2017',None), schedule=schedule_A)>>> time_in_company.what_portion_of(nov2017)0.9090909090909091

更多用例 (More use cases)

You can find more use cases (taken almost from real life) in the jupyter notebook which is the part of the project documentation.

您可以在jupyter笔记本中找到更多用例(几乎取自现实生活)，这是项目文档的一部分。

Please feel free to use timeboard and do not hesitate to leave feedback or open issues on GitHub .

请随时使用timeboard ，不要犹豫，在GitHub上留下反馈或公开问题。

翻译自: https://www.freecodecamp.org/news/introducing-timeboard-a-python-business-calendar-package-a2335898c697/

python 添加数据库表