Python query SQLite create JSON Object and map values to column names

I am creating a python script to query a SQLite DB file. We are querying the database to retrieve inventory information on computers for our asset management database.

Here are the requirements:

• Connect to SQLite db file
• Parse the data in the database
• Run SELECT queries to retrieve device inventory information
• Create a JSON Object
• Import the JSON Object via REST API

I have connected to the SQLite db file and ran the SELECT query to retrieve the device information.

I was able to create a JSON Array with the results.

However, here are the issues:

• The format needs to be a JSON Object (not an array)
• The format is missing the column names.

Question: How can I adapt the script to produce a JSON Object in the correct formatting with the values mapped to named columns?

import sqlite3 as lite
import sys
import json

con = lite.connect('example.db')

with con:

        con.row_factory = lite.Row

        cur = con.cursor()
        cur.execute("SELECT * FROM Devices")

        rows = cur.fetchall()

        rowarray_list = []
        for row in rows:
                t = (row['id'], row['name'], row['type'])
                rowarray_list.append(t)

        j = json.dumps(rowarray_list)
        rowarrays_file = 'rowarrays.js'
        f = open(rowarrays_file,'w')
        print (f, j)

OUTPUT RESULT

<_io.TextIOWrapper name='rowarrays.js' mode='w' encoding='UTF-8'> [[1, "example1", "Computer"], [2, "example2", "Server"], [3, "example3", "Server"]

REQUIRED OUTPUT

{"records":[{"id": "1", "name": "example1", "type": "Computer"}, {"id": "2", "name": "example2", "type": "Server"}, {"id": "3", "name": "example3", "type": "Server"}]}