Sqloxide

https://github.com/wseaton/sqloxide
Admin
Sqloxide is a fast and accurate SQL parser for Python, built with Rust's `sqlparser-rs` via `pyO3`,...
Tokens:2,442
Snippets:13
Trust Score:8.5
License:MIT
Update:1 year ago
Show doc for...
Tokens:
Raw
### Install sqloxide Package

Source: https://github.com/wseaton/sqloxide/blob/main/readme.md

Installs the sqloxide Python package using pip. This package provides pre-compiled wheels for common operating systems, ensuring compatibility.

```shell
pip install sqloxide
```

--------------------------------

### Run sqloxide Dependency Graph Example

Source: https://github.com/wseaton/sqloxide/blob/main/readme.md

This command executes the depgraph example, which reads SQL files from a specified path and builds a dependency graph using graphviz. It demonstrates a practical application of sqloxide.

```shell
poetry run python ./examples/depgraph.py --path {path/to/folder/with/queries}
```

--------------------------------

### Mutate Expressions in SQL AST

Source: https://github.com/wseaton/sqloxide/blob/main/readme.md

Applies a user-defined function to mutate expressions within a parsed SQL query's Abstract Syntax Tree (AST). This example demonstrates converting identifiers to uppercase.

```python
from sqloxide import parse_sql, mutate_expressions

sql = "SELECT something from somewhere where something = 1 and something_else = 2"

def func(x):
    if "CompoundIdentifier" in x.keys():
        for y in x["CompoundIdentifier"]:
            y["value"] = y["value"].upper()
    return x

ast = parse_sql(sql=sql, dialect="ansi")
result = mutate_expressions(parsed_query=ast, func=func)
print(result)
```

--------------------------------

### Mutate Relations in SQL AST

Source: https://github.com/wseaton/sqloxide/blob/main/readme.md

Applies a user-defined function to mutate relation (table) names within a parsed SQL query's Abstract Syntax Tree (AST). This example demonstrates replacing a table name.

```python
from sqloxide import parse_sql, mutate_relations

# Assuming 'ast' contains the parsed query from parse_sql
# ast = parse_sql(...)

def func(x):
    return x.replace("somewhere", "anywhere")

result = mutate_relations(parsed_query=ast, func=func)
print(result)
```

--------------------------------

### Run sqloxide Benchmarks

Source: https://github.com/wseaton/sqloxide/blob/main/readme.md

This command executes the benchmarks for sqloxide using pytest. The benchmarks compare sqloxide's parsing speed against other Python SQL parsing libraries.

```shell
poetry run pytest tests/benchmark.py
```

--------------------------------

### Transform Expressions Programmatically - sqloxide

Source: https://context7.com/wseaton/sqloxide/llms.txt

Applies a Python function to all expressions in a SQL query's Abstract Syntax Tree (AST) using sqloxide. This enables systematic transformations such as case conversion, column renaming, or expression rewriting. The function receives each expression as a Python dictionary and returns the modified expression dictionary. It returns the reconstructed SQL with all transformations applied. Requires the 'sqloxide' library.

```python
from sqloxide import parse_sql, mutate_expressions
sql = """
SELECT employee.first_name, employee.last_name, department.name
FROM employee
JOIN department ON employee.dept_id = department.id
WHERE employee.salary > 50000
"""

ast = parse_sql(sql=sql, dialect='ansi')

# Convert all column identifiers to uppercase
def uppercase_identifiers(expr: dict) -> dict:
    if 'CompoundIdentifier' in expr:
        for part in expr['CompoundIdentifier']:
            part['value'] = part['value'].upper()
    elif 'Identifier' in expr:
        expr['Identifier']['value'] = expr['Identifier']['value'].upper()
    return expr

result = mutate_expressions(parsed_query=ast, func=uppercase_identifiers)
print(result[0])

# Add table alias prefix to unqualified columns
def add_table_prefix(expr: dict) -> dict:
    if 'Identifier' in expr and expr['Identifier'].get('value') in ['salary', 'active']:
        # Convert single identifier to compound identifier with table prefix
        return {
            'CompoundIdentifier': [
                {'value': 'emp', 'quote_style': None},
                {'value': expr['Identifier']['value'], 'quote_style': None}
            ]
        }
    return expr

sql2 = "SELECT first_name, salary FROM employee WHERE active = true"
ast2 = parse_sql(sql=sql2, dialect='ansi')
result = mutate_expressions(parsed_query=ast2, func=add_table_prefix)
print(result[0])
```

