sqldump2json 0.2.4

Parsing SQL Dumps to JSON Objects

sqldump2json

Parsing SQL dumps into JSON objects.

A tool for administrators, data scientists and hackers. With this tool you no longer need to import dumps into Databases. You can extract INSERT data as JSON and analyze them with jq. The dump is not read entirely into RAM, so this utility can be used to process files of any size. And it can even parse corrupted dumps.

Supported DBMS: MySQL, SQL Server, PotsgreSQL and some other (not all formats).

Installation for normal Arch-based Linux ditros:

# install pipx
yay -S python-pipx

# install package from pypi
pipx install sqldump2json

# install lastet version from github
pipx install git+https://github.com/s3rgeym/sqldump2json.git

For other shit like Ubuntu you need to do more steps:

• Install pyenv or asdf-vm.
• Install latest python version and make it global via pyenv or asdf-vm.
• Install sqldump2json.
• OR use Docker.

Insert statements are converted to JSON objects on each new line (JSONL):

$ sqldump2json -i tests/dump.sql
{"table_name": "actor", "values": [1, "PENELOPE", "GUINESS", "2006-02-15 04:34:33"]}
{"table_name": "actor", "values": [2, "NICK", "WAHLBERG", "2006-02-15 04:34:33"]}
{"table_name": "actor", "values": [3, "ED", "CHASE", "2006-02-15 04:34:33"]}
...

Filter and convert to TSV:

$ sqldump2json -i tests/dump.sql | jq -r 'select(.table_name == "actor").values | @tsv'
1       PENELOPE        GUINESS 2006-02-15 04:34:33
2       NICK    WAHLBERG        2006-02-15 04:34:33
3       ED      CHASE   2006-02-15 04:34:33
...

Hex strings are converted to base64:

sqldump2json -i tests/dump.sql | tail -4 | head -1 | jq -r '.values[4]' | base64 -d > image.png

Values converted to dict only if INSERT contains list of fields:

INSERT INTO users (id, name) VALUES (42, 'Vasyan');

Output:

{"table_name": "users", "values": {"id": 42, "name": "Vasyan"}}

Supports basic arifmetic and boolean operations:

$ echo 'insert into test (result) values (-2 + 2 * 2);' | sqldump2json
{"table_name": "test", "values": {"result": 2}}