examples/basic_usage.py annotated source

Back to index 

        

        
Basic Usage Examples


This module demonstrates practical usage patterns for the sqlite-ast-parser library.
It shows how to parse different SQL statement types and inspect the resulting AST nodes.


What You'll Learn


    

  • How to call parse_sql() and interpret results
    • 

  • How to navigate AST node structures
    • 

  • How to handle parse errors gracefully
    • 

  • How to use tokenize_sql() for low-level analysis
    • 



Examples Covered


    

  1. DML Statements: SELECT, INSERT, UPDATE, DELETE
    2. 

  2. DDL Statements: CREATE TABLE with constraints
    3. 

  3. Advanced Features: CTEs (WITH clause), compound queries (UNION), window functions
    4. 

  4. Transaction Control: BEGIN, SAVEPOINT, COMMIT, ROLLBACK
    5. 

  5. Error Handling: How to catch and report parse errors
    6. 

  6. Tokenization: Low-level token inspection
    7. 



Each example function demonstrates a specific SQL feature and shows how to access
the relevant parts of the AST.
24


25"""


26Basic Usage Examples for SQLite SQL Parser


27


28Demonstrates how to use the parser for various SQL statements.


29"""


30


31import sys


32sys.path.insert(0, '..')


33


34from sqlite_parser import parse_sql, tokenize_sql


35from sqlite_parser.errors import ParseError


36


SELECT Statement Parsing


The SELECT statement is the most complex SQL statement type, supporting:
- Column selection with expressions and aliases
- Table joins (INNER, LEFT, RIGHT, CROSS, NATURAL)
- WHERE clauses for filtering
- GROUP BY and HAVING for aggregation
- ORDER BY for sorting
- LIMIT and OFFSET for pagination


The parser returns a SelectStatement node with:
- select_core: Contains columns, FROM, WHERE, GROUP BY
- order_by: ORDER BY clause
- limit: LIMIT expression
- offset: OFFSET expression
52


53def example_select():


54    """Parse a SELECT statement"""


55    print("=" * 60)


56    print("SELECT Statement Example")


57    print("=" * 60)


58


59    sql = """


60    SELECT u.id, u.name, COUNT(o.id) as order_count


61    FROM users u


62    LEFT JOIN orders o ON u.id = o.user_id


63    WHERE u.age > 18


64    GROUP BY u.id, u.name


65    HAVING COUNT(o.id) > 0


66    ORDER BY order_count DESC


67    LIMIT 10


68    """


69


70    ast = parse_sql(sql)


71    print(f"Parsed {len(ast)} statement(s)")


72    print(f"\nStatement type: {type(ast[0]).__name__}")


73    print(f"Has WHERE clause: {ast[0].select_core.where is not None}")


74    print(f"Has GROUP BY: {ast[0].select_core.group_by is not None}")


75    print(f"Has ORDER BY: {ast[0].order_by is not None}")


76    print(f"Has LIMIT: {ast[0].limit is not None}")


77


78


79def example_insert():


80    """Parse an INSERT statement"""


81    print("\n" + "=" * 60)


82    print("INSERT Statement Example")


83    print("=" * 60)


84


85    sql = "INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25)"


86


87    ast = parse_sql(sql)


88    stmt = ast[0]


89


90    print(f"Statement type: {type(stmt).__name__}")


91    print(f"Table: {stmt.table.parts}")


92    print(f"Columns: {stmt.columns}")


93    print(f"Number of rows: {len(stmt.values.rows) if stmt.values else 0}")


94


95


96def example_update():


97    """Parse an UPDATE statement"""


98    print("\n" + "=" * 60)


99    print("UPDATE Statement Example")


100    print("=" * 60)


101


102    sql = "UPDATE users SET age = age + 1, updated_at = CURRENT_TIMESTAMP WHERE id = 42"


103


104    ast = parse_sql(sql)


105    stmt = ast[0]


106


107    print(f"Statement type: {type(stmt).__name__}")


108    print(f"Table: {stmt.table.parts}")


109    print(f"Number of assignments: {len(stmt.assignments)}")


110    print(f"Has WHERE clause: {stmt.where is not None}")


111


112


113def example_delete():


114    """Parse a DELETE statement"""


