SQL 执行

执行 SQL 语句是 Python 应用程序与 神通 数据库通信的主要方式。语句使用 Cursor.execute（） 或 Cursor.executemany（） 方法执行。语句包括查询、数据操作语言 （DML） 和数据定义语言 （DDL）。还可以执行其他一些专业语句。

STPython可用于执行单个语句，一次执行一个。它不会读取 SQL*Plus“.sql”文件。 SQL 语句尾部是否有分号 （“;”）不影响SQL语句执行。

cur.execute("select * from MyTable;")

或

cur.execute("select * from MyTable")

SQL 查询

查询（以 SELECT 或 WITH 开头的语句）只能使用方法 Cursor.execute（） 执行。然后可以迭代行，也可以使用Cursor.fetchone（），Cursor.fetchmany（）或Cursor.fetchall（）之一来读取行，有一个默认类型映射到Python类型。

提示:将用户数据与 SQL 语句进行插值或串联是一种安全风险，会影响性能。请改用绑定变量。例如:

cursor.execute("SELECT * FROM mytab WHERE mycol = '" + myvar + "'")

改成

cursor.execute("SELECT * FROM mytab WHERE mycol = :mybv", mybv=myvar)

获取数据

在 Cursor.execute（）之后，为方便起见，将返回游标。这允许代码循环访问如下行：

cursor = connection.cursor()
for row in cursor.execute("select * from MyTab"):
    print(row)

也可以使用Cursor.fetchone（）方法一次读取一个行：

cur = connection.cursor()
cur.execute("select * from MyTab")
while True:
    row = cur.fetchone()
    if row is None:
        break
    print(row)

如果需要批量处理行，则可以使用方法 Cursor.fetchmany（）。批处理的大小由参数控制，该参数默认为 Cursor.arraysize 的值，Cursor.arraysize 默认为100。

如下例子中，fetchmany将取出3行数据。

cur = connection.cursor()
cur.execute("select * from MyTab")
rows = cur.fetchmany(3)
print(rows)

例子: fetchmany一次取出10条，循环取完所有数据

cur = connection.cursor()
cur.execute("select * from MyTable")
num_rows = 10
while True:
    rows = cur.fetchmany(num_rows)
    if not rows:
        break
    for row in rows:
        print(row)

如果需要读取所有行，并且可以包含在内存中，则可以使用方法 Cursor.fetchall（）。

cur = connection.cursor()
cur.execute("select * from MyTable")
rows = cur.fetchall()
for row in rows:
    print(row)

提取方法将数据作为元组返回。

关闭游标

游标可用于执行多个语句。一旦不再需要它，就应该通过调用 close（） 来关闭它，以便回收数据库中的资源。当引用它的变量超出范围时，它将自动关闭（并且不保留进一步的引用）。控制游标生存期的另一种方法是使用“with”块，这可确保在块完成后关闭游标。

例如：

with connection.cursor() as cursor:
    for row in cursor.execute("select * from MyTable"):
        print(row)

此代码确保在块完成后，游标关闭，资源已由数据库回收。此外，任何在块外部使用变量的尝试都将失败。

查询列元数据

执行查询后，可以使用 Cursor.description 获取列元数据，如列名和数据类型：

cur = connection.cursor()
cur.execute("select * from MyTable")
for column in cur.description:
    print(column)

执行上面代码，可能得到如下结果：

('MYCOL', <class 'STPython.STRING'>, 400, 1600, None, None, 1)

数据类型

STPython数据类型与神通数据库数据类型对比如下表:

STPython类型	数据库数据类型	Python类型
STPython.BOOLEAN	SMALLINT	bool
STPython.NUMBER	INT	Int
STPython.NUMBER	FLOAT	Float
STPython.String	VARCHAR(n)	string
STPython.TIME	TIME	time
STPython.NUMBER	NUMERIC	float
STPython.DATETIME	DATE	datetime.date
STPython.DATETIME	DATE	datetime.datetime
STPython.BINARY	BINARY	Bytes
STPython.CLOB	CLOB	string
STPython.BLOB	BLOB	Bytes
STPython.TIMESTAMP	TIMESTAMP	datetime.datetime

