# Syncany-SQL
[](https://github.com/snower/syncany-sql/actions/workflows/ci.yml)
[](https://github.com/snower/syncany-sql/stargazers)
简单易用的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合并结果
- 支持流式执行
-----
- [安装](#安装)
- [特性与限制](docs/feature-restrictions.md)
- [配置详解](docs/configure.md)
- [驱动依赖](docs/driver-dependency.md)
- [示例详解](examples)
- [内置函数](docs/functions.md)
- [使用教程](docs/使用教程/)
## 安装
```bash
pip3 install syncanysql
```
#### Docker
```bash
docker pull sujin190/syncany-sql
```
## 查询Nginx日志
```sql
-- 查询访问量最高的三个IP
SELECT seg0 AS ip, COUNT(*) AS cnt FROM `file://data/access.log?sep= ` GROUP BY seg0 ORDER BY cnt DESC LIMIT 3;
```
## 查询JSON文件
```sql
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
```python
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.
Raw data
{
"_id": null,
"home_page": "https://github.com/snower/syncany-sql",
"name": "syncanysql",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": null,
"author": "snower",
"author_email": "sujian199@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/0c/f5/fb26802eb1a68c8cc97d10658d3efc0954a0929b71f0194d18d36fe98255/syncanysql-0.1.19.tar.gz",
"platform": null,
"description": "# Syncany-SQL\n[](https://github.com/snower/syncany-sql/actions/workflows/ci.yml)\n[](https://github.com/snower/syncany-sql/stargazers)\n\n\u7b80\u5355\u6613\u7528\u7684SQL\u6267\u884c\u5f15\u64ce\u3002\n\n- \u53ef\u5728\u672c\u5730\u8fd0\u884cMySQL\u8bed\u6cd5\u7ed3\u6784\u7684SQL\n- \u652f\u6301\u67e5\u8be2\u5e38\u7528mysql\u3001mongodb\u3001postgresql\u3001sqlserver\u3001elasticsearch\u3001influxdb\u3001clickhouse\u3001sqlite\u6570\u636e\u5e93\u53caexecl\u3001csv\u3001json\u548c\u666e\u901a\u6587\u672c\u6587\u4ef6\n- \u652f\u6301\u672c\u5730\u4e34\u65f6\u6570\u636e\u8868\u903b\u8f91\u505a\u4e2d\u95f4\u7ed3\u679c\u4fdd\u5b58\n- \u6570\u636e\u5e93\u6570\u636e\u52a0\u8f7d\u4f7f\u7528\u7b80\u5355\u6761\u4ef6\u8fc7\u6ee4\u53caIN\u6761\u4ef6\u67e5\u8be2\n- \u56e0\u7531\u672c\u5730\u5b8c\u6210Join\u5339\u914d\u6240\u4ee5\u652f\u6301\u4e0d\u540c\u5e93\u8868\u3001\u4e0d\u540c\u4e3b\u673a\u53ca\u4e0d\u540c\u7c7b\u578b\u6570\u636e\u5e93\u95f4Join\u5173\u8054\u67e5\u8be2\n- Group By\u5206\u7ec4\u805a\u5408\u8ba1\u7b97\u53caOrder By\u6392\u5e8f\u4e5f\u7531\u672c\u5730\u6267\u884c\uff0c\u4fdd\u8bc1\u6570\u636e\u5e93\u5b89\u5168\u6027\n- \u6570\u636e\u5199Insert Into\u652f\u6301 \u201d\u4ec5\u63d2\u5165 I\u201c\u3001\u201d\u5b58\u5728\u66f4\u65b0\u5426\u5219\u63d2\u5165 UI\u201c\u3001\u201d\u5b58\u5728\u66f4\u65b0\u5426\u5219\u63d2\u5165\u5176\u4f59\u5220\u9664 UDI\u201c\u3001\u201d\u5220\u9664\u540e\u63d2\u5165 DI\u201c\u56db\u79cd\u6a21\u5f0f\n- \u5927\u6570\u636e\u91cf\u652f\u6301\u6279\u6b21\u6267\u884c\uff0c\u6709Group By\u6216Having\u6761\u4ef6\u8fc7\u6ee4\u81ea\u52a8\u6267\u884cReduce\u5408\u5e76\u7ed3\u679c\n- \u652f\u6301\u6d41\u5f0f\u6267\u884c\n\n-----\n\n- [\u5b89\u88c5](#\u5b89\u88c5)\n- [\u7279\u6027\u4e0e\u9650\u5236](docs/feature-restrictions.md)\n- [\u914d\u7f6e\u8be6\u89e3](docs/configure.md)\n- [\u9a71\u52a8\u4f9d\u8d56](docs/driver-dependency.md)\n- [\u793a\u4f8b\u8be6\u89e3](examples)\n- [\u5185\u7f6e\u51fd\u6570](docs/functions.md)\n- [\u4f7f\u7528\u6559\u7a0b](docs/\u4f7f\u7528\u6559\u7a0b/)\n\n## \u5b89\u88c5\n\n```bash\npip3 install syncanysql\n```\n\n#### Docker\n\n```bash\ndocker pull sujin190/syncany-sql\n```\n\n## \u67e5\u8be2Nginx\u65e5\u5fd7\n\n```sql\n-- \u67e5\u8be2\u8bbf\u95ee\u91cf\u6700\u9ad8\u7684\u4e09\u4e2aIP\nSELECT seg0 AS ip, COUNT(*) AS cnt FROM `file://data/access.log?sep= ` GROUP BY seg0 ORDER BY cnt DESC LIMIT 3;\n```\n\n## \u67e5\u8be2JSON\u6587\u4ef6\n\n```sql\nSELECT\n a.site_id,\n b.name AS site_name,\n IF(c.site_amount > 0, c.site_amount, 0) AS site_amount,\n MAX(a.timeout_at) AS timeout_at,\n MAX(a.vip_timeout_at) AS vip_timeout_at,\n now() as `created_at?`\nFROM\n (SELECT\n YIELD_ARRAY(sites) AS site_id,\n IF(vip_type = '2', GET_VALUE(rules, 0, 'timeout_time'), '') AS timeout_at,\n IF(vip_type = '1', GET_VALUE(rules, 0, 'timeout_time'), '') AS vip_timeout_at\n FROM\n `data/demo.json`\n WHERE\n start_date >= '2021-01-01') a\n JOIN\n `data/sites.json` b ON a.site_id = b.site_id\n JOIN\n (SELECT\n site_id, SUM(amount) AS site_amount\n FROM\n `data/orders.json`\n WHERE\n status <= 0\n GROUP BY site_id) c ON a.site_id = c.site_id\nGROUP BY a.site_id;\n```\n\n# Python API\n\n```python\nfrom syncanysql import ScriptEngine\n\nwith ScriptEngine() as engine:\n engine.execute('''\n INSERT INTO `top_ips` SELECT\n ip, cnt\n FROM\n (SELECT\n seg0 AS ip, COUNT(*) AS cnt\n FROM\n `file:///var/log/nginx/access.log?sep= `\n GROUP BY seg0) a\n ORDER BY cnt DESC\n LIMIT 3;\n ''')\n print(engine.pop_memory_datas(\"top_ips\"))\n```\n\n# License\n\nSyncany-SQL uses the MIT license, see LICENSE file for the details.",
"bugtrack_url": null,
"license": "MIT",
"summary": "Simple and easy-to-use sql execution engine",
"version": "0.1.19",
"project_urls": {
"Homepage": "https://github.com/snower/syncany-sql"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "0cf5fb26802eb1a68c8cc97d10658d3efc0954a0929b71f0194d18d36fe98255",
"md5": "f5ac055c54664e0a8d53044d1adb1d6a",
"sha256": "2047f9af64a3ae2f3baa7829e4dd938d8a0f016ca20f0a84199ffd19b2a93385"
},
"downloads": -1,
"filename": "syncanysql-0.1.19.tar.gz",
"has_sig": false,
"md5_digest": "f5ac055c54664e0a8d53044d1adb1d6a",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 58945,
"upload_time": "2024-03-22T10:48:44",
"upload_time_iso_8601": "2024-03-22T10:48:44.644664Z",
"url": "https://files.pythonhosted.org/packages/0c/f5/fb26802eb1a68c8cc97d10658d3efc0954a0929b71f0194d18d36fe98255/syncanysql-0.1.19.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-03-22 10:48:44",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "snower",
"github_project": "syncany-sql",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [
{
"name": "pytz",
"specs": [
[
">=",
"2018.5"
]
]
},
{
"name": "tzlocal",
"specs": [
[
">=",
"1.5.1"
]
]
},
{
"name": "pyyaml",
"specs": [
[
">=",
"5.1.2"
]
]
},
{
"name": "pendulum",
"specs": [
[
">=",
"2.1.2"
]
]
},
{
"name": "sqlglot",
"specs": [
[
">=",
"11.5.5"
],
[
"<",
"12"
]
]
},
{
"name": "Pygments",
"specs": [
[
">=",
"2.14.0"
]
]
},
{
"name": "Pygments",
"specs": [
[
">=",
"2.14.0"
]
]
},
{
"name": "prompt-toolkit",
"specs": [
[
">=",
"3.0.36"
]
]
},
{
"name": "rich",
"specs": [
[
">=",
"9.11.1"
]
]
},
{
"name": "pymongo",
"specs": [
[
">=",
"3.6.1"
]
]
},
{
"name": "PyMySQL",
"specs": [
[
">=",
"0.8.1"
]
]
},
{
"name": "openpyxl",
"specs": [
[
">=",
"2.5.0"
]
]
},
{
"name": "psycopg2-binary",
"specs": [
[
">=",
"2.8.6"
]
]
},
{
"name": "elasticsearch",
"specs": [
[
">=",
"6.3.1"
]
]
},
{
"name": "influxdb",
"specs": [
[
">=",
"5.3.1"
]
]
},
{
"name": "clickhouse_driver",
"specs": [
[
">=",
"0.1.5"
]
]
},
{
"name": "redis",
"specs": [
[
">=",
"3.5.3"
]
]
},
{
"name": "requests",
"specs": [
[
">=",
"2.22.0"
]
]
},
{
"name": "pymssql",
"specs": [
[
">=",
"2.2.7"
]
]
},
{
"name": "prql-python",
"specs": [
[
">=",
"0.11.1"
]
]
},
{
"name": "coverage",
"specs": []
},
{
"name": "pytest",
"specs": []
},
{
"name": "pytest-benchmark",
"specs": []
},
{
"name": "pytest-cov",
"specs": []
},
{
"name": "pytest-integration",
"specs": []
}
],
"lcname": "syncanysql"
}
JSON
