Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Testing SQLAlchemy vs Peewee
{
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment