JayDeBeApi对数据类型的支持
JayDeBeApi对数据类型的支持
常用的数据类型如下：
字符类型
内置字符类型包括：char, nchar, varchar, nvarchar, lvarchar, varchar2和nvarchar2
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),
col6 varchar2(32765),
col7 nvarchar2(32765)
)
"""
cursor.execute(create)
insert = """
insert into tab_string(col1, col2, col3, col4, col5) values
('字符字段char类型','本地化字符字段nchar类型','可变长度字符字段varchar类型',
'本地化可变长度字符字段nvarchar类型','扩展可变长度字符字段lvarchar类型'
'可变长度字符字段varchar2类型','本地化可变长度字符字段nvarchar2类型')
"""
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]))
print("字段6的字节长度为：{} ,值为：\"{}\"". format(len(result[5].encode('utf8')),result[5]))
print("字段7的字节长度为：{} ,值为：\"{}\"". format(len(result[6].encode('utf8')),result[6]))数值类型
内置的数值类型包括：
整型：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 仅能按float/int8类型精度解析decimal(p) 类型，超出精度将得到非预期结果。
参考代码：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]))
# 注意：这里的值不应超过int8范围。
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)这样的别名在新版本中支持
TIMESTAMP(n) WITH TIME ZONE在新版本的Oralce兼容模式下支持
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,
col7 timestamp,
col8 timestamp(1),
col9 timestamp(3),
cola timestamp(5),
colb timestamp(6),
colc timestamp with time zone,
cold timestamp(0) with time zone,
cole timestamp(1) with time zone,
colf timestamp(3) with time zone,
colg timestamp(5) with time zone,
colh timestamp(6) with time zone
)
"""
cursor.execute(create)
prepare = """
insert into tab_datetime(col1,col2,col3,col4,col5,col6,col7,col8,col9,cola,colb,colc,cold,cole,colf,colg,colh)
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',
'2025-03-26 09:43:43','2025-03-26 09:43:43.1','2025-03-26 09:43:43.123','2025-03-26 09:43:43.12345',
'2025-03-26 09:43:43.123456','2025-03-26 09:43:43 +8:00','2025-03-26 09:43:43 +8:00',
'2025-03-26 09:43:43.1 +8:00','2025-03-26 09:43:43.123 +8:00',
'2025-03-26 09:43:43.12345 +8:00','2025-03-26 09:43:43.123456 +8:00'])
select = "select col1,col2,col3,col4,col5,col6,col7,col8,col9,cola,colb,colc,cold,cole,colf,colg,colh 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]))
print("字段 7的类型timestamp ,值为：{}" . format(result[6]))
print("字段 8的类型timestamp(1) ,值为：{}" . format(result[7]))
print("字段 9的类型timestamp(3) ,值为：{}" . format(result[8]))
print("字段 a的类型timestamp(5) ,值为：{}" . format(result[9]))
print("字段 b的类型timestamp(6) ,值为：{}" . format(result[10]))
print("字段 c的类型timestamp with time zone,值为：{}" . format(result[11]))
print("字段 d的类型timestamp(0) with time zone,值为：{}" . format(result[12]))
print("字段 e的类型timestamp(1) with time zone,值为：{}" . format(result[13]))
print("字段 f的类型timestamp(3) with time zone,值为：{}" . format(result[14]))
print("字段 g的类型timestamp(5) with time zone,值为：{}" . format(result[15]))
print("字段 h的类型timestamp(6) with time zone,值为：{}" . format(result[16]))注：2025-03-26更新
布尔类型
布尔类型：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])