summaryrefslogtreecommitdiff
path: root/examples/performance/bulk_inserts.py
diff options
context:
space:
mode:
Diffstat (limited to 'examples/performance/bulk_inserts.py')
-rw-r--r--examples/performance/bulk_inserts.py154
1 files changed, 154 insertions, 0 deletions
diff --git a/examples/performance/bulk_inserts.py b/examples/performance/bulk_inserts.py
new file mode 100644
index 000000000..9c3cff5b2
--- /dev/null
+++ b/examples/performance/bulk_inserts.py
@@ -0,0 +1,154 @@
+"""This series of tests illustrates different ways to INSERT a large number
+of rows in bulk.
+
+
+"""
+from . import Profiler
+
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy import Column, Integer, String, create_engine, bindparam
+from sqlalchemy.orm import Session
+
+Base = declarative_base()
+engine = None
+
+
+class Customer(Base):
+ __tablename__ = "customer"
+ id = Column(Integer, primary_key=True)
+ name = Column(String(255))
+ description = Column(String(255))
+
+
+Profiler.init("bulk_inserts", num=100000)
+
+
+@Profiler.setup
+def setup_database(dburl, echo, num):
+ global engine
+ engine = create_engine(dburl, echo=echo)
+ Base.metadata.drop_all(engine)
+ Base.metadata.create_all(engine)
+
+
+@Profiler.profile
+def test_flush_no_pk(n):
+ """Individual INSERT statements via the ORM, calling upon last row id"""
+ session = Session(bind=engine)
+ for chunk in range(0, n, 1000):
+ session.add_all([
+ Customer(
+ name='customer name %d' % i,
+ description='customer description %d' % i)
+ for i in range(chunk, chunk + 1000)
+ ])
+ session.flush()
+ session.commit()
+
+
+@Profiler.profile
+def test_bulk_save_return_pks(n):
+ """Individual INSERT statements in "bulk", but calling upon last row id"""
+ session = Session(bind=engine)
+ session.bulk_save_objects([
+ Customer(
+ name='customer name %d' % i,
+ description='customer description %d' % i
+ )
+ for i in range(n)
+ ], return_defaults=True)
+ session.commit()
+
+
+@Profiler.profile
+def test_flush_pk_given(n):
+ """Batched INSERT statements via the ORM, PKs already defined"""
+ session = Session(bind=engine)
+ for chunk in range(0, n, 1000):
+ session.add_all([
+ Customer(
+ id=i + 1,
+ name='customer name %d' % i,
+ description='customer description %d' % i)
+ for i in range(chunk, chunk + 1000)
+ ])
+ session.flush()
+ session.commit()
+
+
+@Profiler.profile
+def test_bulk_save(n):
+ """Batched INSERT statements via the ORM in "bulk", discarding PKs."""
+ session = Session(bind=engine)
+ session.bulk_save_objects([
+ Customer(
+ name='customer name %d' % i,
+ description='customer description %d' % i
+ )
+ for i in range(n)
+ ])
+ session.commit()
+
+
+@Profiler.profile
+def test_bulk_insert_mappings(n):
+ """Batched INSERT statements via the ORM "bulk", using dictionaries."""
+ session = Session(bind=engine)
+ session.bulk_insert_mappings(Customer, [
+ dict(
+ name='customer name %d' % i,
+ description='customer description %d' % i
+ )
+ for i in range(n)
+ ])
+ session.commit()
+
+
+@Profiler.profile
+def test_core_insert(n):
+ """A single Core INSERT construct inserting mappings in bulk."""
+ conn = engine.connect()
+ conn.execute(
+ Customer.__table__.insert(),
+ [
+ dict(
+ name='customer name %d' % i,
+ description='customer description %d' % i
+ )
+ for i in range(n)
+ ])
+
+
+@Profiler.profile
+def test_dbapi_raw(n):
+ """The DBAPI's API inserting rows in bulk."""
+
+ conn = engine.pool._creator()
+ cursor = conn.cursor()
+ compiled = Customer.__table__.insert().values(
+ name=bindparam('name'),
+ description=bindparam('description')).\
+ compile(dialect=engine.dialect)
+
+ if compiled.positional:
+ args = (
+ ('customer name %d' % i, 'customer description %d' % i)
+ for i in range(n))
+ else:
+ args = (
+ dict(
+ name='customer name %d' % i,
+ description='customer description %d' % i
+ )
+ for i in range(n)
+ )
+
+ cursor.executemany(
+ str(compiled),
+ list(args)
+ )
+ conn.commit()
+ conn.close()
+
+if __name__ == '__main__':
+ Profiler.main()