CODEWITHSUNDEEP
jsonpython-3.x
Besarion
Besarion

Reputation: 149

Loop Over dictionary converting Json into SQL insert with Python

Hi there i would like to loop over a dictionary that contains JSON arrays per line E.g.

For val in json_object:
    print(val)

produces: 

[{'album': 'Led Zepplin 4', 'Artist': 'Led Zeplin', 'Time': 17:56.12, 'SongWriter': 'JimmyPage', 'Producer': None, 'Released': '1975-07-01'}, 
{'album': 'Led Zepplin 4', 'Artist': 'Led Zeplin', 'Time': 17:56.12, 'SongWriter': 'JimmyPage', 'Producer': None, 'Released': '1975-07-01'}, 
{'album': 'Led Zepplin 4', 'Artist': 'Led Zeplin', 'Time': 17:56.12, 'SongWriter': 'JimmyPage', 'Producer': None, 'Released': '1975-07-01', 'AlbumArtist': 'ajsfa'}]

I would like to create a list of INSERT statements for each line. Sometimes AlbumArtist appears sometimes it doesnt. If it didnt thats fine it can be NULL

INSERT INTO MusicTest ([album], [Artist], [Time], [SongWriter], [Producer], [Released], [AlbumArtist]) VALUES ();

Ive tried writing my own python piece which normally works fine:

def extract_values(obj, key):
    """Pull all values of specified key from nested JSON."""
    arr = []

    def extract(obj, arr, key):
        """Recursively search for values of key in JSON tree."""
        if isinstance(obj, dict):
            for k, v in obj.items():
                if isinstance(v, (dict, list)):
                    extract(v, arr, key)
                elif k == key:
                    arr.append(v)
        elif isinstance(obj, list):
            for item in obj:
                extract(item, arr, key)
        return arr

    results = extract(obj, arr, key)
    return results

To be used like:

    for val in json_object:
        print(val)
        name = extract_values(val.json(), "name")
        print(name)

But i keep getting the error: 'dict' object has no attribute 'json' at: name = extract_values(val.json(), "name")

any ideas?

I've also tried this:

    for json in r:
        keylist = "("
        valuelist="("
        firstPair = True
        for attribute, value in json.items():
            if not firstPair:
                keylist += ", "
                valuelist += ", "
            firstPair = False
            keylist += key
            if type(value) in (str, unicode):
                valuelist += "'" + value + "'"
            else:
                valuelist += str(value)
        keylist += ")"
        valuelist += ")"
        sqlstatement += "INSERT INTO IMPORTTABLE " + keylist + " VALUES " + valuelist + "\n"
        print(sqlstatement)

But get the error 'str' object has no attribute 'items' at for attribute, value in json.items():

Upvotes: 0

Views: 416

Answers (1)

krishna
krishna

Reputation: 1029

Please chek this.

data_dict_lis = [{'album': 'Led Zepplin 4', 'Artist': 'Led Zeplin', 'Time': "17:56.12", 'SongWriter': 'JimmyPage', 'Producer': None, 'Released': '1975-07-01'},
{'album': 'Led Zepplin 4', 'Artist': 'Led Zeplin', 'Time': "17:56.12", 'SongWriter': 'JimmyPage', 'Producer': None, 'Released': '1975-07-01'},
{'album': 'Led Zepplin 4', 'Artist': 'Led Zeplin', 'Time': "17:56.12", 'SongWriter': 'JimmyPage', 'Producer': None, 'Released': '1975-07-01', 'AlbumArtist': 'ajsfa'}]



for data_dict in data_dict_lis:
    table_keys = ",".join([key for key in data_dict.keys()])
    table_values = ",".join([str(value) for value in data_dict.values()])

    sql = "INSERT INTO IMPORTTABLE ("+table_keys+") VALUES ("+table_values+")"
    print(sql)

Upvotes: 2

Related Questions