[PYTHON] Play by converting the Japanese dialect corpus into a database (8) Add a file format conversion function
This is a serialized article. Since we have created a list of utterances for each discourse / speaker up to the last time, this time we will implement the final function "Mutual conversion between Excel and TextGrid on the site". It's a work memo for myself, and I don't think there are enough explanations, but please forgive me.
As mentioned earlier, this time we already have a Python script that converts TextGrid and Excel (we won't go into details), so we'll aim to embed it in our Laravel app and run it on the server.
--Part 1: Playing by converting the Japanese dialect corpus into a database (1) Thinking about the configuration --Part 2: Play with Japanese dialect corpus as DB (2) DB with SQLite3 --Third: Play with Japanese dialect corpus as DB (3) Operate with PHP Laravel --The 4th: Playing by converting the Japanese dialect corpus into a database (4) Deciding the overall picture of the service --Fifth: Playing by converting the Japanese dialect corpus into a database (5) Database migration and model creation --The 6th: Play by converting the Japanese dialect corpus into a database (6) Make a list of utterances for each discourse --The 7th: Play by converting the Japanese dialect corpus into a database (7) Make a list of utterances for each speaker ――The 8th: Play by converting Japanese dialect corpus into DB (8) Add file format conversion function ← Now here ――The 9th: Play Japanese dialect corpus as DB (9) Deploy with Heroku
Advance preparation
This time, we will save the file in the storage on the server, convert it, and create a mechanism to download it, so first make the settings around that. Uploaded files are saved in the storage folder, but since it is the public folder that is open to the public, it is customary to symlink public / storage to storage / app / public. You can paste it by yourself with the artisan command below [^ symbolic].
[^ symbolic]: As will be described later, locally created symbolic links will not be created on Heroku without permission, so write the necessary instructions in composer.json and create a symbolic link at build time. You need to be able to.
cmd
php artisan storage:link
-Laravel 7.x File Storage | ReaDouble
Screen transition diagram
The screen transition diagram will be posted again. It's one page.
Component routing
Since there is only one component, there is nothing special to explain.
resources/js/app.js
+ import ConvertComponent from "./components/ConvertComponent";
+ {
+ path: "/convert",
+ name: "convert",
+ component: ConvertComponent
+ }
Creating a component
Although it is one screen, it is more complicated than the last time because it has many functions.
resouces/js/components/ConvertComponent.vue
<template>
<div>
<form enctype="multipart/form-data">
<input
type="file"
name="file"
id="fileRef"
style="display: none"
@change="fileSelected"
/>
<div class="input-group">
<input
type="text"
id="fileShow"
class="form-control"
placeholder="select file..."
readonly
/>
<div class="input-group-append">
<span class="input-group-btn">
<button
type="button"
class="btn btn-outline-success"
onclick="fileRef.click()"
>
Browse
</button>
</span>
<button
type="button"
class="btn btn-success"
@click="fileUpload"
>
Upload
</button>
</div>
</div>
</form>
<div class="pt-3">
<table class="table table-sm table-striped">
<thead>
<tr class="thead-dark">
<th colspan="2">
<div class="text-center">File list</div>
</th>
</tr>
</thead>
<tbody>
<tr v-for="file of files" v-bind:key="file.name">
<td>
<span class="pl-3">{{ file.replace("public/", "") }}</span>
</td>
<td>
<div class="text-right">
<span
class="btn btn-success btn-sm"
@click="toTextgrid(file)"
v-if="file.indexOf('.xls') != -1"
>
to TextGrid
</span>
<span
class="btn btn-outline-success btn-sm disabled"
v-else
>
to TextGrid
</span>
<span
class="btn btn-success btn-sm"
@click="toExcel(file)"
v-if="file.indexOf('.txt') != -1 || file.indexOf('.TextGrid') != -1"
>
to Excel
</span>
<span
class="btn btn-outline-success btn-sm disabled"
v-else
>
to Excel
</span>
<a
v-bind:href="'./storage' + file.replace('public', '')"
v-bind:download="file.replace('public', '')"
>
<span class="btn btn-warning btn-sm">
download
</span>
</a>
<span
class="btn btn-danger btn-sm"
@click="deleteFile(file)"
>
delete
</span>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
</template>
<script>
export default {
data: function() {
return {
files: [],
uploadingFileInfo: ""
};
},
methods: {
fileSelected(event) {
this.uploadingFileInfo = event.target.files[0];
fileShow.value = fileRef.value.replace("C:\\fakepath\\", "");
},
fileUpload() {
if (this.uploadingFileInfo) {
const formData = new FormData();
formData.append("file", this.uploadingFileInfo);
axios.post("/api/toolkit/upload", formData).then(res => {
fileRef.value = "";
fileShow.value = "";
this.uploadingFileInfo = "";
this.getFileList();
});
} else {
alert("Please select the file to upload");
}
},
getFileList() {
axios.get("/api/convert/files").then(res => {
this.files = res.data;
});
},
to_textgrid(path) {
axios.post("/api/convert/toTextgrid", { filepath: path }).then(() => {
this.getFileList();
});
},
to_excel(path) {
axios.post("/api/convert/toExcel", { filepath: path }).then(() => {
this.getFileList();
});
},
deleteFile(path) {
axios.post("/api/convert/delete", { filepath: path }).then(() => {
this.getFileList();
});
}
},
mounted() {
this.getFileList();
}
};
</script>
File selection form
The file form doesn't look very good with bootstrap alone. Some simple methods have been devised, but this time I referred to the following site.
-Bootstrap's file upload form is ugly, so I decorated it | vdeep
Convert button
The [to TextGrid] and [to Excel] buttons are switched according to the file extension so that they are fired by clicking only when the extension is appropriate. Since there is a format called TextGrid, it is not possible to use case classification by mimetype, so it is simply classified by whether the file name contains a character string such as .txt or .TextGrid [^ validate] ]. The case classification itself is v-if ・ v-else.
[^ validate]: Actually, it is not a verification of such a front end, but the input file must be verified properly on the server side.
<!-- .txt/.TextGrid displays the above enable button-->
<span
class="btn btn-success btn-sm"
@click="toExcel(file)"
v-if="file.indexOf('.txt') != -1 || file.indexOf('.TextGrid') != -1"
>
to Excel
</span>
<!--If not, show the disable button below-->
<span
class="btn btn-outline-success btn-sm disabled"
v-else
>
to Excel
</span>
Download button
Various conversions and deletions are done by clicking to execute the function, but only the download has a direct link to the file. There are several ways to download a file from Laravel's server, but the method using the Storage façade andresponse ()did not work (several losses) [^ down], so link directly. I adopted the method of pasting.
If you get the file path by a simple method as described later, the path under / storage / app will be returned (the path like = /public/filename.ext will be returned), so replace it appropriately and symbolic link. Paste the link directly to the previous (/ public) /storage/filename.ext.
-How to download Laravel file | Earl effect
download link
<a
v-bind:href="'./storage' + file.replace('public', '')"
v-bind:download="file.replace('public', '')"
>
<span class="btn btn-warning btn-sm">
download
</span>
</a>
[^ down]: The path resolution failed, a 403 error occurred, and the file contents were piled up in the POST response but could not be downloaded.
Routing to controller
Since everything is implemented in FileController, write the routing in ʻapi.php`, considering the function name appropriately.
routes/api.php
+ Route::get('/convert/files', 'FileController@getFileList');
+ Route::post('/convert/upload', 'FileController@upload');
+ Route::post('/convert/e_t', 'FileController@toTextgrid');
+ Route::post('/convert/t_e', 'FileController@toExcel');
+ Route::post('/convert/delete', 'FileController@deleteFile');
Creating a controller
We will implement the five functions we decided to use earlier.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\Storage;
class FileController extends Controller{
//Upload and save the file
public function upload(Request $request){
$filename = $request->file('file')->getClientOriginalName();
$request->file('file')->storeAs('public/',$filename);
}
//Convert Excel to TextGrid and save
public function toTextgrid(Request $request) {
exec("which python", $pythonpath);
$scriptpath = app_path('Python/excel_to_textgrid.py');
$filepath = storage_path('app/' . $request->input('filepath'));
$command = $pythonpath[0] . ' ' . $scriptpath . ' ' . $filepath;
exec($command);
}
//Convert TextGrid to Excel and save; almost the same as above
public function toExcel(Request $request) {
exec("which python", $pythonpath);
$scriptpath = app_path('Python/textgrid_to_excel.py');
$filepath = storage_path('app/' . $request->input('filepath'));
$command = $pythonpath[0] . ' ' . $scriptpath . ' ' . $filepath;
exec($command);
}
//Get a list of files
public function getFileList(){
//true is.Exclude dotfiles such as gitignore
$files = Storage::allfiles('public/', true);
//SplFileInfo type is awkward on javascript, so return it as a file path string (bad?)
$filepaths = explode('#', implode('#', $files));
return $filepaths;
}
//Delete file
public function deleteFile(Request $request){
$filepath = $request->input('filepath');
Storage::delete($filepath);
}
}
Run python script
As long as you have Python installed on your server, you can run Python with PHP's ʻexec` command. As we'll see later, don't forget to install the modules you use with Python when building Heroku.
Since Heroku is a Linux system, [^ dyno], I will write it with the Linux command in mind. This time, I developed it on Windows 10 without container virtualization, but since this article deals with a little bit, there was no big problem.
[^ dyno]: Heroku uses Dyno, a lightweight Linux container that runs on a huge instance of Amazon EC2.
The procedure to execute is simple. The script used this time is "If you give the path of the target file, that file is converted and saved in the same directory", so ** Python executable path, script path, target file path ** All you have to do is get it and build a command based on it. This time, the script is put under / app / Python, so you can safely get the path using a path helper such as ʻapp_path` (otherwise it will be unstable against root misalignment). ).
-Laravel 7.x Helper | ReaDouble
<?php
//Convert Excel to TextGrid and save
public function toTextgrid(Request $request) {
//Get the path to python in the runtime environment
//Exec for Windows cmd("where python", $pythonpath);
exec("which python", $pythonpath);
//Get the path of the python script you want to run
$scriptpath = app_path('Python/excel_to_textgrid.py');
//Get the POSTed filepath and convert it to the appropriate relative path
$filepath = storage_path('app/' . $request->input('filepath'));
//Assemble and execute commands
//Note index if your environment has multiple versions of Python
$command = $pythonpath[0] . ' ' . $scriptpath . ' ' . $filepath;
exec($command);
}
The script used here is set to save the output file in the same directory as the input file.
Completion drawing
It should look like this.
/convert
Improvement points
In addition to the error handling mentioned above, ʻexec is a big problem in terms of security. In general, it is very dangerous to just throw data that can be tampered with by the user into PHP's ʻexec function, so it must be escaped appropriately. This time I'm going through Laravel's path helper, so I think it's okay, but unless you know the exact behavior of the helper, you should do everything in your power.
next time
I will raise it to Heroku (final).
――The 9th: Play Japanese dialect corpus as DB (9) Deploy with Heroku
Recommended Posts