Skip to main content

Simple and easy-to-use sql execution engine

Project description

Syncany-SQL

Tests GitHub Repo stars

简单易用的SQL执行引擎。

  • 可在本地运行MySQL语法结构的SQL
  • 支持查询常用mysql、mongodb、postgresql、sqlserver、elasticsearch、influxdb、clickhouse、sqlite数据库及execl、csv、json和普通文本文件
  • 支持本地临时数据表逻辑做中间结果保存
  • 数据库数据加载使用简单条件过滤及IN条件查询
  • 因由本地完成Join匹配所以支持不同库表、不同主机及不同类型数据库间Join关联查询
  • Group By分组聚合计算及Order By排序也由本地执行,保证数据库安全性
  • 数据写Insert Into支持 ”仅插入 I“、”存在更新否则插入 UI“、”存在更新否则插入其余删除 UDI“、”删除后插入 DI“四种模式
  • 大数据量支持批次执行,有Group By或Having条件过滤自动执行Reduce合并结果
  • 支持流式执行

安装

pip3 install syncanysql

Docker

docker pull sujin190/syncany-sql

查询Nginx日志

-- 查询访问量最高的三个IP
SELECT seg0 AS ip, COUNT(*) AS cnt FROM `file://data/access.log?sep= ` GROUP BY seg0 ORDER BY cnt DESC LIMIT 3;

查询JSON文件

SELECT
    a.site_id,
    b.name AS site_name,
    IF(c.site_amount > 0, c.site_amount, 0) AS site_amount,
    MAX(a.timeout_at) AS timeout_at,
    MAX(a.vip_timeout_at) AS vip_timeout_at,
    now() as `created_at?`
FROM
    (SELECT
        YIELD_ARRAY(sites) AS site_id,
            IF(vip_type = '2', GET_VALUE(rules, 0, 'timeout_time'), '') AS timeout_at,
            IF(vip_type = '1', GET_VALUE(rules, 0, 'timeout_time'), '') AS vip_timeout_at
    FROM
        `data/demo.json`
    WHERE
        start_date >= '2021-01-01') a
        JOIN
    `data/sites.json` b ON a.site_id = b.site_id
        JOIN
    (SELECT
        site_id, SUM(amount) AS site_amount
    FROM
        `data/orders.json`
    WHERE
        status <= 0
    GROUP BY site_id) c ON a.site_id = c.site_id
GROUP BY a.site_id;

Python API

from syncanysql import ScriptEngine

with ScriptEngine() as engine:
    engine.execute('''
        INSERT INTO `top_ips` SELECT
            ip, cnt
        FROM
            (SELECT
                seg0 AS ip, COUNT(*) AS cnt
            FROM
                `file:///var/log/nginx/access.log?sep= `
            GROUP BY seg0) a
        ORDER BY cnt DESC
        LIMIT 3;
    ''')
    print(engine.pop_memory_datas("top_ips"))

License

Syncany-SQL uses the MIT license, see LICENSE file for the details.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

syncanysql-0.1.19.tar.gz (58.9 kB view details)

Uploaded Source

File details

Details for the file syncanysql-0.1.19.tar.gz.

File metadata

  • Download URL: syncanysql-0.1.19.tar.gz
  • Upload date:
  • Size: 58.9 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.7.1 importlib_metadata/4.10.1 pkginfo/1.8.2 requests/2.22.0 requests-toolbelt/0.9.1 tqdm/4.62.3 CPython/3.8.10

File hashes

Hashes for syncanysql-0.1.19.tar.gz
Algorithm Hash digest
SHA256 2047f9af64a3ae2f3baa7829e4dd938d8a0f016ca20f0a84199ffd19b2a93385
MD5 f5ac055c54664e0a8d53044d1adb1d6a
BLAKE2b-256 0cf5fb26802eb1a68c8cc97d10658d3efc0954a0929b71f0194d18d36fe98255

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page