ZetCode

Python sqlite3.Cursor.setinputsizes Method

Last modified April 15, 2025

This guide explores Python's sqlite3.Cursor.setinputsizes method, which is part of the DB-API 2.0 specification but not implemented in SQLite.

Basic Definitions

The setinputsizes method is defined in Python's DB-API 2.0 as a way to predefine memory areas for parameter binding. It's meant to optimize repeated executions of the same statement.

In SQLite's implementation, this method does nothing as SQLite handles parameter binding dynamically. The method exists for API compatibility but has no effect.

Method Signature

The method signature is simple: setinputsizes(sizes) where sizes can be a sequence or None. The sizes parameter specifies the expected data types.

Despite being callable, the method performs no operation in SQLite. It's present to maintain compatibility with the DB-API 2.0 specification.

Basic Usage

This example demonstrates calling the method with different parameter types. Despite the calls, they have no effect on SQLite operations.

basic_usage.py
import sqlite3

with sqlite3.connect(':memory:') as conn:
    with conn.cursor() as cur:
        # These calls do nothing in SQLite
        cur.setinputsizes(None)
        cur.setinputsizes([sqlite3.TEXT, sqlite3.INTEGER])
        cur.setinputsizes(50)  # Arbitrary size
        
        # Execute a query normally
        cur.execute("CREATE TABLE test (id INTEGER, name TEXT)")
        cur.execute("INSERT INTO test VALUES (?, ?)", (1, 'Alice'))

The example shows that setinputsizes can be called but doesn't affect subsequent operations. SQLite handles parameter binding dynamically.

With execute

This example attempts to use setinputsizes before executing a query. The method call has no impact on the execution.

with_execute.py
import sqlite3

with sqlite3.connect(':memory:') as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TABLE data (id INTEGER, value REAL)")
        
        # Set input sizes (no effect)
        cur.setinputsizes([sqlite3.INTEGER, sqlite3.REAL])
        
        # Insert data works the same with or without setinputsizes
        cur.execute("INSERT INTO data VALUES (?, ?)", (1, 3.14159))
        
        # Verify insertion
        cur.execute("SELECT * FROM data")
        print(cur.fetchone())  # (1, 3.14159)

The data insertion works identically whether setinputsizes is called or not. SQLite determines parameter types at execution time.

Multiple Parameter Types

This example shows that SQLite ignores the type hints provided to setinputsizes and handles type conversion automatically.

multiple_types.py
import sqlite3

with sqlite3.connect(':memory:') as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TABLE items (id INTEGER, name TEXT, price REAL)")
        
        # Set input sizes for different types (ignored)
        cur.setinputsizes([sqlite3.INTEGER, sqlite3.TEXT, sqlite3.REAL])
        
        # Insert with automatic type conversion
        cur.execute("INSERT INTO items VALUES (?, ?, ?)", 
                   ('1', 42.99, '3.50'))  # Note reversed types
        
        # The values are stored correctly despite type hints
        cur.execute("SELECT * FROM items")
        print(cur.fetchone())  # (1, '42.99', 3.5)

SQLite performs type conversion regardless of the setinputsizes calls. The method doesn't enforce or influence type handling in SQLite.

With executemany

This example demonstrates that setinputsizes has no effect on batch operations using executemany.

with_executemany.py
import sqlite3

data = [
    (1, 'Apple', 0.99),
    (2, 'Banana', 0.59),
    (3, 'Cherry', 1.99)
]

with sqlite3.connect(':memory:') as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TABLE products (id INTEGER, name TEXT, price REAL)")
        
        # Set input sizes (no effect)
        cur.setinputsizes([sqlite3.INTEGER, sqlite3.TEXT, sqlite3.REAL])
        
        # Batch insert works normally
        cur.executemany("INSERT INTO products VALUES (?, ?, ?)", data)
        
        # Verify all rows inserted
        cur.execute("SELECT COUNT(*) FROM products")
        print(cur.fetchone()[0])  # 3

The executemany operation proceeds normally regardless of the setinputsizes call. SQLite handles each parameter set dynamically.

With Custom Types

This example shows that setinputsizes doesn't affect custom type adapters in SQLite.

custom_types.py
import sqlite3
import datetime

# Register custom adapter
sqlite3.register_adapter(datetime.date, lambda d: d.isoformat())

with sqlite3.connect(':memory:') as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TABLE events (id INTEGER, date TEXT)")
        
        # Set input sizes (ignored)
        cur.setinputsizes([sqlite3.INTEGER, sqlite3.TEXT])
        
        # Custom type conversion still works
        today = datetime.date.today()
        cur.execute("INSERT INTO events VALUES (?, ?)", (1, today))
        
        # Verify date stored correctly
        cur.execute("SELECT date FROM events WHERE id = 1")
        print(cur.fetchone()[0])  # ISO formatted date

The custom date adapter works regardless of the setinputsizes call. SQLite's type adaptation system operates independently of this method.

Performance Comparison

This example demonstrates that setinputsizes doesn't provide any performance benefit in SQLite, unlike some other database systems.

performance.py
import sqlite3
import time

def time_inserts(use_setinputsizes):
    with sqlite3.connect(':memory:') as conn:
        with conn.cursor() as cur:
            cur.execute("CREATE TABLE nums (n INTEGER)")
            
            if use_setinputsizes:
                cur.setinputsizes([sqlite3.INTEGER])
            
            start = time.time()
            for i in range(1000):
                cur.execute("INSERT INTO nums VALUES (?)", (i,))
            conn.commit()
            return time.time() - start

# Compare execution times
time_with = time_inserts(True)
time_without = time_inserts(False)

print(f"With setinputsizes: {time_with:.4f}s")
print(f"Without setinputsizes: {time_without:.4f}s")

The execution times will be nearly identical, showing that setinputsizes provides no performance optimization in SQLite.

Error Handling

This example shows that setinputsizes doesn't validate parameters or affect error handling in SQLite.

error_handling.py
import sqlite3

with sqlite3.connect(':memory:') as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)")
        
        # These calls don't affect error handling
        cur.setinputsizes([sqlite3.INTEGER, sqlite3.TEXT])
        cur.setinputsizes("invalid")  # Non-standard parameter
        
        try:
            # This will still raise an error
            cur.execute("INSERT INTO test VALUES (?, ?)", ("text", 123))
        except sqlite3.InterfaceError as e:
            print(f"Error occurred: {e}")

The example demonstrates that setinputsizes doesn't prevent or influence type-related errors in SQLite. Errors are raised during execution.

Best Practices

Source References

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Python tutorials.