How to add a nested dictionary to PostgreSQL with Python?

Question

I have a dictionry, I don't know it's a nested dict or no. My dictionary is:

my_dict = {"username": "XYZ", "email": "[email protected]",
           "location": "Mumbai", "Address": {"Country": "", "City": "", "Street": ""}}

I addedd two values like this:

my_dict['name'] = []
my_dict['number'] = []

and finaly I want to add this dict to Postgresql table. The table is:

My full code is:

my_dict = {"username": "XYZ", "email": "[email protected]",
           "location": "Mumbai", "Address": {"Country": "", "City": "", "Street": ""}}

my_dict['name'] = []
my_dict['number'] = []

my_dict["name"] = 'Test'
my_dict["number"] = 30
my_dict["Address"]["Country"] = "Turkey"
my_dict["Address"]["City"] = "Istanbul"
my_dict["Address"]["Street"] = "221sk"


cursor_connection.execute(
    '''INSERT into test_table VALUES (%(username)s, %(email)s , %(location)s, %(Country)s , %(City)s , %(Street)s , %(name)s , %(number)s);''', my_dict)
connection_db.commit()

I received this error:

KeyError: 'Country'

I can insert this dict without "Address"'s dictionary but I have a problem with "Address"

In formal format, dictionaries are equivalent to json, try json and jsonb in PostgreSQL. — George Y
– George Y, Commented Jul 17, 2021 at 12:48

azro · Accepted Answer · 2021-07-17 10:34:47Z

1

The query expects to find a Country in the dict at first level, but here isn't.

I'd suggets you create a new dict, with setting the Address values at first level

values = {**my_dict, **my_dict['Address']}
del values['Address']

cursor_connection.execute(
    '''INSERT into test_table VALUES (%(username)s, %(email)s , %(location)s, %(Country)s , %(City)s , %(Street)s , %(name)s , %(number)s);''', values)
connection_db.commit()


# my_dict
{'username': 'XYZ', 'email': '[email protected]', 'location': 'Mumbai', 'name': 'Test',
 'Address': {'Country': 'Turkey', 'City': 'Istanbul', 'Street': '221sk'}, 'number': 30}

# values
{'username': 'XYZ', 'email': '[email protected]', 'location': 'Mumbai', 'name': 'Test', 
  'number': 30, 'Country': 'Turkey', 'City': 'Istanbul', 'Street': '221sk'}

answered Jul 17, 2021 at 10:34

azro

54.2k9 gold badges38 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add a nested dictionary to PostgreSQL with Python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related