这个虚拟机节省了我的时间

Have you ever struggled when running your Python Scripts locally? Very general, isn’t it? Let me enumerate some struggling examples below:

在本地运行Python脚本时，您是否遇到过困难？ 很笼统，不是吗？ 让我列举以下一些艰难的例子：

• Local Storage
  本地存储
• Network Availability
  网络可用性
• PC always turned on
  PC始终打开

As I’ve been working with a Python Project that requires frequent execution, the examples above are the main ones that encouraged me to look for how to run away from handling it locally.

由于我一直在处理需要频繁执行的Python项目 ，因此上面的示例是鼓励我寻找如何摆脱本地处理的主要示例。

And why’s that? Because I’m not always logged in my computer and, even so, I didn’t want to keep executing my Python Script every 15 minutes (or any other frequency).

那为什么呢？ 因为我并不总是登录计算机，即使这样，我也不想每15分钟(或任何其他频率)继续执行我的Python脚本。

This scenario led me to define the following Project Structure:

这种情况导致我定义了以下项目结构：

Project Design — By the Author

项目设计—作者

In order to achieve so, I invite you to check the following sessions, in which I covered the step-by-step and the main information regarding each of these Project elements:

为了实现这一目标，我邀请您检查以下课程，其中涵盖了有关每个项目元素的逐步说明和主要信息：

• Google Sheets (1) and API (2)
  Google表格(1)和API(2)
• Google Cloud Platform (3) and Linux VM (4)
  Google Cloud Platform(3)和Linux VM(4)

PS: I grouped the different elements as above because I think it gets easier to follow up the flow. The first group shows you what is our goal and anticipates us the API enablement. The latter group covers the actions needed on Google Platform and, then, move forward with the Linux VM itself (including the Python Script).

PS：我将上述不同元素进行了分组，因为我认为跟进流程变得更加容易。 第一组向您展示我们的目标是什么，并期望我们实现API。 后一组讨论了Google平台上所需的操作，然后继续使用Linux VM本身(包括Python脚本)。

1. Google表格 (1. Google Sheets)

As just mentioned, let’s take a look at what is this Python Project. The goal is to update every 15 minutes the current price of each owned Stock (from the Brazilian market).

如前所述，让我们看一下什么是Python项目。 我们的目标是每15分钟更新每只拥有的股票(来自巴西市场)的当前价格。

The initial data is available on Google Sheets, as the session’s title suggests. Below, you can see the data being considered in this Project:

如会议标题所示，初始数据可在Google表格上找到。 在下面，您可以看到该项目中正在考虑的数据：

Google Sheets’ Original Data

Google表格的原始数据

Let’s check each column:

让我们检查每一列：

• Stock: Company’s transaction code (it’s been considered the most negotiated stocks covered in IBOV, as of August 25th)
  股票：公司的交易代码(截至8月25日，它被认为是IBOV涵盖的谈判最多的股票)
• Qty: Quantity Owned
  数量：拥有数量
• Book Price: Asset’s price value at purchase (it’s been considered the stocks’ prices as of 31st July close market)
  账面价格：资产的购买价(被视为截至7月31日收盘价的股票价格)
• Price: Current Price (at each application execution)
  价格：当前价格(每次执行应用程序时)
• Unit P/L: “Book Price” - “Price”
  单位损益：“帐面价格”-“价格”
• Total P/L: “Unit P/L” * “Qty”
  总损益：“单位损益” *“数量”
• Total P/L (%): “P/L” / (“Book Price” * “Qty”)
  总损益(％)：“损益” /(“帐面价格” *“数量”)

You might be asking yourself what does Python is going to do here. The answer is: update the column “Price”. By doing so, the dependent columns are going to be updated accordingly.

您可能会问自己，Python在这里将要做什么。 答案是：更新“价格”列。 这样，将相应地更新从属列。

The other fields are:

其他字段是：

• Last Update: Python is going to update this cell as it runs each time. This helps us to check if Python is “reaching” Google Sheets;
  最后更新：Python将在每次运行时更新此单元格。 这有助于我们检查Python是否正在“访问” Google表格；
