What is the syntax in python to run a oracle query, and have the results inserted into a new oracle-CodePudding

I am new at python, and am still unsure of all the ins and outs of it. I have managed to create a new table in oracle using python, but now I want to be able to insert 3 attributes from a previously created table.

so basically, my query is currently something simple like:

select attribute1, attribute2, attribute 3 from mytable where qc_code = 'F';

the new table i created also has attribute1, attribute2, attribute 3 as the only attributes in the table.

sorry if this question isn't clear; i guess the main point is, how do i insert results from a query into another table using python.

The reason im using python, is because i plan to add multiple queries, but since im still learning, im just trying to get this one to work.

Thanks.

CodePudding user response：

Don't get the results into python and then put them back into the database as that is inefficient. Just use an INSERT ... SELECT statement and do all the processing in the database:

INSERT INTO other_table (attribute1, attribute2, attribute3)
select attribute1,
       attribute2,
       attribute3
from   mytable
where  qc_code = 'F';

CodePudding user response：

From the documentation of cx_Oracle. It shows how to insert data and query data. So you want to query first, do some python-magic to the data and finally insert it back into the database.

import cx_Oracle

connection = cx_Oracle.connect(
    user="demopython",
    password="XXXXX",
    dsn="localhost/xepdb1")

print("Successfully connected to Oracle Database")

cursor = connection.cursor()

# Create a table

cursor.execute("""
    begin
        execute immediate 'drop table todoitem';
        exception when others then if sqlcode <> -942 then raise; end if;
    end;""")

cursor.execute("""
    create table todoitem (
        id number generated always as identity,
        description varchar2(4000),
        creation_ts timestamp with time zone default current_timestamp,
        done number(1,0),
        primary key (id))""")

# Insert some data

rows = [ ("Task 1", 0 ),
         ("Task 2", 0 ),
         ("Task 3", 1 ),
         ("Task 4", 0 ),
         ("Task 5", 1 ) ]

cursor.executemany("insert into todoitem (description, done) values(:1, :2)", rows)
print(cursor.rowcount, "Rows Inserted")

connection.commit()

# Now query the rows back
for row in cursor.execute('select description, done from todoitem'):
    if (row[1]):
        print(row[0], "is done")
    else:
        print(row[0], "is NOT done")