Metadata-Version: 2.4
Name: pysqlast
Version: 0.1.0
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Rust
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
License-File: LICENSE
Summary: Fast, friendly Python SQL AST powered by sqlparser-rs
Keywords: sql,ast,parser,sqlparser,mcp
Author: ssskip
License: Apache-2.0
Requires-Python: >=3.8
Description-Content-Type: text/markdown; charset=UTF-8; variant=GFM
Project-URL: Homepage, https://github.com/ssskip/pysqlast
Project-URL: Issues, https://github.com/ssskip/pysqlast/issues

# pysqlast

> Fast, friendly Python SQL AST — powered by [`sqlparser-rs`](https://github.com/apache/datafusion-sqlparser-rs).

`pysqlast` is a thin, ergonomic Python binding around the battle-tested
Rust `sqlparser` crate used by Apache DataFusion. It is designed for
**read-only SQL tooling** — linters, query analyzers, lineage extractors,
and especially **MCP tools** that need to safely inspect SQL without
executing it.

- Parses **13 SQL dialects** (Postgres, MySQL, SQLite, Snowflake,
  BigQuery, Redshift, MsSQL, Hive, ClickHouse, DuckDB, Databricks,
  ANSI, Generic).
- Returns a plain **dict/list AST** — no custom classes to learn,
  serialize directly to JSON.
- High-level helpers for the 90% case: `tables()`, `columns()`,
  `statement_type()`, `is_read_only()`.
- **Rust speed**, Python ergonomics. abi3 wheels for Python ≥ 3.8.

## Install

```bash
uv add pysqlast
```

```bash
pip install pysqlast
```

Pre-built wheels are published for Linux (x86_64, aarch64), macOS
(x86_64, aarch64), and Windows (x64). No Rust toolchain required to
install.

## Usage

### Parse a statement

```python
import pysqlast

ast = pysqlast.parse_one(
    "SELECT u.id, u.name FROM users u WHERE u.active",
    dialect="postgres",
)
# ast is a plain dict mirroring the sqlparser AST:
# {'Query': {'body': {'Select': {...}}, ...}}
```

`parse(sql)` returns a **list** of statements; `parse_one(sql)` insists
on exactly one statement and returns it directly.

### Extract tables and columns

```python
sql = """
  SELECT u.id, o.total
  FROM analytics.users u
  JOIN orders o ON o.uid = u.id
  WHERE u.created_at > now() - interval '7 days'
"""

pysqlast.tables(sql, dialect="postgres")
# ['analytics.users', 'orders']

pysqlast.columns(sql, dialect="postgres")
# ['u.id', 'o.total', 'o.uid', 'u.created_at']
```

### Classify and guard read-only

Great for MCP tools that should never let an LLM execute a mutation:

```python
pysqlast.statement_type("SELECT 1; UPDATE t SET x=1")
# ['SELECT', 'UPDATE']

pysqlast.is_read_only("SELECT * FROM users")          # True
pysqlast.is_read_only("EXPLAIN SELECT * FROM users")  # True
pysqlast.is_read_only("DELETE FROM users")            # False

def guard(sql: str) -> None:
    if not pysqlast.is_read_only(sql, dialect="postgres"):
        raise PermissionError("only read-only SQL is allowed here")
```

#### Safety model

`is_read_only` is **deny-by-default**. The top-level statement must be
one of an explicit allowlist — `Query` (SELECT/WITH/UNION), `Explain`,
`ExplainTable`, or `Show*`. Anything else is refused, including:

- DML/DDL: `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `TRUNCATE`,
  `CREATE *`, `ALTER *`, `DROP *`, `GRANT`, `REVOKE`.
- Bulk data movement: Redshift `UNLOAD` / `COPY` / `VACUUM` /
  `ANALYZE`, Snowflake `COPY INTO` / `PUT` / `REMOVE`, MySQL
  `LOAD DATA` / `FLUSH`, MSSQL `BACKUP` / `RESTORE`, ClickHouse
  `OPTIMIZE`.
- Session / runtime state: `SET`, `USE`, `BEGIN`, `COMMIT`,
  `ROLLBACK`, `LOCK`, `REINDEX`, `CLUSTER`, Postgres `LISTEN`/`NOTIFY`,
  DuckDB `ATTACH`/`INSTALL`/`LOAD`.
- Indirect execution: `PREPARE`, `EXECUTE`, `CALL`, `DEALLOCATE` —
  these run code we can't statically inspect, so we treat them as
  unsafe.

For statements that *are* in the allowlist, the AST is then walked to
catch data-modifying CTEs (the only way a write can hide inside a
SELECT):

```sql
-- Refused: nested INSERT inside a CTE.
WITH ins AS (INSERT INTO t VALUES (1) RETURNING *) SELECT * FROM ins;
```

Multi-statement input is read-only only if **every** statement is.
Parse failures raise `ValueError` — an MCP guard should treat that as
refusal as well.

**Limitations.** Static SQL analysis can't see what user-defined
functions or stored procedures do; `SELECT my_udf_that_writes()` will
be classified as read-only because the parser sees only a function
call. If your environment exposes such functions, deny them at a layer
below this one (e.g. role-based DB permissions).

### Walk the AST

```python
ast = pysqlast.parse_one("SELECT a, b FROM t WHERE a > 1")

# Every literal in the tree:
literals = pysqlast.find_all(
    ast, lambda n: isinstance(n, dict) and "Value" in n
)
```

`pysqlast.walk(node)` yields every nested node depth-first.
`pysqlast.find(node, pred)` returns the first match;
`pysqlast.find_all(node, pred)` returns every match.

### Dialects

Pass any of these strings as `dialect=`:

```
generic  ansi  postgres  mysql  sqlite  mssql  snowflake
bigquery redshift hive clickhouse duckdb databricks
```

Common aliases work too: `pg`, `postgresql`, `my`, `ms`, `sqlserver`,
`tsql`, `bq`, `gbq`, `sf`, `rs`, `ch`, `duck`, `standard`. Dialect
strings are case-insensitive. See `pysqlast.supported_dialects()`.

## Performance

`pysqlast` calls into the same Rust parser used by Apache DataFusion. The
hot path is fully in Rust; Python overhead is limited to one
serialization of the AST to a Python `dict` per call (via `pythonize`).

Measured on a realistic query (CTE + 3 joins + WHERE/ORDER BY/LIMIT),
Apple M-series, Python 3.14, Postgres dialect:

| Operation                             | Time / call |
| ------------------------------------- | ----------- |
| `tables()`                            | ~27 µs      |
| `columns()`                           | ~28 µs      |
| `statement_type()` / `is_read_only()` | ~26 µs      |
| `parse_one()` (full dict AST)         | ~46 µs      |

That's roughly **20k–35k SQL statements per second per core** on a
single-threaded Python loop. Bigger queries scale roughly linearly with
their length.

Notes:

- `tables`, `columns`, `statement_type`, `is_read_only` **do not
  materialize the Python AST** — they walk the Rust AST directly and
  return only primitives. Prefer these when you don't need the full tree.
- If you call `parse()` and then walk it repeatedly in Python, cache the
  result; the dict tree is the expensive part, not the parse itself.
- The parser is allocation-heavy but single-threaded; the GIL is held
  for the duration of each call, so concurrency wins come from
  multiprocessing, not threads.

## Why dicts instead of typed classes?

The Rust AST has hundreds of variants and evolves with each
`sqlparser` release. A typed Python mirror would either be a massive
hand-written wrapper (brittle) or auto-generated (still brittle, and
hard to keep ergonomic). Returning the serde JSON shape keeps the
binding small, lets you `json.dumps(ast)` for free, and pairs naturally
with the `walk` / `find` helpers when you do need to look inside.

If you want types, the shape is documented by the
[`sqlparser` Rust source](https://docs.rs/sqlparser/latest/sqlparser/ast/index.html);
each Python dict key matches a Rust enum variant or struct field.

## Development

Requires a Rust toolchain and Python ≥ 3.8.

```bash
pip install maturin pytest
maturin develop --release
pytest
```

To build wheels locally:

```bash
maturin build --release --strip
```

A GitHub Actions workflow (`.github/workflows/release.yml`) builds
manylinux / macOS / Windows wheels and publishes to PyPI via
[trusted publishing](https://docs.pypi.org/trusted-publishers/) when
you push a `v*` tag.

