执行SQL语句并处理结果

向数据库发出执行SQL的请求时，需要使用cursor发出查询，服务端执行完成后将返回执行结果。

执行查询

直接执行

调用conn.cursor()函数获取句柄，调用cursor.execute函数执行插入语句：

cursor = conn.cursor()
cursor.execute("insert into tb_test(id, name) values(1, 'Anna')")

预编译方式执行

调用cursor.execute()，使用占位符表示SQL中的变量，再变量值传入：

cursor = conn.cursor()
name = 'Jack'
cursor.execute("insert into tb_test(id, name) values(2, '%s')" %name)

使用游标获取结果

可以通过以下几种方式获取结果。

遍历游标

该方法将顺序遍历获取结果集中所有结果。

cursor.execute("select * from tb_test")
for record in cursor:
# do something

cur.fetchone()

该方法将获取结果集中下1行结果，如果结果集中没有后续数据，则返回空。

cursor.execute("select * from tb_test")
record = cursor.fetchone()

cur.fetchall()

该方法将一次获取结果集中所有结果。

cursor.execute("select * from tb_test")
records = cursor.fetchall()
for record in records:
# do something

cur.fetchmany([size=fetchsize])

该方法将获取结果集中后续fetchsize行结果，如果不足则返回剩下的所有行，如果结果集中没有后续数据，则返回空。当结果集数量庞大时，建议指定fetchsize分批获取。

cursor.execute("select * from tb_test")
records = cursor.fetchmany(10)
for record in records:
# do something

执行更新

要更改数据(执行一个insert，update或者delete)，可以使用cur.execute函数执行完整的SQL，也可以使用预编译的方式绑定参数后再执行。下面的示例将以预编译的方式执行。

插入

cursor.execute("insert into tb_test(id, name) values(%s, %s)" %(2, 'Taxol'))

更新

cursor.execute("update tb_test set name = %s where id = %s" %('Elsa', 1))

删除

cursor.execute("delete from tb_test where id = 2")

创建或修改数据库对象

要创建、更改或者删除一个类似表或者视图这样的数据库对象，可以调用 cursor.execute() 方法。

cursor.execute("create table tb_test(id integer, name text)")
cursor.execute("create view v_tb_test as select * from tb_test")

执行存储过程

下面的例子展示了如何调用存储过程。

sql_procedure = " CREATE OR REPLACE PROCEDURE pro_test(a integer,inout b integer) as \
                  begin \
                      b := a+b; \
                  end"
cursor.execute(sql_procedure)
cursor.execute("call pro_test(1,5)")
result = cursor.fetchone()

下面的例子展示了如何调用函数。

sql_function = "CREATE OR REPLACE FUNCTION func_test (a int,out b int) RETURNS int AS \
                $$ \
                begin \
                    b := a+2; \
                return ;  \
                end; \
                $$ LANGUAGE 'plpgsql';"
cursor.execute(sql_function)
cursor.execute("select func_test(1)")
result = cursor.fetchone()

处理大数据类型

二进制类型

Vastbase G100提供两种不同的方法存储二进制数据。二进制数据可以使用二进制数据类型 BYTEA存储在表中，或者使用大对象特性以一种特殊的格式将二进制数据存储在一个独立的表中，然后通过在表中保存一个类型为 OID的值来引用该表。以下为这两种方法的使用示例。

使用BYTEA类型存储

1) 创建表

cursor.execute("create table lo_test_tab (id int, img bytea)")

2) 插入数据

with open("/data/test.data", 'rb') as file:
data = file.read()
cursor.execute("insert into  lo_test_tab values(1, %s)" % psycopg2.Binary(data))

3) 读取数据

cursor.execute("select img from lo_test_tab where id = 1")
record = cursor.fetchone()
byteadata = record[0]
print(byteadata.tobytes())

使用OID存储

1) 创建表

cursor.execute("create table lo_test_tab (name text, img oid)")

2) 导入数据

该步骤会将操作系统文件”image”导入成一个大对象并生成一个oid，查询lo_test_tab表时则显示oid。

cursor.execute("insert into  lo_test_tab values('beautiful image', lo_import('/tmp/image'))")

3) 导出数据

该操作将数据库中存储的大对象导出到一个操作系统文件。

cursor.execute("select lo_export(lo_test_tab.img, '/tmp/image-bak') from lo_test_tab where name = 'beautiful image'")

字符类型

Vastbase G100中TEXT类型与VARCHAR类型都是可变长的字符类型，区别在于VARCHAR类型通过VARCHAR(n)中的n来限制最大长度，而TEXT类型没有。TEXT类型与VARCHAR类型几乎没有性能差别，TEXT类型最多可存储1G数据。

使用TEXT类型存储数据时，可用以下方式来进行读写。

1) 创建表

cursor.execute("create table images (id int, msg text)")

2) 插入数据

cursor.execute("insert into  images  values(1, '%s')" %("the text data"))

3) 读取数据

cursor.execute("select msg from images where id = 1")
record = cursor.fetchone()
byteadata = record[0]

VastbaseG100