How to parse a LUA Table and push to SQL Database using Python?

I play a game called DCS (Digital Combat Simulator) and was looking to export in game statistics to a database and later recall with PHP on a webpage. The game saves code to a LUA Table and sample data is shown below…

Categories show the aircraft type, time in air, planes killed, type of plane, total planes killed, weapons, weapon types, hits, kills, shots, actions, losses, pilot death, crash, eject, pvp, kills, join date, last join, names and more.

stats = 
{
    ["b3961df7f720c4288522019d0455fa4a"] = 
    {
        ["times"] = 
        {
            ["AV8BNA"] = 
            {
                ["inAir"] = 1441.802,
                ["weapons"] = 
                {
                    ["AIM-9M"] = 
                    {
                        ["numHits"] = 0,
                        ["kills"] = 0,
                        ["shot"] = 1,
                        ["hit"] = 0,
                    }, -- end of ["AIM-9M"]
                    ["kamikaze"] = 
                    {
                        ["numHits"] = 0,
                        ["kills"] = 0,
                        ["shot"] = 0,
                        ["hit"] = 0,
                    }, -- end of ["kamikaze"]
                    ["AGM-122"] = 
                    {
                        ["numHits"] = 0,
                        ["kills"] = 0,
                        ["shot"] = 1,
                        ["hit"] = 0,
                    }, -- end of ["AGM-122"]
                }, -- end of ["weapons"]
                ["actions"] = 
                {
                    ["losses"] = 
                    {
                        ["pilotDeath"] = 1,
                        ["crash"] = 1,
                        ["eject"] = 0,
                    }, -- end of ["losses"]
                }, -- end of ["actions"]
                ["total"] = 2132.704,
            }, -- end of ["AV8BNA"]
            ["FA-18C_hornet"] = 
            {
                ["total"] = 13412.988,
                ["kills"] = 
                {
                    ["Planes"] = 
                    {
                        ["Fighters"] = 6,
                        ["total"] = 6,
                    }, -- end of ["Planes"]
                }, -- end of ["kills"]
                ["inAir"] = 8568.488,
                ["weapons"] = 
                {
                    ["M-61"] = 
                    {
                        ["hit"] = 0,
                        ["kills"] = 0,
                        ["shot"] = 664,
                        ["numHits"] = 48,
                    }, -- end of ["M-61"]
                    ["AGM-88C"] = 
                    {
                        ["hit"] = 0,
                        ["kills"] = 0,
                        ["shot"] = 1,
                        ["numHits"] = 0,
                    }, -- end of ["AGM-88C"]
                    ["AIM-120C"] = 
                    {
                        ["hit"] = 0,
                        ["kills"] = 4,
                        ["shot"] = 4,
                        ["numHits"] = 4,
                    }, -- end of ["AIM-120C"]
                    ["F/A-18C Lot 20"] = 
                    {
                        ["numHits"] = 0,
                        ["kills"] = 0,
                        ["shot"] = 0,
                        ["hit"] = 0,
                    }, -- end of ["F/A-18C Lot 20"]
                    ["AIM-9X"] = 
                    {
                        ["hit"] = 0,
                        ["kills"] = 2,
                        ["shot"] = 6,
                        ["numHits"] = 4,
                    }, -- end of ["AIM-9X"]
                    ["AGM-154A"] = 
                    {
                        ["hit"] = 0,
                        ["kills"] = 0,
                        ["shot"] = 2,
                        ["numHits"] = 0,
                    }, -- end of ["AGM-154A"]
                }, -- end of ["weapons"]
                ["actions"] = 
                {
                    ["losses"] = 
                    {
                        ["pilotDeath"] = 4,
                        ["crash"] = 4,
                        ["eject"] = 0,
                    }, -- end of ["losses"]
                }, -- end of ["actions"]
                ["pvp"] = 
                {
                    ["kills"] = 6,
                }, -- end of ["pvp"]
            }, -- end of ["FA-18C_hornet"]
            ["F-5E-3"] = 
            {
                ["inAir"] = 0,
                ["total"] = 3574.806,
            }, -- end of ["F-5E-3"]
            ["Ka-50"] = 
            {
                ["inAir"] = 0,
                ["total"] = 3034.86,
            }, -- end of ["Ka-50"]
            ["F-15C"] = 
            {
                ["inAir"] = 700.745,
                ["actions"] = 
                {
                    ["losses"] = 
                    {
                        ["pilotDeath"] = 1,
                        ["crash"] = 1,
                        ["eject"] = 0,
                    }, -- end of ["losses"]
                }, -- end of ["actions"]
                ["total"] = 5986.455,
            }, -- end of ["F-15C"]
            ["UH-1H"] = 
            {
                ["inAir"] = 0,
                ["total"] = 420.383,
            }, -- end of ["UH-1H"]
            ["F-14B"] = 
            {
                ["total"] = 21862.031,
                ["kills"] = 
                {
                    ["Planes"] = 
                    {
                        ["Fighters"] = 4,
                        ["total"] = 4,
                    }, -- end of ["Planes"]
                }, -- end of ["kills"]
                ["inAir"] = 11952.818,
                ["weapons"] = 
                {
                    ["AIM_54A_Mk60"] = 
                    {
                        ["hit"] = 0,
                        ["kills"] = 4,
                        ["shot"] = 4,
                        ["numHits"] = 5,
                    }, -- end of ["AIM_54A_Mk60"]
                }, -- end of ["weapons"]
                ["actions"] = 
                {
                    ["losses"] = 
                    {
                        ["pilotDeath"] = 0,
                        ["crash"] = 3,
                        ["eject"] = 0,
                    }, -- end of ["losses"]
                }, -- end of ["actions"]
                ["pvp"] = 
                {
                    ["kills"] = 4,
                }, -- end of ["pvp"]
            }, -- end of ["F-14B"]
        }, -- end of ["times"]
        ["joinDate"] = 1589674831,
        ["lastJoin"] = 1591926810,
        ["id"] = 3,
        ["names"] = 
        {
            [1] = "DRAGON 1-2 | DeathTrooper",
            [2] = "DeathTrooper",
        }, -- end of ["names"]
} -- end of stats

I am seeking advice on how to separate the values in the sample data ( a way to parse LUA Tables to SQL via Python) and and tie them all to the UUID (Unique user identification) shown as the string “b3961df7f720c4288522019d0455fa4a” above.

The Python code I currently use to load all this data is as follows…

import mysql.connector

# Make connection to DB with below...
db = mysql.connector.connect(
    host="192.168.1.20",
    user="user",
    passwd="password",
    db="database"
)
# Declare cursor
cursor = db.cursor()
# Open file to be read and imported to table in "database" database
file = open(r"C:UsersUsernameDesktopslmodstats.lua", "r", encoding='utf-8')
file_content = file.read()
file.close()

# Debug for me to see what the file content is...
print(file_content, "n")

# Send data to table VALUES type LONGTEXT as one long string (proof of concept)
# Push all that was read and is now "%s" from prior string and set query...
query = "REPLACE INTO data VALUES (%s);"
# insert all data from "query" and file content as %s, push to db
cursor.execute(query, (file_content,))
# Commit changes and close...
db.commit()
db.close()

Is there any way to more easily parse the data from the LUA Table file, filter it into categories tied to the UUID, make it end parsing when seeing “– end of stats”, and push to a database for eventual display with PHP on a webpage?

Asking a lot, I apologize but I’ve researched for a while now with little results. Think I’ve got a decent foundation though.

Answer

How about just using Lua to parse the file?

#!/usr/bin/env lua
dofile("stat.data")
for k,v in pairs(stats) do
    -- generate a data format that can be easily parsed by your program
    -- e.g. json
end

Then you can use either pipe or temp file to connect the two programs