ORM (Object Relational Mapper)

This is a comparison betweengorp andsqlalchemy.

Using pq and psycopg2 it creates a bunch of ORM instance objects,then edits them all one by one and then deletes them all. This exampleassumes PostgreSQL and that the table already exists.

It creates X number of "talks" which has the following column types:

• id serial integer
• topic varchar(200)
• when timestamp
• tags array of text
• duration real
  Then lastly it measures how long it takes to do all the inserts, all theupdates and all the deletes.

When running these for 10,000 iterations on my computer I get thefollowing outputs:

$ python orm.py
insert 3.09894585609
edit 30.3197979927
delete 18.6974749565
TOTAL 52.1162188053
$ go run orm.go
insert 2.542336905s
edit 10.28062312s
delete 6.851942699s
TOTAL 19.674902724s

Python

# *- coding: utf-8 -*
import time
import random
import datetime
 
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, DateTime, Sequence
from sqlalchemy.dialects import postgresql
 
HOW_MANY = 1000
 
# import logging
# logging.basicConfig()
# logger = logging.getLogger('sqlalchemy.engine')
# logger.setLevel(logging.INFO)
 
Base = declarative_base()
 
 
class Talk(Base):
    __tablename__ = 'talks'
 
    id = Column(Integer, Sequence('talks_id_seq'), primary_key=True)
    topic = Column(String)
    when = Column(DateTime)
    tags = Column(postgresql.ARRAY(String))
    duration = Column(Float)
 
 
def _random_topic():
    return random.choice((
        u'No talks added yet',
        u"I'm working on a branch of django-mongokit that I "
        "thought you'd like to know about.",
        u'I want to learn Gaelic.',
        u"I'm well, thank you.",
        u' (Kaw uhn KEU-ra shin KAW-la root uh CHOO-nik mee uhn-royer?)',
        u'Chah beh shin KEU-ra, sheh shin moe CHYEH-luh uh vah EEN-tchuh!',
        u'STUH LUH-oom BRISS-kaht-chun goo MAWR',
        u"Suas Leis a' Ghàidhlig! Up with Gaelic!",
        u"Tha mi ag iarraidh briosgaid!",
    ))
 
 
def _random_when():
    return datetime.datetime(random.randint(2000, 2010),
                             random.randint(1, 12),
                             random.randint(1, 28),
                             0, 0, 0)
 
 
def _random_tags():
    tags = [u'one', u'two', u'three', u'four', u'five', u'six',
            u'seven', u'eight', u'nine', u'ten']
    random.shuffle(tags)
    return tags[:random.randint(0, 3)]
 
 
def _random_duration():
    return round(random.random() * 10, 1)
 
 
def run():
    engine = create_engine(
        'postgresql://peterbe:test123@localhost/fastestdb',
        echo=False
    )
 
    Session = sessionmaker(bind=engine)
    session = Session()
 
    session.query(Talk).delete()
 
    t0 = time.time()
    # CREATE ALL
    talks = []
    for i in range(HOW_MANY):
        talk = Talk(
            topic=_random_topic(),
            when=_random_when(),
            duration=_random_duration(),
            tags=_random_tags()
        )
        session.add(talk)
        talks.append(talk)
 
    session.commit()
 
    t1 = time.time()
    # EDIT ALL
 
    for talk in talks:
        talk.topic += "extra"
        talk.duration += 1.0
        talk.when += datetime.timedelta(days=1)
        talk.tags.append("extra")
        session.merge(talk)
 
    session.commit()
    t2 = time.time()
 
    # DELETE EACH
    for talk in talks:
        session.delete(talk)
    session.commit()
    t3 = time.time()
 
    print "insert", t1 - t0
    print "edit", t2 - t1
    print "delete", t3 - t2
    print "TOTAL", t3 - t0
 
if __name__ == '__main__':
    run()

Go

package main
 
import (
    "database/sql"
    "errors"
    "fmt"
    "github.com/coopernurse/gorp"
    _ "github.com/lib/pq"
    "log"
    "math/rand"
    // "os"
    "regexp"
    "strings"
    "time"
)
 
type StringSlice []string
 
// Implements sql.Scanner for the String slice type
// Scanners take the database value (in this case as a byte slice)
// and sets the value of the type.  Here we cast to a string and
// do a regexp based parse
func (s *StringSlice) Scan(src interface{}) error {
    asBytes, ok := src.([]byte)
    if !ok {
        return error(errors.New("Scan source was not []bytes"))
    }
 
    asString := string(asBytes)
    parsed := parseArray(asString)
    (*s) = StringSlice(parsed)
 
    return nil
}
 
func ToArray(str []string) string {
    L := len(str)
    out := "{"
    for i, s := range str {
        out += "\"" + s + "\""
        if i+1 < L {
            out += ","
        }
    }
    out += "}"
 
    return out
}
 
// construct a regexp to extract values:
var (
    // unquoted array values must not contain: (" , \ { } whitespace NULL)
    // and must be at least one char
    unquotedChar  = `[^",\\{}\s(NULL)]`
    unquotedValue = fmt.Sprintf("(%s)+", unquotedChar)
 
    // quoted array values are surrounded by double quotes, can be any
    // character except " or \, which must be backslash escaped:
    quotedChar  = `[^"\\]|\\"|\\\\`
    quotedValue = fmt.Sprintf("\"(%s)*\"", quotedChar)
 
    // an array value may be either quoted or unquoted:
    arrayValue = fmt.Sprintf("(?P<value>(%s|%s))", unquotedValue, quotedValue)
 
    // Array values are separated with a comma IF there is more than one value:
    arrayExp = regexp.MustCompile(fmt.Sprintf("((%s)(,)?)", arrayValue))
 
    valueIndex int
)
 
