【QyDBLib】1.CRUD-Api

1.CRUP Api

CRUD API 是基于Python编写的一套数据库操作类库，目前只在PostgreSQL、CitusDB数据库环境下使用。

目标：简化基于PostgreSQL、CitusDB数据库环境下的数据库表增删改查操作，软件开发人员只需要将Web传输的JSON数据处理好以后直接调用对应的方法即可完成开发工作，从而使软件开发任意能够从数据库操作的代码中解放出来，更加专注于业务逻辑的处理。

1.1 环境变量

在应用系统的settings.py文件中设置以下环境变量

LOG_PATH = '/u01/qydblib.log'
DB_URL = 'postgresql://[username]:[password]@[host]:[port]/[dbname]'

1.2 支持的数据类型

varchar

char

date

decimal

float4

float8

int2

int4

int8

numeric

text

time

timestamp(0)

varchar[]

int2[]

int4[]

int8[]

json

说明：

1. 日期格式：YYYY-MM-DD

2. 时间格式：HH24:MI:SS

3. 时间戳格式：YYYY-MM-DD HH24:MI:SS

4. 数组仅支持一维数据

以下代码所使用的表结构：

drop table if exists test.demo_table;
​
create table test.demo_table(
    id varchar(64) primary key,
    bool1 bool,
    char1 char(8),   
    date1 date,   
    decimal1 decimal,   
    decimal2 decimal(9),   
    decimal3 decimal(19,6),   
    float1 float4,   
    float2 float8,   
    int1 int2,   
    int2 int4,   
    int3 int8,   
    numeric1 numeric,   
    numeric2 numeric(9),   
    numeric3 numeric(19, 6),   
    text1 text,   
    time1 time,   
    timestamp1 timestamp(0),   
    varchar1 varchar(256),   
    array1 varchar(32)[],   
    array2 int8[],
    json1 json
);

1.3 API

1.3.1 Create

1.create_data(schema_name, table_name, value)

• 功能：在对应的数据表中插入一行数据，主键值为UUID字符串

• 参数：

  schema_name：表所在schema，字符串；

  table_name：表名，字符串；

  value：数据，字典，以数据表字段名为key的字典数据；

• 返回：

  true/false，表示是否操作成功

示例：

# coding: utf-8
import qydblib.create as create
import json
​
if __name__ == '__main__':
    data = {
        'bool1': True, 
        'char1': '字符', 
        'date1': '2022-03-05', 
        'decimal1': 0.1, 
        'decimal2': 100, 
        'decimal3': 0.3,
        'float1': 10.1, 
        'float2': 10.2, 
        'int1': 1, 
        'int2': 2, 
        'int3': 3, 
        'numeric1': 100.1, 
        'numeric2': 200,
        'numeric3': 300.1, 
        'text1': '文本', 
        'time1': '14:50:30', 
        'timestamp1': '2022-03-05 14:50:30',
        'varchar1': '字符串', 
        'array1': ['字符串1', '字符串2', '字符串3'], 
        'array2': [1, 2, 3],
        'json1': json.dumps({
            'name': '赵XX', 
            'sex': '女', 
            'education': '本科', 
            'school': '北京大学'
        })}
    create.create_data('test', 'demo_table', data)

2.create_data_include_primary(schema_name, table_name, value)

• 功能：在对应的数据表中插入一行数据，但value中已经包含了主键值

• 参数：同上

• 返回：同上

3.create_data_auto_primary(schema_name, table_name, value)

• 功能：在对应的数据表中插入一行数据，适用于主键类型为Serial类型的数据表

• 参数：同上

• 返回：同上

1.3.2 Retrieve

1.retrieve_data_many(sql, param)

• 功能：执行sql语句，并返回结果集

• 参数：

  sql：sql语句，字符串类型，sql语句中参数的写法：冒号加参数名，如：“:personId”，注：sql语句中如果包含函数计算，必须给出字段别名；

  param：给sql语句传递参数值，字典类型。如：{'personId': 'abcde12345'}

• 返回值：

  列表结果集，结果为以sql语句查询的字段为键，对应数据为值的字典

2.retrieve_data_one(sql, param)

• 功能：执行sql语句，并返回单行结果

• 参数：

  sql：sql语句，字符串类型，sql语句中参数的写法：冒号加参数名，如：“:personId”，注：sql语句中如果包含函数计算，必须给出字段别名；

  param：给sql语句传递参数值，字典类型。如：{'personId': 'abcde12345'}

• 返回值：

  结果为以sql语句查询的字段为键，对应数据为值的字典，如果无结果，则返回{}；判断是否返回结果，可以使用row != {}，对于统计类查询如果没有数据，仍然会返回以字段名为键的数据，值数据为None；

示例：

# coding: utf-8
import qydblib.retrieve as retrieve
​
if __name__ == '__main__':
    sql = 'select * from test.demo_table'
    print(retrieve.retrieve_data_many(sql))
​
    sql = 'select * from test.demo_table where id=:id'
    id = '8384E8349C5E11EC8C9870C94EC87656'
    print(retrieve.retrieve_data_one(sql, param={'id': id}))

1.3.3 Retrieve Paging

1.retrieve_paging_data(sql, param=None, current_page=1, page_size=10)

• 功能：执行SQL语句，对数据进行分页查询，返回指定页内的数据

