Cannot get data from MySQL database

Question

down below is my code to get some data from MySQL for my Discord Bot:

import traceback
import mysql.connector


DB_CONFIG = {
    'host': 'mydomain',
    'port': 3306,
    'user': 'user',
    'password': 'pass',
    'database': 'dbname'
}

def fetch(table):
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute(f"SELECT json_data FROM {table} WHERE id = 1")
        result = cursor.fetchone()
        if result:
            return json.dumps(result[0])
        else:
            return {}

    except Exception as e:
        print(str(e))
        return {}

    finally:
        cursor.close()
        conn.close()

def save(data, table):
    try:
        json_data = json.dumps(str(data))
        print(json_data)
        check_table(table)
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute(f"UPDATE {table} SET json_data = %s WHERE id = 1", (json_data,))
        conn.commit()
        print(f"{table}'s table saved successfully.")
      
    except Exception as e:
        print(str(e))
        traceback.print_exc()

    finally:
        cursor.close()
        conn.close()


test = {"a": "b"}
save(test, "test")
print(fetch("test"))

The output of print(fetch("test")) always None and i dont know why.

I tried to check the database, and look into the code for 1 million times, but the database is working normal, and I dont know why

Any idea about this?

Answer 1

Thanks to yall help! I've seen Barmar comment:

save() only works if there's already a row in the table with id=1. It won't create the row if it doesn't exist.

Actually, I'm already have another function to check the table exists or not:

def check_table(table):
    try:
        query = f"""CREATE TABLE IF NOT EXISTS {table} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            json_data JSON
        );
        """
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute(f"SHOW TABLES LIKE '{table}'")
        table_exists = cursor.fetchone()
        if not table_exists:
            cursor.execute(query)
            print(f"Table '{table}' created successfully.")
        conn.commit()
    except Exception as e:
        print(str(e))
        traceback.print_exc()

    finally:
        cursor.close()
        conn.close()

But I'm just realized, it doesn't fell good in this problem. I tried to update my save() function:

...
        cursor = conn.cursor()
        # cursor.execute(f"UPDATE {table} SET json_data = %s WHERE id = 1", (json_data,))    Old code line
        # First, check if the record with id=1 exists
        cursor.execute(f"SELECT id FROM {table} WHERE id = 1")
        existing_record = cursor.fetchone()

        if existing_record:
            # If the record with id=1 exists, update it
            cursor.execute(
                f"UPDATE {table} SET json_data = %s WHERE id = 1",
                (json_data,)
            )
        else:
            # If the record with id=1 doesn't exist, insert it
            cursor.execute(
                f"INSERT INTO {table} (id, json_data) VALUES (1, %s)",
                (json_data,)
            )
...

And it's likely to working now