Bilingual

SQLAlchemy 2 In Practice - Chapter 3 - One-To-Many Relationships
2 days ago
- This chapter covers one-to-many relationships in SQLAlchemy 2, focusing on moving duplicate data (like manufacturers) to separate tables.
- It explains the implementation of a one-to-many relationship using foreign keys and SQLAlchemy ORM relationship attributes for transparent navigation.
- The chapter demonstrates updating an importer script to handle separate Manufacturer and Product models, and shows how to query across these tables using joins.
- It discusses relationship loaders (lazy vs. eager loading), specifically the 'select' default and 'joined' loader, and their impact on database performance.
- The section on cascades covers deletion behavior, including 'all, delete-orphan' for automatic deletion of related products when a manufacturer is removed.
- Exercises at the end provide practice with one-to-many queries, such as finding products by manufacturer or manufacturers with specific criteria.
SQLAlchemy 2 In Practice - Chapter 6: A Page Analytics Solution
2 days ago
- Build a web traffic analytics solution using SQLAlchemy to reinforce database design concepts.
- Introduce blog articles and authors with one-to-many relationships, including optional product association.
- Track blog sessions and views with UUID primary keys, allowing duplicate views via auto-incrementing IDs.
- Implement page analytics queries to count views, rank articles, and report by product or period.
- Use outer joins (full, left, right) to include articles with no product or zero views.
- Add multi-language support with a Language model and self-referential translation_of relationship for originals and translations.
- Run complex queries using aliased tables, CASE expressions, and aggregations to analyze views by original articles or language.
SQLite: The Advantages of Flexible Typing
14 days ago
- SQLite's flexible type system allows storing any data type in any column regardless of column definition, promoting adaptability and simplifying schema design.
- Flexible typing supports various real-world use cases, such as key-value storage, handling mixed or dirty data (e.g., CSV files), and seamless evolution of data types over time without migrations.
- The feature enhances interoperability with other SQL databases by accepting diverse type names and is beneficial for dynamic programming languages like TCL, aligning with SQLite's original design goals.
- Arguments against flexible typing often stem from unfamiliarity or the belief that strict type enforcement prevents bugs, but SQLite's single 'sqlite3_value' superclass reduces the need for such enforcement in high-level abstractions.
- SQLite offers STRICT tables for rigid type enforcement if desired, but after years, no evidence shows rigid types preventing bugs, while flexibility aids long-term resilience, as illustrated by the Fossil version control system's SHA hash migration example.
SQLite Is Dynamically Typed (2020)
20 days ago
- SQLite columns are flexibly typed, allowing any value in any column.
- Columns have a 'type affinity', and SQLite performs implicit conversions, like turning numeric strings into integers.
- The NULL type remains special, and NOT NULL constraints are enforced, unlike other flexible typing aspects.
- SQLite can store mismatched types (e.g., writing bytes as-is) when conversions aren't possible, unlike many databases that might reject such data.
What ORMs have taught me: just learn SQL (2014)
a month ago
- The author concludes that ORMs are more detrimental than beneficial, suggesting they should augment SQL rather than replace it.
- Key issues with ORMs include attribute creep (wide tables), excessive foreign key joins, and inefficient data retrieval, requiring SQL knowledge for optimization.
- The dual-schema problem leads to redundant data definitions in databases and applications, complicating migrations and management.
- Entity identity mismatches between application memory pointers and database identifiers cause extra ORM manipulation overhead.
- Transaction handling in ORMs involves boilerplate code and dynamic scoping, clashing with lexical scoping paradigms.
- The author advocates viewing databases as data types with query APIs, moving away from object storage thinking, and recommends learning SQL directly.

database design

SQLAlchemy 2 In Practice - Chapter 3 - One-To-Many Relationships

SQLAlchemy 2 In Practice - Chapter 6: A Page Analytics Solution

SQLite: The Advantages of Flexible Typing

SQLite Is Dynamically Typed (2020)

What ORMs have taught me: just learn SQL (2014)