• IBOV: It’s the most important Brazilian Financial Market index. Is being used as a reference for the Python Script (is going to be explained further on);
  IBOV：这是巴西最重要的金融市场指数。 被用作Python脚本的参考(将在后面进行解释)；
• Total: Summarizes the total results.
  总计：汇总总计结果。

Until here, the struggling point of Local Storage has been solved.

到这里为止，本地存储的难题已经解决。

2. API (2. API)

As briefly mentioned in the last session, Python needs to “reach” Google Sheets. This is performed by enabling some Google APIs in order to authorize and authenticate the Python application to “Acces, read and write data” on Google Sheets.

如上一课所述，Python需要“访问” Google表格。 这是通过启用一些Google API来执行的，以授权和认证Python应用程序以在Google表格上“访问，读取和写入数据”。

In the article below, I covered the step-by-step of enabling the mentioned APIs. I invite you to check the thoroughly step-by-step covered there.

在下面的文章中，我逐步介绍了启用上述API的步骤。 我邀请您检查其中的详细分步说明。

We’ve reached the first part, which enables you to take a look at the data that are going to be managed.

我们已经到了第一部分，它使您可以查看将要管理的数据。

Now, let’s take a look at the actions needed to deploy the application itself.

现在，让我们看一下部署应用程序本身所需的操作。

3. Google Cloud Platform (3. Google Cloud Platform)

Google Cloud Platform was used as the Cloud Infra Service for this Project. Like other Cloud Services, Google’s platform offers you Computing Resources as-a-Service.

Google Cloud Platform被用作该项目的Cloud Infra服务。 与其他Cloud Services一样，Google的平台为您提供“ 计算资源即服务”。

And in order to use these Computing Resources you need to:

为了使用这些计算资源，您需要：

3.1. Create a project on Google Cloud Platform;

3.1。 在Google Cloud Platform上创建一个项目；

3.2. Instantiate a VM.

3.2。 实例化VM。

Let’s check each of them.

让我们检查每个。

3.1。 在Google Cloud Platform上建立专案 (3.1. Create a project on Google Cloud Platform)

You can create your project in a single step:

您可以一步创建项目：

3.2。 实例化虚拟机 (3.2. Instantiate a VM)

Click on “Compute Engine” at the left-side-bar menu > VM Instances. Then, click on “Create Instance” and choose some of the available parameters:

单击左侧栏菜单> VM实例上的“计算引擎”。 然后，单击“创建实例”并选择一些可用参数：

As you can see from the above image, there are some parameters regarding the region your VM’s resources are physically located in and also the number of vCPUs you are considering. Please, evaluate if this is sensible to your case. For this project, the following is more than enough. It’s also important to mention that Google is currently offering some credits to be used with this Cloud Service, however, it’s a chargeable service.

从上图可以看到，有一些参数与VM资源的物理位置以及要考虑的vCPU数量有关。 请评估这是否适合您的情况。 对于此项目，以下内容已绰绰有余。 同样重要的是，Google目前提供一些可用于此Cloud Service的积分，但是，这是一项收费服务。

The image above is important, as it defines a specific Linux distribution being considered in this project (Debian).

上图非常重要，因为它定义了此项目(Debian)中考虑的特定Linux发行版。

After defining these parameters and clicking on “Create”, you should be able to see the following entry listed on your VM Instances page:

定义这些参数并单击“创建”后，您应该能够看到“ VM实例”页面上列出的以下条目：

By pressing to start the SSH connection, the Linux Terminal will be opened.

按开始SSH连接，将打开Linux终端。

4. Linux虚拟机 (4. Linux VM)

As both the Infra and APIs have been enabled, now it’s time to set up the Linux VM you’ve just instantiated.

由于已启用Infra和API，现在是时候设置刚刚实例化的Linux VM。

The major steps are:

主要步骤是：

4.1. Deploying Python Script

4.1。 部署Python脚本

