created by https://github.com/tr3buchet/gister

sqlalchemy - friends.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlalchemy\n",
      "from sqlalchemy import Column\n",
      "from sqlalchemy import Integer\n",
      "from sqlalchemy import String\n",
      "from sqlalchemy import Table\n",
      "from sqlalchemy import ForeignKey\n",
      "from sqlalchemy import UniqueConstraint\n",
      "from sqlalchemy.orm import relationship\n",
      "from sqlalchemy.orm import backref\n",
      "from sqlalchemy.orm import sessionmaker\n",
      "from sqlalchemy.ext.declarative import declarative_base\n",
      "\n",
      "from pprint import pprint\n",
      "\n",
      "Base = declarative_base()\n",
      "db = sqlalchemy.create_engine('sqlite:///:memory:', echo=False)\n",
      "Session = sessionmaker(bind=db)\n",
      "\n",
      "\n",
      "friendship = Table(\n",
      "    'friendships', Base.metadata,\n",
      "    Column('user_id', Integer, ForeignKey('users.id'), index=True),\n",
      "    Column('friend_id', Integer, ForeignKey('users.id')),\n",
      "    UniqueConstraint('user_id', 'friend_id', name='unique_friendships'))\n",
      "\n",
      "\n",
      "class User(Base):\n",
      "    __tablename__ = 'users'\n",
      "\n",
      "    id = Column(Integer, primary_key=True)\n",
      "    name = Column(String(255))\n",
      "\n",
      "    friends = relationship('User',\n",
      "                           secondary=friendship,\n",
      "                           primaryjoin=id==friendship.c.user_id,\n",
      "                           secondaryjoin=id==friendship.c.friend_id)\n",
      "    \n",
      "    def befriend(self, friend):\n",
      "        if friend not in self.friends:\n",
      "            self.friends.append(friend)\n",
      "            friend.friends.append(self)\n",
      "            \n",
      "    def unfriend(self, friend):\n",
      "        if friend in self.friends:\n",
      "            self.friends.remove(friend)\n",
      "            friend.friends.remove(self)\n",
      "    \n",
      "    def __repr__(self):\n",
      "        return '<User(name=|%s|)>' % self.name"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "Base.metadata.create_all(db)\n",
      "session = Session()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a = User(name='a')\n",
      "b = User(name='b')\n",
      "c = User(name='c')\n",
      "d = User(name='d')\n",
      "session.add_all([a, b, c, d])\n",
      "for user in session.query(User):\n",
      "    print user\n",
      "    pprint(user.friends)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "<User(name=|a|)>\n",
        "[]\n",
        "<User(name=|b|)>\n",
        "[]\n",
        "<User(name=|c|)>\n",
        "[]\n",
        "<User(name=|d|)>\n",
        "[]\n"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# a is friends with b and c and d\n",
      "a.befriend(b)\n",
      "a.befriend(c)\n",
      "a.befriend(d)\n",
      "\n",
      "# a is friends with c (idempotent)\n",
      "a.befriend(c)\n",
      "\n",
      "# c is friends with a (idempotent)\n",
      "c.befriend(a)\n",
      "\n",
      "# b is friends with c and d\n",
      "b.befriend(c)\n",
      "b.befriend(d)\n",
      "\n",
      "# now display\n",
      "for user in session.query(User):\n",
      "    print user\n",
      "    pprint(user.friends)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "<User(name=|a|)>\n",
        "[<User(name=|b|)>, <User(name=|c|)>, <User(name=|d|)>]\n",
        "<User(name=|b|)>\n",
        "[<User(name=|a|)>, <User(name=|c|)>, <User(name=|d|)>]\n",
        "<User(name=|c|)>\n",
        "[<User(name=|a|)>, <User(name=|b|)>]\n",
        "<User(name=|d|)>\n",
        "[<User(name=|a|)>, <User(name=|b|)>]\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# now a and d hate each other\n",
      "d.unfriend(a)\n",
      "\n",
      "# now display\n",
      "for user in session.query(User):\n",
      "    print user\n",
      "    pprint(user.friends)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "<User(name=|a|)>\n",
        "[<User(name=|b|)>, <User(name=|c|)>]\n",
        "<User(name=|b|)>\n",
        "[<User(name=|a|)>, <User(name=|c|)>, <User(name=|d|)>]\n",
        "<User(name=|c|)>\n",
        "[<User(name=|a|)>, <User(name=|b|)>]\n",
        "<User(name=|d|)>\n",
        "[<User(name=|b|)>]\n"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print [row for row in db.execute('select * from friendships')]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(2, 1), (2, 3), (2, 4), (1, 2), (1, 3), (4, 2), (3, 1), (3, 2)]\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a.friends"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "[<User(name=|b|)>, <User(name=|c|)>]"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b.friends"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "[<User(name=|a|)>, <User(name=|c|)>, <User(name=|d|)>]"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.friends"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "[<User(name=|a|)>, <User(name=|b|)>]"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "d.friends"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "[<User(name=|b|)>]"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}