Database Topics in Day 36-45 of Python-100-Days: MySQL, SQL, and Python Integration

Days 36-45 of the Python-100-Days curriculum deliver a comprehensive ten-day progression from relational database theory through advanced SQL, MySQL 8.0 features, and Python database connectivity, culminating in Hive data warehousing fundamentals.

The jackfrued/Python-100-Days repository structures its second major milestone around practical database engineering, moving from entity-relationship modeling to production-grade Python-MySQL implementations. These ten days—captured in the Day36-45/ directory—provide the foundational knowledge required for data persistence in Python applications.

Relational Database Foundations (Day 36)

The journey begins in Day36-45/36.关系型数据库和MySQL概述.md with entity-relationship (ER) modeling and the core principles of relational database management systems (RDBMS). Learners explore database normalization, primary and foreign key constraints, and the architectural differences between MySQL storage engines such as InnoDB and MyISAM.

SQL Language Fundamentals (Days 37-40)

Days 37 through 40 dissect SQL into its four functional categories, each occupying a dedicated markdown file in the repository.

Data Definition Language (DDL)

Day36-45/37.SQL详解之DDL.md covers schema management through CREATE, ALTER, and DROP statements. The material demonstrates table creation with constraint definitions, column modifications, and index creation syntax essential for database structure management.

Data Manipulation Language (DML)

Day36-45/38.SQL详解之DML.md focuses on INSERT, UPDATE, and DELETE operations. The content emphasizes batch insertion techniques, safe update practices using WHERE clauses, and transaction-aware data modifications to prevent accidental data loss.

Data Query Language (DQL)

Day36-45/39.SQL详解之DQL.md provides exhaustive coverage of the SELECT statement, progressing from basic WHERE clauses to complex JOIN operations, GROUP BY aggregations with HAVING filters, and correlated subqueries. The file introduces window functions for advanced analytical queries.

Data Control Language (DCL)

Day36-45/40.SQL详解之DCL.md addresses database security through GRANT and REVOKE statements for user privilege management. The section also covers transaction control language (TCL) concepts including COMMIT, ROLLBACK, and savepoint management for ACID compliance.

MySQL 8.0 Modern Features (Day 41)

Day36-45/41.MySQL新特性.md introduces capabilities exclusive to MySQL 8.0, including JSON data type support for semi-structured data, Common Table Expressions (CTEs) for recursive queries, and enhanced window functions such as ROW_NUMBER(), RANK(), and LEAD()/LAG() for analytical processing without self-joins.

Database Objects and Optimization (Days 42-43)

Views, Functions, and Stored Procedures

Day36-45/42.视图、函数和过程.md teaches encapsulation of complex logic through database views for simplified querying, stored functions for reusable calculations, and stored procedures for batch operations with input/output parameters.

Indexing Strategies

Day36-45/43.索引.md explores B-Tree and Hash index types, composite index design, and the performance implications of covering indexes versus full table scans. The content addresses when to index foreign keys and how to analyze query execution plans.

Python-MySQL Integration (Day 44)

Day36-45/44.Python接入MySQL数据库.md bridges SQL knowledge with Python application development using pymysql and mysql-connector-python libraries. The implementation patterns include:

import pymysql

# Establish connection with context manager for automatic cleanup

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        # Parameterized query preventing SQL injection

        sql = "INSERT INTO employees (name, salary) VALUES (%s, %s)"
        cursor.execute(sql, ('张三', 15000))
        
        # Transaction control

        conn.commit()
        
        # Fetching results

        cursor.execute("SELECT * FROM employees WHERE salary > %s", (10000,))
        results = cursor.fetchall()
finally:
    conn.close()

The file also demonstrates connection pooling configurations and proper exception handling for production environments.

Data Warehousing with Hive (Day 45)

Day36-45/45.Hive实战.md transitions to big data ecosystems, introducing HiveQL syntax for querying distributed datasets stored in Hadoop. Topics include external tables, partition strategies for performance, and the differences between HiveQL and standard MySQL SQL dialects.

Summary

• Day 36 establishes relational theory and MySQL architecture in Day36-45/36.关系型数据库和MySQL概述.md
• Days 37-40 provide exhaustive SQL coverage across DDL, DML, DQL, and DCL categories with dedicated files for each language component
• Day 41 introduces modern MySQL 8.0 features including JSON support and window functions in Day36-45/41.MySQL新特性.md
• Days 42-43 address database programmability through views/stored procedures and indexing optimization strategies
• Day 44 delivers practical Python-MySQL connectivity patterns using pymysql and parameterized queries for secure database access
• Day 45 expands into big data with HiveQL fundamentals for data warehousing scenarios

Frequently Asked Questions

What SQL categories are covered in Days 37-40?

Days 37-40 dissect SQL into four functional categories: DDL (Data Definition Language) for schema management in Day36-45/37.SQL详解之DDL.md, DML (Data Manipulation Language) for data modifications in Day36-45/38.SQL详解之DML.md, DQL (Data Query Language) for data retrieval in Day36-45/39.SQL详解之DQL.md, and DCL (Data Control Language) for security and transaction management in Day36-45/40.SQL详解之DCL.md.

How does Day 44 demonstrate Python-MySQL connectivity?

Day36-45/44.Python接入MySQL数据库.md demonstrates connectivity through the pymysql and mysql-connector-python libraries, emphasizing context managers for resource cleanup, parameterized queries to prevent SQL injection, explicit transaction control via commit() and rollback(), and connection pooling for high-performance applications.

What MySQL 8.0 features are introduced in Day 41?

Day36-45/41.MySQL新特性.md covers JSON data types for storing semi-structured data, Common Table Expressions (CTEs) for recursive and readable complex queries, and enhanced window functions including ROW_NUMBER(), RANK(), and LEAD()/LAG() for advanced analytical operations without requiring self-joins.

Does Day 45 cover standard SQL or something different?

Day 45 (Day36-45/45.Hive实战.md) covers HiveQL, a SQL-like query language designed for Hadoop-based data warehouses. While syntactically similar to MySQL SQL, HiveQL specifically addresses distributed data processing, external tables, and partitioning strategies for petabyte-scale datasets, distinguishing it from the transactional SQL covered in earlier days.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}