CODEWITHSUNDEEP
pythonsqlite
Prem Prakash
Prem Prakash

Reputation: 21

Storing dictionary of dictionary in Sqlite3

Suppose I have a dictionary into an another dictionary like- 

dict={ 'Prem': { 'age' : '23' , 'sex': 'male' } , 'Sameer' : { 'age' : '22' , 'sex' : 'male'} }

. How can we store this into Sqlite3 database?

Upvotes: 2

Views: 5822

Answers (4)

PiWeb
PiWeb

Reputation: 1

Here is how I did it using df.to_sql() function from pandas. I don't know if it's the best way but it works.

import pandas as pd 
import sqlite3


your_dict = {
    'Prem': {'age': '23', 'sex': 'male'},
    'Sameer': {'age': '22', 'sex': 'male'}
}

# Transform the dictionary to the desired format
data = [{'name': name, 'age': info['age'], 'sex': info['sex']} for name, info in your_dict.items()]

# Create the DataFrame
df = pd.DataFrame(data)

# Connect and create SGL database
conn = sqlite3.connect("database.sqlite")
c = conn.cursor()

# Create SQL Table
c.execute('CREATE TABLE IF NOT EXISTS employees (name, age, sex);')

# Write the DataFrame in the SQL database
df.to_sql("database", conn, if_exists="replace", index=False)


# Read the SQL database using pandas
def run_query(query):
    return pd.read_sql_query(query, conn)

query='''
SELECT * FROM database
'''

# Display DataFrame
display(run_query(query))

# After all database operations
conn.close()

Upvotes: 0

SomeMosa
SomeMosa

Reputation: 451

You could save the dictionary as type TEXT, so it would be saved as:

"{ 'Prem': { 'age' : '23' , 'sex': 'male' } , 'Sameer' : { 'age' : '22' , 'sex' : 'male'} }"

You can save this in the database like this:

ogdict = { 'Prem': { 'age' : '23' , 'sex': 'male' } , 'Sameer' : { 'age' : '22' , 'sex' : 'male'} }
c.execute('INSERT INTO tablename VALUES ("{}")'.format(ogdict))

And then use the builtin python eval() funtion to turn it back into a dictionary:

c.execute('SELECT * FROM tablename')
dbdict = eval(c.fetchone()[0])

Now, the dictionary from the database is saved in the dbdict, and you can use it like a normal dictionary, so calling dbdict['Prem'] will yield the value of {'age': '23' , 'sex': 'male'}.

Upvotes: 3

holdenweb
holdenweb

Reputation: 37153

Relational data is stored in tables. You don't say whether the interior dicts always have the same structure, so for the purposes of this answer I assume they have.

In these circumstances it would be simplest to define a table with three colummns, which I call name, age and sex. The definition would look something like

CREATE TABLE t(
    name    VARCHAR(20),
    age     INTEGER,
    sex     CHAR(1))

A program to enter your sample data would look something like this:

import sqlite3
mydict={'Prem': { 'age': '23' , 'sex': 'male' },
        'Sameer' : { 'age': '22' , 'sex': 'male'}
       } # reformatted to `PEP008` standard, renamed

conn = sqlite3.connect("/tmp/db") # creates a new file if necessary
curs = conn.curs()
for k in mydict:
    curs.execute("""INSERT INTO t (name, age, sex)
                    VALUES (?, ?, ?)""",
                    (k, mydict[k]['age'], mydict[k]['sex'])
conn.commit()

This iterates over the keys of mydict (I renamed it because dictis the name of a built-in type) and inserts the key as name and the values of age and sex from the interior dict.

The commit() call is very important - that makes the databases changes permanent!

Upvotes: 3

Dmitry.Samborskyi
Dmitry.Samborskyi

Reputation: 485

You need two tables.

First for exmpl Persons:

id name
1  Prem  
2  Sameer
...

Second stored value:

id fk_persons age sex
1  1          23  male
2  2          22  male

usualy structure is table for dictionary.

Upvotes: 0

Related Questions