Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use Pydantic v2 to validate and shape Python data, and use SQLite to define tables and store it. Pydantic does not create SQLite tables: you still write SQL, but you can avoid interpolating values into SQL strings by mapping model fields to columns and binding values with placeholders.
What Pydantic and SQLite each do
A Pydantic model is a Python class derived from BaseModel, with fields declared through type annotations and optional constraints. When you create an instance from input, Pydantic processes that input and returns values that conform to the model’s declared types and constraints. As the Pydantic model documentation puts it, “Pydantic guarantees the types and constraints of the output, not the input data.” Pydantic may coerce values rather than reject them; use strict validation when the application must reject a convertible but incorrectly typed input.
SQLite remains responsible for tables, columns, constraints, indexes, transactions, and persisted data. Your Python code connects the two: validate incoming data, explicitly map model fields to SQL columns, and pass values separately from the SQL statement.
Define and validate a model
This example accepts convenient conversion where Pydantic supports it. It also rejects unrecognized input keys so that a typo such as emial does not silently disappear. Pydantic’s default behavior is to ignore extra fields; configure a different policy explicitly when you need one.
#1 Best Overall
from pydantic import BaseModel, ConfigDict, EmailStr
class Contact(BaseModel):
model_config = ConfigDict(extra="forbid")
name: str
email: EmailStr
age: int | None = None
contact = Contact.model_validate({
"name": "Ada Lovelace",
"email": "ada@example.com",
"age": "36",
})Here, Pydantic can convert the age string to an integer. If a field must reject coercible values, select strict validation for that field or model and verify that the policy fits the input sources. Validation applies when you construct or validate the model; it does not continuously police later mutations or data written by another application.
Create the SQLite table explicitly
Write the database schema as SQL. This example makes the email unique and allows age to be null; those are database rules, not automatic consequences of the Pydantic annotations.
import sqlite3
con = sqlite3.connect("contacts.db")
con.execute("""
CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
age INTEGER
)
""")
con.commit()Choose SQLite types and constraints deliberately. A Pydantic model can help you design the table, but generated JSON Schema is not SQLite DDL and does not provide table creation or a migration history. Pydantic documents JSON Schema output against JSON Schema Draft 2020-12 and OpenAPI Specification v3.1.0; those describe data structures for JSON-oriented uses, not SQLite migrations. See the Pydantic JSON Schema documentation.
Map fields and insert with bound parameters
Use model_dump() to get a Python dictionary, then explicitly select the fields that correspond to database columns. The placeholders below are named; SQLite binds their values separately from the SQL text.
payload = contact.model_dump()
con.execute(
"INSERT INTO contacts (name, email, age) VALUES (:name, :email, :age)",
{
"name": payload["name"],
"email": str(payload["email"]),
"age": payload["age"],
},
)
con.commit()Python’s sqlite3 documentation recommends placeholders rather than assembling SQL with string formatting. Do not put untrusted values into an f-string or concatenate them into a statement. Placeholders are for values, not SQL identifiers: if a table name or column name must vary, choose it from a controlled allowlist and construct that SQL structure separately.
model_dump() defaults to Python-mode output, which can include values that are not SQLite-bindable primitives. Decide how to store types such as dates, decimals, enums, or nested models. Convert them deliberately to a suitable SQLite value or use JSON-compatible output with model_dump(mode="json") when JSON serialization is the intended representation. JSON mode is serialization; it is not JSON Schema generation.
Choose relational columns or a JSON text column
For a small record, choose storage based on how the application will query and maintain it. Neither Pydantic nor sqlite3 prescribes one universal choice.
| Approach | Useful when | Trade-offs |
|---|---|---|
| One SQLite column per field | You need direct filtering, sorting, joins, uniqueness rules, or other database constraints on individual fields. | You map fields explicitly and update the schema through migrations as the model evolves. |
| One JSON text column | The payload is nested or is usually read and written as a whole. | Encoding and decoding become part of the storage contract; field-level SQL querying and constraints are less direct. |
For JSON text, serialize to JSON-compatible output and encode it with a JSON library, then bind the resulting string to a TEXT column. On retrieval, decode the text before passing the resulting mapping to Pydantic. Keep stable, frequently queried fields in relational columns when you need ordinary SQL constraints or access patterns.
Read rows and validate them again
By default, sqlite3 returns each row as a tuple. Map selected columns into the shape expected by the model before calling model_validate(). In this example, the selected columns match the model’s fields, so the mapping can be built directly.
row = con.execute(
"SELECT name, email, age FROM contacts WHERE email = ?",
("ada@example.com",),
).fetchone()
if row is not None:
stored_contact = Contact.model_validate({
"name": row[0],
"email": row[1],
"age": row[2],
})For larger queries, keep the selected column order and the row-to-field mapping explicit, or configure a row representation appropriate to the application. Validation on read can detect data that no longer conforms to the current model, including values written by older code or other database clients.
Handle commits, errors, and schema changes
A successful execute() is not by itself a durable write: commit the transaction. For multi-step work, use a transaction boundary so related changes succeed or roll back together, and close the connection when finished. Python’s sqlite3 documentation describes connection and transaction behavior, including commits and context management.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
try:
with con:
con.execute(
"UPDATE contacts SET age = ? WHERE email = ?",
(37, "ada@example.com"),
)
finally:
con.close()The connection context manager commits on successful exit and rolls back when an exception escapes the block; it does not close the connection, which is why the example closes it separately. Handle expected integrity errors, such as a duplicate email violating UNIQUE, at the appropriate application boundary rather than assuming model validation can prevent conflicts with existing rows.
When fields change, decide whether that is only a validation-model change or also a database change. Adding a model field does not add a column. Write and apply an explicit SQLite migration when the stored schema must change, and coordinate application versions with that migration. Pydantic v2 uses APIs such as model_dump() and model_validate(); its migration guide documents breaking changes from v1.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
