Python Query Builder: Constructing SQL Queries Dynamically

Building SQL queries directly within code can be error-prone and difficult to maintain, especially when dealing with user input or complex filtering. This challenge asks you to create a simple query builder in Python that allows you to construct SQL queries programmatically, promoting cleaner code and reducing the risk of SQL injection vulnerabilities. The query builder should allow users to specify the table, select columns, add where clauses, and order the results.

Problem Description

You are tasked with creating a QueryBuilder class in Python. This class should provide a fluent interface for constructing SQL queries. The class should support the following functionalities:

• from_table(table_name): Initializes the query builder with the specified table name.
• select(columns): Specifies the columns to select. columns should be a list of strings.
• where(conditions): Adds a WHERE clause to the query. conditions should be a list of strings, where each string represents a single condition (e.g., ['id = 1', 'name = "John"']).
• order_by(column, direction='ASC'): Adds an ORDER BY clause. column is the column to order by (string), and direction is either 'ASC' (ascending) or 'DESC' (descending). Defaults to 'ASC'.
• build(): Returns the constructed SQL query as a string.

The QueryBuilder class should handle invalid inputs gracefully and provide meaningful error messages. The build() method should return an empty string if no table is specified.

Examples

Example 1:

Input:
builder = QueryBuilder()
query = builder.from_table("users").select(["id", "name", "email"]).where(["age > 25", "city = 'New York'"]).order_by("name", "DESC").build()

Output:
SELECT id, name, email FROM users WHERE age > 25 AND city = 'New York' ORDER BY name DESC

Explanation: The query builder constructs a SQL query selecting id, name, and email from the users table, filtering by age > 25 and city = 'New York', and ordering the results by name in descending order.

Example 2:

Input:
builder = QueryBuilder()
query = builder.from_table("products").select(["product_id", "product_name"]).build()

Output:
SELECT product_id, product_name FROM products

Explanation: A simple query selecting product_id and product_name from the products table.

Example 3: (Edge Case - No Table Specified)

Input:
builder = QueryBuilder()
query = builder.build()

Output:
""

Explanation: If no table is specified using from_table(), the build() method should return an empty string.

Constraints

• The SQL query generated should be valid SQL syntax.
• The where clause should combine conditions with AND.
• The order_by direction must be either 'ASC' or 'DESC' (case-insensitive). If an invalid direction is provided, default to 'ASC'.
• The select method should handle an empty list of columns gracefully (select all columns).
• The from_table method must be called before any other methods. Calling other methods before from_table should raise an AttributeError.
• All method calls should be chained.

Notes

• Focus on creating a clean and readable API.
• Consider using string formatting or template literals for constructing the SQL query.
• Error handling is important. Think about what should happen if a user provides invalid input.
• This is a simplified query builder. It does not need to support all SQL features (e.g., joins, subqueries, aggregate functions). The goal is to demonstrate the basic principles of dynamic query construction.
• The generated SQL should be safe from SQL injection vulnerabilities, assuming the input data is properly sanitized elsewhere. This challenge focuses on the query construction logic, not input sanitization.