115    print("\n" + "=" * 60)


116    print("DELETE Statement Example")


117    print("=" * 60)


118


119    sql = "DELETE FROM users WHERE created_at < date('now', '-1 year')"


120


121    ast = parse_sql(sql)


122    stmt = ast[0]


123


124    print(f"Statement type: {type(stmt).__name__}")


125    print(f"Table: {stmt.table.parts}")


126    print(f"Has WHERE clause: {stmt.where is not None}")


127


128


129def example_create_table():


130    """Parse a CREATE TABLE statement"""


131    print("\n" + "=" * 60)


132    print("CREATE TABLE Statement Example")


133    print("=" * 60)


134


135    sql = """


136    CREATE TABLE IF NOT EXISTS products (


137        id INTEGER PRIMARY KEY AUTOINCREMENT,


138        name TEXT NOT NULL,


139        price REAL CHECK(price > 0),


140        created_at TEXT DEFAULT CURRENT_TIMESTAMP,


141        UNIQUE(name)


142    )


143    """


144


145    ast = parse_sql(sql)


146    stmt = ast[0]


147


148    print(f"Statement type: {type(stmt).__name__}")


149    print(f"Table: {stmt.table_name.parts}")


150    print(f"IF NOT EXISTS: {stmt.if_not_exists}")


151    print(f"Number of columns: {len(stmt.columns)}")


152    print(f"Number of table constraints: {len(stmt.constraints)}")


153


154    for col in stmt.columns:


155        print(f"  Column: {col.name} {col.type_name or '(no type)'}")


156


157


Common Table Expressions (CTEs)


WITH clauses (CTEs) allow defining temporary named result sets that can be referenced
in the main query. They're especially powerful with the RECURSIVE keyword for hierarchical
or iterative queries.


Recursive CTE Pattern


    

  1. Anchor member: Initial SELECT (e.g., SELECT 1)
    2. 

  2. UNION ALL: Combines anchor with recursive member
    3. 

  3. Recursive member: SELECT that references the CTE itself
    4. 

  4. Termination: WHERE clause stops recursion
    5. 



The AST provides:
- with_clause.recursive: Boolean indicating if RECURSIVE keyword present
- with_clause.ctes: List of CTE definitions
- Each CTE has name, optional columns, and select query
175


176def example_with_cte():


177    """Parse a WITH clause (CTE)"""


178    print("\n" + "=" * 60)


179    print("WITH Clause (CTE) Example")


180    print("=" * 60)


181


182    sql = """


183    WITH RECURSIVE cnt(x) AS (


184        SELECT 1


185        UNION ALL


186        SELECT x+1 FROM cnt WHERE x < 10


187    )


188    SELECT x FROM cnt


189    """


190


191    ast = parse_sql(sql)


192    stmt = ast[0]


193


194    print(f"Statement type: {type(stmt).__name__}")


195    print(f"Has WITH clause: {stmt.with_clause is not None}")


196    print(f"Recursive: {stmt.with_clause.recursive}")


197    print(f"Number of CTEs: {len(stmt.with_clause.ctes)}")


198


199


200def example_compound_select():


201    """Parse a compound SELECT with UNION"""


202    print("\n" + "=" * 60)


203    print("Compound SELECT (UNION) Example")


204    print("=" * 60)


205


206    sql = """


207    SELECT name FROM employees


208    UNION


209    SELECT name FROM contractors


210    ORDER BY name


211    """


212


213    ast = parse_sql(sql)


214    stmt = ast[0]


215


216    print(f"Statement type: {type(stmt).__name__}")


217    print(f"Number of compound parts: {len(stmt.compound_selects)}")


218    if stmt.compound_selects:


219        print(f"Compound operator: {stmt.compound_selects[0][0].value}")


220


221


222def example_transactions():


223    """Parse transaction control statements"""


224    print("\n" + "=" * 60)


225    print("Transaction Control Example")


226    print("=" * 60)


227


228    sqls = [


229        "BEGIN TRANSACTION",


230        "SAVEPOINT sp1",


231        "UPDATE users SET balance = balance - 100 WHERE id = 1",


232        "UPDATE users SET balance = balance + 100 WHERE id = 2",


233        "RELEASE SAVEPOINT sp1",


234        "COMMIT"


235    ]


