Question 180726 in PostgreSQL Question posted in
The official documentation can be found here.

Why is my Python code not inserting data into PostgreSQL database from a JSON file?

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

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search