tessl/pypi-sqlglot

A comprehensive SQL parser, transpiler, optimizer, and engine supporting 30+ dialects

Overview

Eval results

Files

Expression Building and AST Manipulation

Name: tessl/pypi-sqlglot
Author: tessl

Programmatic SQL construction using builder functions and direct AST manipulation. Build complex SQL queries through code, modify existing parsed queries, and traverse expression trees for analysis and transformation.

Capabilities

Core Expression Builders

Essential functions for constructing SQL expressions programmatically.

def select(*expressions) -> Select:
    """
    Creates a SELECT expression with the specified columns/expressions.
    
    Args:
        *expressions: Column names, expressions, or Expression objects to select
        
    Returns:
        Select: A SELECT expression object
    """

def column(col: str) -> Column:
    """
    Creates a column reference expression.
    
    Args:
        col (str): Column name, optionally qualified (e.g., "table.column")
        
    Returns:
        Column: Column reference expression
    """

def table_(name: str) -> Table:
    """
    Creates a table reference expression.
    
    Args:
        name (str): Table name, optionally qualified (e.g., "schema.table")
        
    Returns:
        Table: Table reference expression
    """

def func(name: str, *args) -> Function:
    """
    Creates a function call expression.
    
    Args:
        name (str): Function name (e.g., "COUNT", "SUM", "MAX")
        *args: Function arguments as strings or Expression objects
        
    Returns:
        Function: Function call expression
    """

Logical Operators

Build complex logical conditions and boolean expressions.

def and_(*conditions) -> And:
    """
    Creates an AND logical expression combining multiple conditions.
    
    Args:
        *conditions: Boolean expressions to combine with AND
        
    Returns:
        And: AND logical expression
    """

def or_(*conditions) -> Or:
    """
    Creates an OR logical expression combining multiple conditions.
    
    Args:
        *conditions: Boolean expressions to combine with OR
        
    Returns:
        Or: OR logical expression
    """

def not_(condition) -> Not:
    """
    Creates a NOT logical expression negating a condition.
    
    Args:
        condition: Boolean expression to negate
        
    Returns:
        Not: NOT logical expression
    """

Advanced Expression Builders

More complex SQL constructs for sophisticated query building.

def case() -> Case:
    """
    Creates a CASE expression for conditional logic.
    
    Returns:
        Case: CASE expression object with .when() and .else_() methods
    """

def cast(expression, to: str) -> Cast:
    """
    Creates a CAST expression for type conversion.
    
    Args:
        expression: Expression or value to cast
        to (str): Target data type
        
    Returns:
        Cast: CAST expression
    """

def alias(expression, alias: str) -> Alias:
    """
    Creates an alias expression (AS clause).
    
    Args:
        expression: Expression to alias
        alias (str): Alias name
        
    Returns:
        Alias: Alias expression
    """

def subquery(query: Expression) -> Select:
    """
    Creates a subquery expression wrapped in parentheses.
    
    Args:
        query (Expression): SELECT or other query expression
        
    Returns:
        Select: Subquery expression
    """

Statement Builders

Build complete SQL statements beyond SELECT queries.

def insert() -> Insert:
    """
    Creates an INSERT statement expression.
    
    Returns:
        Insert: INSERT statement with .into() and .values() methods
    """

def delete() -> Delete:
    """
    Creates a DELETE statement expression.
    
    Returns:
        Delete: DELETE statement with .from_() and .where() methods
    """

def merge() -> Merge:
    """
    Creates a MERGE statement expression for upsert operations.
    
    Returns:
        Merge: MERGE statement expression
    """

Set Operations

Combine query results using set operations.

def union(*queries) -> Union:
    """
    Creates a UNION expression combining multiple queries.
    
    Args:
        *queries: SELECT expressions to union
        
    Returns:
        Union: UNION expression
    """

def intersect(*queries) -> Intersect:
    """
    Creates an INTERSECT expression.
    
    Args:
        *queries: SELECT expressions to intersect
        
    Returns:
        Intersect: INTERSECT expression
    """

def except_(*queries) -> Except:
    """
    Creates an EXCEPT expression.
    
    Args:
        *queries: SELECT expressions for set difference
        
    Returns:
        Except: EXCEPT expression
    """

Utility Functions

Helper functions for common expression operations.

def condition(expression) -> Expression:
    """
    Converts an expression into a boolean condition.
    
    Args:
        expression: Expression to convert to condition
        
    Returns:
        Expression: Boolean condition expression
    """

def maybe_parse(sql: str | Expression, **opts) -> Expression:
    """
    Parses SQL string or returns Expression if already parsed.
    
    Args:
        sql: SQL string or Expression object
        **opts: Parse options if parsing needed
        
    Returns:
        Expression: Parsed or existing expression
    """

def to_column(col: str | Expression) -> Column:
    """
    Converts string or expression to Column reference.
    
    Args:
        col: Column name or expression
        
    Returns:
        Column: Column reference expression
    """

def to_table(table: str | Expression) -> Table:
    """
    Converts string or expression to Table reference.
    
    Args:
        table: Table name or expression
        
    Returns:
        Table: Table reference expression
    """