236


237    for sql in sqls:


238        ast = parse_sql(sql)


239        print(f"{sql:60} -> {type(ast[0]).__name__}")


240


241


Error Handling


The parser raises typed exceptions that provide detailed context about parsing failures.
All parser errors inherit from ParseError, making them easy to catch.


Error Information


Parse errors include:
- Message: Human-readable description of the problem
- Position: Line and column numbers where the error occurred
- Context: The problematic line with a caret (^) pointing to the error


Best Practices


    

  • Catch ParseError for all parser failures
    • 

  • Display the error message to users (it includes context)
    • 

  • Use try-except when parsing user-provided SQL
    • 

  • Log errors for debugging and monitoring
    • 

260


261def example_error_handling():


262    """Demonstrate error handling"""


263    print("\n" + "=" * 60)


264    print("Error Handling Example")


265    print("=" * 60)


266


267    invalid_sql = "SELECT FROM"  # Missing column list


268


269    try:


270        ast = parse_sql(invalid_sql)


271    except ParseError as e:


272        print(f"Caught parse error:")


273        print(f"  {e}")


274


Low-Level Tokenization


The tokenize_sql() function provides access to tokens without building an AST.
This is useful for:
- Syntax highlighting
- Building custom parsers
- Debugging lexer issues
- Token-based analysis (counting keywords, identifying patterns)


Each token contains:
- type: TokenType enum value (e.g., SELECT, IDENTIFIER, NUMBER)
- value: The actual text (e.g., "SELECT", "users", "42")
- position: Line and column where the token starts
288


289def example_tokenization():


290    """Show tokenization"""


291    print("\n" + "=" * 60)


292    print("Tokenization Example")


293    print("=" * 60)


294


295    sql = "SELECT * FROM users WHERE age > 18"


296


297    tokens = tokenize_sql(sql)


298    print(f"Tokenized into {len(tokens)} tokens:\n")


299


300    for token in tokens[:10]:  # Show first 10 tokens


301        print(f"  {token.type.name:20} {repr(token.value):20} at {token.position}")


302


303


304def example_complex_query():


305    """Parse a complex real-world query"""


306    print("\n" + "=" * 60)


307    print("Complex Query Example")


308    print("=" * 60)


309


310    sql = """


311    WITH monthly_sales AS (


312        SELECT


313            strftime('%Y-%m', order_date) as month,


314            product_id,


315            SUM(quantity) as total_quantity,


316            SUM(price * quantity) as total_revenue


317        FROM orders


318        WHERE order_date >= date('now', '-12 months')


319        GROUP BY month, product_id


320    )


321    SELECT


322        p.name as product_name,


323        ms.month,


324        ms.total_quantity,


325        ms.total_revenue,


326        ROUND(ms.total_revenue / ms.total_quantity, 2) as avg_price,


327        LAG(ms.total_revenue) OVER (


328            PARTITION BY ms.product_id


329            ORDER BY ms.month


330        ) as prev_month_revenue


331    FROM monthly_sales ms


332    JOIN products p ON ms.product_id = p.id


333    WHERE ms.total_quantity > 10


334    ORDER BY ms.month DESC, ms.total_revenue DESC


335    LIMIT 100


336    """


337


338    ast = parse_sql(sql)


339    stmt = ast[0]


340


341    print(f"Successfully parsed complex query!")


342    print(f"  Has CTE: {stmt.with_clause is not None}")


343    print(f"  Number of result columns: {len(stmt.select_core.columns)}")


344    print(f"  Has window functions: (check for OVER clauses)")


345    print(f"  Has joins: (check FROM clause)")


346


347


348def main():


349    """Run all examples"""


350    print("\n" + "#" * 60)


351    print("# SQLite SQL Parser - Usage Examples")


352    print("#" * 60)


353


354    example_select()


355    example_insert()


356    example_update()


357    example_delete()


358    example_create_table()


359    example_with_cte()


360    example_compound_select()


361    example_transactions()


362    example_complex_query()


363    example_tokenization()


364    example_error_handling()


365


366    print("\n" + "#" * 60)


367    print("# All examples completed successfully!")


368    print("#" * 60)


369


370


371if __name__ == "__main__":


372    main()


373