Getting a whole object or one object from a column

SQLAlchemy_speed_test.py

from datetime import datetime

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship


engine = create_engine('sqlite:///:memory:')


Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    children = relationship(u'Child', backref="parent",
                            cascade="all, delete, delete-orphan")


Parent.__table__.create(bind=engine)


class Child(Base):
    __tablename__ = "child"

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Parent.id), nullable=False,
                       index=True)


Child.__table__.create(bind=engine)


Session = sessionmaker(bind=engine)
session = Session()

assert not session.query(Parent).count()

p = Parent(name="one")
session.add(p)
session.commit()


def full_object_query():
    start = datetime.now()
    session.query(Parent).filter_by(name="one").one().name
    return (datetime.now() - start).total_seconds()


def column_only_query():
    start = datetime.now()
    session.query(Parent.name).filter_by(name="one").scalar()
    return (datetime.now() - start).total_seconds()


full = 0
column = 0

for i in range(0, 1000):
    full += full_object_query()
    column += column_only_query()

print "full: {}, column: {}".format(full/i, column/i)


# full: 0.000742357357357, column: 0.000597437437437
# full: 0.000837444444444, column: 0.000617333333333
# full: 0.000798385385385, column: 0.000645242242242

# Take with a grain of salt.  This is on a one column object with one row.