SA ORM

psql.sql

annotation=> explain (analyze, timing off) select label, value from data limit 10000;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..322.22 rows=10000 width=8) (actual rows=10000 loops=1)
   ->  Seq Scan on data  (cost=0.00..33860.40 rows=1050840 width=8) (actual rows=10000 loops=1)
 Total runtime: 5.994 ms
(3 rows)

Time: 6.801 ms

psycopg2.py

import psycopg2
def pscopg2_select(limit=20):
    conn = psycopg2.connect("dbname=db user=user password=password host=localhost")
    cur = conn.cursor()
    try:
        # Note: In prod, escape SQL queries.
        stmt = f"select value, label from data limit {limit}"
        cur.execute(stmt)
        return cur.fetchall()
    finally:
        cur.close()
        conn.close()

sa_orm.py

def sa_orm(limit=20):
    sess = create_session()
    try:
        return sess.query(Data.value, Data.label).limit(limit).all()
    finally:
        sess.close()

sa_orm_declaration.py

class Data(Base):
    __tablename__ = 'data'

    id = Column(Integer, primary_key=True)
    value = Column(Integer)
    # 0 => Training, 1 => test
    label = Column(Integer, default=0, index=True)
    x = Column(postgresql.ARRAY(Integer))
    y = Column(postgresql.ARRAY(Integer))

sa_select.py

def sa_select(limit=20):
    tbl = Data.__table__
    sess = create_session()
    try:
        stmt = select([tbl.c.value, tbl.c.label]).limit(limit)
        return sess.execute(stmt).fetchall()
    finally:
        sess.close()