On Mon, May 6, 2019 at 5:12 PM Chris Stephens <cstephen...@gmail.com> wrote: > > 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)
The code you have already does what you need and is specific to cx_Oracle in any case, so I would recommend just doing what you have. If you want to use create_engine(), that's fine, here's how to work with the raw cursor after that point: https://docs.sqlalchemy.org/en/13/core/connections.html#working-with-raw-dbapi-connections There is support for OUT parameters as well, using the isoutparam flag on a bindparameter() object, but it doesn't really support this level of specificity at the moment, I can show you a recipe to make it do what you're doing there, but it would be complicated and it's not clear what advantage it gets you. The point is, you have this very specific thing that i assume you need to run in just one special place, so the clearest / quickest approach is to just do it the way it works. > > #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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