// Find the index of the 'value' named expression
func init() {
    for i, subexp := range arrayExp.SubexpNames() {
        if subexp == "value" {
            valueIndex = i
            break
        }
    }
}
 
// Parse the output string from the array type.
// Regex used: (((?P<value>(([^",\\{}\s(NULL)])+|"([^"\\]|\\"|\\\\)*")))(,)?)
func parseArray(array string) []string {
    results := make([]string, 0)
    matches := arrayExp.FindAllStringSubmatch(array, -1)
    for _, match := range matches {
        s := match[valueIndex]
        // the string _might_ be wrapped in quotes, so trim them:
        s = strings.Trim(s, "\"")
        results = append(results, s)
    }
    return results
}
 
const HOW_MANY = 1000
 
func random_topic() string {
    topics := []string{
        "No talks added yet",
        "I'm working on a branch of django-mongokit that I thought you'd like to know about.",
        "I want to learn Gaelic.",
        "I'm well, thank you.",
        "(Kaw uhn KEU-ra shin KAW-la root uh CHOO-nik mee uhn-royer?)",
        "Chah beh shin KEU-ra, sheh shin moe CHYEH-luh uh vah EEN-tchuh!",
        "STUH LUH-oom BRISS-kaht-chun goo MAWR",
        "Suas Leis a' Ghàidhlig! Up with Gaelic!",
        "Tha mi ag iarraidh briosgaid!",
    }
 
    return topics[rand.Intn(len(topics))]
}
 
func random_when() time.Time {
    return time.Date(
        2000+rand.Intn(10),
        time.November,
        rand.Intn(12),
        rand.Intn(28),
        0, 0, 0, time.UTC)
}
 
func random_tags() []string {
    tags := []string{
        "one",
        "two",
        "three",
        "four",
        "five",
        "six",
        "seven",
        "eight",
        "nine",
        "ten",
    }
    return tags[:rand.Intn(4)]
}
 
func random_duration() float64 {
    return rand.Float64() * 10
}
 
func main() {
    dbmap := initDb()
    defer dbmap.Db.Close()
 
    // alter sequence talks_id_seq restart with 1;
 
    err := dbmap.TruncateTables()
    checkErr(err, "TruncateTables failed")
 
    // dbmap.TraceOn("[gorp]", log.New(os.Stdout, "myapp:", log.Lmicroseconds))
 
    t0 := time.Now()
    var talks [HOW_MANY]Talk
 
    trans, err := dbmap.Begin()
    if err != nil {
        panic(err)
    }
    // CREATE
    for i := 0; i < HOW_MANY; i++ {
        topic := random_topic()
        when := random_when()
        tags := random_tags()
        duration := random_duration()
 
        talk := Talk{
            Topic:    topic,
            When:     when,
            Tags:     ToArray(tags),
            Duration: duration,
        }
 
        err = dbmap.Insert(&talk)
        checkErr(err, "Insert failed")
        talks[i] = talk
 
    }
 
    trans.Commit()
    t1 := time.Since(t0)
    t0 = time.Now()
 
    trans, err = dbmap.Begin()
    if err != nil {
        panic(err)
    }
 
    // EDIT ALL
    for _, talk := range talks {
 
        talk.Topic += "extra"
        talk.Duration += 1.0
        talk.When = talk.When.Add(time.Hour * 24)
        tags := parseArray(talk.Tags)
        talk.Tags = ToArray(append(tags, "extra"))
 
        _, err := dbmap.Update(&talk)
        checkErr(err, "Update failed")
    }
 
    trans.Commit()
    t2 := time.Since(t0)
    t0 = time.Now()
 
    trans, err = dbmap.Begin()
    if err != nil {
        panic(err)
    }
 
    // DELETE ALL
    for _, talk := range talks {
        _, err = dbmap.Exec("delete from talks where id=$1", talk.Id)
        checkErr(err, "Delete failed")
    }
 
    trans.Commit()
    t3 := time.Since(t0)
 
    fmt.Println("insert", t1)
    fmt.Println("edit", t2)
    fmt.Println("delete", t3)
    fmt.Println("TOTAL", t1+t2+t3)
 
}
 
type Talk struct {
    // db tag lets you specify the column name
    // if it differs from the struct field
    Id    int64     `db:"id"`
    Topic string    `db:"topic"`
    When  time.Time `db:"when"`
    // Tags    StringSlice
    Tags     string  `db:"tags"`
    Duration float64 `db:"duration"`
}
 
func initDb() *gorp.DbMap {
    // connect to db using standard Go database/sql API
    // use whatever database/sql driver you wish
    db, err := sql.Open("postgres", `
        user=peterbe dbname=fastestdb
        password=test123 sslmode=disable`)
    checkErr(err, "sql.Open failed")
 
    // construct a gorp DbMap
    dbmap := &gorp.DbMap{Db: db, Dialect: gorp.PostgresDialect{}}
 
    // add a table, setting the table name to 'talks' and
    // specifying that the Id property is an auto incrementing PK
    dbmap.AddTableWithName(Talk{}, "talks").SetKeys(true, "Id")
 
    return dbmap
}
 
func checkErr(err error, msg string) {
    if err != nil {
        log.Fatalln(msg, err)
    }
}