Is is possible to implement the following behavior in SQLAlchemy against an
Oracle database? (Bulk inserts returning PK IDs generated by
trigger/sequence on PK column in database)
#database ddl
############
DROP TABLE INS_RET_TEST;
CREATE TABLE INS_RET_TEST
(ID NUMBER NOT NULL ENABLE,
COL1 NUMBER NOT NULL ENABLE,
CONSTRAINT INS_RET_TEST_PK PRIMARY KEY (ID)
USING INDEX);
DROP SEQUENCE INS_RET_TEST_SEQ;
CREATE SEQUENCE INS_RET_TEST_SEQ MINVALUE 1 MAXVALUE 999999999
INCREMENT BY 1 START WITH 1;
CREATE OR REPLACE TRIGGER INS_RET_TEST_TRG
BEFORE INSERT ON INS_RET_TEST
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.ID IS NULL THEN
SELECT INS_RET_TEST_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER INS_RET_TEST_TRG ENABLE;
#Python
######
#!/usr/bin/env python
import cx_Oracle
import time
db = cx_Oracle.connect(dsn="connect_info)
cur = db.cursor()
data = [[1],[2],[3],[4],[5]]
IdArr = cur.var(int, arraysize = len(data))
cur.setinputsizes(None, IdArr)
cur.executemany("insert into ins_ret_test(col1) values (:1) returning id
into :2", data)
for ix, datum in enumerate(data):
print("Data: " , datum[0], "ID: ", IdArr.getvalue(ix))
If so, can someone point me to the documentation or provide an example?
I've been unable to find anything.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
