Testing SQLAlchemy vs Peewee

Testing SQLAlchemy vs Peewee.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This test is to get some base line comparisons for [SmokeDetector](https://github.com/Charcoal-SE/SmokeDetector) as it prepares to migrate from flat files to SQLite. The project is also picking an ORM library to utilize.\n",
    "\n",
    "Before we get to the tests, I think this [comment from the Peewee author is important](https://www.reddit.com/r/Python/comments/4tnqai/choosing_a_python_ormpeewee_vs_sqlalchemy/d5jyuug/) (posted July 20, 2016):\n",
    "\n",
    "> I'm the author of Peewee so as you might expect, I'm much more familiar with it's strengths and weaknesses. I can say, however, that SQLAlchemy is the gold standard for ORM in the Python world. It has a very active community and a maintainer who is committed to excellence. If you're a glass-half-empty guy, to put it another way, you can't go wrong if you choose SQLAlchemy.\n",
    ">\n",
    "> Why would you choose Peewee, then?\n",
    ">\n",
    "> - It's damn small, and easy to pick up and use. I think this is the #1 reason people use it.\n",
    "> - I've worked very hard to provide consistent APIs, meaning learn once/apply everywhere.\n",
    "> - I fix bugs really quickly.\n",
    "> - Raymond Hettinger said \"The code is nicely written and is easily read start to finish.\" :)\n",
    "> - AsyncIO via [aiopeewee](http://peewee-async.readthedocs.io/en/latest/) / [muffin-peewee](http://muffin.readthedocs.io/en/latest/)\n",
    "> - Admin interface via [flask-admin](https://flask-admin.readthedocs.io/en/latest/)\n",
    "> - Cool [extensions](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html)\n",
    "> - Fast, memory efficient, good performance for an ActiveRecord ORM\n",
    ">\n",
    "> Weaknesses:\n",
    ">\n",
    "> - Pretty much just me working on it, though I do accept patches and work hard to fix bugs quickly.\n",
    "> - Small ecosystem of third-party libraries / integrations\n",
    "> - ActiveRecord as opposed to [Data Mapper / ID Map / Unit-of-work implemented in SQLA](http://techspot.zzzeek.org/2012/02/07/patterns-implemented-by-sqlalchemy/)\n",
    "> - Lower \"google-ability\" factor\n",
    "> - Fewer stackoverflow answers\n",
    "> - No automatic schema migrations\n",
    "> - Doesn't support Oracle or Microsoft SQL Server.\n",
    "> - It's named peewee\n",
    "\n",
    "Lastly, as of this post, there are ~9500 [sqlalchemy](http://stackoverflow.com/questions/tagged/sqlalchemy) posts on Stack Overflow versus ~520 [peewee](http://stackoverflow.com/questions/tagged/peewee) posts.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLAlchemy \n",
    "\n",
    "## Testing:\n",
    "\n",
    " - Insert two settings\n",
    " - Retreive one setting\n",
    " - Insert list of users\n",
    " - Retrieve list of users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Create settings:\n",
      "122 ms ± 12.4 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "Get Setting:\n",
      "480 µs ± 2.45 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "Create Users:\n",
      "180 ms ± 9.32 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "Get group of users:\n",
      "32.6 µs ± 635 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import *\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "engine = create_engine('sqlite:///sqlalchemy.db', echo=False)\n",
    "Base = declarative_base()\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "class Setting(Base):\n",
    "    __tablename__ = 'settings'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    key = Column(String(100))\n",
    "    value = Column(String(500))\n",
    "    description = Column(String(500))\n",
    "\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True)\n",
    "    username = Column(String(100))\n",
    "    blacklisted = Column(Boolean)\n",
    "    \n",
    "Base.metadata.create_all(engine)\n",
    "\n",
    "def create_settings():\n",
    "    session.add(Setting(key=\"metasmoke_key\", value=\"AsuperSecretStringOfCharacters0)9123%$12*()\", description=\"I like keys!\"))\n",
    "    session.add(Setting(key=\"charcoal_only\", value=\"False\", description=\"Only post to charcoal\"))\n",
    "    session.commit()\n",
    "\n",
    "def create_users():\n",
    "    users = []\n",
    "    for x in range(1000):\n",
    "        users.append(User(username=\"User {}\".format(x), blacklisted=x%2))\n",
    "    session.bulk_save_objects(users)\n",
    "    session.commit()\n",
    "    \n",
    "print(\"Create settings:\")\n",
    "%timeit create_settings()\n",
    "print(\"Get Setting:\")\n",
    "%timeit session.query(Setting).filter_by(key='metasmoke_key').first()\n",
    "print(\"Create Users:\")\n",
    "%timeit create_users()\n",
    "print(\"Get group of users:\")\n",
    "%timeit session.query(User).filter_by(blacklisted=True)\n",
    "\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Peewee\n",
    "\n",
    "## Testing:\n",
    "\n",
    " - Insert two settings\n",
    " - Retreive one setting\n",
    " - Insert list of users\n",
    " - Retrieve list of users\n",
    " \n",
    "Note: Bulk inserts are being done using the \"Fastest\" method mentioned in the documentation - http://docs.peewee-orm.com/en/latest/peewee/querying.html#bulk-inserts . It needed to be broken into chunks due to an `OperationalError: too many SQL variables` exception"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Create settings:\n",
      "234 ms ± 32 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "Get Setting:\n",
      "173 µs ± 1.37 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n",
      "Create Users:\n",
      "166 ms ± 16.7 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "Get group of users:\n",
      "199 µs ± 2.62 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n"
     ]
    }
   ],
   "source": [
    "from peewee import *\n",
    "\n",
    "db = SqliteDatabase('peewee.db')\n",
    "        \n",
    "class PWSetting(Model):\n",
    "    id = PrimaryKeyField()\n",
    "    key = CharField(max_length=100)\n",
    "    value = CharField(max_length=500)\n",
    "    description = CharField(max_length=100)\n",
    "    \n",
    "    class Meta:\n",
    "        database = db    \n",
    "\n",
    "class PWUser(Model):    \n",
    "    id = PrimaryKeyField()\n",
    "    username = CharField(max_length=100)\n",
    "    blacklisted = BooleanField()\n",
    "    \n",
    "    class Meta:\n",
    "        database = db\n",
    "\n",
    "\n",
    "PWSetting.create_table(fail_silently=True)\n",
    "PWUser.create_table(fail_silently=True)\n",
    "\n",
    "def create_settings():\n",
    "    PWSetting.create(key=\"metasmoke_key\", value=\"AsuperSecretStringOfCharacters0)9123%$12*()\", description=\"I like keys!\")\n",
    "    PWSetting.create(key=\"charcoal_only\", value=\"False\", description=\"Only post to charcoal\")\n",
    "    \n",
    "def create_users():\n",
    "    users = []\n",
    "    for x in range(1000):\n",
    "        users.append({'username': \"User {}\".format(x), 'blacklisted':x%2})\n",
    "\n",
    "    with db.atomic():\n",
    "        for idx in range(0, len(users), 100):\n",
    "            PWUser.insert_many(users[idx:idx+100]).execute()\n",
    "\n",
    "print(\"Create settings:\")\n",
    "%timeit create_settings()\n",
    "print(\"Get Setting:\")\n",
    "%timeit PWSetting.get(PWSetting.key == 'metasmoke_key')\n",
    "print(\"Create Users:\")\n",
    "%timeit create_users()\n",
    "print(\"Get group of users:\")\n",
    "%timeit PWUser.get(blacklisted=True)\n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>\n",
       "  table {margin-left: 0 !important;}\n",
       "</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<style>\n",
    "  table {margin-left: 0 !important;}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Final Comparison\n",
    "\n",
    "| Test | SQLAlchemy | Peewee |\n",
    "| ---- | ---------- | ------ |\n",
    "| Create two settings | 122 ms | 234 ms |\n",
    "| Get one setting | 480 µs | 173 µs |\n",
    "| Create 1000 users | 180 ms | 166 ms |\n",
    "| Get black listed users | 32.6 µs | 199 µs |\n",
    "| Database filesize | 1428 KB | 1428 KB |"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}