如何将JSON中的列表值存入SQLite单列？RabbitMQ接收JSON数据存入SQLite报错解决

Hey there! You’re spot-on with your guess—this error happens because SQLite doesn’t natively support storing Python list objects directly in a column. To get those list values into a single database cell, we need to convert them into a format SQLite understands, like a string. Here are two straightforward solutions tailored to your needs:

Option 1: Serialize Lists to JSON Strings (Recommended) #

Since your original message is already JSON, converting the lists back to JSON strings is clean and preserves their structure. Later, when you retrieve the data, you can easily convert it back to a Python list.

Here’s the modified callback function:

import json
import sqlite3

def callback(ch, method, properties, body):
    print("%r" % body)
    body = json.loads(body)

    # Convert lists to JSON-formatted strings
    items_json = json.dumps(body["items"])
    prices_json = json.dumps(body["prices"])
    # Handle "full price" (extract the single value since it's a 1-element list)
    full_price = body["full price"][0] if body["full price"] else None

    # Use a context manager to auto-manage the database connection
    with sqlite3.connect('pythonDB.db') as conn:
        c = conn.cursor()
        # Update table schema: set list columns to TEXT type
        c.execute('''CREATE TABLE IF NOT EXISTS Table_3 
                     (ticket TEXT, items TEXT, prices TEXT, FullPrice INTEGER)''')
        c.execute("INSERT INTO Table_3 VALUES(?,?,?,?)", 
                  (body["ticket"], items_json, prices_json, full_price))
        conn.commit()

Why this works: #

• json.dumps() turns your Python lists into valid JSON strings that SQLite can store in a TEXT column.
• When you need to use the list data later, just run json.loads() on the stored string to convert it back to a Python list.
• The context manager (with statement) automatically closes the database connection, avoiding resource leaks.

Option 2: Join Lists into Comma-Separated Strings #

If you don’t need to convert the data back to a list later and just want a human-readable string, you can join the list elements with a separator like a comma.

Here’s how that looks:

import json
import sqlite3

def callback(ch, method, properties, body):
    print("%r" % body)
    body = json.loads(body)

    # Join list elements into a single string
    items_str = ", ".join(body["items"])
    # Convert numeric prices to strings first, then join
    prices_str = ", ".join(map(str, body["prices"]))
    full_price = body["full price"][0] if body["full price"] else None

    with sqlite3.connect('pythonDB.db') as conn:
        c = conn.cursor()
        c.execute('''CREATE TABLE IF NOT EXISTS Table_3 
                     (ticket TEXT, items TEXT, prices TEXT, FullPrice INTEGER)''')
        c.execute("INSERT INTO Table_3 VALUES(?,?,?,?)", 
                  (body["ticket"], items_str, prices_str, full_price))
        conn.commit()

Key Notes: #

• For numeric lists like prices, we use map(str, ...) to convert each number to a string before joining.
• This method is simpler for quick readability, but you’ll lose the ability to easily convert the string back to a structured list later.

Critical Schema Fix #

Notice we changed the prices column type from INTEGER to TEXT in both examples. Since we’re storing string representations of lists, an INTEGER column won’t work—this was part of your original error too!

内容的提问来源于stack exchange，提问作者Jerry