[FIXED] sqlite3 error "ValueError: parameters are of unsupported type"

Issue

I have an sqlite3 table that I created like this:

CREATE TABLE packages (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    price DECIMAL(5,2),
    enabled INTEGER DEFAULT 1
);

I am trying to query the table as below:

import sqlite3
from flask import current_app, g


def connection():
    if 'db' not in g or not isinstance(g.db, sqlite3.Connection):
        g.db = sqlite3.connect(current_app.config.get('DBPATH'),
            detect_types=sqlite3.PARSE_DECLTYPES)
        g.db.row_factory = sqlite3.Row

    return g.db


packageid = 6

cur = connection().cursor()

cur.execute("""
    SELECT id
    FROM packages
    WHERE id = :packageid
    """, {'packageid', packageid})
rec = cur.fetchone()
# Omitted logic to handle rec being None or not None

When I run the code above, it fails with ValueError: parameters are of unsupported type for line 16.

Why do I get this error? id is an INTEGER in sqlite3, and packageid is an int in python. I don't see why these two types are incompatible.

Solution

I guess the problem is not the type of your variable. If you take a look at the documentation for the Cursor.execute method (available here) you'll see that it expects a dictionary or a sequence as the second argument.

In your code the second parameter is actually a set (probably a typo.. been there, trust me). Just change it from {'packageid', packageid} to {'packageid': packageid} and it will probably work.

Answered By - Ralubrusto