Re: [sqlalchemy] How to bulk-insert millions of values into Oracle

[Mike Bayer]

On 09/21/2016 02:08 PM, Seth P wrote:
> The answer to this is probably RTFM, but I can't figure it out.
>
> Suppose I have a declarative model of the form
>
> class MyModel(Model):
> idx = sa.Column(sa.Integer, primary_key=True)
> c1 = sa.Column(sa.Float)
> c2 = sa.Column(sa.Integer)
> ...
> c10 = sa.Column(sa.Float)
>
> And a list of (millions of) tuples of (c1, c2, ..., c10) values, e.g.
> data = [
> (1.1, 1, ..., 1.0),
> (2.9, 2, ...., 2.345),
> ...
> (14312.2, 12412, ..., 1251.15)
> ]
>
> What is the fastest way to insert this data into the database?
>
> With Postgresql I would get a cursor from a raw connection, and call
> cursor.copy_expert("COPY <tablename> (<column names>) FROM STDIN
> (<options>)",
> StringIO(''.join(','.join(row) + '\n' for row in data)))
>
> For other databases, e.g. Oracle, what's the best way to accomplish
> this? I don't see a similar function in cx_Oracle.
> I imagine I should use core, and not the ORM. I've read
> http://docs.sqlalchemy.org/en/rel_1_0/core/dml.html#sqlalchemy.sql.expression.Insert.values,
> but it's not clear to me how to go in this case.


you don't need to use values(). insert() will construct the VALUES
clause automatically from a consistent set of parameters. Basic example
is in the Core Tutorial at
http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements
.



>
> Thanks.
>
> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Seth P]

Thanks. I guess my confusion is that the example at http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements uses an array of dictionaries, not of unlabeled tuples. Meanwhile I ended up using Oracle's sqlldr, which seems to get the job done, though is much more convoluted than Postgresql's COPY FROM...