How can I translate the dictionary into JSON so that I can then load it into POSTGRESQL?

Question

Python

Как перевести словарь в JSON, чтобы потом можно было его загрузить в POSTGRESQL и по этому можно было делать select'ы и join'ы?

Есть код который на выходе дает словарь со спарсеными значениями.
Дальше ступор ..

Приложу ссылку на архив https://drive.google.com/drive/folders/1t0tIVOMI36i6kl0ByPr4gxddUEC0h9lQ

    import psycopg2
    from psycopg2 import Error
    import pandas as pd
    import sqlalchemy
    from sqlalchemy import create_engine
    import tarfile
    import re
    import json
    import os


    ##############################
    #      STEP 1
    ##############################
    name_tar = "fio-example (3).tar.gz"
    tar = tarfile.open(name_tar)
    tar.extractall()
    tar.close()
    #распакует весь архив, то есть путь будет ..\fio-example\test.log
    ##############################
    #      STEP 2
    ##############################


    dir_t = os.path.dirname(__file__)
    filename = os.path.join(dir_t, 'fio-example', 'test.log')

    def reader(filename):

        regexp = r'{(?s:.*)}'

        with open(filename) as f:
            log = f.read()

            json_value = re.findall(regexp, log)

            log_dict = json.loads(json_value[0])

            #print(log_dict["fio version"])#проверочный
        return log_dict

    #if name == 'main':
        #reader(filename)

    #############################
    #       STEP 3
    #############################

    
    data_dict = reader(filename)
    print(data_dict)



    #-------------------------------------РАБОТА С БД-------------------------------------------------------------


    try:
        # Подключиться к существующей базе данных
        connection = psycopg2.connect(user="postgres",
                                    # пароль, который указали при установке PostgreSQL
                                    password="qwerty",
                                    host="127.0.0.1",
                                    port="5432",
                                    database="result_test_parsing")

        # Создайте курсор для выполнения операций с базой данных
        cursor = connection.cursor()
        # Пример SQL-запроса для создания новой таблицы
        create_table_query = '''CREATE TABLE fio
                            (ID INT PRIMARY KEY     NOT NULL,
                            MODEL           TEXT    NOT NULL,
                            PRICE         REAL); '''
        # Выполнение команды: это создает новую таблицу
        cursor.execute(create_table_query)
        connection.commit()
        print("Таблица успешно создана в PostgreSQL")

    except (Exception, Error) as error:
        print("Ошибка при работе с PostgreSQL", error)
    finally:
        if connection:
            cursor.close()
            connection.close()
            print("Соединение с PostgreSQL закрыто")

Answer 1

Eva Spence , yes. In order...
1. JSON is a data storage format. From a Python point of view, JSON is just a string of text in a specific format. For example, the result of reading from a file. Usually the task is to parse this line of text into some data structure - for example, into the same dictionary, or list, or a combination of them. The json.load() or json.loads() functions from the json module are used for this purpose. You seem to have dealt with them.
2. Storing JSON in a database is an ambiguous thing. You can take some data structure (no matter what), turn it into a text stringin JSON format using json.dumps(), and save this string to the database. It's simple, it will work for any data structure, but it's also pretty much useless. So I doubt that this is the case. You will have to clarify this.
Or you can figure out what kind of data is coming to us, what is their structure, what part of it interests us, and what kind of database structure is required to save this part correctly (normal forms of relational databases and all that).
I looked at an example - there is a rather voluminous data structure, what exactly needs to be saved from it? Because it’s not easy to single out entities in it (which will become database tables) offhand. Or does the database already exist and the tables have already been created? Here we need additional information from the one who set this task.
3. How to connect to the database using sqlalchemy or another library, form the necessary tables in it (if required), and execute an INSERT query. This is a rather tedious, but trivial task, which can be solved by thoughtfully reading the documentation for the library used to work with the database. You may have to play around with the local test database - for starters.