4.2. Setting execution recurrence.

4.2。 设置执行重复。

How do they work?

它们如何工作？

4.1。 部署Python脚本 (4.1. Deploying Python Script)

Firsts things first. Let’s check if any Python version is already installed in your environment.

首先是事情。 让我们检查您的环境中是否已安装任何Python版本。

You could navigate to /usr/bin and check if/which Python versions already installed. However, it’s way simpler to just type python3 and check if the environment recognizes it as a system command:

您可以导航到/usr/bin 并检查是否已经安装了哪个Python版本。 但是，仅键入python3并检查环境是否将其识别为系统命令更简单：

As you can see from the above, python3 (version 3.5.3) is already installed. However, pip3, which is the command used for new packages installation, hasn’t been recognized. So, let’s install it using sudo apt install python3-pip (make sure to press Y over the installation process).

从上面可以看到，已经安装了python3(版本3.5.3)。 但是，尚未识别出pip3(用于安装新软件包的命令)。 因此，让我们使用sudo apt install python3-pip 进行 sudo apt install python3-pip (确保在安装过程中按Y)。

Let’s check if it’s been installed by typing pip3 --version :

让我们通过输入pip3 --version来检查它是否已安装：

Everything’s ok now.

现在一切都很好。

Let’s navigate to the directory we’re going to create the .py file:

让我们导航到要创建.py文件的目录：

In this example, I created a new directory on /home/lucasnunesfe9 called pythonScript (in this example, “lucasnunesfe9” is the Cloud Service username. Make sure to use yours).

在此示例中，我在/home/lucasnunesfe9上创建了一个名为pythonScript的新目录(在此示例中，“ lucasnunesfe9”是Cloud Service用户名。请确保使用您的用户名)。

Access this new directory and create your .py file:

访问此新目录并创建您的.py文件：

After typing nano myScript.py, you’re supposed to write the Python script itself:

输入nano myScript.py ，您应该编写Python脚本本身：

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime

We begin by importing the needed libraries:

我们首先导入所需的库：

• gspread is needed for handling data in Google Sheets;

  gspread是处理Google表格中的数据所必需的；

• oauth2client is needed for Authentication and Authorization via API;

  通过API进行身份验证和授权需要oauth2client ；

• datetime is needed for printing the moment the script is running.

  在脚本运行时打印，需要datetime 。

scope = ['https://www.googleapis.com/auth/drive']#this variable reads the json file that holds your credentials 
credentials = ServiceAccountCredentials.from_json_keyfile_name('/home/lucasnunesfe9/pythonScript/your_file_name.json', scope)gc = gspread.authorize(credentials)#spreadsheet file named "example"
sps = gc.open('example')#first worksheet = 0
wks = sps.get_worksheet(0)

The above part is enough for making sure you can connect to Google Sheets. Make sure to give the full path towards your .json file (this file needs yet to be created).

上面的部分足以确保您可以连接到Google表格。 确保提供指向您的.json文件的完整路径(此文件需要创建)。

#updates current time
dt_string = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
wks.update_cell(2,1,dt_string)#this function will loop through the column reference until it finds "IBOV" cell value
def getLastRow(reference):
    #i = reference cell's position
    i = wks.find(reference).row    #loops i trough a static column (reference)
    while wks.cell(i, wks.find(reference).col).value != "IBOV":
        i += 1    #subtract the last iteration
    return (i - 1)#this function writes the Google Finance API's formula in each cell of the column "Price"
