Python Code to Generate SQL table from Freelancer INI
Here’s a rough Python script to generate SQL from an INI . I tried alternatives to get the data, but have trouble running Data Storm. DB Browser for SQL Lite breaks data from the FLStat (the one written by Dan Tascau and “hacked” by Jason Hood).
Script has NO guard rails and is all hard-coded at this point. For simple specific reuse, what you need to change is at top.
The root of the Freelancer install you want to query is specified in FlDataRoot, and the relative path to the INI of interest should be in CurrentDataFile. (With single quotes ’ ’ around the paths, Python won’t choke if you do forget to change ‘’ to ‘/’).
I’ve only used it on weapon_equip and weapon_good, but parsing is OK there.
Note that the script will create the SQL export file in the same location as the INI, just changing the suffix to SQL. Also, it WILL overwrite an existing “.SQL” file without prompting.
# Hardcoded Configuration variables/constants for testing
FlDataRoot = 'c:/Apps/Freelancer-0/DATA/'
CurrentDataFile = 'EQUIPMENT/weapon_equip.ini'
# Generate SQLOutputFile by replacing the extension with ".sql"
SQLOutputFile = os.path.splitext(CurrentDataFile)[0] + ".sql"
import os, re
def infer_data_type(value):
"""Infer SQL data type from a string value."""
if re.match(r'^\d+$', value):
return 'INT'
elif re.match(r'^\d+\.\d+$', value):
return 'FLOAT'
else:
return 'VARCHAR(255)'
def parse_file(file_contents):
tables = {}
current_table = None
new_record = True # Flag to indicate when to start a new record
for line in file_contents:
if line.startswith('['):
current_table = line.strip().strip('[]')
if current_table not in tables:
tables[current_table] = {'fields': {}, 'records': []}
new_record = True # New table, so start a new record
elif '=' in line and current_table:
field, value = line.strip().split(' = ')
if field not in tables[current_table]['fields']:
tables[current_table]['fields'][field] = infer_data_type(value)
if not new_record and field in tables[current_table]['records'][-1]:
new_record = True
if new_record:
tables[current_table]['records'].append({})
new_record = False
tables[current_table]['records'][-1][field] = value
return tables
def generate_sql(tables):
"""Generate SQL CREATE TABLE and INSERT INTO statements."""
create_statements = []
insert_statements = []
for table, data in tables.items():
field_defs = [f'"{name}" {type}' for name, type in data['fields'].items()]
create_statement = 'CREATE TABLE "{}" (\n {}\n);'.format(table, ",\n ".join(field_defs))
create_statements.append(create_statement)
for record in data['records']:
if record: # Ensure the record is not empty
fields = ', '.join([f'"{f}"' for f in record])
values = ', '.join(['\'{}\''.format(v) for v in record.values()])
insert_statement = 'INSERT INTO "{}" ({}) VALUES ({});'.format(table, fields, values)
insert_statements.append(insert_statement)
return create_statements, insert_statements
# Usage example:
# file_contents = <read the file contents into a list of lines>
# tables = parse_file(file_contents)
# create_statements, insert_statements = generate_sql(tables)
# combined_sql_statements = '\n\n'.join(create_statements + insert_statements)
# Write combined_sql_statements to an output file
# Construct full paths
input_file_path = os.path.join(FlDataRoot, CurrentDataFile)
output_file_path = os.path.join(FlDataRoot, SQLOutputFile)
# Read the input file
with open(input_file_path, 'r') as file:
file_contents = file.readlines()
# Process the file contents
tables = parse_file(file_contents)
create_statements, insert_statements = generate_sql(tables)
# Combine all CREATE TABLE and INSERT INTO statements
combined_sql_statements = '\n\n'.join(create_statements + insert_statements)
# Write the SQL statements to the output file
with open(output_file_path, 'w') as output_file:
output_file.write(combined_sql_statements)
print(f"SQL statements have been written to {output_file_path}")
).