Welcome to the fascinating world of SQLOXIDE, where Rust’s speed converges with Python’s simplicity for parsing SQL! In this article, we will explore how to install and use SQLOXIDE to parse SQL queries effortlessly. Buckle up as we dive deeper into its functionalities!

What is SQLOXIDE?

SQLOXIDE wraps the Rust bindings for sqlparser-rs into a Python package using PyO3. It was designed with the goal of providing a rapid, efficient, and accurate SQL parser ideal for constructing data lineage graphs across extensive code bases. Old SQL parsing methods in Python often fall short—especially when dealing with complex nested queries. But SQLOXIDE is here to change the game!

Installation

Getting started with SQLOXIDE is a breeze, thanks to its availability on PyPI. To ensure compatibility across various systems, manylinux2014 wheels are provided along with native wheels for macOS and Windows.

Follow these steps to install:

• Open your terminal
• Run the following command:

pip install sqloxide

Usage

Using SQLOXIDE is as straightforward as a single command line! Let’s dive into parsing and AST rewrites to illustrate its capabilities.

Parsing

Parsing a SQL query is quite simple:

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)

Upon printing the output, you’ll find a structured representation resembling a JSON document, transitioning into a typed Abstract Syntax Tree (AST) per the sqlparser-rs AST schema.

Restoring the SQL Query

After parsing, you might want to convert back from the AST to a SQL query:

from sqloxide import restore_ast

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

This functionality allows modifications to the AST in Python seamlessly.

AST Rewrites

You can edit the AST more systematically using visitor patterns! Here’s how you can transform expressions into uppercase:

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) # Output: [SELECT SOMETHING FROM SOMEWHERE WHERE SOMETHING = 1 AND SOMETHING_ELSE = 2]

And, for a structured edit on the table name, you can easily modify it using:

from sqloxide import parse_sql, mutate_relations

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

result = mutate_relations(parsed_query=ast, func=func)
print(result) # Output: [SELECT SOMETHING FROM ANYWHERE WHERE SOMETHING = 1 AND SOMETHING_ELSE = 2]

Benchmarks

To assert SQLOXIDE’s prowess, benchmark tests against various Python SQL parsing libraries reveal its exceptional performance:

poetry run pytest tests/benchmark.py

Example Usage

For an example that reads SQL files and builds a dependency graph, use:

poetry run python .examples/depgraph.py --path pathtofolderwithqueries

Troubleshooting

If you encounter issues during installation, ensure that your Python version is compatible and that you have pip and poetry up to date. Also, check for required system binaries if using native wheels.

For further assistance, please explore the project repository for FAQs or open issues, and remember, for more insights, updates, or to collaborate on AI development projects, stay connected with fxis.ai.

Developing with SQLOXIDE

For those looking to contribute, follow these guidelines:

1. Install rustup.
2. Run poetry install to automatically create a virtual environment and install all required packages.

Final Thoughts

At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.

Happy Parsing!