0

I'm trying desing and implement database model in python from a json file but my python code seems like is not inserting data into table and i'm not sure where is the problem in my code

import json
import psycopg2


class Policy:
    def __init__(self, type, name, rule_exists):
        self.type = type
        self.name = name
        self.rule_exists = rule_exists


# load json data from the file
with open("demo_data.json", 'r') as file:
    policy_data = json.load(file)
    first_level = policy_data["uniconfig-topology:configuration"]
    second_level = first_level["Cisco-IOS-XR-infra-policymgr-cfg:policy-manager"]
    third_level = second_level["policy-maps"]
    forth_level = third_level["policy-map"]

table_of_policies = []
for item in forth_level:
    if "policy-map" in item:
    # forth_level = item
        type = forth_level['type']
        name = forth_level['name']
        rule_exists = 'policy-map-rule' in forth_level
        policy_map = Policy(type, name, rule_exists)
        table_of_policies.append(policy_map)
    #print(forth_level)


conn = psycopg2.connect(
    host="localhost",
    database="postgres",
    user="postgres",
    password="postgres",
    port="5432"
)


cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS policy_map")
cursor.execute('''create table policy_map
( type VARCHAR(255),
  name VARCHAR(255),
  rule_exists BOOLEAN)
''')

for policy_map in table_of_policies:
    insert_data = "INSERT INTO policy_map (type, name, rule_exists) VALUES (%s, %s, %s)"
    cursor.execute(insert_data, (policy_map.type,
                   policy_map.name, policy_map.rule_exists))


conn.commit()

I tried several different approach for dict in json file to be sure im on right level of dict but it never append any data to my list. Would like to know whats wrong with my code

infuro
  • 3
  • 2

2 Answers2

0

type is a built-in python function that returns the type of a variable. Overwriting it might cause your code to work unexpectedly.

You can check the documentation , avoid using build-in function names for variables.

Regarding table column naming you can refer to this question.

Apart from these you're looping over forth_level but not using the item:

type = forth_level['type']
name = forth_level['name']

I would expect this code to continue like:

    if "policy-map" in item:
    type = item['type']
    name = item['name']
    rule_exists = 'policy-map-rule' in item
    policy_map = Policy(type, name, rule_exists)
    table_of_policies.append(policy_map)

But without seeing the JSON I cannot tell for sure.

Monata
  • 308
  • 1
  • 9
  • That's not the problem even if i replace variable named type its still not adding any data – infuro Jun 02 '23 at 09:08
  • I have added another possible problem with your code but without seeing the actual file you're using this is just intuition, as the previous version would end up returning duplicates. – Monata Jun 02 '23 at 09:20
0

Read the contents of file demo_data.json into a string variable, i.e. json_text = file.read() and then pass json_text as a single parameter to this query (in variable the_insert_query):

with t as
(
 select j ->> 'name' as "name",
        j ->> 'type' as "type",
        j -> 'policy-map-rule' is not null as rule_exists
 from json_array_elements
 (
  %s::json -> 'uniconfig-topology:configuration'
           -> 'Cisco-IOS-XR-infra-policymgr-cfg:policy-manager'
           -> 'policy-maps'
           -> 'policy-map'
 ) as j
)
insert into policy_map("name", "type", rule_exists) 
select "name", "type", rule_exists from t;

cursor.execute(the_insert_query, (json_text,))

PostgreSQL would handle the whole case quickly and easily. No class Policy, table_of_policies or a loop over it are needed at all.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21