How to add a list values from JSON to sqlite column-CodePudding

I receive a json message thru rabbitMQ and I want to store it to the database.

Message looks like this:

message ={
    "ticket": "ticket_12334345",
    "items" : ["item1","item2","item3"],
    "prices" : [10,20,15],
    "full price" : [45]
}

Storing to database looks like this:

def callback(ch, method, properties, body):
    print("%r" % body)
    body = json.loads(body)
    conn = sqlite3.connect('pythonDB.db')
    c = conn.cursor()
    c.execute('CREATE TABLE IF NOT EXISTS Table_3 (ticket TEXT, items TEXT, prices INTEGER,'
              'FullPrice INTEGER)')

    c.execute("INSERT INTO Table_3 VALUES(?,?,?,?)", (body["ticket"],
                                                    body["items"], body["prices"], body["full price"],
                                                    ))
    conn.commit()

I get an error sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

I assume it's because I'm not storing list data correctly. I want in the column all the values from the list in one cell.

CodePudding user response：

Use str() function, like this:

def callback(ch, method, properties, body):
    print("%r" % body)
    body = json.loads(body)
    conn = sqlite3.connect('pythonDB.db')
    c = conn.cursor()
    c.execute('CREATE TABLE IF NOT EXISTS Table_3 (ticket TEXT, items TEXT, prices INTEGER,'
              'FullPrice INTEGER)')

    c.execute("INSERT INTO Table_3 VALUES(?,?,?,?)", (body["ticket"],
                                                    str(body["items"]), str(body["prices"]), str(body["full price"][0]),
                                                    ))
    conn.commit()