Python in Science: Fastest Python Database Interface - The Diigo Meta page

• 01 Dec 10
  

  Russ
  • SQLAlchemy's ORM is not designed for bulk insert operations. it issues every INSERT distinctly and also must look at cursor.lastrowid in some cases, and also in some cases will post-fetch information about the row, like defaults that fired off. this is all to deliver rich ORM behavior where your mapped objects remain consistent with what's in the database.
    
    therefore for bulk insert, use insert() statements directly.
  • the reason for the SA slowdown is because the various String/Integer types perform "type processing" on each bind parameter. This is the feature that allows SQLalchemy to encode unicode strings on the fly and to convert datetime() objects into strings for SQLite, for example.
    
    so yes, in this case, SQLAlchemy still adds a lot of overhead to a bulk insert of that size. we can either add some kind of flag somewhere to "disable" the bind parameter processing, or you can use the raw connection provided by SQLAlchemy's engine to bypass this processing (i.e. engine.connect().connection.executemany()). a third option would be more complicated optimization, which would involve checking just the first set of bind params for any types that need converting; if none found, then the rest of the result goes in raw. feel free to add a ticket for this case to trac.
• 25 Nov 09
  

  Tarunn R Pasrija

  sqlalchemy

• 07 Sep 09
  

  xu ling
  • a major performance issue with SQLObject: it was extremely slow for a large number of inserts.
  • So I finally decided to do what I must have done years ago: write a simple benchmark script for all the interfaces I cared about.
    I simply created a table, inserted 100000 short records into the database, and selected them back to check if the insertion operation had been completed. Then I dropped the table. I repeated this operation for all interfaces.

  • I was surprised at a few things (ok, at just about everything!):
    

    • How slow SQLObject really was (not really, I guess I already expected that...)
    • SQLite was not substantially faster with the DB in memory when compared to an "on disk" DB.
    • SQLite is pretty darn fast!
    • MySQL is even Faster!!!
    • PsycoPg2 is not as fast as people say...
    • SQLAlchemy does an excellent job at doing everything it does and not sacrificing performance in the process!

  1 more annotation...

• 20 Jun 09
  

  anarcher

  python benchmarks

• 30 Jun 08
  

  harry palmer

  python programming benchmarks databases mysql postgresql
• 15 Jul 07
  

  Daniele Tricoli

  database performance python programming benchmark

• 

  mariusz w

  python database

• 13 Jul 07
  

  jfunai

  dbms howto orm python

• 12 Jul 07
  

  benpjohnson

  database python performance

• 

  Edgar Muñoz

  python database performance benchmark programming db

• 

  andreys

  python database performance benchmark

Would you like to comment?

Join Diigo for a free account, or sign in if you are already a member.