def to_identifier(name: str | Expression) -> Identifier:
    """
    Converts string to Identifier expression.
    
    Args:
        name: Identifier name
        
    Returns:
        Identifier: Identifier expression
    """

def from_(*tables) -> From:
    """
    Creates a FROM clause expression.
    
    Args:
        *tables: Table names or expressions
        
    Returns:
        From: FROM clause expression
    """

def update() -> Update:
    """
    Creates an UPDATE statement expression.
    
    Returns:
        Update: UPDATE statement with .set() and .where() methods
    """

Usage Examples

Building Complex Queries

import sqlglot
from sqlglot import select, column, table_, func, and_, or_, case

# Build a complex analytical query
query = (
    select(
        column("user_id"),
        func("COUNT", "*").as_("total_orders"),
        func("SUM", column("amount")).as_("total_spent"),
        case()
            .when(func("SUM", column("amount")) > 1000, "VIP")
            .when(func("SUM", column("amount")) > 500, "Premium")
            .else_("Standard")
            .as_("customer_tier")
    )
    .from_(table_("orders"))
    .join(table_("users"), on=column("orders.user_id") == column("users.id"))
    .where(
        and_(
            column("order_date") >= "2023-01-01",
            column("status") == "completed"
        )
    )
    .group_by(column("user_id"))
    .having(func("COUNT", "*") > 5)
    .order_by(func("SUM", column("amount")).desc())
)

print(query.sql())

Modifying Existing Queries

import sqlglot

# Parse existing query
original = sqlglot.parse_one("SELECT name, age FROM users WHERE age > 25")

# Add additional columns
original = original.select("email", "phone", append=True)

# Modify WHERE condition  
original = original.where(
    and_(
        column("age") > 25,
        column("status") == "active"
    ),
    copy=False
)

# Add ORDER BY
original = original.order_by("name")

print(original.sql())

Working with Expressions

import sqlglot
from sqlglot import column, func, and_, or_

# Build complex conditions
condition1 = column("age") > 18
condition2 = column("status") == "active"
condition3 = column("last_login") > "2023-01-01"

# Combine conditions
complex_condition = and_(
    condition1,
    or_(condition2, condition3)
)

# Use in query
query = (
    sqlglot.select("*")
    .from_("users")
    .where(complex_condition)
)

print(query.sql())
# SELECT * FROM users WHERE age > 18 AND (status = 'active' OR last_login > '2023-01-01')

Function Calls and Aggregations

import sqlglot
from sqlglot import select, func, column

# Common aggregation functions
query = select(
    func("COUNT", "*").as_("total_rows"),
    func("AVG", column("price")).as_("avg_price"),
    func("MAX", column("created_date")).as_("latest_date"),
    func("STRING_AGG", column("name"), ", ").as_("all_names")
).from_("products")

# Window functions (using raw SQL for now)
windowed_query = sqlglot.parse_one("""
    SELECT 
        name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
    FROM employees
""")

print(query.sql())
print(windowed_query.sql())

Types

class Expression:
    """Base class for all SQL expressions with manipulation methods."""
    
    def sql(self, dialect: str = None, **opts) -> str:
        """Generate SQL string from expression."""
    
    def transform(self, fun: Callable, **opts) -> Expression:
        """Apply transformation function to expression tree."""
    
    def find(self, expression_type: Type, **opts) -> Optional[Expression]:
        """Find first occurrence of expression type in tree."""
    
    def find_all(self, expression_type: Type, **opts) -> List[Expression]:
        """Find all occurrences of expression type in tree."""
    
    def replace(self, expression: Expression, **opts) -> Expression:
        """Replace this expression with another."""

class Select(Expression):
    """SELECT statement expression with builder methods."""
    
    def select(self, *expressions, append: bool = False) -> Select:
        """Add columns to SELECT clause."""
    
    def from_(self, *tables) -> Select:
        """Set FROM clause."""
    
    def where(self, condition, copy: bool = True) -> Select:
        """Add WHERE condition."""
    
    def group_by(self, *expressions) -> Select:
        """Add GROUP BY clause."""
    
    def having(self, condition) -> Select:
        """Add HAVING condition."""
    
    def order_by(self, *expressions) -> Select:
        """Add ORDER BY clause."""
    
    def limit(self, count: int) -> Select:
        """Add LIMIT clause."""
    
    def join(self, table, on: Expression = None, join_type: str = "INNER") -> Select:
        """Add JOIN clause."""

class Column(Expression):
    """Column reference expression."""
    
    def __eq__(self, other) -> Binary:
        """Create equality comparison."""
    
    def __gt__(self, other) -> Binary:
        """Create greater than comparison."""
    
    def __lt__(self, other) -> Binary:
        """Create less than comparison."""
    
    def desc(self) -> Ordered:
        """Create descending order expression."""
    
    def asc(self) -> Ordered:
        """Create ascending order expression."""

class Function(Expression):
    """Function call expression."""
    
    def as_(self, alias: str) -> Alias:
        """Create alias for function result."""

class Case(Expression):
    """CASE expression for conditional logic."""
    
    def when(self, condition, then_value) -> Case:
        """Add WHEN clause to CASE expression."""
    
    def else_(self, value) -> Case:
        """Add ELSE clause to CASE expression."""

Install with Tessl CLI