批量插入数据

--创建表
create table student(id number primary key,name varchar(200) not null,sex varchar(10),score number,create_date date);
--删除表
drop table student;
--指定表名注释
comment on table student is '学生表';
--指定列注释
comment on column student.id is '学生ID';
comment on column student.name is '学生姓名';
comment on column student.sex is '学生性别';
comment on column student.sex is '学生分数';
comment on column student.create_date is '创建日期';
--查询表
select t.*,rowid from student t;
--创建序列
create sequence student_seq
increment by 1 --每次增加1个
start with 1000000 --从1000000开始计数
nomaxvalue --不设置最大值
nocycle --一直累加，不循环
nocache --不建立缓冲区
--创建触发器
create or replace trigger student_trg
before insert on student
for each row -- WHEN (new.ID is null) 设置主键存在时，不触发触发器
begin
select student_seq.nextval into :new.id from dual;
end;

--插入数据
insert into student(name,sex,score,create_date)values('王芬','女',89.77,to_date('2021-2-19 11:18:24','yyyy-mm-dd hh24:mi:ss'));
--插入已存在的数据
insert into student(name,sex,score,create_date) select name,sex,score,create_date from student;
--将两个字符串的值合并在一起
update student set name=concat('测试',id);

--使用python代码插入数据到oracle数据库

import random
import time
import cx_Oracle as oracle
from config.db_url import DB_URL

db_url = f'{DB_URL}'
print('连接数据库：', db_url)
conn = oracle.connect(db_url)

# 定义游标
cur = conn.cursor()
# 循环次数
count = 0
# 定义数据表的字段名的初始值
sscore =30

while count < 10:
    count +=1
    sname = '测试'+str(count)
    ssex = ['男','女']
    score = sscore
    name = sname
    sex = random.choice(ssex)
    localtime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
    create_date = f'to_date(\'{localtime}\',\'yyyy-mm-dd hh24:mi:ss\')'
    insert_values = (str(name),str(sex),int(score),create_date)
    sql = f'INSERT INTO student (name,sex,score,create_date) values {insert_values}'
    # 去掉sql语句中的双引号
    sql = sql.replace("\"","")
    with conn.cursor() as cursor:
        cursor.execute(sql)
    conn.commit()
    print('条目' + str(count) + "已经创建成功")
print("数据已经插入完成")
conn.close()