def currentPrice (reference):    #loops i from the reference row to the referece value until getLastRow value
    for i in range(wks.find(reference).row+1,getLastRow("Stock")+1):        #writes current Stock price available from Google Finance API according colum 1 ("Stock" column)
        #preserve needed Google Finance's syntax
        wks.update_cell(i,wks.find(reference).col,"=GOOGLEFINANCE
("+"\""+wks.cell(i,1).value+"\""+")")#running both functions
currentPrice("Price")

The part above is the one which really performs what we need. In summary, it updates the time on which the script is being executed and loops through all the Stocks in order to update their current Prices. In order to achieve the latter, we used Google Finance API.

上面的部分是真正执行我们需要的部分。 总而言之，它会更新脚本的执行时间，并循环遍历所有股票以更新其当前价格。 为了实现后者，我们使用了Google Finance API。

After finishing to write the Script, hit Ctrl + Shift, then Y, and then hit Enter. This will save your myScript.py file.

完成编写脚本后，按Ctrl + Shift，然后按Y，然后按Enter。 这将保存您的myScript.py文件。

Let’s now create the .json file as just made with .py file:

现在让我们创建.json文件，就像用.py文件制作的一样：

Make sure to follow the tutorial I shared at the beginning of this article. You can check there more details on the content of this .json file (it’s simple). Then, after pasting the content in the .json file, Ctrl + Shift, then Y, and then hit Enter.

确保遵循本文开头分享的教程。 您可以在此查看有关此.json文件内容的更多详细信息(很简单)。 然后，将内容粘贴到.json文件中后，先按Ctrl + Shift，然后按Y，然后按Enter。

Let’s set the recurrence you’re going to execute the Python Script.

让我们设置要执行Python脚本的重复周期。

4.2。 设置执行重复 (4.2. Setting execution recurrence)

For this task, we’re going to use crontab jobs.

对于此任务，我们将使用crontab作业 。

First, a sanity check. Let’s see if there’s already any crontab job created for your user with crontab -l :

首先，进行健全性检查。 让我们看看是否已经使用crontab -l为您的用户创建了任何crontab作业：

Nothing… Let’s create a new one with crontab -e :

没什么...让我们用crontab -e创建一个新的：

Type 1. The following window will be opened. Go straight to the last line:

类型1 。 将打开以下窗口。 直接转到最后一行：

What does the last line mean?

最后一行是什么意思？

As the explanation given on the screen, each portion means a unit of time:

如屏幕上的说明，每个部分表示一个时间单位：

• */15 : every 15 minutes

  */15 ：每15分钟

• 13-20 : between hours 13 and 20 (UTC time). This range covers the period Brazilian Financial Market is open (Brazil’s Time Zone is UTC - 3)

  13-20 ：在13到20小时之间(UTC时间)。 此范围涵盖了巴西金融市场开放期间(巴西的时区为UTC-3)

• * : every day of the month (dom)

  * ：每月的每一天(dom)

• * : every month (mon)

  * ：每月(星期一)

• MON-FRI : from Monday to Friday

  MON-FRI ：从星期一到星期五

So, every 15 minutes of weeks days, between 13 and 20, my script located on /home/lucasnunesfe9/pythonScript/myscript.py will be executed by python3 . One more time, save the file.

因此，在工作日的每15分钟中的13到20之间，位于/home/lucasnunesfe9/pythonScript/myscript.py脚本将由python3执行。 再过一遍，保存文件。

Await until your script is supposed to be executed and, then, type sudo service cron status | tail -n 1 :

等待，直到应该执行脚本，然后键入sudo service cron status | tail -n 1 sudo service cron status | tail -n 1 ：

You can see that the crontab has been executed (note that this doesn’t necessarily mean the Python Script has been executed properly).

您可以看到crontab已执行(请注意，这不一定意味着Python脚本已正确执行)。

So, what’s happened with Google Sheets?

那么，Google表格如何处理？

Google Sheets’ after 1st Python execution (15h45)

第一次执行Python之后的Google表格(15h45)

Seems ok… one more time:

似乎还可以…再过一次：

And, then:

然后：

Google Sheets’ after 2nd Python execution (16h00)

第2次Python执行后的Google Sheets(16h00)

Working just fine!

工作正常！

Now, both remaining struggling points, Network Availability and the need of keeping your PC always turned on, have also been solved.

现在，所有剩余的难题，网络可用性和保持PC始终打开的需求都得到了解决。

翻译自: https://towardsdatascience.com/this-virtual-machine-is-saving-my-time-23076f592b94