Pyodbc对GBase 8s数据库的数据类型的支持
Pyodbc对数据类型的支持
常用的数据类型如下：
字符类型
内置字符类型包括：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)
cursor.commit()
insert = "insert into tab_string(col1, col2, col3, col4, col5) values(?,?,?,?,?)"
cursor.execute(insert,['字符字段char类型','本地化字符字段nchar类型','可变长度字符字段varchar类型','本地化可变长度字符字段nvarchar类型','扩展可变长度字符字段lvarchar类型'])
cursor.commit()
select = "select * from tab_string"
cursor.execute(select)
data = cursor.fetchall()
for result in data :
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这样的别名同样支持
注意：bigint/bigserail需要指定转换
参考代码：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)
cursor.commit()
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)这样的别名在部分版本中支持
interval 不支持该类型（不支持读取 ODBC SQL type 109 is not yet supported） ，需要转换
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 minute
)
"""
cursor.execute(create)
cursor.commit()
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'])
cursor.commit()
select = "select col1,col2,col3,col4,col5 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)
cursor.commit()
prepare = """
insert into tab_boolean(col1,col2) values(0,?)
"""
cursor.execute(prepare,(True,))
cursor.execute(prepare,(False,))
cursor.execute(prepare,(None,))
cursor.commit()
select = "select * from tab_boolean"
cursor.execute(select)
result = cursor.fetchall()
for row in result :
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)
cursor.commit()
prepare = """
insert into tab_list(col1,col2) values(0,?)
"""
col2_list = "LIST{'aaaa','bbbb','cccc'}"
cursor.execute(prepare, col2_list)
cursor.commit()
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类型：可以以字符形式或者二进制形式存储
参考代码：test_json_bson_type.py
create = """
create table tab_json_bson(
col1 serial not null,
col2 bson,
col3 json
)
"""
cursor.execute(create)
cursor.commit()
prepare = "insert into tab_json_bson(col1,col2,col3) values(0,?,?)"
col2_bson = "bson类型输入"
col3_json = "json类型输入"
cursor.execute(prepare,[col2_bson,col3_json])
cursor.commit()
select = "select col1,col2,col3 from tab_json_bson"
cursor.execute(select)
while True :
row = cursor.fetchone()
if not row:
break
print("字段1的值为：", row.col1)
print("字段2的值为：", row.col2)
print("字段3的值为：", row.col3)大对象类型
包含：
text和byte
两种类型均使用bytes来处理
参考代码：test_byte_text_type.py
create = """
create table tab_lob(
col1 serial not null,
col2 byte,
col3 text
)
"""
cursor.execute(create)
cursor.commit()
prepare = "insert into tab_lob(col1,col2,col3) values(0,?,?)"
col2_byte = bytes('byte类型输入', encoding='UTF-8')
col3_text = bytes('text类型输入', encoding='UTF-8')
cursor.execute(prepare,[col2_byte,col3_text])
cursor.commit()
select = "select col1,col2,col3 from tab_lob"
cursor.execute(select)
while True :
row = cursor.fetchone()
if not row:
break
print("字段1的值为：", row.col1)
print("字段2的值为：", row.col2.decode())
print("字段3的值为：", row.col3)智能大对象类型
包含：
blob和clob
两种类型均只可以查询，只能使用函数操作插入
clob类型似乎结果集有多余的数据？
参考代码：test_blob_clob_type.py
create = """
create table tab_lob(
col1 serial not null,
col2 blob,
col3 clob
)
"""
cursor.execute(create)
cursor.commit()
blob_str = 'blob类型输入，最大支持4TB'
blob_file = open('blob_file.txt', 'w',encoding='utf-8')
blob_file.write(blob_str)
blob_file.close()
clob_str = 'clob类型输入，最大支持4TB'
clob_file = open('clob_file.txt', 'w',encoding='utf-8')
clob_file.write(clob_str)
clob_file.close()
prepare = "insert into tab_lob(col1,col2,col3) values(0,filetoblob('blob_file.txt','client'),filetoclob('clob_file.txt','client'))"
cursor.execute(prepare)
cursor.commit()
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)
while True :
row = cursor.fetchone()
if not row:
break
print("字段1的值为：", row.col1)
print("字段2的值为：", row.col2.decode())
print("字段3的值为：", row.col3)注意
Pyodbc使用的环境变量示例如下：
export GBASEDBTDIR=/opt/gbase8s-odbc-driver
export CSDK_HOME=/opt/gbase8s-odbc-driver
export PATH=$GBASEDBTDIR/bin:$PATH
export LD_LIBRARY_PATH=$GBASEDBTDIR/lib:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql:$LD_LIBRARY_PATH
export DB_LOCALE=zh_CN.utf8
export CLIENT_LOCALE=zh_CN.utf8
export GL_USEGLU=1
export ODBCINI=/opt/gbase8s-odbc-driver/etc/odbc.ini参考代码：
test_pyodbc.tar.gz
更多Pyodbc的信息参考：https://github.com/mkleehammer/pyodbc/wiki/
- 上一篇: DbtPy对GBase 8s数据库的数据类型的支持
- 下一篇: JayDeBeApi对数据类型的支持