Fixing Python sqlite3 That Returns Bytes Instead of Strings for TEXT Columns

You write a clean SELECT query against a TEXT column, fetch the rows, and immediately hit TypeError: a bytes-like object is required — or worse, your output silently shows b'Paris' where you expected 'Paris'. The data is there; it just came back wrapped in bytes.

This is one of those bugs that looks like data corruption but is almost always a one-line configuration mistake. This article pinpoints the exact causes and gives you working fixes you can drop straight into your code.

What you'll learn

• Why Python's sqlite3 module sometimes returns bytes for TEXT columns instead of str
• The three most common configuration mistakes that trigger this
• How to fix each one with minimal changes to your existing code
• How to tell whether your column is genuinely BLOB (not TEXT)
• How to write a quick end-to-end test to confirm the fix held

Prerequisites

• Python 3.6 or later (the built-in sqlite3 module — no third-party packages needed)
• A basic understanding of opening a connection and running queries
• An existing database or the ability to create a throwaway test one

What's actually happening

SQLite itself is loosely typed. The Python sqlite3 module uses a layer called type adapters and converters to map SQLite storage classes back to Python types. When that layer is misconfigured — or when Python has no registered converter for the column's declared type — the module falls back to returning raw bytes objects rather than decoded strings.

This fallback is intentional: the module would rather give you the bytes than silently mangle data it doesn't know how to decode. The side-effect is that your code breaks in confusing ways at runtime instead of at the connection setup step where the misconfiguration lives.

Why sqlite3 returns bytes for TEXT columns

The sqlite3 module maps SQLite's storage classes to Python types using two knobs: the detect_types flag on the connection and the text_factory attribute. Both have defaults that work correctly in most situations, but either one can be overridden — accidentally or intentionally — in a way that produces bytes.

A third, less common cause is that the column was declared as BLOB (or with no type affinity at all), even though the application treats it as text. SQLite will store whatever bytes you handed it, and the module will faithfully return those bytes to you.

The three main causes

1. detect_types is set but no converter is registered

When you pass detect_types=sqlite3.PARSE_DECLTYPES or detect_types=sqlite3.PARSE_COLNAMES, the module looks up a registered converter for each column's declared type. If it finds one, it calls it. If it finds none, it returns the raw bytes from SQLite's storage layer.

For a TEXT column named something like "category [mytype]", the module will search for a mytype converter. When it doesn't find one, you get bytes.

2. text_factory has been changed from str to bytes

The connection attribute text_factory controls what Python type is used for TEXT values. Its default is str. Changing it to bytes (or any callable that returns bytes) will cause every TEXT column on that connection to come back as bytes. This change is sometimes made intentionally for legacy code, then forgotten.

3. The column type is BLOB, not TEXT

SQLite stores data as one of five storage classes: NULL, INTEGER, REAL, TEXT, BLOB. If you inserted a Python bytes object originally, SQLite stored it as BLOB. Querying it back always returns bytes regardless of what type the column was declared with, because SQLite respects the stored type over the declared type.

Fix 1: Check and remove detect_types misuse

Start by inspecting your connection call. The problem often looks like this:

