SMF SimpleMachines Forum Export / Backup to JSON

Export_SMF_messages.py

import mysql.connector, json

def get_column_names(db_name, table_name):
    query = f"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{db_name}' AND TABLE_NAME = '{table_name}'"
    cursor.execute(query)
    return [row["COLUMN_NAME"] for row in cursor]

db_name = 'your_db'
db_user = 'root'
db_pass = ''
conn = mysql.connector.connect(user=db_user, password=db_pass, host='127.0.0.1', database=db_name)
cursor = conn.cursor(dictionary=True)

board_columns = get_column_names(db_name,'smf_boards')
topic_columns = get_column_names(db_name,'smf_topics')
message_columns = get_column_names(db_name,'smf_messages')

cursor.execute("""
SELECT * FROM smf_messages m
JOIN smf_topics t ON t.id_topic = m.id_topic
JOIN smf_boards b ON b.id_board = t.id_board
ORDER BY b.id_board, t.id_topic, m.id_msg
""")

structure = {}

for row in cursor:
    board_id = row['id_board']
    topic_id = row['id_topic']

    # make board data
    board_data = {col: row[col] for col in board_columns}

    # make topic data
    topic_data = {col: row[col] for col in topic_columns}

    # add board toe if not exits
    if board_id not in structure:
        structure[board_id] = {'board_data': board_data, 'topics': {}}

    # add topic if not exists
    if topic_id not in structure[board_id]['topics']:
        structure[board_id]['topics'][topic_id] = {'topic_data': topic_data, 'messages': []}

    # make message data and add tot topics
    message_data = {col: row[col] for col in message_columns}
    structure[board_id]['topics'][topic_id]['messages'].append(message_data)

cursor.close()
conn.close()

json.dump(structure, open("result.json","w"), indent=2, ensure_ascii=False)

example:

{
  "2": {
    "board_data": {
      "board_order": 3,
      "child_level": 0,
      "count_posts": 0,
      "description": "board info",
      "id_board": 2,
      "id_cat": 1,
      "id_last_msg": 3514,
      "id_msg_updated": 3514,
      "id_parent": 0,
      "id_profile": 1,
      "id_theme": 0,
      "member_groups": "1,2,3,-1,0",
      "name": "Welkom",
      "num_posts": 46,
      "num_topics": 8,
      "override_theme": 0,
      "redirect": "",
      "unapproved_posts": 0,
      "unapproved_topics": 0
    },
    "topics": {
      "1657": {
        "topic_data": {
          "approved": 1,
          "id_board": 2,
          "id_first_msg": 2422,
          "id_last_msg": 3514,
          "id_member_started": 0,
          "id_member_updated": 0,
          "id_poll": 0,
          "id_previous_board": 0,
          "id_previous_topic": 0,
          "id_topic": 1657,
          "is_sticky": 0,
          "locked": 0,
          "num_replies": 6,
          "num_views": 240,
          "unapproved_posts": 0
        },
        "messages": [
          {
            "approved": 1,
            "body": "Dit is een testbericht :o",
            "icon": "clip",
            "id_board": 2,
            "id_member": 0,
            "id_msg": 2422,
            "id_msg_modified": 2422,
            "id_topic": 1657,
            "modified_name": "",
            "modified_time": 0,
            "poster_email": "",
            "poster_ip": "IP....",
            "poster_name": "peer",
            "poster_time": 1430748002,
            "smileys_enabled": 1,
            "subject": "Test"
          },
          {
            "approved": 1,
            "body": "Message 2",
            "icon": "xx",
            "id_board": 2,
            "id_member": 0,
            "id_msg": 2423,
            "id_msg_modified": 2423,
            "id_topic": 1657,
            //....

improved version with attachments

import mysql.connector, json
from pprint import pprint
import sys

def get_column_names(db_name, table_name):
    query = f"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{db_name}' AND TABLE_NAME = '{table_name}'"
    cursor.execute(query)
    return [row["COLUMN_NAME"] for row in cursor]

db_name = 'your_db'
db_user = 'root'
db_pass = ''
conn = mysql.connector.connect(user=db_user, password=db_pass, host='127.0.0.1', database=db_name)
cursor = conn.cursor(dictionary=True)

board_columns = get_column_names(db_name,'smf_boards')
topic_columns = get_column_names(db_name,'smf_topics')
message_columns = get_column_names(db_name,'smf_messages')
attachment_columns = get_column_names(db_name,'smf_attachments')

cursor.execute("""
SELECT m.*, t.*, b.*, a.*,
    m.id_msg AS id_msg # overwrite smf_attachments.id_msg in dict
FROM smf_messages m
JOIN smf_topics t ON t.id_topic = m.id_topic
JOIN smf_boards b ON b.id_board = t.id_board
LEFT JOIN smf_attachments a ON a.id_msg = m.id_msg
-- where filename like 'unloved%'
ORDER BY b.id_board, t.id_topic, m.id_msg
""")

structure = {}

i=0
for row in cursor:
    board_id = row['id_board']
    topic_id = row['id_topic']
    message_id = row['id_msg']

    # Maak board data
    board_data = {col: row[col] for col in board_columns}

    # Maak topic data
    topic_data = {col: row[col] for col in topic_columns}

    # Voeg board toe als het niet bestaat
    if board_id not in structure:
        structure[board_id] = {'board_data': board_data, 'topics': {}}

    # Voeg topic toe als het niet bestaat
    if topic_id not in structure[board_id]['topics']:
        structure[board_id]['topics'][topic_id] = {'topic_data': topic_data, 'messages': {}}
    
    # Voeg message toe aan het topic als het nog niet bestaat
    if message_id not in structure[board_id]['topics'][topic_id]['messages']:
        structure[board_id]['topics'][topic_id]['messages'][message_id] = {col: row[col] for col in message_columns}
        structure[board_id]['topics'][topic_id]['messages'][message_id]['attachments'] = []

    # Voeg attachments toe aan het bericht
    if row['id_attach']:  # Controleer of er een attachment is voor dit bericht
        attachment_data = {col: row[col] for col in attachment_columns}
        structure[board_id]['topics'][topic_id]['messages'][message_id]['attachments'].append(attachment_data)

cursor.close()
conn.close()

json.dump(structure, open("result.json","w"), indent=2, ensure_ascii=False)