STPython定义数据类型时，有些也支持python原生的类型，如int型:

var = cursor.var(int)
var.setvalue(0,5)
print(var)

等同于

var = cursor.var(STPython.NUMBER)
var.setvalue(0,5)
print(var)

支持python原生类型，如datetime类型：

import datetime
var = cursor.var(datetime.date)
var.setvalue(0,datetime.date(2007, 5, 18))
print(var)

使用输出类型处理程序更改提取的数据类型

有时，必须更改从 神通 数据库类型到 Python 类型的默认转换，以防止数据丢失或满足 Python 应用程序的用途。在这种情况下，可以为查询指定输出类型处理程序。输出类型处理程序不会影响从 Cursor.callfunc（） 或 Cursor.callproc（） 返回的值。 可以在连接或游标上指定输出类型处理程序。如果在游标上指定，则仅在该特定游标上更改读取类型处理。如果在连接上指定，则该连接创建的所有游标都将更改其读取类型处理。 输出类型处理程序应为具有以下签名的函数：

handler(cursor, name, defaultType, size, precision, scale)

这些参数与在 Cursor.description 中找到的查询列元数据的信息相同。该函数为要提取的每一列调用一次。该函数应返回一个变量对象（通常通过调用 Cursor.var（））或值 。该值指示应使用默认类型。handler分为inputtypehandler和outputtypehandler，inputtypehandler和outputtypehandler不自定义情况下默认都为None。

如下例子，使用outputtypehandler 确保在获取某些数字时不会丢失数值精度:

import decimal

def number_to_decimal(cursor, name, default_type, size, precision, scale):
    if default_type == STPython.NUMBER:
        return cursor.var(decimal.Decimal, arraysize=cursor.arraysize)
cur = connection.cursor()
cur.outputtypehandler = number_to_decimal
cur.execute("select * from test_float")
val, = cur.fetchone()
print(val, "* 3 =", val * 3)

例子: 定义Building类、input_type_handler和output_type_handler：

class Building:
    def __init__(self, building_id, description, num_floors, date_built):
        self.building_id = building_id
        self.description = description
        self.num_floors = num_floors
        self.date_built = date_built
    def __repr__(self):
        return "<Building %s: %s>" % (self.building_id, self.description)

def input_type_handler(cursor, value, num_elements):
    if isinstance(value, Building):
        return cursor.var(obj_type, arraysize=num_elements,
                          inconverter=building_in_converter)
def output_type_handler(cursor, name, default_type, size, precision, scale):
        return cursor.var(obj_type, arraysize=cursor.arraysize,
                          outconverter=building_out_converter)

自定义inputtypehandler ：

buildings = [
    Building(1, "The First Building", 5, datetime.date(2007, 5, 18)),
    Building(2, "The Second Building", 87, datetime.date(2010, 2, 7)),
    Building(3, "The Third Building", 12, datetime.date(2005, 6, 19)),
]
cur = con.cursor()
cur.inputtypehandler = input_type_handler
for building in buildings:
    try:
        cur.execute("insert into TestBuildings values (:1, :2)",
                (building.building_id, building))
    except STPython.DatabaseError as e:
        error, = e.args
        print("CONTEXT:", error.context)
        print("MESSAGE:", error.message)
        raise

自定义outputtypehandler ：

cur = con.cursor()
cur.outputtypehandler = output_type_handler
print("WITH OUTPUT TYPE HANDLER:")
for row in cur.execute("select * from TestBuildings order by BuildingId"):
    print(row)

使用outconverter更改查询结果

def out_converter(value):
    if value is None:
        return ''
    return value
def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type in (STPython.STRING, ):
        return cursor.var(str, size, arraysize=cur.arraysize,
                          outconverter=out_converter)
connection.outputtypehandler = output_type_handler

使用rowfactories更改查询结果

Cursor.rowfactory（）是为从数据库中检索的每一行调用的方法。Cursor.rowfactory（） 方法使用通常从数据库返回的元组调用。该方法可以将元组转换为其他值，并将其返回到应用程序以代替元组。 例如，要将查询的每一行作为字典来获取：

