Hone logo
Hone
Problems

Python Query Builder

This challenge involves creating a flexible and extensible Python class that can construct SQL-like SELECT queries dynamically. A query builder is a powerful tool for applications that need to interact with databases, allowing for programmatic construction of queries, which can improve security (by preventing SQL injection) and maintainability.

Problem Description

Your task is to implement a QueryBuilder class in Python that can generate SQL SELECT statements. The builder should allow users to specify various clauses of a SELECT statement, such as the columns to select, the table to query from, filtering conditions (WHERE), ordering, and limiting results.

Key Requirements:

  • Column Selection: Ability to specify which columns to select. This could be a list of column names or '*' for all columns.
  • Table Specification: Ability to specify the table(s) to query from.
  • Filtering (WHERE Clause): Support for adding conditions to the WHERE clause. Conditions should be combined using AND or OR operators. Support for basic comparison operators (=, !=, >, <, >=, <=, LIKE, IN, IS NULL).
  • Ordering (ORDER BY Clause): Ability to specify columns for sorting and the direction (ASC/DESC).
  • Limiting (LIMIT Clause): Ability to limit the number of returned rows.
  • Offsetting (OFFSET Clause): Ability to skip a certain number of rows.
  • String Representation: The QueryBuilder object should be able to return a string representation of the generated SQL query.
  • Immutability (Optional but Recommended): Each method that modifies the query should ideally return a new QueryBuilder instance, preserving the original. This makes chaining operations more predictable.

Expected Behavior:

The QueryBuilder class should provide methods like:

  • select(columns)
  • from_(table) (using from_ to avoid Python keyword conflict)
  • where(condition)
  • and_(condition)
  • or_(condition)
  • order_by(column, direction='ASC')
  • limit(count)
  • offset(count)

When the object is converted to a string (e.g., using str(query_builder)), it should produce a valid SQL SELECT statement based on the configured options.

Edge Cases to Consider:

  • No columns selected (defaults to *).
  • No table specified (should raise an error or return an invalid query).
  • Multiple WHERE conditions: how they are chained (AND vs. OR).
  • Empty lists for columns or orderings.
  • Handling different data types in WHERE conditions (strings, numbers, None).
  • What happens if LIMIT is used without OFFSET or vice-versa.

Examples

Example 1: Basic Query

# Input:
builder = QueryBuilder()
query_string = str(builder.select(['name', 'email']).from_('users').limit(10))

# Output:
# SELECT name, email FROM users LIMIT 10

Explanation: The builder is initialized, then select is called to specify columns, from_ to set the table, and limit to restrict the results. The str() conversion generates the final SQL string.

Example 2: Complex Filtering and Ordering

# Input:
builder = QueryBuilder()
query_string = str(
    builder
    .select(['product_name', 'price'])
    .from_('products')
    .where('price > 100')
    .and_('category = "Electronics"')
    .or_('is_discounted = TRUE')
    .order_by('price', direction='DESC')
    .limit(5)
    .offset(10)
)

# Output:
# SELECT product_name, price FROM products WHERE price > 100 AND category = "Electronics" OR is_discounted = TRUE ORDER BY price DESC LIMIT 5 OFFSET 10

Explanation: This example demonstrates chaining multiple methods to build a more complex query with WHERE conditions combined by AND and OR, sorting in descending order, and applying both LIMIT and OFFSET.

Example 3: Handling IS NULL and IN

# Input:
builder = QueryBuilder()
query_string = str(
    builder
    .select(['id', 'status'])
    .from_('orders')
    .where('shipped_date IS NULL')
    .and_('status IN ("Pending", "Processing")')
)

# Output:
# SELECT id, status FROM orders WHERE shipped_date IS NULL AND status IN ("Pending", "Processing")

Explanation: This shows how to construct queries with special WHERE conditions like IS NULL and IN.

Constraints

  • The QueryBuilder should handle up to 100 WHERE conditions.
  • The LIMIT and OFFSET values will be non-negative integers.
  • Column names and table names are assumed to be valid SQL identifiers (alphanumeric and underscores, no special characters that require quoting).
  • String literals within WHERE conditions will be properly quoted by the user (as shown in examples).
  • The QueryBuilder should be implemented in a single Python file.
  • Performance is not a primary concern for this challenge; correctness and clarity of the generated SQL are prioritized.

Notes

  • Consider how to manage the internal state of the query builder.
  • Think about how to represent and combine different parts of the SQL query (e.g., list of columns, conditions, orderings).
  • The from_ method is used to avoid conflict with the Python keyword from.
  • You might find it useful to use helper methods or internal data structures to keep track of the different clauses.
  • For simplicity, you don't need to implement complex SQL features like JOINs, GROUP BY, or HAVING for this challenge. Focus on the core SELECT, FROM, WHERE, ORDER BY, LIMIT, and OFFSET.
Loading editor...
python