""""
数据库中创建表和序列
create table dt_stat.books(
bookID number(6) not null,
bookName nvarchar2(30) not null,
bookContent nclob,
author nvarchar2(30) not null,
wordQty number(8),
constraint PK_books primary key (bookID)
) LOB(bookContent) STORE AS (tablespace lob_dt) --大字段单独存放表空间
tablespace stat_dt;
create sequence dt_stat.seq_bookID
increment by 1
maxvalue 999999
minvalue 1;
"""
import cx_Oracleas cx
#连接数据库，下面括号里内容根据自己实际情况填写
connection_oracle = cx.connect('books/books5@oracle5')
# 使用cursor()方法获取操作游标
cursor_oracle = connection_oracle.cursor()
#使用execute方法执行SQL语句
"""
insert 单条记录
形式参数方式用 :参数名称1、:参数名称2、:参数名称3。。。
实际参数使用字典定义： {参数名称1:值, 参数名称2:值, 参数名称3:值}
execute方法执行
"""
sql = 'insert into books(bookID, bookName, bookContent, author) values(seq_bookID.nextVal,:bookName,:bookContent,:author)'
book = {'bookName':'cixiquanzhuan','bookContent':'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','author':'高阳'}
cursor_oracle.execute(sql,book)
"""
同时插入多条记录
sql中的参数用:1、:2、:3。。。方式依次排列
实际参数用list方式传入
executemany方式执行
"""
sql ='insert into books(bookID, bookName, bookContent, author) values(seq_bookID.nextVal,:1,:2,:3)'
books = [['cixiq1','aaaaaaaaaaaaaaaaaa1','gaoyan1'],
['cixiqua2','aaaaaaaaaaaaaaaaaa2','gaoyan2'],
['cixiqua3','aaaaaaaaaaaaaaa3','gaoyan3'],
['cixiqu4','aaaaaaaaaaaaa4','gaoyan4'],
['cixiqu5','aaaaaaaaaaaaaaa5','gaoyan5']]
cursor_oracle.executemany(sql,books)
connection_oracle.commit()
#关闭游标和连接
cursor_oracle.close()
connection_oracle.close()
