javascript - 在 React Application 的节点 js 服务器中运行创建数据库的 python 脚本
问题描述
我需要在单击 React 应用程序中的按钮时运行 python 脚本。我正在使用 Node 来运行 python 脚本。python 脚本创建一个 sqlite3 数据库并且不打印任何内容。我该怎么做这个操作？如果我只是从这个脚本中打印任何东西，那么我可以通过节点 API 调用来获取它，但是如果我正在运行我的原始脚本，我什么也得不到，数据库没有被创建。
我的节点 server.js 文件如下 -
const express = require('express');
const app = express();
app.get('/api/python', function (req, res) {
var dataToSend;
var spawn = require('child_process').spawn;
var process = spawn('python', ['./projectdm.py']);
process.stdout.on('data', function (data) {
res.send(data.toString());
});
process.on('close', (code) => {
console.log(`child process close all stdio with code ${code}`);
console.log(dataToSend)
// send data to browser
});
});
app.listen(8080, () => {
console.log('Server listening on the port 8080');
});
我在按钮单击时调用并调用节点 API 的反应应用程序功能如下 -
onProcessHandler = () => {
console.log('started');
axios.get("http://localhost:8080/api/python")
.then(res => {
console.log(res.data);
this.setState({jsonData: res.data});
console.log('Processing success')
})
.catch(err => {
console.log('Processing fail')
})
console.log('ended');
}
我的python脚本文件如下 -
import sqlite3
import pandas as pd
conn = sqlite3.connect('data.db')
c = conn.cursor()
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='hotel_record_historical'; ''')
if (c.fetchone()[0]==0) :
c.execute('''CREATE TABLE hotel_record_historical ([generated_id] INTEGER PRIMARY KEY, [Customer Name] text, [Aadhar no] text, [Contact no] text, [Address(House No, Street)] text, [City of Residence] text, [State of Residence] text, [City of Hotel] text, [State of Hotel] text, [Stay Date] date);''')
read_hist_hotel=pd.read_csv(r'central_hotel_record_historical.csv')
read_hist_hotel.to_sql('hotel_record_historical', conn, if_exists='append', index = False)
conn.commit()
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='hotel_record_current' ;''')
if c.fetchone()[0]==0 :
c.execute('''CREATE TABLE hotel_record_current ([generated_id] INTEGER PRIMARY KEY, [Customer Name] text, [Aadhar no] text, [Contact no] text, [Address(House No, Street)] text, [City of Residence] text, [State of Residence] text, [City of Hotel] text, [State of Hotel] text, [Stay Date] date);''')
read_cur_hotel = pd.read_csv (r'central_hotel_record_current.csv')
read_cur_hotel.to_sql('hotel_record_current', conn, if_exists='append', index = False)
conn.commit()
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='fir_record_historical' ;''')
if c.fetchone()[0]==0 :
c.execute('''CREATE TABLE fir_record_historical([Complainant Name] text, [Aadhar no] text, [Mobile No] text, [Incident Date] date, [Report Date] date);''')
read_hist_fir = pd.read_csv (r'FIR_stolen_no_historical.csv')
read_hist_fir.to_sql('fir_record_historical', conn, if_exists='append', index = False)
conn.commit()
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='fir_record_current' ;''')
if c.fetchone()[0]==0 :
c.execute('''CREATE TABLE fir_record_current([Complainant Name] text, [Aadhar no] text, [Mobile No] text, [Incident Date] date, [Report Date] date);''')
read_cur_fir = pd.read_csv (r'FIR_stolen_no_current.csv')
read_cur_fir.to_sql('fir_record_current', conn, if_exists='append', index = False)
conn.commit()
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='Registered_numbers' ;''')
if c.fetchone()[0]==0 :
c.execute('''CREATE TABLE Registered_numbers([generated_id] INTEGER PRIMARY KEY, [Name] text, [Mobile no] text, [Aadhar no] text);''')
read_registered = pd.read_csv (r'registered_phone_records.csv')
read_registered.to_sql('Registered_numbers', conn, if_exists='append', index = False)
conn.commit()
conn.commit()
import sqlite3
import pandas as pd
from pandas import DataFrame
conn = sqlite3.connect('data.db')
c = conn.cursor()
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='Mediator' ''')
if c.fetchone()[0]==0 :
# Crating Mediator DB
c.execute('''CREATE TABLE Medium AS SELECT * FROM hotel_record_historical WHERE hotel_record_historical.[Contact no] IN (SELECT [Mobile No] FROM fir_record_historical);''')
c.execute('''CREATE TABLE Medium2 AS SELECT Medium."Customer Name", Medium."Aadhar no", Medium."Contact no", Medium."Address(House No, Street)", Medium."City of Residence", Medium."State of Residence", Medium."City of Hotel", Medium."State of Hotel", Medium."Stay Date" FROM (Medium INNER JOIN fir_record_historical ON (fir_record_historical.[Mobile No]= Medium.[Contact no])) WHERE (SELECT (julianday(Medium.[Stay Date]) - julianday(fir_record_historical.[Report Date]))<90);''')
c.execute('''CREATE TABLE Medium3 AS SELECT Medium."Customer Name", Medium."Aadhar no", Medium."Contact no", Medium."Address(House No, Street)", Medium."City of Residence", Medium."State of Residence", Medium."City of Hotel", Medium."State of Hotel", Medium."Stay Date" FROM (Medium INNER JOIN fir_record_historical ON (fir_record_historical.[Mobile No]= Medium.[Contact no])) WHERE (( SELECT (julianday(Medium.[Stay Date]) - julianday(fir_record_historical.[Report Date]))) BETWEEN 90 AND 180) AND (Medium.[Contact no] NOT IN (SELECT [Mobile no] FROM Registered_numbers));''')
c.execute('''DROP TABLE Medium;''')
c.execute('''ALTER TABLE Medium2 RENAME TO Mediator;''')
c.execute('''INSERT INTO Mediator SELECT * FROM Medium3;''')
c.execute('''DROP TABLE Medium3;''')
# Adding columns for Mediator DB
c.execute('''ALTER TABLE Mediator ADD COLUMN score REAL;''')
c.execute('''ALTER TABLE Mediator ADD COLUMN c_residence REAL;''')
c.execute('''ALTER TABLE Mediator ADD COLUMN c_hotel REAL;''')
c.execute('''ALTER TABLE Mediator ADD COLUMN c_dup_cont_add REAL;''')
c.execute('''ALTER TABLE Mediator ADD COLUMN Reason VARCHAR(500);''')
c.execute('''UPDATE Mediator SET Reason="";''')
#Set score based on state of residence:
c.execute('''UPDATE Mediator SET c_residence=round(7.0/69, 4) WHERE [State of Residence] IN ("Arunachal Pradesh", "Assam", "Manipur", "Meghalaya", "Mizoram", "Nagaland");''')
c.execute('''UPDATE Mediator SET c_residence=round(10.0/69, 4) WHERE [State of Residence] = "Jammu and Kashmir";''')
c.execute('''UPDATE Mediator SET c_residence=round(3.0/69, 4) WHERE [State of Residence] IN ("Andhra Pradesh", "Chhattishgarh", "Jharkhand", "Odisha", "Bihar");''')
c.execute('''UPDATE Mediator SET c_residence=round(2.0/69, 4) WHERE [State of Residence] = "Punjab";''')
c.execute('''UPDATE Mediator SET c_residence=round(0.0/69, 4) WHERE [c_residence] IS NULL;''')
c.execute('''UPDATE Mediator SET Reason=Reason || "Person resides in " || [State of Residence] WHERE [c_residence]!=0.0;''')
#Set score based on state of hotel:
c.execute('''UPDATE Mediator SET c_hotel=round(3.0/111, 4) WHERE [State of Hotel] = "Tripura";''')
c.execute('''UPDATE Mediator SET c_hotel=round(5.0/111, 4) WHERE [State of Hotel] = "Tamil Nadu";''')
c.execute('''UPDATE Mediator SET c_hotel=round(2.0/111, 4) WHERE [State of Hotel] = "Haryana";''')
c.execute('''UPDATE Mediator SET c_hotel=round(4.0/111, 4) WHERE [State of Hotel] = "Punjab";''')
c.execute('''UPDATE Mediator SET c_hotel=round(1.0/111, 4) WHERE [State of Hotel] = "Uttarakhand";''')
c.execute('''UPDATE Mediator SET c_hotel=round(14.0/111, 4) WHERE [State of Hotel] = "Maharashtra";''')
c.execute('''UPDATE Mediator SET c_hotel=round(5.0/111, 4) WHERE [State of Hotel] = "West Bengal";''')
c.execute('''UPDATE Mediator SET c_hotel=round(6.0/111, 4) WHERE [State of Hotel] = "Karnataka";''')
c.execute('''UPDATE Mediator SET c_hotel=round(4.0/111, 4) WHERE [State of Hotel] = "Rajasthan";''')
c.execute('''UPDATE Mediator SET c_hotel=round(8.0/111, 4) WHERE [State of Hotel] = "Delhi";''')
c.execute('''UPDATE Mediator SET c_hotel=round(8.0/111, 4) WHERE [State of Hotel] = "Assam";''')
c.execute('''UPDATE Mediator SET c_hotel=round(1.0/111, 4) WHERE [State of Hotel] = "Goa";''')
c.execute('''UPDATE Mediator SET c_hotel=round(2.0/111, 4) WHERE [State of Hotel] = "Andhra Pradesh";''')
c.execute('''UPDATE Mediator SET c_hotel=round(20.0/111, 4) WHERE [State of Hotel] = "Jammu and Kashmir";''')
c.execute('''UPDATE Mediator SET c_hotel=round(7.0/111, 4) WHERE [State of Hotel] = "Uttar Pradesh";''')
c.execute('''UPDATE Mediator SET c_hotel=round(3.0/111, 4) WHERE [State of Hotel] = "Bihar";''')
c.execute('''UPDATE Mediator SET c_hotel=round(2.0/111, 4) WHERE [State of Hotel] = "Gujrat";''')
c.execute('''UPDATE Mediator SET c_hotel=round(3.0/111, 4) WHERE [State of Hotel] = "Hyderabad";''')
c.execute('''UPDATE Mediator SET c_hotel=round(2.0/111, 4) WHERE [State of Hotel] = "Manipur";''')
c.execute('''UPDATE Mediator SET c_hotel=round(8.0/111, 4) WHERE [State of Hotel] = "Chhattishgarh";''')
c.execute('''UPDATE Mediator SET c_hotel=round(2.0/111, 4) WHERE [State of Hotel] = "Jharkhand";''')
c.execute('''UPDATE Mediator SET c_hotel=round(1.0/111, 4) WHERE [State of Hotel] = "Madhya Pradesh";''')
c.execute('''UPDATE Mediator SET c_hotel=round(0.0/111, 4) WHERE [c_hotel] IS NULL;''')
c.execute('''UPDATE Mediator SET Reason=Reason || " ," || "The Hotel booked is in " || [State of Hotel] WHERE [c_hotel]!=0.0;''')
#Duplicate Address and Contact no.:
c.execute('''CREATE TABLE M AS SELECT Mediator.[Address(House No, Street)],Mediator.[City of Residence], COUNT(*) c FROM Mediator GROUP BY Mediator.[Address(House No, Street)],Mediator.[City of Residence] HAVING c > 1;''')
c.execute('''CREATE TABLE Dup_Address AS SELECT * FROM Mediator h JOIN M ON (h.[Address(House No, Street)]= M.[Address(House No, Street)] AND h.[City of Residence]=M.[City of Residence]);''')
c.execute('''CREATE TABLE Dup_Contact AS SELECT * FROM Mediator WHERE Mediator.[Contact no] IN (SELECT [Contact no] FROM (SELECT Mediator.[Contact no], COUNT(*) c FROM Mediator GROUP BY Mediator.[Contact no] HAVING c > 1));''')
c.execute('''DROP TABLE M;''')
c.execute('''UPDATE Mediator SET c_dup_cont_add=round(6.0/6, 4) WHERE (Mediator.[Address(House No, Street)], Mediator.[City of Residence]) IN (SELECT Dup_Address.[Address(House No, Street)], Dup_Address.[City of Residence] FROM Dup_Address) OR Mediator.[Contact no] IN (SELECT Dup_Contact.[Contact no] FROM Dup_Contact);''')
c.execute('''UPDATE Mediator SET c_dup_cont_add=round(0.0/6, 4) WHERE c_dup_cont_add IS NULL;''')
c.execute('''UPDATE Mediator SET Reason=Reason || ", Person's Address is a duplicate, " WHERE (Mediator.[Address(House No, Street)], Mediator.[City of Residence]) IN (SELECT Dup_Address.[Address(House No, Street)], Dup_Address.[City of Residence] FROM Dup_Address) OR Mediator.[Contact no] IN (SELECT Dup_Contact.[Contact no] FROM Dup_Contact);''')
c.execute('''UPDATE Mediator SET Reason=Reason ||", Person's Contact number is a duplicate, " WHERE Mediator.[Contact no] IN (SELECT [Contact no] FROM (SELECT Mediator.[Contact no], COUNT(*) c FROM Mediator GROUP BY Mediator.[Contact no] HAVING c > 1));''')
#Calculating score:
c.execute('''UPDATE Mediator SET score=round(100 *((0.4*Mediator.[c_residence])+(0.5*Mediator.[c_hotel])+(0.1*Mediator.[c_dup_cont_add])), 4);''')
c.execute('''CREATE TABLE Dup_Aadhar AS SELECT * FROM Mediator WHERE [Aadhar no] IN (SELECT [Aadhar no] FROM (SELECT Mediator.[Aadhar no], COUNT(*) c FROM Mediator GROUP BY Mediator.[Aadhar no] HAVING c > 1));''')
c.execute('''CREATE TABLE Reported AS SELECT a."Customer Name", a."Aadhar no", a."Contact no", a."Address(House No, Street)", a."City of Residence", a."State of Residence", a."City of Hotel", a."State of Hotel", a."Stay Date", a."score", a."Reason" FROM Dup_Aadhar AS a JOIN Dup_Aadhar AS b ON (a.[Aadhar no]=b.[Aadhar no] AND ((SELECT (julianday(a.[Stay Date]) - julianday(b.[Stay Date]))>30) OR (SELECT (julianday(b.[Stay Date]) - julianday(a.[Stay Date]))>30)) );''')
c.execute('''DROP TABLE Dup_Aadhar;''')
c.execute('''DROP TABLE Dup_Address;''')
c.execute('''DROP TABLE Dup_Contact;''')
conn.commit()
conn.close()
解决方案
推荐阅读
- python - Python获取数字序列中2列之间的最小值最大值
- context-free-grammar - 这种任意语言中的“开始”和“结束”是否应该与 CFG 有关？
- android - 如何在底部导航栏 Android Studio 中添加外部网站链接
- powershell - 从 Citrix Director 检索历史数据
- php - 使用 Laravel 5.6 的控制器不存在
- r - 按其他列选择数据表中的值
- c# - C# Json 泛型添加
- java - Spring boot，如何隐藏或防止加载实体的某些属性
- npm-install - npm install：不支持的平台警告问题
- c++ - 在模板中使用“普通”constexpr 函数参数