--------------------------------

### Parse SQL to AST with sqloxide

Source: https://context7.com/wseaton/sqloxide/llms.txt

Parses SQL statement strings into a typed Abstract Syntax Tree (AST) using the specified SQL dialect. It returns a list of Python dictionaries representing parsed statements. Supports multiple SQL dialects. Handles potential parsing errors by raising ValueError.

```python
from sqloxide import parse_sql

sql = """
SELECT employee.first_name, employee.last_name,
       call.start_time, call.end_time, call_outcome.outcome_text
FROM employee
INNER JOIN call ON call.employee_id = employee.id
INNER JOIN call_outcome ON call.call_outcome_id = call_outcome.id
WHERE employee.department = 'Sales'
ORDER BY call.start_time ASC;
"""

try:
    ast = parse_sql(sql=sql, dialect='ansi')
    print(f"Parsed {len(ast)} statement(s)")

    # Access query components
    query = ast[0]['Query']
    print(f"Has WHERE clause: {'where_clause' in query['body']['Select']}")
    print(f"Number of projections: {len(query['body']['Select']['projection'])}")

    # Example output structure:
    # [{
    #   "Query": {
    #     "ctes": [],
    #     "body": {
    #       "Select": {
    #         "distinct": false,
    #         "projection": [...],
    #         "from": [...],
    #         "selection": {{}},
    #         ...
    #       }
    #     },
    #     "order_by": []
    #   }
    # }]

except ValueError as e:
    print(f"Parse error: {e}")
```

--------------------------------

### Parse SQL Query to AST

Source: https://github.com/wseaton/sqloxide/blob/main/readme.md

Parses a given SQL query string into a Pythonic Abstract Syntax Tree (AST) using a specified dialect. The output is a typed AST that mirrors the sqlparser-rs schema.

```python
from sqloxide import parse_sql

sql = """
SELECT employee.first_name, employee.last_name,
       call.start_time, call.end_time, call_outcome.outcome_text
FROM employee
INNER JOIN call ON call.employee_id = employee.id
INNER JOIN call_outcome ON call.call_outcome_id = call_outcome.id
ORDER BY call.start_time ASC;
"""

output = parse_sql(sql=sql, dialect='ansi')

print(output)
```

--------------------------------

### Reconstruct SQL from AST with sqloxide

Source: https://context7.com/wseaton/sqloxide/llms.txt

Converts a parsed Abstract Syntax Tree (AST) back into valid SQL query strings. This is useful after programmatically modifying the AST. It returns a list of SQL strings, corresponding to each statement in the input AST.

```python
from sqloxide import parse_sql, restore_ast

sql = """
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.active = true
"""

# Parse to AST
ast = parse_sql(sql=sql, dialect='ansi')

# Manually modify the AST
query = ast[0]['Query']
projection = query['body']['Select']['projection']

# Add a new column to the SELECT list
new_column = {
    "UnnamedExpr": {
        "CompoundIdentifier": [
            {"value": "employee", "quote_style": None},
            {"value": "email", "quote_style": None}
        ]
    }
}
projection.append(new_column)

# Reconstruct SQL from modified AST
reconstructed = restore_ast(ast=ast)
print(reconstructed[0])
# Output: SELECT employee.first_name, employee.last_name, employee.email FROM employee WHERE employee.active = true
```

--------------------------------

### Restore AST to SQL Query

Source: https://github.com/wseaton/sqloxide/blob/main/readme.md

Reconstructs a SQL query string from a given Abstract Syntax Tree (AST). This is useful for manual AST edits in Python before generating the final SQL.

