执行SQL语句的多种方式

#!/usr/bin/python3
import xgcondb

## 建立数据库连接，客户端与数据库连接字符集默认GBK，可以设置 UTF8
conn = xgcondb.connect(host="127.0.0.1", port="5138", database="SYSTEM", user="SYSDBA", password="SYSDBA",charset="GBK")
## 创建连接的游标
cur = conn.cursor()
## 设置非自动提交，默认自动提交
conn.autocommit(False)
## 直接执行SQL语句的方式
## DDL语句
cur.execute("create table test2(a bigint,b boolean,c boolean,d varchar);")
## DML语句
cur.execute("insert into test2 values(?,?,?,?);",(234,False,True,None))
conn.commit();
cur.execute("select * from test2;")
try:
    rows = cur.fetchall()
    print(rows)
except Exception as e:
    print(e)
cur.execute("create table test(a int, b varchar(10),c char(100),d datetime,e double,f numeric(18,4));")
cur.execute("insert into test values(1,'xugu','ouguan','2017-05-26',12.5,12323423.3432);")
conn.commit();
cur.execute("insert into test values(%i,'%s','%s','%s',%f,%f);" % (2,'xugu','ouguan','2017-05-26',12.5,12323423.3432))
conn.commit();
## execute() 使用参数形式执行SQL语句
## 备注：目前支持的Python的数据类型包括：整形，字符型，浮点型，boolean，None
sql = "insert into test values(?,?,?,?,?,?);"
#第二个参数表示数据库行数据，建议使用Tuple类型，支持List，不支持dict
cur.execute(sql,(3,'xugu','ouguan','2017-04-27',12.5,12323423.3432))
cur.execute(sql,[3,'xugu','ouguan','2017-06-27',12.5,12323423.3432])
cur.execute(sql,((3,'xugu','ouguan','2019-01-10',13.5,12323423.3432),(3,'xugu','ouguan','2019-01-10',13.6,12323423.3432)))
cur.execute(sql,[(3,'xugu','ouguan','2019-02-10',14.6,12323423.3432),(3,'xugu','ouguan','2019-02-10',14.6,12323423.3432)])
cur.execute(sql,[[3,'xugu','ouguan','2019-03-10',15.7,12323423.3432],[3,'xugu','ouguan','2019-03-10',15.6,12323423.3432]])
cur.execute(sql,([3,'xugu','ouguan','2019-04-10',16.8,12323423.3432],[3,'xugu','ouguan','2019-04-10',16.6,12323423.3432]))
conn.commit();
## executemany() 批量执行,备注：使用方式和支持类型参考execute()，区别：executemany()执行insert时是用preparestatment
rows = ((4,'xugu','ouguan','2017-05-26','12.5','12323423.3432'),('4','xugu','ouguan','2017-05-26','12.5','12323423.3432'))
cur.executemany(sql,rows)
cur.executemany("select * from dual;")
cur.executemany(sql,(5,'xugu','ouguan','2017-07-27',112.5,12323423.3432))
conn.commit()
cur.execute("select * from test;")
try:
    rows = cur.fetchall()
    print(rows)
except Exception as e:
    print(e)
cur.execute("drop table test;")
cur.execute("drop table test2;")
conn.close()