I created a script to get your merit data into a sqllite file, which IMO is best for your situation. I also created an API to query merit data.
Both files are written in python. To run the files, you navigate to the folder they are located in and in the command prompt type
python [file name]; the file name should end in .py. You will need to run the first file first. Neither file will cause your SQLlite database to update automatically, but if the API is deployed, I could easily write a script to update the SQLlite database with new merit transactions.
When you run the file that gets data into the SQLlite file, it will create a SQLlite file. This file may also take a minute or so to run.
The API file should be run second ideally. It will need to continue running as long as you want the API to run.
As a standard disclaimer: I am not aware of any security vulnerabilities with my code, but you should implement my code at your own risk.
When merit transactions are being queried, if there are none, a single dummy transaction with 0 merit is returned.
If my code is implemented, and there is demand, I can update the API to return the usernames associated with the UIDs in question.
My API file is not very well commented. I don't have very much free time to spend on coding side projects, and sat down a number of times to work on this code. If you need better comments, I can create better/more comments.
My script to get your merit data into a SQLlite file is below:
#dependencies
import pandas as pd
import numpy as np
#from datetime import datetime #this library is not required
from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
#path of sqlite file goes here in relation to current file
database_path = 'merit.sqlite'
#create DataFrame from mert transaction history file on loyce.club
data = pd.read_csv('http://loyce.club/Merit/merit.all.txt', sep=' ', header=None)
#rename columns
data = data.rename(columns={0: 'time', 1: 'number_of_merit', 2:'message_id', 3:'UID_from', 4: 'UID_to'})
#connect to sqllite db
engine = create_engine(f"sqlite:///{database_path}")
session = Session(engine)
#use default declarative base function as variable 'Base'
Base = declarative_base()
#define table schema
class Merit(Base):
__tablename__ = 'merit'
id = Column(Integer, primary_key=True)#this is a column with a unique value for each transaction
time = Column(Integer) #unix time
number_of_merit = Column(Integer)
message_id = Column(String(25))
uid_from = Column(Integer)
uid_to = Column(Integer)
#create table in sqllite file
Base.metadata.create_all(engine)
#set first value for id as 1
id1 = 1
#loop through DataFrame to add each row in the DataFrame to the SQLlite DB.
for x in np.arange(len(data)):
session.add(Merit(id=id1, time=int(data['time'][x]), number_of_merit=int(data['number_of_merit'][x]),
message_id=data['message_id'][x], uid_from=int(data['UID_from'][x]), uid_to=int(data['UID_to'][x])))
id1 = id1 + 1 #after the row is added, the id1 variable value will be increased by one
#will commit rows in batches of 100
if len(session.new) > 100:
session.commit()
session.commit() #commit last batch of rows
#check to make sure all rows were successfully imported
if len(session.query(Merit.id).all()) == len(data):
print(f'All the data from the DataFrame was successfully imported into a SQL file found at {database_path}')
else:
print(f'There was a problem importing all the merit transactions and {len(data) - len(session.query(Merit.id).all())} were not imported. Troubleshooting is required')
The code for the API is below:
#dependencies
import pandas as pd
import numpy as np
from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, String, Float, func
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.automap import automap_base
from flask import Flask, jsonify
#path of sqlite file goes here in relation to current file
database_path = 'merit.sqlite'
#connect to sqllite db
engine = create_engine(f"sqlite:///{database_path}")
#reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(engine, reflect=True)
# Save reference to the table
Merit = Base.classes.merit
#set variable 'app' to run Flask
app = Flask(__name__)
@app.route("/")
def welcome():
description1 = '''
Available Routes:<br/>
/api/v0.1/number_received/[uid]</br>
/api/v0.1/number_sent/[uid]</br>
/api/v0.1/between/[uid_from]/[uid_to]</br>
/api/v0.1/received/[uid]</br>
/api/v0.1/sent/[uid]</br>
</br></br>
/api/v0.1/number_received/[uid]</br>
replace [uid] with uid of forum member, must be an integer</br>
returns:</br> Total Received Merit: the total merit the uid has received</br>
uid: the uid you are querying</br></br>
/api/v0.1/number_sent/[uid]</br>
replace [uid] with uid of forum member, must be an integer</br>
returns:</br> Total Sent Merit: the total merit the uid has sent</br>
uid: the uid you are querying</br></br>
/api/v0.1/between/[uid_from]/[uid_to]</br>
replace [uid_from] and [uid_to] with uids of forum members, must be integers</br>
Queries merit transactions from [uid_from] to [uid_to]</br>
returns:</br>Total Received Merit: the total merit sent to [uid_to] from [uid_from]</br>
Sent from: [uid_from]</br>
Sent to: [uid_to]</br>
Transactions: List of dictionaries of merit transactions that contain the following information:</br>
time: time of merit transaction in the following format: yyyy-mm-dd hh:mm:ss</br>
Month: Month of merit transaction</br>
Day of Week: the day of the week of the merit transaction
number of merit: the number of merit that was sent from [uid_from] to [uid_to] in the merit transaction</br>
Post: Post ID that received merit </br></br>
/api/v0.1/received/[uid]</br>
replace [uid] with uid of forum member, must be an integer</br>
returns:<br/>
Total Received Merit: the total merit the uid has received</br>
Sent to: the uid you are querying</br>
Transactions: List of dictionaries of merit transactions that contain the following information:</br>
time: time of merit transaction in the following format: yyyy-mm-dd hh:mm:ss</br>
Month: Month of merit transaction</br>
Day of Week: the day of the week of the merit transaction
number of merit: the number of merit that was sent to [uid] in the merit transaction</br>
Post: Post ID that received merit </br>
Sent from: the uid that sent the merit transaction</br></br>
/api/v0.1/sent/[uid]</br>
replace [uid] with uid of forum member, must be an integer</br>
returns:<br/>
Total Sent Merit: the total merit the uid has sent</br>
Sent to: the uid you are querying</br>
Transactions: List of dictionaries of merit transactions that contain the following information:</br>
time: time of merit transaction in the following format: yyyy-mm-dd hh:mm:ss</br>
Month: Month of merit transaction</br>
Day of Week: the day of the week of the merit transaction
number of merit: the number of merit that was sent from [uid_from] in the merit transaction</br>
Post: Post ID that received merit </br>
Sent to: the uid that merit was sent to in the subject transaction</br></br>
'''
return (description1)
@app.route('/api/v0.1/number_received/<uid>')
def number_received(uid):
try:
#confirm the input was an integer
uid1 = int(uid)
#connect to sqllite DB
session = Session(engine)
response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_to == uid1).all()
session.close()
response = list(np.ravel(response))
response = int(response[0])
response_list = []
merit_received = {}
merit_received['Total Received Merit'] = response
merit_received['uid'] = uid1
response_list.append(merit_received)
return jsonify(response_list) #jsonify
except ValueError:
value1 = {'Error': f'{uid} is not an Integer. Please reformat into an Integer and try again'}
return jsonify(value1)
except TypeError:
no_merit = {"Total Received Merit":0, "uid":uid1}
return jsonify(no_merit)
@app.route('/api/v0.1/number_sent/<uid>')
def number_sent(uid):
try:
#confirm the input was an integer
uid1 = int(uid)
#connect to sqllite DB
session = Session(engine)
response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_from == uid1).all()
session.close()
response = list(np.ravel(response))
response = int(response[0])
response_list = []
merit_sent = {}
merit_sent['Total Sent Merit'] = response
merit_sent['uid'] = uid1
response_list.append(merit_sent)
return jsonify(response_list)
except ValueError:
value1 = {'Error': f'{uid} is not an Integer. Please reformat into an Integer and try again'}
return jsonify(value1)
except TypeError:
no_merit = {"Total Sent Merit":0, "uid":uid1}
return jsonify(no_merit)
@app.route('/api/v0.1/between/<fromm>/<to>')
def between(fromm, to):
try:
#confirm the input was an integer
from1 = int(fromm)
to1 = int(to)
#connect to sqllite DB
session = Session(engine)
response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_from == from1).filter(Merit.uid_to == to1).all()
response2 = session.query(Merit.number_of_merit, Merit.message_id, Merit.time).filter(Merit.uid_from == from1).filter(Merit.uid_to == to).all()
session.close()##pick up coding here
response = list(np.ravel(response))
response = int(response[0])
response_list = []
merit_sent = {}
merit_sent['Total Received Merit'] = response
merit_sent['Sent from'] = from1
merit_sent['Sent to'] = to1
response3 = []
for merit_number, message_id, time in response2:
response2_dict = {}
time1 = datetime.utcfromtimestamp(int(time)).strftime('%Y-%m-%d %H:%M:%S')
time_day = datetime.utcfromtimestamp(int(time)).strftime('%A') #day of week
time_month = datetime.utcfromtimestamp(int(time)).strftime('%B') #month
response2_dict['time'] = time1
response2_dict['Month'] = time_month
response2_dict['Day of Week'] = time_day
response2_dict['number of merit'] = merit_number
response2_dict['Post'] = message_id
response3.append(response2_dict)
merit_sent['Transactions'] = response3
response_list.append(merit_sent)
return jsonify(response_list)
except ValueError:
value1 = {'Error': f'{fromm} or {to} is not an Integer. Please reformat into an Integer and try again'}
return jsonify(value1)
except TypeError:
no_merit = {"Total Received Merit":0, "Sent from":from1, 'Sent to': to1,
'Transactions':[{'time': '2009-01-08 08:21:00','Month':
'January','Day of Week': 'Thursday','number of merit': 0,
'Post': '9999999.msg999999999'}] }
return jsonify(no_merit)
@app.route('/api/v0.1/received/<to>')
def transactions_received(to):
try:
#confirm the input was an integer
to1 = int(to)
#connect to sqllite DB
session = Session(engine)
response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_to == to1).all()
response2 = session.query(Merit.uid_from, Merit.number_of_merit, Merit.message_id, Merit.time).filter(Merit.uid_to == to).all()
session.close()
response = list(np.ravel(response))
response = int(response[0])
response_list = []
merit_sent = {}
merit_sent['Total Received Merit'] = response
merit_sent['Sent to'] = to1
response3 = []
for received_from, merit_number, message_id, time in response2:
response2_dict = {}
time1 = datetime.utcfromtimestamp(int(time)).strftime('%Y-%m-%d %H:%M:%S')
time_day = datetime.utcfromtimestamp(int(time)).strftime('%A') #day of week
time_month = datetime.utcfromtimestamp(int(time)).strftime('%B') #month
response2_dict['time'] = time1
response2_dict['Month'] = time_month
response2_dict['Day of Week'] = time_day
response2_dict['number of merit'] = merit_number
response2_dict['Post'] = message_id
response2_dict['Sent from'] = received_from
response3.append(response2_dict)
merit_sent['Transactions'] = response3
response_list.append(merit_sent) #this might need to be moved to the end
return jsonify(response_list)
except ValueError:
value1 = {'Error': f'{to} is not an Integer. Please reformat into an Integer and try again'}
return jsonify(value1)
except TypeError:
no_merit = {"Total Received Merit":0, 'Sent to': to1,
'Transactions':[{'time': '2009-01-08 08:21:00','Month':
'January','Day of Week': 'Thursday','number of merit': 0,
'Post': '9999999.msg999999999', 'Sent from': 2}] }
return jsonify(no_merit)
@app.route('/api/v0.1/sent/<fromm>')
def transactions_sent(fromm):
try:
#confirm the input was an integer
from1 = int(fromm)
#connect to sqllite DB
session = Session(engine)
response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_from == from1).all()
response2 = session.query(Merit.uid_to, Merit.number_of_merit, Merit.message_id, Merit.time).filter(Merit.uid_from == from1).all()
session.close()##pick up coding here
response = list(np.ravel(response))
response = int(response[0])
response_list = []
merit_sent = {}
merit_sent['Total Sent Merit'] = response
merit_sent['Sent from'] = from1
response3 = []
for sent_to, merit_number, message_id, time in response2:
response2_dict = {}
time1 = datetime.utcfromtimestamp(int(time)).strftime('%Y-%m-%d %H:%M:%S')
time_day = datetime.utcfromtimestamp(int(time)).strftime('%A') #day of week
time_month = datetime.utcfromtimestamp(int(time)).strftime('%B') #month
response2_dict['time'] = time1
response2_dict['Month'] = time_month
response2_dict['Day of Week'] = time_day
response2_dict['number of merit'] = merit_number
response2_dict['Post'] = message_id
response2_dict['sent to'] = sent_to
response3.append(response2_dict)
merit_sent['Transactions'] = response3
response_list.append(merit_sent) #this might need to be moved to the end
return jsonify(response_list)
except ValueError:
value1 = {'Error': f'{fromm} is not an Integer. Please reformat into an Integer and try again'}
return jsonify(value1)
except TypeError:
no_merit = {"Total Received Merit":0, "Sent from":from1,
'Transactions':[{'time': '2009-01-08 08:21:00','Month':
'January','Day of Week': 'Thursday','number of merit': 0,
'Post': '9999999.msg999999999', 'Sent to': 2}] }
return jsonify(no_merit)
if __name__ == '__main__':
app.run(debug=False)
#debug needs to be set to False in production, but can be set to True in a non-public facing setting.
If the indenting did not transfer over properly, let me know and I will upload this to github. When you run the API file, you may get an error message saying not to run the code in production, but I believe it should be sufficient for your level of use.