```python
from sqloxide import restore_ast

# Assuming 'output' contains the AST from parse_sql
# output = parse_sql(...)

query = restore_ast(ast=output)
print(query)
```

--------------------------------

### Extract Expressions from SQL Queries - sqloxide

Source: https://context7.com/wseaton/sqloxide/llms.txt

Extracts all expressions from a parsed SQL query using the visitor pattern in sqloxide. It returns a list of all Expression AST nodes, including column references, literals, function calls, operators, and subqueries. This is useful for analyzing what data is accessed or computed. Requires the 'sqloxide' library.

```python
from sqloxide import parse_sql, extract_expressions
sql = """
SELECT
    employee.first_name || ' ' || employee.last_name AS full_name,
    salary * 1.1 AS increased_salary,
    CASE
        WHEN dept = 'Engineering' THEN 'Tech'
        ELSE 'Non-Tech'
    END AS category
FROM employee
WHERE hire_date > '2020-01-01' AND active = true
"""

ast = parse_sql(sql=sql, dialect='ansi')
expressions = extract_expressions(parsed_query=ast)

print(f"Found {len(expressions)} expressions")

# Examine specific expression types
for expr in expressions:
    if 'CompoundIdentifier' in expr:
        parts = [part['value'] for part in expr['CompoundIdentifier']]
        print(f"Column reference: {'.'.join(parts)}")
    elif 'BinaryOp' in expr:
        print(f"Binary operation: {expr['BinaryOp']['op']}")
    elif 'Value' in expr:
        print(f"Literal value: {expr['Value']}")
    elif 'Case' in expr:
        print("CASE expression found")
```

--------------------------------

### Extract Table Relations from SQL with sqloxide

Source: https://context7.com/wseaton/sqloxide/llms.txt

Extracts all table or relation references from parsed SQL statements using a visitor pattern. It returns a list containing lists of ObjectName structures for each query, useful for dependency analysis and lineage tracking.

```python
from sqloxide import parse_sql, extract_relations

sql = """
WITH active_employees AS (
    SELECT * FROM hr.employee WHERE active = true
)
SELECT e.name, d.department_name
FROM active_employees e
INNER JOIN hr.department d ON e.dept_id = d.id
INNER JOIN hr.location l ON d.location_id = l.id
"""

ast = parse_sql(sql=sql, dialect='ansi')
relations = extract_relations(parsed_query=ast)

# Each relation is a list of identifiers forming the qualified name
for relation in relations[0]:
    identifier = relation['Identifier']
    print(f"Table: {identifier['value']}")
    if identifier.get('span'):
        span = identifier['span']
        print(f"  Location: line {span['start']['line']}, col {span['start']['column']}")

# Expected output shows all table references:
# Table: employee (from CTE)
# Table: employee (from main query alias)
# Table: department
# Table: location
```

--------------------------------

### Transform Table Names with Python Function - sqloxide

Source: https://context7.com/wseaton/sqloxide/llms.txt

Applies a Python function to all table/relation names in a SQL query parsed by sqloxide. This allows for systematic renaming or transformation of table references. The function receives each table name component as a string and returns the transformed string. It returns the reconstructed SQL with all changes applied. Requires the 'sqloxide' library.

```python
from sqloxide import parse_sql, mutate_relations
sql = """
SELECT e.name, d.dept_name
FROM prod.employee e
INNER JOIN prod.department d ON e.dept_id = d.id
WHERE EXISTS (
    SELECT 1 FROM prod.salary s WHERE s.emp_id = e.id
)
"""

ast = parse_sql(sql=sql, dialect='ansi')

# Transform all table names from prod to dev schema
def change_schema(table_name: str) -> str:
    return table_name.replace('prod', 'dev')

result = mutate_relations(parsed_query=ast, func=change_schema)
print(result[0])

# Add prefix to all tables
def add_prefix(table_name: str) -> str:
    return f"archive_{table_name}"

result = mutate_relations(parsed_query=ast, func=add_prefix)
print(result[0])
```
Tokens:
Raw