SQLAlchemy Demo

sqlalchemy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlalchemy as sa\n",
    "from sqlalchemy.orm import relationship\n",
    "from sqlalchemy.ext.declarative import declarative_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Base1 = declarative_base()\n",
    "Base2 = declarative_base()\n",
    "engine = sa.create_engine('sqlite:///test.sql')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "class Parent(Base1):\n",
    "    __tablename__ = 'parent'\n",
    "    cid = sa.Column(sa.Integer, primary_key=True)\n",
    "    \n",
    "class Child(Base2):\n",
    "    __tablename__ = 'child'\n",
    "    oid = sa.Column(sa.Integer, primary_key=True)\n",
    "    fk_cid = sa.Column(sa.Integer, sa.ForeignKey(Parent.cid))\n",
    "    \n",
    "    core = relationship(lambda: Parent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.orm.session import sessionmaker\n",
    "from sqlalchemy.orm.scoping import scoped_session\n",
    "Session = scoped_session(sessionmaker())\n",
    "Session.configure(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Base1.metadata.create_all(bind=engine)\n",
    "Base2.metadata.create_all(bind=engine)\n",
    "\n",
    "Base1.query = Session.query_property()\n",
    "Base2.query = Session.query_property()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "session = Session(bind=engine)\n",
    "session.query(Parent).delete()\n",
    "session.query(Child).delete()\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# session = Session(bind=engine)\n",
    "dad = Parent(cid=1)\n",
    "mom = Parent(cid=2)\n",
    "boy = Child(oid=1, core=dad)\n",
    "girl = Child(oid=2, core=mom)\n",
    "session.add(dad)\n",
    "session.add(mom)\n",
    "session.add(boy)\n",
    "session.add(girl)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# session = Session(bind=engine)\n",
    "assert session.query(Parent).all() == Parent.query.all()\n",
    "assert Child.query.count() == 2\n",
    "# session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}