JayDeBeApi对数据类型的支持

JayDeBeApi对数据类型的支持

常用的数据类型如下：

字符类型

内置字符类型包括：char, nchar, varchar, nvarchar 和lvarchar
CHARACTER(n) 和 CHARACTER VARYING(n)这样的别名同样支持
参考代码：test_string_type.py

    create = """create table tab_string(col1 char(32767),col2 nchar(32767),col3 varchar(255),col4 nvarchar(255),col5 lvarchar(32739))"""cursor.execute(create)insert = """insert into tab_string(col1, col2, col3, col4, col5) values('字符字段char类型','本地化字符字段nchar类型','可变长度字符字段varchar类型','本地化可变长度字符字段nvarchar类型','扩展可变长度字符字段lvarchar类型')"""cursor.execute(insert)select = "select * from tab_string"cursor.execute(select)rows = cursor.fetchall()for result in rows :print("字段1的字节长度为：{} ,值为：\"{}\"". format(len(result[0].encode('utf8')),result[0]))print("字段2的字节长度为：{} ,值为：\"{}\"". format(len(result[1].encode('utf8')),result[1]))print("字段3的字节长度为：{} ,值为：\"{}\"". format(len(result[2].encode('utf8')),result[2]))print("字段4的字节长度为：{} ,值为：\"{}\"". format(len(result[3].encode('utf8')),result[3]))print("字段5的字节长度为：{} ,值为：\"{}\"". format(len(result[4].encode('utf8')),result[4]))

数值类型

内置的数值类型包括：
整型：smallint, integer, int8, bigint
自增长整型：serial, serial8, bigserail
浮点型：smallfloat, float, decimal
精确值型：decimal(P,S)
货币类型：money(P,S)
DEC(p,s)、NUMERIC(p,s)、INT和DOUBLE PRECISION这样的别名同样支持
注意：JayDeBeApi无法解析decimal§ 类型，结果显示异常
参考代码：test_numeric_type.py

    create = """create table tab_numeric(col1 serial not null,col2 smallint,col3 int,col4 int8,col5 bigint,col6 smallfloat,col7 float,col8 decimal(32,20),col9 decimal(32),colx money(32,20))"""cursor.execute(create)insert = """insert into tab_numeric(col1, col2, col3, col4, col5, col6, col7, col8, col9, colx) values(0,32767,2147483647,9223372036854775807,9223372036854775807,1234567890,12345678901234567890,123456789012.123456789,12345678901234567890123456789012,123456789012.123456789)"""cursor.execute(insert)select = "select * from tab_numeric"cursor.execute(select)data = cursor.fetchall()for result in data:print("字段 1的类型serail         ,值为：{}" . format(result[0]))print("字段 2的类型samllint       ,值为：{}" . format(result[1]))print("字段 3的类型integer        ,值为：{}" . format(result[2]))print("字段 4的类型int8           ,值为：{}" . format(result[3]))print("字段 5的类型bigint         ,值为：{}" . format(result[4]))print("字段 6的类型smallfloat     ,值为：{}" . format(result[5]))print("字段 7的类型float          ,值为：{}" . format(result[6]))print("字段 8的类型decimal(32,20) ,值为：{}" . format(result[7]))print("字段 9的类型decimal(32)    ,值为：{}" . format(result[8]))print("字段10的类型money(32,20)   ,值为：{}" . format(result[9]))

日期时间型

日期型：date
日期时间型：datetime [first to last]
间隔类型：interval [first to list]
TIMESTAMP(n)这样的别名在部分版本中支持

    create = """create table tab_datetime(col1 serial not null,col2 date,col3 datetime year to day,col4 datetime year to second,col5 datetime year to fraction(5),col6 interval day to second)"""cursor.execute(create)prepare = """insert into tab_datetime(col1,col2,col3,col4,col5,col6) values(0,?,?,?,?,?)"""cursor.execute(prepare,['2023-03-24','2023-03-24','2023-03-24 12:34:56','2023-03-24 12:34:56.98765','-3 12:13:14'])select = "select col1,col2,col3,col4,col5,col6 from tab_datetime"cursor.execute(select)data = cursor.fetchall()for result in data:print("字段 1的类型serail                  ,值为：{}" . format(result[0]))print("字段 2的类型date                    ,值为：{}" . format(result[1]))print("字段 3的类型datetime Y-m-d          ,值为：{}" . format(result[2]))print("字段 4的类型datetime Y-m-d H:M:S    ,值为：{}" . format(result[3]))print("字段 5的类型datetime Y-m-d H:M:S.F5 ,值为：{}" . format(result[4]))print("字段 6的类型interval d H:M          ,值为：{}" . format(result[5]))

