# SqlOnJson

**Repository Path**: jack2nb/SqlOnJson

## Basic Information

- **Project Name**: SqlOnJson
- **Description**: 数据不在孤岛，用一个平台支持数据湖
web api 调用数据
基于 sqlalchemy 2.0
- **Primary Language**: Python
- **License**: GPL-3.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-01-26
- **Last Updated**: 2024-10-25

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 数据平台（增删改查）
随着企业信息化 会使用越来越多的系统，数据信息孤岛越来越严重
数据平台解决连接全部的数据库


基于 sqlalchemy 2.0 支持几乎所以的数据库
通过json api 用web接口读取数据


| 数据库     | 连接字符                                                   | 依赖库   |
| ---------- | ---------------------------------------------------------- | -------- |
| mysql      | mysql+pymysql://username:password@host:port/database       | pymysql  |
| postgresql | postgresql+psycopg2://username:password@host:port/database | psycopg2 |
| sql server | mssql+pymssql://sa:密码@localhost:1433/sss                 | pymssql  |
| sqlite     | sqlite:////tmp/test.db                                     | 无       |

## 表结构
### 用户表
```sql
CREATE TABLE "user" (
	"id"	INTEGER,
	"username"	varchar(20),
	"nickname"	varchar(20),
	"email"	varchar(20),
	PRIMARY KEY("id" AUTOINCREMENT)
);
```

### 评论表

```sql
CREATE TABLE "moment" (
	"id"	INTEGER,
	"content"	INTEGER,
	"date"	date,
	"user_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
```



## 条件查询

| 查询语句           | 含义                         | 备注 |
| ------------------ | ---------------------------- | ---- |
| "username":"jack"  | 更加name精确匹配             |      |
| "username*":"%k%"  | 用模糊查询                   |      |
| "id#":[1,2]        | id  in (1,2)                 |      |
| "id&":'>1,<=4,!=2' | id >1 and id <=4 and id <> 2 |      |
| "id^":'=user_id'   | 同一行 两值相等              |      |
| "id$":1            | id非空                       |      |
| "@order":"-id"     | 排序根据id降序               |      |
| "@limit": 4        | 只返回4行                    |      |
| "@page":2          | 显示第几页                   |      |
|                    |                              |      |





### python例子

```python
ja.get(
    {"oa/user": 
        {  
        "@column": "id,nickname ",
        "@limit": 4
        }
    }
) 
```



## web方式使用
### curl 获取数据

```


curl -H "Content-Type: application/json" -X POST -d '{"user":{"@limit": 2}}'  http://10.147.20.198:2028/jsonapi/get



```

### win curl

```

curl -H "Content-Type: application/json" -X POST -d "{\"user\":{\"@limit\": 2}}" http://127.0.0.1:2028/jsonapi/get

```



## 数据操作

### 插入

```python
dc= {"oa/user":{
    "username":"jack"
    ,"nickname":"杰克x"
    ,"email":"jack@demo.com"
    }
 }
ja_db( dc,'post' )
```
### 批量插入

```
dc= {"user":[{
    "username":"jack"
    ,"nickname":"杰克x"
    ,"email":"jack@demo.com"
    },....]
 }
```



### 更新

```python
vn = {
        "oa/user": {
            "@update":{
            	"nickname":"来之 http的更新 good news"
        	}
            "id": 8
        },
       
    }
    
ja_db( vn,'put' )
```

### 删除

```python
vn = {
        "user": {
            "id": 7
        }
    }
ja_db( vn,'del' )
```



# 高级功能

## 分组group

```python
vn = { "oa/user": {
                "@column": "nickname"
                ,"@group":"nickname"
            }
     }
ja.get( vn )
```



| 查询语句 | 含义               | 备注                |
| -------- | ------------------ | ------------------- |
| @column  | 指定字段           | "id,nickname "      |
| @column  | 结合group          | "nickname.count.ct" |
| @column  | 加入函数max别名成m | "id.max.m"          |
|          |                    |                     |

### 分组统计

```python
vn = { "oa/user": {
                "@column": "nickname.count.ct"
                ,"@group":"nickname"
            }
     }
ja.get( vn )
```

### 得到id最大最小值

 ```python
vn = { "oa/user": {
                "@column": "nickname,id.max.m,id.min.n"
                ,"@group":"nickname"
            }
     }
ja.get( vn )
 ```

## 字段查询别名

返回的行 字段名栏外名 将是中文



```python
vn = { "oa/user": 
        {  
        "@column": "id.唯一号,nickname.昵称 ",
        "@limit": 4
        }
}

ja.get( vn )

 
```

# 查多表



## 一个表查多次

查询别名  表名%别名

```python
vn = { 
    "oa/user%abc": 
         {
            "@column": "nickname.count.ct ,nickname,id.max.m,id.min.n"
            ,"@group":"nickname"
         },
 
    "oa/user": 
        {  
            "@column": "id,nickname ",
            "@limit": 4
        }
}

ja.get( vn )


```

## join查询

```python
vn = { "oa/user": {
                "@column": "id,nickname"
                ,"nickname":"杰克" 
            },
      "oa/group": {
                
                 "user_id|":"user.id"
            }
      
     }
ja.get( vn )
```

`"user_id|":"user.id"`引用其他表的数据为条件





# 其他

在参数中以#开头的表名  直接被忽略，可以用来做其他验证授权的方式适用



```python
vn = {
        "oa/user": {
            "id": 7
        }
        "#session_id":"jklsdnfa-skjdfapsiduf"
    }
ja_db( vn )

```


## 同时查多个数据库


## datatable

实现join 语法

## 配置
```json
{
	"#comment": "提供html文件,基础信息ip时间,json配文件微服务 不依赖其他网络和服务",
	"webPort": 2028,
	"debug": true,
	"dbs": [{
			"name": "k3c",
			"uri": "sqlite:////jack/k3数据探索/k3clouddic.db"
		},
		{
			"name": "erp",
			"uri": "mssql+pymssql://user:Pwd@192.168.0.48/AIS20230131214054?charset=utf8"
		},
		{
			"name": "oa",
			"uri": "mssql+pymssql://user:Pwd@192.168.0.107/dbname?charset=utf8"
		}
	]

}
```