Introduction to ORM and Peewee Usage in Python

Object Relational Mapping (ORM) is a technique that bridges object‑oriented programming and relational databases such as MySQL. It maps classes to tables, instances to rows, and attributes to columns, allowing developers to persist objects without writing SQL.

Django includes a built‑in ORM that automatically translates model operations into SQL, enabling data access through Python objects instead of raw queries.

Compared with direct PyMySQL usage, ORM eliminates the need to write repetitive SQL for CRUD operations, improves code readability, and abstracts database details, though it may incur some performance overhead.

Common Python ORM options include SQLObject, Storm, Django’s ORM, Peewee, and SQLAlchemy. Their strengths and weaknesses are summarized in a comparison table.

After evaluation, the team chose Peewee for its lightweight nature, SQLAlchemy‑like core, and easy integration with any web framework.

Using Peewee in automation:

1. Create a model via the pwiz command, which generates a .py file containing the model definition.

2. Insert data using save() , create() , insert() , or insert_many() .

3. Update records with save() (when the instance has a primary key) or update() combined with where() .

4. Delete records using delete() with optional where() or delete_instance() on a model instance.

5. Query data via methods such as get() , select() , and chained calls like where() , order_by() , limit() .

Example of a complex update with a sub‑query:

# Update example
update tebleC
set not_found_time = DATE_SUB(not_found_time,interval 1 day)
where item_id =(select poi.id
                from tebleA poi
                left join tebleB pid
                on pid.id = poi.in_id
                where pid.e_code = 'SFXXXXXXXXX');

Peewee also provides logical operators ( & for AND, | for OR, ~ for NOT) and comparison operators (==, <, <=, >, >=, !=, <<, >>, %, **).

The adoption of ORM and object‑oriented design has significantly reduced manual data‑creation effort, cutting test‑data generation time from hours to seconds and improving overall development efficiency.