• 参数：

  sql：sql语句，字符串类型，sql语句中参数的写法：冒号加参数名，如：“:personId”，注：sql语句中如果包含函数计算，必须给出字段别名；

  param：给sql语句传递参数值，字典类型。如：{'personId': 'abcde12345'}

  current_page：当前页码

  page_size：页内尺寸

• 返回：

  返回值类型为字典，其内容为： {total: n, datas:[]}

  total - 数据总行数 datas - 当前页面内的数据

2.load_paging_data(sql, param=None, current_page=1, page_size=10)

• 功能：同上

• 参数：同上

• 返回：只返回页内数据，不返回数据总行数，适用于类似手机页面上的上拉加载

1.3.4 Update

1.update_data(schema_name, table_name, value, primary_value)

• 功能：更新主键值等于primary_value的行

• 参数：

  schema_name：表所在schema，字符串；

  table_name：表名，字符串；

  value：数据，字典，以数据表字段名为key的字典数据；

  primary_value：主键值，根据数据表主键字段类型决定数据类型；

• 返回：

  true/false，表示是否操作成功

# coding: utf-8
import json
from qydblib import update as update
​
if __name__ == '__main__':
    data = {'bool1':  False, 'char1': '这里是字符'}
    update.update_data('test', 'demo_table', data, '83ADA36C9C5E11ECB78670C94EC87656')

1.3.5 Delete

1.delete_data(schema_name, table_name, primary_value)

• 功能：在对应的数据表上删除主键为primary_value的一行数据

• 参数：

  schema_name：表所在schema，字符串；

  table_name：表名，字符串；

  value：数据，字典，以数据表字段名为key的字典数据；

  primary_value：主键值，根据数据表主键字段类型决定数据类型；

• 返回：

  true/false，表示是否操作成功

2.delete_data_many(schema_name, table_name, primary_values)

• 功能：在对应的数据表上删除主键值包含在primary_values中的数据

• 参数：

  schema_name：表所在schema，字符串；

  table_name：表名，字符串；

  value：数据，字典，以数据表字段名为key的字典数据；

  primary_values：主键值列表，根据数据表主键字段类型决定数据类型，列表；

• 返回：

  true/false，表示是否操作成功

示例：

# coding: utf-8
import qydblib.delete as delete
​
if __name__ == '__main__':
    delete.delete_data('test', 'demo_table', '83ADA36C9C5E11ECB78670C94EC87656')
    delete.delete_data_many('test', 'demo_table', ['83A604489C5E11ECBC2870C94EC87656','839DD3C09C5E11ECA2CF70C94EC87656'])

1.3.6 Actuator

1.act(sql, param)

• 功能：执行任意sql语句

• 参数：同Retrieve

• 返回：true/false

2.act_with_result

• 功能：执行任意sql语句，并返回结果集

• 参数：同Retrieve

• 返回：true/false

示例：

# coding: utf-8
import qydblib.actuator as actuator
​
if __name__ == '__main__':
    sql = 'update test.demo_table set char1 = :char1'
    param = {'char1': '这里是字符'}
    actuator.act(sql, param=param)
​
    sql = 'select * from test.demo_table'
    datas = actuator.act_with_result(sql)
    print(datas)

1.3.7 事务

当我们在实际开发中，有很多业务不是通过执行一条SQL语句来完成的，在一个业务逻辑中，我们可能需要执行很多SQL语句来完成，这时就需要以事务方式进行数据处理，采用事务，可以在一个数据库会话中执行多条SQL语句，更有利于提高系统性能。

事务以类的形式进行定义，其构造函数：

Transaction()

1.begin()

开始事务

2.abort()

中止事务，当执行SQL语句出现异常时调用

3.end()

结束事务，当所有SQL语句全部执行结束后调用

其他数据处理方面的函数与CRUD的函数相同。

示例：

# coding: utf-8
from logger import logger
import qydblib.transaction as ts
import json
​
if __name__ == '__main__':
    ts = ts.Transaction()
    try:
        ts.begin()
​
        data = {
            'bool1': True, 
            'char1': '字符', 
            'date1': '2022-03-05', 
            'decimal1': 0.1, 
            'decimal2': 100, 
            'decimal3': 0.3
        }
        ts.create_data('test', 'demo_table', data)
​
        data = {'bool1': False, 'char1': '这是字符'}
        ts.update_data('test', 'demo_table', data, '746833409D2811ECAB8D70C94EC87656')
​
        ts.delete_data('test', 'demo_table', '746833409D2811ECAB8D70C94EC87656')
        ts.delete_data_many('test', 'demo_table', ['D3E2642E9D2811EC95AD70C94EC87656', 'D3CDD7EC9D2811ECB5A270C94EC87656'])
​
        sql = 'select * from test.demo_table'
        print(ts.retrieve_data_many(sql))
​
        sql = 'select * from test.demo_table where id=:id'
        id = '73C979949D2811EC8E5E70C94EC87656'
        print(ts.retrieve_data_one(sql, param={'id': id}))
​
        sql = 'select * from test.demo_table order by timestamp1 desc'
        print(ts.retrieve_paging_data(sql, current_page=2, page_size=3))
        print(ts.load_paging_data(sql, current_page=2, page_size=3))
​
        sql = 'select * from test.demo_table'
        print(ts.act_with_result(sql))
​
        sql = 'delete from test.demo_table'
        ts.act(sql)
​
        ts.end()
    except Exception as ex:
        logger.error(ex)
        ts.abort()