布尔类型

布尔类型：boolean
取值范围：‘t’/1, ‘f’/0, null(None)
参考代码：test_boolean_type.py

    create = """create table tab_boolean(col1 serial not null,col2 boolean)"""cursor.execute(create)prepare = """insert into tab_boolean(col1,col2) values(0,?)"""cursor.execute(prepare,(True,))cursor.execute(prepare,(False,))cursor.execute(prepare,(None,))select = "select * from tab_boolean"cursor.execute(select)rows = cursor.fetchall()for row in rows :print("字段 1的类型serail   ,值为：{}" . format(row[0]))print("字段 2的类型boolean  ,值为：{}" . format(row[1]))

扩展类型LIST

LIST类型：list(TYPE not null)
参考代码：test_list_type.py

    create = """create table tab_list(col1 serial not null,col2 LIST(varchar(20) not null))"""cursor.execute(create)prepare = """insert into tab_list(col1,col2) values(0,?)"""col2_list = "LIST{'aaaa','bbbb','cccc'}"cursor.execute(prepare, (col2_list,))select = "select * from tab_list"cursor.execute(select)data = cursor.fetchall()for result in data:print("字段 1的类型serail   ,值为：{}" . format(result[0]))print("字段 2的类型list     ,值为：{}" . format(result[1]))

JSON和BSON类型

JSON类型：以字符形式存储
BSON类型：可以以字符形式或者二进制形式存储
注：不能对bson类型进行转换
参考代码：test_json_bson_type.py

    create = """create table tab_json_bson(col1 serial not null,col2 bson,col3 json)"""cursor.execute(create)prepare = "insert into tab_json_bson(col1,col2,col3) values(0,?,?)"col2_bson = "bson类型输入"col3_json = "json类型输入"cursor.execute(prepare,(col2_bson,col3_json))select = "select col1,col2,col3 from tab_json_bson"cursor.execute(select)rows = cursor.fetchall()for row in rows:print("字段1的值为：{}".format(row[0]))print("字段2的值为：{}".format(""))print("字段3的值为：{}".format(row[2]))

大对象类型

包含：
text和byte
参考代码：test_byte_text_type.py

    create = """create table tab_lob(col1 serial not null,col2 byte,col3 text)"""cursor.execute(create)prepare = "insert into tab_lob(col1,col2,col3) values(0,?,?)"col2_byte = bytes('byte类型输入', encoding='UTF-8')col3_text = 'text类型输入'cursor.execute(prepare,(col2_byte,col3_text))select = "select col1,col2,col3 from tab_lob"cursor.execute(select)rows = cursor.fetchall()for row in rows:print("字段1的值为：", row[0])print("字段2的值为：", row[1])print("字段3的值为：", row[2])

智能大对象类型

包含：
blob和clob
两种类型均只可以插入，查询时无法转换
参考代码：test_blob_clob_type.py

    create = """create table tab_lob(col1 serial not null,col2 blob,col3 clob)"""cursor.execute(create)blob_str  = 'blob类型输入，最大支持4TB'clob_str  = 'clob类型输入，最大支持4TB'prepare = "insert into tab_lob(col1,col2,col3) values(0,?,?)"cursor.execute(prepare,(blob_str,clob_str))if(os.path.isfile('blob_file.txt')):os.remove('blob_file.txt')if(os.path.isfile('clob_file.txt')):os.remove('clob_file.txt')select = "select col1,col2,col3 from tab_lob"cursor.execute(select)rows = cursor.fetchall()for row in rows :print("字段1的值为：", row[0])print("字段2的值为：", row[1])print("字段3的值为：", row[2])

参考代码：
test_JayDeBeApi.tar.gz