JayDeBeApi对GBase 8s数据库数据类型的支持
本文基于GBase 8s V8.8 3.6.2_2版本编写, 是对原文档:https://gbasedbt.com/index.php/archives/459/ 的更新。
常用的数据类型如下:
GBase 8s数据库数据类型参考:https://gbasedbt.com/index.php/archives/628/
1, 字符类型
内置字符类型包括:char, nchar, varchar, nvarchar, lvarchar, varchar2, nvarchar2和clob2
CHARACTER(n) 和 CHARACTER VARYING(n)这样的别名同样支持
启用SQLMODE=Oralce,即Oracle兼容模式
参考代码:test01_string_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | drop = "drop table if exists tab_string" cursor.execute(drop) create = """ create table tab_string( c_char char(32767), c_nchar nchar(32767), c_varchar varchar(32765), c_nvarchar nvarchar(32765), c_lvarchar lvarchar(32739), c_varchar2 varchar2(32765), c_nvarchar2 nvarchar2(32765), c_clob2 clob2 ) """ cursor.execute(create) insert = """ insert into tab_string(c_char,c_nchar,c_varchar,c_nvarchar,c_lvarchar,c_varchar2,c_nvarchar2,c_clob2) values (?,?,?,?,?,?,?,?) """ cursor.execute(insert,( 'c_char字段类型char' , 'c_nchar字段类型nchar' , 'c_varchar字段类型varchar' , 'c_nvarchar字段类型nvarchar' , 'c_lvarchar字段类型lvarchar' , 'c_varchar2字段类型varchar2' , 'c_nvarchar2字段类型nvarchar2' , 'c_clob2字段类型clob2' )) select = "select * from tab_string" cursor.execute(select) rows = cursor.fetchall() # print(rows) for row in rows : print ( "字段1 char 的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 0 ])), len (row[ 0 ].encode( 'utf8' )),row[ 0 ])) print ( "字段2 nchar 的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 1 ])), len (row[ 1 ].encode( 'utf8' )),row[ 1 ])) print ( "字段3 varchar 的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 2 ])), len (row[ 2 ].encode( 'utf8' )),row[ 2 ])) print ( "字段4 nvarchar 的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 3 ])), len (row[ 3 ].encode( 'utf8' )),row[ 3 ])) print ( "字段5 lvarchar 的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 4 ])), len (row[ 4 ].encode( 'utf8' )),row[ 4 ])) print ( "字段6 varchar2 的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 5 ])), len (row[ 5 ].encode( 'utf8' )),row[ 5 ])) print ( "字段7 nvarchar2的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 6 ])), len (row[ 6 ].encode( 'utf8' )),row[ 6 ])) print ( "字段8 clob2 的类型为:{},字节长度为:{} ,值为:\"{}\"" . format ( str ( type (row[ 7 ])), len (row[ 7 ].encode( 'utf8' )),row[ 7 ])) |
输出结果:
1 2 3 4 5 6 7 8 | 字段1 char 的类型为:<class 'str'>,字节长度为:22 ,值为:"c_char字段类型char" 字段2 nchar 的类型为:<class 'str'>,字节长度为:24 ,值为:"c_nchar字段类型nchar" 字段3 varchar 的类型为:<class 'str'>,字节长度为:28 ,值为:"c_varchar字段类型varchar" 字段4 nvarchar 的类型为:<class 'str'>,字节长度为:30 ,值为:"c_nvarchar字段类型nvarchar" 字段5 lvarchar 的类型为:<class 'str'>,字节长度为:30 ,值为:"c_lvarchar字段类型lvarchar" 字段6 varchar2 的类型为:<class 'str'>,字节长度为:30 ,值为:"c_varchar2字段类型varchar2" 字段7 nvarchar2的类型为:<class 'str'>,字节长度为:32 ,值为:"c_nvarchar2字段类型nvarchar2" 字段8 clob2 的类型为:<class 'str'>,字节长度为:24 ,值为:"c_clob2字段类型clob2" |
2, 数值类型
内置的数值类型包括:
整型:smallint, integer, int8, bigint
自增长整型:serial, serial8, bigserail
浮点型:smallfloat, float, decimal(P)
精确值型:decimal(P,S)
货币类型:money(P,S)
DEC(p,s)、NUMERIC(p,s)、INT和DOUBLE PRECISION这样的别名同样支持
注意:同一个表仅允许出现个自增长整形
注意:JayDeBeApi 仅能按int8类型精度解析decimal(p) 类型,超出精度将得到非预期结果。
参考代码:test02_numeric_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | drop = "drop table if exists tab_numeric" cursor.execute(drop) create = """ create table tab_numeric( i_serial serial not null, i_smallint smallint, i_int int, i_int8 int8, i_bigint bigint, f_smallfloat smallfloat, f_float float, d_decimal decimal(32,20), f_decimal_1 decimal(32), f_decimal_2 decimal(32), d_money money(32,20) ) """ cursor.execute(create) insert = """ insert into tab_numeric(i_serial,i_smallint,i_int,i_int8,i_bigint,f_smallfloat,f_float,d_decimal,f_decimal_1,f_decimal_2, d_money) values (0,?,?,?,?,?,?,?,?,?,?) """ # 超过限制时,需要指明类型 cursor.execute(insert,( 32767 , 2147483647 , 9223372036854775807 , 9223372036854775807 , float ( 12345678901234567890 ), float ( 12345678901234567890 ), 123456789012.12345678901234567890 , 9223372036854775807 , float ( 9223372036854775809 ), 123456789012.12345678901234567890 )) select = "select * from tab_numeric" cursor.execute(select) rows = cursor.fetchall() # print(rows) for row in rows: print ( "字段 1 serail 的类型为{},值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) print ( "字段 2 samllint 的类型为{},值为:{}" . format ( str ( type (row[ 1 ])),row[ 1 ])) print ( "字段 3 integer 的类型为{},值为:{}" . format ( str ( type (row[ 2 ])),row[ 2 ])) print ( "字段 4 int8 的类型为{},值为:{}" . format ( str ( type (row[ 3 ])),row[ 3 ])) print ( "字段 5 bigint 的类型为{},值为:{}" . format ( str ( type (row[ 4 ])),row[ 4 ])) print ( "字段 6 smallfloat 的类型为{},值为:{}" . format ( str ( type (row[ 5 ])),row[ 5 ])) print ( "字段 7 float 的类型为{},值为:{}" . format ( str ( type (row[ 6 ])),row[ 6 ])) print ( "字段 8 decimal(32,20) 的类型为{},值为:{}" . format ( str ( type (row[ 8 ])),row[ 7 ])) # decimal(p)被解析成JLong类型,值不应超出int8的上限 print ( "字段 9 decimal(32) 的类型为{},值为:{}" . format ( str ( type (row[ 8 ])),row[ 8 ])) print ( "字段10 decimal(32) 的类型为{},值为:{}" . format ( str ( type (row[ 9 ])),row[ 9 ])) print ( "字段11 money(32,20) 的类型为{},值为:{}" . format ( str ( type (row[ 10 ])),row[ 10 ])) |
输出结果及说明:
1 2 3 4 5 6 7 8 9 10 11 | 字段 1 serail 的类型为<java class 'java.lang.Integer'>,值为:1 字段 2 samllint 的类型为<java class 'java.lang.Short'>,值为:32767 字段 3 integer 的类型为<java class 'java.lang.Integer'>,值为:2147483647 字段 4 int8 的类型为<java class 'java.lang.Long'>,值为:9223372036854775807 字段 5 bigint 的类型为<java class 'java.lang.Long'>,值为:9223372036854775807 字段 6 smallfloat 的类型为<java class 'java.lang.Float'>,值为:1.2345679395506094e+19 # smallfloat最大仅7位有效数字 字段 7 float 的类型为<java class 'java.lang.Double'>,值为:1.2345678901234567e+19 # float最大仅16位有效数字 字段 8 decimal(32,20) 的类型为<java class 'JLong'>,值为:123456789012.12346 # 保留17数 字段 9 decimal(32) 的类型为<java class 'JLong'>,值为:9223372036854775807 # decimal(p)小于int8上限时正常 字段10 decimal(32) 的类型为<java class 'JLong'>,值为:-9223372036854775616 # decimal(p)大于int8上限时越界 字段11 money(32,20) 的类型为<java class 'JDouble'>,值为:123456789012.12346 # 保留17位数 |
3, 日期时间型
日期型:date
日期时间型:datetime [first to last], timestamp(n)
间隔类型:interval [first to list]
日期时间带时区型:TIMESTAMP(n) WITH TIME ZONE
参考代码:test03_datetime_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | drop = "drop table if exists tab_datetime" cursor.execute(drop) create = """ create table tab_datetime( i_serial serial not null, t_date date, t_dt_y2d datetime year to day, t_dt_y2s datetime year to second, t_dt_y2f datetime year to fraction, t_dt_y2f1 datetime year to fraction(1), t_dt_y2f2 datetime year to fraction(2), t_dt_y2f3 datetime year to fraction(3), t_dt_y2f4 datetime year to fraction(4), t_dt_y2f5 datetime year to fraction(5), t_it_d2d interval day to day, t_it_d2h interval day to hour, t_it_d2m interval day to minute, t_it_d2s interval day to second, t_ts timestamp, t_ts0 timestamp(0), t_ts1 timestamp(1), t_ts2 timestamp(2), t_ts3 timestamp(3), t_ts4 timestamp(4), t_ts5 timestamp(5), t_ts6 timestamp(6), t_tz timestamp with time zone, t_tz0 timestamp(0) with time zone, t_tz1 timestamp(1) with time zone, t_tz2 timestamp(2) with time zone, t_tz3 timestamp(3) with time zone, t_tz4 timestamp(4) with time zone, t_tz5 timestamp(5) with time zone, t_tz6 timestamp(6) with time zone ) """ cursor.execute(create) insert = """ insert into tab_datetime(i_serial,t_date,t_dt_y2d,t_dt_y2s,t_dt_y2f, t_dt_y2f1,t_dt_y2f2,t_dt_y2f3,t_dt_y2f4,t_dt_y2f5, t_it_d2d,t_it_d2h,t_it_d2m,t_it_d2s, t_ts,t_ts0,t_ts1,t_ts2,t_ts3,t_ts4,t_ts5,t_ts6, t_tz,t_tz0,t_tz1,t_tz2,t_tz3,t_tz4,t_tz5,t_tz6 ) values(0,?,?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?) """ cursor.execute(insert,( '2025-03-26' , '2025-03-26' , '2025-03-26 14:15:16' , '2025-03-26 14:15:16.123' , '2025-03-26 14:15:16.1' , '2025-03-26 14:15:16.12' , '2025-03-26 14:15:16.123' , '2025-03-26 14:15:16.1234' , '2025-03-26 14:15:16.12345' , '98' , '98 12' , '98 12:34' , '98 12:34:56' , '2025-03-26 14:15:16.123456' , '2025-03-26 14:15:16' , '2025-03-26 14:15:16.1' , '2025-03-26 14:15:16.12' , '2025-03-26 14:15:16.123' , '2025-03-26 14:15:16.1234' , '2025-03-26 14:15:16.12345' , '2025-03-26 14:15:16.123456' , '2025-03-26 14:15:16 +08:00' , '2025-03-26 14:15:16 +08:00' , '2025-03-26 14:15:16.1 +08:00' , '2025-03-26 14:15:16.12 +08:00' , '2025-03-26 14:15:16.123 +08:00' , '2025-03-26 14:15:16.1234 +08:00' , '2025-03-26 14:15:16.12345 +08:00' , '2025-03-26 14:15:16.123456 +08:00' )) select = "select * from tab_datetime" cursor.execute(select) rows = cursor.fetchall() # print(rows) for row in rows: print ( "字段 1 i_serial 的类型为{},值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) print ( "字段 2 i_date 的类型为{},值为:{}" . format ( str ( type (row[ 1 ])),row[ 1 ])) print ( "字段 3 t_dt_y2d 的类型为{},值为:{}" . format ( str ( type (row[ 2 ])),row[ 2 ])) print ( "字段 4 t_dt_y2s 的类型为{},值为:{}" . format ( str ( type (row[ 3 ])),row[ 3 ])) print ( "字段 5 t_dt_y2f 的类型为{},值为:{}" . format ( str ( type (row[ 4 ])),row[ 4 ])) print ( "字段 6 t_dt_y2f1的类型为{},值为:{}" . format ( str ( type (row[ 5 ])),row[ 5 ])) print ( "字段 7 t_dt_y2f2的类型为{},值为:{}" . format ( str ( type (row[ 6 ])),row[ 6 ])) print ( "字段 8 t_dt_y2f3的类型为{},值为:{}" . format ( str ( type (row[ 7 ])),row[ 7 ])) print ( "字段 9 t_dt_y2f4的类型为{},值为:{}" . format ( str ( type (row[ 8 ])),row[ 8 ])) print ( "字段10 t_dt_y2f5的类型为{},值为:{}" . format ( str ( type (row[ 9 ])),row[ 9 ])) print ( "字段11 t_it_d2d 的类型为{},值为:{}" . format ( str ( type (row[ 10 ])),row[ 10 ])) print ( "字段12 t_it_d2h 的类型为{},值为:{}" . format ( str ( type (row[ 11 ])),row[ 11 ])) print ( "字段13 t_it_d2m 的类型为{},值为:{}" . format ( str ( type (row[ 12 ])),row[ 12 ])) print ( "字段14 t_it_d2s 的类型为{},值为:{}" . format ( str ( type (row[ 13 ])),row[ 13 ])) print ( "字段15 t_ts 的类型为{},值为:{}" . format ( str ( type (row[ 14 ])),row[ 14 ])) print ( "字段16 t_ts0 的类型为{},值为:{}" . format ( str ( type (row[ 15 ])),row[ 15 ])) print ( "字段17 t_ts1 的类型为{},值为:{}" . format ( str ( type (row[ 16 ])),row[ 16 ])) print ( "字段18 t_ts2 的类型为{},值为:{}" . format ( str ( type (row[ 17 ])),row[ 17 ])) print ( "字段19 t_ts3 的类型为{},值为:{}" . format ( str ( type (row[ 18 ])),row[ 18 ])) print ( "字段20 t_ts4 的类型为{},值为:{}" . format ( str ( type (row[ 19 ])),row[ 19 ])) print ( "字段21 t_ts5 的类型为{},值为:{}" . format ( str ( type (row[ 20 ])),row[ 20 ])) print ( "字段22 t_ts6 的类型为{},值为:{}" . format ( str ( type (row[ 21 ])),row[ 21 ])) print ( "字段23 t_tz 的类型为{},值为:{}" . format ( str ( type (row[ 22 ])),row[ 22 ])) print ( "字段24 t_tz0 的类型为{},值为:{}" . format ( str ( type (row[ 23 ])),row[ 23 ])) print ( "字段25 t_tz1 的类型为{},值为:{}" . format ( str ( type (row[ 24 ])),row[ 24 ])) print ( "字段26 t_tz2 的类型为{},值为:{}" . format ( str ( type (row[ 25 ])),row[ 25 ])) print ( "字段27 t_tz3 的类型为{},值为:{}" . format ( str ( type (row[ 26 ])),row[ 26 ])) print ( "字段28 t_tz4 的类型为{},值为:{}" . format ( str ( type (row[ 27 ])),row[ 27 ])) print ( "字段29 t_tz5 的类型为{},值为:{}" . format ( str ( type (row[ 28 ])),row[ 28 ])) print ( "字段30 t_tz6 的类型为{},值为:{}" . format ( str ( type (row[ 29 ])),row[ 29 ])) |
输出结果及说明:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 字段 1 i_serial 的类型为<java class 'java.lang.Integer'>,值为:1 字段 2 i_date 的类型为<class 'str'>,值为:2025-03-26 字段 3 t_dt_y2d 的类型为<class 'str'>,值为:2025-03-26 00:00:00 字段 4 t_dt_y2s 的类型为<class 'str'>,值为:2025-03-26 14:15:16 字段 5 t_dt_y2f 的类型为<class 'str'>,值为:2025-03-26 14:15:16.123000 # 输出6位fraction的字符型 字段 6 t_dt_y2f1的类型为<class 'str'>,值为:2025-03-26 14:15:16.100000 字段 7 t_dt_y2f2的类型为<class 'str'>,值为:2025-03-26 14:15:16.120000 字段 8 t_dt_y2f3的类型为<class 'str'>,值为:2025-03-26 14:15:16.123000 字段 9 t_dt_y2f4的类型为<class 'str'>,值为:2025-03-26 14:15:16.123400 字段10 t_dt_y2f5的类型为<class 'str'>,值为:2025-03-26 14:15:16.123450 字段11 t_it_d2d 的类型为<java class 'com.gbasedbt.lang.IntervalDF'>,值为:98 # IntervalDF 字段12 t_it_d2h 的类型为<java class 'com.gbasedbt.lang.IntervalDF'>,值为:98 12 字段13 t_it_d2m 的类型为<java class 'com.gbasedbt.lang.IntervalDF'>,值为:98 12:34 字段14 t_it_d2s 的类型为<java class 'com.gbasedbt.lang.IntervalDF'>,值为:98 12:34:56 字段15 t_ts 的类型为<class 'str'>,值为:2025-03-26 14:15:16.123450 # 同datetime类型 字段16 t_ts0 的类型为<class 'str'>,值为:2025-03-26 14:15:16 字段17 t_ts1 的类型为<class 'str'>,值为:2025-03-26 14:15:16.100000 字段18 t_ts2 的类型为<class 'str'>,值为:2025-03-26 14:15:16.120000 字段19 t_ts3 的类型为<class 'str'>,值为:2025-03-26 14:15:16.123000 字段20 t_ts4 的类型为<class 'str'>,值为:2025-03-26 14:15:16.123400 字段21 t_ts5 的类型为<class 'str'>,值为:2025-03-26 14:15:16.123450 字段22 t_ts6 的类型为<class 'str'>,值为:2025-03-26 14:15:16.123450 字段23 t_tz 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.0 # timestamp with time zone的类型是Timestamp 字段24 t_tz0 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.0 字段25 t_tz1 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.1 字段26 t_tz2 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.12 字段27 t_tz3 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.123 字段28 t_tz4 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.1234 字段29 t_tz5 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.12345 字段30 t_tz6 的类型为<java class 'java.sql.Timestamp'>,值为:2025-03-26 14:15:16.12345 |
4, 布尔类型
布尔类型:boolean
取值范围:'t'/1, 'f'/0, null(None)
参考代码:test04_boolean_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | drop = "drop table if exists tab_boolean" cursor.execute(drop) create = """ create table tab_boolean( i_serial serial not null, b_boolean boolean ) """ cursor.execute(create) insert = """ insert into tab_boolean(i_serial,b_boolean) values(0,?) """ cursor.execute(insert,( True ,)) cursor.execute(insert,( False ,)) cursor.execute(insert,( None ,)) select = "select * from tab_boolean" cursor.execute(select) rows = cursor.fetchall() for row in rows : print ( "字段 1 i_serial 的类型为:{},值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) print ( "字段 2 b_boolean的类型为:{},值为:{}" . format ( str ( type (row[ 1 ])),row[ 1 ])) print ("") |
输出结果及说明:
1 2 3 4 5 6 7 8 | 字段 1 i_serial 的类型为:<java class 'java.lang.Integer'>,值为:1 字段 2 b_boolean的类型为:<java class 'java.lang.Boolean'>,值为:True 字段 1 i_serial 的类型为:<java class 'java.lang.Integer'>,值为:2 字段 2 b_boolean的类型为:<java class 'java.lang.Boolean'>,值为:False 字段 1 i_serial 的类型为:<java class 'java.lang.Integer'>,值为:3 字段 2 b_boolean的类型为:<class 'NoneType'>,值为:None |
5, 扩展类型LIST
LIST类型:list(TYPE not null)
参考代码:test05_list_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | drop = "drop table if exists tab_list" cursor.execute(drop) create = """ create table tab_list( i_serial serial not null, l_list LIST(varchar(20) not null) ) """ cursor.execute(create) insert = """ insert into tab_list(i_serial,l_list) values(0,?) """ l_list_list = "LIST{'aaaa','bbbb','cccc'}" cursor.execute(insert, (l_list_list,)) select = "select * from tab_list" cursor.execute(select) rows = cursor.fetchall() for row in rows: print ( "字段 1 i_serial 的类型为{} ,值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) print ( "字段 2 l_list 的类型为{} ,值为:{}" . format ( str ( type (row[ 1 ])),row[ 1 ])) |
输出结果及说明:
1 2 | 字段 1 i_serial 的类型为<java class 'java.lang.Integer'> ,值为:1 字段 2 l_list 的类型为<java class 'java.util.ArrayList'> ,值为:[aaaa, bbbb, cccc] # 结果为arraylist |
6, JSON和BSON类型
JSON类型:以字符形式存储
BSON类型:可以以字符形式或者二进制形式存储
注:bson类型需要转换
参考代码:test06_json_bson_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | drop = "drop table if exists tab_json_bson" cursor.execute(drop) create = """ create table tab_json_bson( i_serial serial not null, b_bson bson, j_json json ) """ cursor.execute(create) insert = "insert into tab_json_bson(i_serial,b_bson,j_json) values(0,?,?)" b_bson_bson = "bson类型输入" j_json_json = "json类型输入" cursor.execute(insert,(b_bson_bson,j_json_json)) select = "select * from tab_json_bson" cursor.execute(select) rows = cursor.fetchall() for row in rows: print ( "字段1 i_serial的类型为:{}, 值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) # bson的格式为IfxBSONObject, 可转换为byte[],再转换为文本(视需要) print ( "字段2 b_bson 的类型为:{}, 值为:{}" . format ( str ( type (row[ 1 ])), str (row[ 1 ].getBytes(), 'UTF-8' ))) print ( "字段3 j_json 的类型为:{}, 值为:{}" . format ( str ( type (row[ 2 ])),row[ 2 ])) |
输出结果及说明:
1 2 3 | 字段1 i_serial的类型为:<java class 'java.lang.Integer'>, 值为:1 字段2 b_bson 的类型为:<java class 'com.gbasedbt.jdbc.IfxBSONObject'>, 值为:bson类型输入 # 需要转换,使用驱动里的类进行转换 字段3 j_json 的类型为:<class 'str'>, 值为:json类型输入 |
7, 大对象类型
包含:
text和byte
注:byte需要额外处理
参考代码:test07_byte_text_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | drop = "drop table if exists tab_lob" cursor.execute(drop) create = """ create table tab_lob( i_serial8 serial8 not null, lob_byte byte, lob_text text ) """ cursor.execute(create) insert = "insert into tab_lob(i_serial8,lob_byte,lob_text) values(0,?,?)" # 二进制类型 # lob_byte = bytes('byte类型输入', encoding='UTF-8') lob_byte = 'byte类型输入' lob_text = 'text类型输入' cursor.execute(insert,(lob_byte,lob_text)) select = "select * from tab_lob" cursor.execute(select) rows = cursor.fetchall() for row in rows: print ( "字段1 i_serial8 的类型为:{}, 值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) print ( "字段2 lob_byte 的类型为:{}, 值为:{}" . format ( str ( type (row[ 1 ])), str (row[ 1 ], 'UTF-8' ))) print ( "字段3 lob_text 的类型为:{}, 值为:{}" . format ( str ( type (row[ 2 ])),row[ 2 ])) |
输出结果及说明:
1 2 3 | 字段1 i_serial8 的类型为:<java class 'java.lang.Long'>, 值为:1 字段2 lob_byte 的类型为:<java class 'byte[]'>, 值为:byte类型输入 字段3 lob_text 的类型为:<class 'str'>, 值为:text类型输入 |
8, 智能大对象类型
包含:
blob和clob
注:两种类型读取时均需额外处理
参考代码:test08_blob_clob_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | drop = "drop table if exists tab_smart_lob" cursor.execute(drop) create = """ create table tab_smart_lob( i_bigserial bigserial not null, slob_blob blob, slob_clob clob ) """ cursor.execute(create) slob_blob = 'blob类型输入,最大支持4TB' slob_clob = 'clob类型输入,最大支持4TB' insert = "insert into tab_smart_lob values(0,?,?)" cursor.execute(insert,(slob_blob,slob_clob)) select = "select * from tab_smart_lob" cursor.execute(select) rows = cursor.fetchall() for row in rows : print ( "字段1 i_bigserial 的类型为:{}, 值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) # 类型为IfxBblob,使用java驱动类的getBytes(); IfxBblob的length()类型为JLong,需要转换成int print ( "字段2 i_bigserial 的类型为:{}, 值为:{}" . format ( str ( type (row[ 1 ])), str (row[ 1 ].getBytes( 1 , int (row[ 1 ].length())), 'UTF-8' ))) # 类型为IfxCblob,使用java驱动类的getSubString(); IfxCblob的length()类型为JLong,需要转换成int print ( "字段3 i_bigserial 的类型为:{}, 值为:{}" . format ( str ( type (row[ 2 ])),row[ 2 ].getSubString( 1 , int (row[ 2 ].length())))) |
输出结果及说明:
1 2 3 | 字段1 i_bigserial 的类型为:<java class 'java.lang.Long'>, 值为:1 字段2 i_bigserial 的类型为:<java class 'com.gbasedbt.jdbc.IfxBblob'>, 值为:blob类型输入,最大支持4TB # getBytes后换需转码 字段3 i_bigserial 的类型为:<java class 'com.gbasedbt.jdbc.IfxCblob'>, 值为:clob类型输入,最大支持4TB # getSubString |
9, RAW类型
包含:
raw
参考代码:test09_raw_type.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | drop = "drop table if exists tab_raw" cursor.execute(drop) create = """ create table tab_raw( i_bigserial bigserial not null, raw_raw raw ) """ cursor.execute(create) # 仅接受16进制数值 raw_raw1 = "63646566" raw_raw2 = "6768696a" insert = "insert into tab_raw values(0,?)" cursor.execute(insert,(raw_raw1,)) cursor.execute(insert,(raw_raw2,)) select = "select * from tab_raw" cursor.execute(select) rows = cursor.fetchall() for row in rows : print ( "字段1 i_bigserial 的类型为:{}, 值为:{}" . format ( str ( type (row[ 0 ])),row[ 0 ])) print ( "字段2 raw_raw 的类型为:{}, 值为:{}" . format ( str ( type (row[ 1 ])),row[ 1 ])) print ("") |
输出结果及说明:
1 2 3 4 5 | 字段1 i_bigserial 的类型为:<java class 'java.lang.Long'>, 值为:1 字段2 raw_raw 的类型为:<java class 'byte[]'>, 值为:63646566 字段1 i_bigserial 的类型为:<java class 'java.lang.Long'>, 值为:2 字段2 raw_raw 的类型为:<java class 'byte[]'>, 值为:6768696A |
- 上一篇: GBase 8s的TRANSLATE() 函数使用说明
- 下一篇: 没有了