On Tue, Jun 19, 2018 at 8:01 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > On Tue, Jun 19, 2018 at 6:18 AM, naor volkovich <naor200...@gmail.com> wrote: >> I'm getting 1000x slower inserts with sqlalchemy in comparison with plain >> inserts with cx_oracle. >> The issue seems to be only when using the Insert object rather than a plain >> insert sql as a string. >> Inside the function _execute_context, it calls context.pre_exec() which >> inside cx_oracle's pre_exec function, you have the call for the >> self.set_input_sizes that causes the issue... >> Without that set_input_sizes command, the code runs in seconds instead of >> minutes. >> In my example, all the data is from the type Text which in turn become CLOB >> in Oracle. > > so the first suggestion is, use a lengthed String type instead, > because if you are using Text(), that means you want to be able to > store textual data of unlimited size. the cx_Oracle driver makes > decisions based on sending that CLOB which I believe include being > able to handle a very huge amount of text, though the current unit > test suite in SQLAlchemy does not seem to exercise this. It does > produce a failure which is that an empty string value will fail to > persist correctly, it will store it as NULL instead, if you don't use > setinputsizes. > > If you want to use CLOB for real and you want to skip the setinputizes > and hope cx_Oracle does what you want you can do this: > > from sqlalchemy.dialects import oracle > > class OracleText(oracle.CLOB): > def dialect_impl(self, dialect): > return self > > class A(Base): > __tablename__ = 'a' > > id = Column(Integer, primary_key=True) > data = Column(Text().with_variant(OracleText(), "oracle")) > > I would recommend opening an upstream issue at > https://github.com/oracle/python-cx_Oracle/ that binding CLOB is very > slow but that might just be the way it is. Note that I communicate > with Anthony several times a month about changing behaviors of > setinputsizes() as the usage patterns keep getting adjusted so it > might end up there as well.
nevermind I'll put it up > > > > > > >> >> I'm using sqlalchemy 1.2.7 (Not the latest but doesn't seem like there's any >> change in that code in 1.2.8 or in source) and cx_oracle 6.3.1 (latest) >> My Python version is 3.6.x and if it matters, the code that calls the >> sqlalchemy code is pandas 0.23.0 (0.23.1 is latest) but the issue seems to >> be sqlalchemy-related. >> I would love any help with it! For now I copied the execute function without >> that line just to make things faster for now but it would be much better if >> there was a real solution. >> >> -- >> 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.