cursor.execute("select * from mytab")
columns = [col[0] for col in cursor.description]
cursor.rowfactory = lambda *args: dict(zip(columns, args))
data = cursor.fetchone()
print(data)

输出为字典格式：

{'MYCOL': 'aaa', 'COLUMN1': 112, 'BOOLEN': 1}

可滚动光标

可滚动游标使应用程序能够向后、向前移动、跳过行以及移动到查询结果集中的特定行。结果集将缓存在数据库服务器上，直到游标关闭。相反，常规游标仅限于向前移动。 可滚动游标是通过在创建游标时设置参数来创建的。方法 Cursor.scroll（） 用于移动到结果集中的不同位置。scrollable=True

cursor = connection.cursor(scrollable=True)
cursor.execute("select * from mytab order by column1")

cursor.scroll(mode="last")
print("LAST ROW:", cursor.fetchone())
cursor.scroll(mode="first")
print("FIRST ROW:", cursor.fetchone())
cursor.scroll(8, mode="absolute")
print("ROW 8:", cursor.fetchone())
cursor.scroll(2)
print("SKIP 6 ROWS:", cursor.fetchone())
cursor.scroll(-4)
print("SKIP BACK 4 ROWS:", cursor.fetchone())

插入和更新语句

SQL 数据操作语言语句 （DML）（如 INSERT 和 UPDATE）可以通过STPython轻松执行。例如：

cursor = connection.cursor()
cursor.execute("insert into MyTab values (:a, :b)", ["Fredico",15])
connection.commit()

不要将用户数据连接或插值到 SQL 语句中。请参阅改用绑定变量。 有关提交和回滚数据更改的最佳做法，请参阅事务管理。 处理多个数据值时，请使用 executemany（） 来提高性能。请参见批处理语句执行和大容量加载 例如：

createSql = """
CREATE TABLE test_rowid
(
    ID integer NOT NULL AUTO_INCREMENT,
    A character varying(50) ,
    B integer ,
    CONSTRAINT test_rowid_PKEY PRIMARY KEY (ID)
)
"""
cursor.execute(createSql)
rows = [
    ["First",  1],
    ["Second", 2],
    ["Third",  3],
    ["Fouth",  4],
    ["Fifth",  5]
]

cursor.executemany("insert into test_rowid(a,b) values(:1,:2)",rows)
connection.commit()

获取插入数据的lastInsertId

createSql = """
CREATE TABLE test_rowid
(
    ID integer NOT NULL AUTO_INCREMENT,
    A character varying(50) ,
    B integer ,
    CONSTRAINT test_rowid_PKEY PRIMARY KEY (ID)
)
"""
cursor.execute("insert into test_rowid(a,b) values(:1,:2)",["First", 1])
print(cursor.lastrowid)

输出: 自增id的值

PL/SQL 存储过程

Cursor.callproc（） 方法用于调用 PL/SQL 过程。
cursor = connection.cursor()

proc = """
create or replace procedure myproc (
    a_Value1                            number,
    a_Value2                            out number
) as
begin
    a_Value2 := a_Value1 * 2;
end;
"""
cursor.execute(proc)

out_val = cursor.var(int)
cursor.callproc('myproc', [123, out_val])
print(out_val.getvalue())

调用 Cursor.callproc（） 实际上会生成一个匿名的 PL/SQL 块，cursor.callproc('myproc', [123, out_val])相当于：

cursor.execute("begin myproc(:1,:2); end;", [123, out_val])

有关绑定的信息，请参阅使用绑定变量。

PL/SQL 存储函数

Cursor.callfunc（） 方法用于调用 PL/SQL 函数。 callfunc（） 的参数应为 Python 类型、STPython类型之一或对象类型

myfunc= """
create or replace function myfunc (
    a_StrVal varchar2,
    a_NumVal number
) return number as
begin
    return length(a_StrVal) + a_NumVal * 2;
end;
"""
cursor.execute(myfunc)

return_val = cursor.callfunc("myfunc", int, ["a string", 15])
print(return_val)