# Problematic — detect_types is set but no converter exists for the column type
conn = sqlite3.connect("app.db", detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.execute("SELECT city FROM users")
print(cursor.fetchone()[0])  # b'London' instead of 'London'

If you don't actually need custom type converters, removing the detect_types argument is the fastest fix:

# Fixed — no detect_types, TEXT columns come back as str by default
conn = sqlite3.connect("app.db")
cursor = conn.execute("SELECT city FROM users")
print(cursor.fetchone()[0])  # 'London'

If you do need detect_types for other columns in the same connection, register an explicit converter for the TEXT type so the module knows what to do:

import sqlite3

# Register a converter that decodes bytes to str for any column declared TEXT
sqlite3.register_converter("TEXT", lambda b: b.decode("utf-8"))

conn = sqlite3.connect("app.db", detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.execute("SELECT city FROM users")
print(cursor.fetchone()[0])  # 'London'

Register the converter before you open the connection. Converters registered afterward are picked up by subsequent queries on already-open connections, but registering before is cleaner and less error-prone.

Fix 2: Reset text_factory to str

Search your codebase for any assignment to text_factory. A single assignment like the one below affects every query on that connection for its entire lifetime:

# Somewhere in your setup code — easy to miss
conn.text_factory = bytes  # This causes ALL TEXT columns to return bytes

Reset it to str, which is the documented default:

conn.text_factory = str  # Restores normal behaviour

If the bytes assignment was there to handle encoding errors from non-UTF-8 content, use a safer replacement that preserves readability:

# Decodes as UTF-8, replacing any unrecognisable bytes with the replacement character
conn.text_factory = lambda b: b.decode("utf-8", errors="replace")

This way you get strings back, and any genuinely malformed bytes show up as � rather than silently corrupting downstream logic. For a deeper look at how Python sqlite3 handles non-ASCII data, see fixing sqlite3 that loses data when writing Unicode or emoji characters.

Fix 3: Decode at the cursor level with a row factory

Sometimes you can't change the connection configuration — it's shared, it's in a library you don't own, or changing it breaks something else. In that case, apply a row factory that decodes bytes columns on the way out.

import sqlite3

def decode_bytes_row(cursor, row):
    """Decode any bytes values in a row to UTF-8 strings."""
    return tuple(
        value.decode("utf-8") if isinstance(value, bytes) else value
        for value in row
    )

conn = sqlite3.connect("app.db", detect_types=sqlite3.PARSE_DECLTYPES)
conn.row_factory = decode_bytes_row

cursor = conn.execute("SELECT city, population FROM users")
row = cursor.fetchone()
print(row[0])  # 'London'  — str
print(row[1])  # 8900000  — int, untouched

This approach is surgical: it only changes output handling for this connection and leaves integer, float, and None values alone. It also gives you a natural place to add logging if you want to track which columns are still returning bytes unexpectedly.

One thing to keep in mind: if you're already using sqlite3.Row as your row factory for column-name access, you'll need to decide which one takes priority. You can combine both by building a small wrapper:

import sqlite3

def make_row_factory(cursor, row):
    """Row factory that gives dict-style access and decodes bytes."""
    fields = [description[0] for description in cursor.description]
    return {
        field: (value.decode("utf-8") if isinstance(value, bytes) else value)
        for field, value in zip(fields, row)
    }

conn = sqlite3.connect("app.db", detect_types=sqlite3.PARSE_DECLTYPES)
conn.row_factory = make_row_factory

cursor = conn.execute("SELECT city, population FROM users")
row = cursor.fetchone()
print(row["city"])       # 'London'
print(row["population"]) # 8900000

When the column type is genuinely BLOB

Before applying any fix, confirm that the column really is TEXT and not BLOB. Run this query against your database:

PRAGMA table_info(users);

The output lists each column's name and declared type. If a column you're treating as text shows BLOB or is blank (no type declared), that's the root cause — not a Python configuration problem.

If the schema is wrong, the cleanest fix is to migrate the data into a properly declared TEXT column:

-- Step 1: add a correctly typed column
ALTER TABLE users ADD COLUMN city_text TEXT;

-- Step 2: copy the BLOB data, casting to text
UPDATE users SET city_text = CAST(city AS TEXT);

-- Step 3: verify, then drop the old column (requires re-creating the table in SQLite)

SQLite doesn't support DROP COLUMN before version 3.35.0. For older SQLite versions you'll need to recreate the table. See the guide on fixing wrong values after ALTER TABLE for the full recreate-and-copy pattern.

If you inserted rows by passing a Python bytes object where you meant to pass a str, the fix is upstream in your write code:

# Wrong — inserts as BLOB
conn.execute("INSERT INTO users (city) VALUES (?)", (b"London",))

# Correct — inserts as TEXT
conn.execute("INSERT INTO users (city) VALUES (?)", ("London",))

Verifying the fix end-to-end

After applying whichever fix fits your situation, run a quick assertion test before committing the change:

import sqlite3

def test_text_columns_return_str():
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE t (name TEXT, score INTEGER)")
    conn.execute("INSERT INTO t VALUES (?, ?)", ("Alice", 42))
    conn.commit()

    row = conn.execute("SELECT name, score FROM t").fetchone()
    assert isinstance(row[0], str), f"Expected str, got {type(row[0])}"
    assert isinstance(row[1], int), f"Expected int, got {type(row[1])}"
    print("All assertions passed.")
    conn.close()

test_text_columns_return_str()

Using ":memory:" keeps this self-contained — no files created, no cleanup required. Run it as part of your normal test suite so any future change to connection setup doesn't silently reintroduce the problem.

If your project uses threading and the same connection is shared across threads, make sure to also review the common issue of sqlite3 returning no results inside a thread, since threading adds another layer of configuration that can interact with type handling.

Common pitfalls after the fix

Fixing one connection but not all of them

If your application opens multiple connections — perhaps one for reads and one for writes — make sure the fix is applied at connection creation time, not just for one branch. A helper function that centralises connection setup is the most reliable approach.

def get_connection(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path)
    conn.text_factory = str  # always explicit
    conn.row_factory = sqlite3.Row
    return conn

Assuming the schema matches reality

SQLite's flexible type system means a column declared TEXT can still contain BLOB values if someone inserted bytes at some point. After fixing the connection settings, run a data audit if you see intermittent bytes values: SELECT rowid, city FROM users WHERE typeof(city) = 'blob'. Fix those rows individually with a targeted UPDATE.

Forgetting about pickled objects stored as bytes

Some patterns serialize Python objects with pickle, store the result as bytes in a TEXT or BLOB column, then expect to deserialize on read. After you add a decoding row factory, those pickled bytes will be mangled by .decode("utf-8"). Add a type check in your factory: only decode if the column name suggests it's plain text, or check for a valid UTF-8 decode before applying it.

Next steps

• Centralise connection setup. Move all sqlite3.connect() calls behind a single factory function that enforces text_factory = str and your preferred row factory.
• Run PRAGMA table_info. Check every table's declared column types. Fix any TEXT column that was accidentally created as BLOB or with no type.
• Add a type assertion test. Copy the test from the verification section above into your test suite so regressions surface immediately.
• Audit existing rows. Use typeof(column) in a SELECT to find rows where the stored type doesn't match the declared column type, then repair them with a targeted UPDATE.
• Review your insert code. Search for any place you pass bytes literals or variables into TEXT column parameters and convert them to str before the insert.