{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-10-15T10:20:01.851973Z",
     "start_time": "2018-10-15T10:20:01.476818Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapper: Mapper|User|users\n",
      "Found user1\n",
      "User('user1','username1', '3833b3a1c69cf71a31d86cb5bb4d3866789b4d1e')\n",
      "SELECT * FROM users WHERE name=?\n",
      "2\n",
      "<class '__main__.User'>\n",
      "user1 username1 3833b3a1c69cf71a31d86cb5bb4d3866789b4d1e\n",
      "<class '__main__.User'>\n",
      "user2 username2 148dfdc3c539d35004cb808ca84e17ff962af744\n",
      "<class 'sqlalchemy.util._collections.result'>\n",
      "username1\n",
      "<class 'sqlalchemy.util._collections.result'>\n",
      "username2\n",
      "查總筆數\n",
      "2\n",
      "where多筆條件\n",
      "1\n",
      "遞增排序\n",
      "<class 'sqlalchemy.util._collections.result'>\n",
      "1\n",
      "<class 'sqlalchemy.util._collections.result'>\n",
      "2\n",
      "遞減排序\n",
      "<class 'sqlalchemy.util._collections.result'>\n",
      "2\n",
      "<class 'sqlalchemy.util._collections.result'>\n",
      "1\n",
      "限制輸出筆數\n",
      "User('user1','username1', '3833b3a1c69cf71a31d86cb5bb4d3866789b4d1e')\n",
      "1\n",
      "User('user2','username2', '148dfdc3c539d35004cb808ca84e17ff962af744')\n",
      "2\n",
      "MultipleResultsFound!\n",
      "MultipleResultsFound!\n",
      "1\n",
      "2\n",
      "刪除\n",
      "Affected rows: 1\n",
      "id 1 not found\n",
      "更新\n",
      "Affected rows: 1\n",
      "20 ysuper\n"
     ]
    }
   ],
   "source": [
    "import hashlib\n",
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import Column, Integer, String\n",
    "from sqlalchemy import ForeignKey\n",
    "from sqlalchemy import desc\n",
    "from sqlalchemy.orm import relationship, backref\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "Base = declarative_base()\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    username = Column(String)\n",
    "    password = Column(String)\n",
    "    def __init__(self, name, username, password):\n",
    "        self.name = name\n",
    "        self.username = username\n",
    "        self.password = hashlib.sha1(password).hexdigest()\n",
    "    def __repr__(self):\n",
    "        return \"User('{}','{}', '{}')\".format(\n",
    "            self.name,\n",
    "            self.username,\n",
    "            self.password\n",
    "        )\n",
    "if __name__ == '__main__':\n",
    "#     engine = create_engine('sqlite:///:memory:', echo=True)\n",
    "    engine = create_engine('sqlite:///ysupertemp.db', echo=False)\n",
    "    Base.metadata.create_all(engine)\n",
    "    auser = User('user1', 'username', 'userpassword'.encode('utf-8'))\n",
    "    print('Mapper:', auser.__mapper__)\n",
    "    Session = sessionmaker(bind=engine)\n",
    "    session = Session()\n",
    "    \n",
    "    user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))\n",
    "    session.add(user_1)\n",
    "    row = session.query(User).filter_by(name='user1').first()\n",
    "    if row:\n",
    "        print('Found user1')\n",
    "        print(row)\n",
    "    else:\n",
    "        print('Can not find user1')\n",
    "#     session.rollback() # 資料庫回到新增 user1 之前的狀態\n",
    "#     row = session.query(User).filter_by(name='user1').first()\n",
    "#     if row:\n",
    "#         print('Found user1 after rollback')\n",
    "#         print(row)\n",
    "#     else:\n",
    "#         print('Can not find user1 after rollback')\n",
    "    user_2 = User('user2', 'username2', 'password_2'.encode('utf-8'))\n",
    "    session.add(user_2)\n",
    "    session.commit()\n",
    "    rows = session.query(User).from_statement('SELECT * FROM users WHERE name=:name').params(name='user2')\n",
    "    print(rows)\n",
    "    for r in rows:\n",
    "        print(r.id)\n",
    "    for r in session.query(User):\n",
    "        print(type(r))\n",
    "        print(r.name, r.username, r.password)\n",
    "    for r in session.query(User.username):\n",
    "        print(type(r))\n",
    "        print(r.username)\n",
    "    print(\"查總筆數\")\n",
    "    print(session.query(User).count())        \n",
    "    print(\"where多筆條件\")\n",
    "    for r in session.query(User.id).filter(User.id==1).filter(User.name=='user1'):\n",
    "        print(r.id)\n",
    "    print(\"遞增排序\")\n",
    "    for r in session.query(User.id).order_by(User.id.asc()):\n",
    "        print(type(r))\n",
    "        print(r.id)        \n",
    "    print(\"遞減排序\")\n",
    "    for r in session.query(User.id).order_by(User.id.desc()):\n",
    "        print(type(r))\n",
    "        print(r.id)\n",
    "    print(\"限制輸出筆數\")\n",
    "    for row in session.query(User)[0:5]: #其中 5 減 0 的數值為 LIMIT 的數值，限制 5 筆；而 0 為 OFFSET 從第 1 筆資料開始查詢。\n",
    "        print(row)\n",
    "        print(row.id)\n",
    "    \n",
    "    # first() 只回傳第一筆結果，即使有多筆結果也只會回傳第一筆\n",
    "    # scalar() 只回傳第一筆結果，查無結果則回傳 None ，若有查詢有多筆結果則會產生例外錯誤\n",
    "    try:\n",
    "        result = session.query(User).scalar()\n",
    "    except sqlalchemy.orm.exc.MultipleResultsFound:\n",
    "        print('MultipleResultsFound!')\n",
    "    else:\n",
    "        if result is None:\n",
    "            print('NoResultFound!')\n",
    "        else:\n",
    "            print(result.id)\n",
    "    # one() 回傳一筆結果，如有多筆或查無結果的情況則會產生例外錯誤\n",
    "    try:\n",
    "        result = session.query(User).one()\n",
    "    except sqlalchemy.orm.exc.NoResultFound:\n",
    "        print('NoResultFound!')\n",
    "    except sqlalchemy.orm.exc.MultipleResultsFound:\n",
    "        print('MultipleResultsFound!')\n",
    "    else:\n",
    "        print(result.id)\n",
    "    # all() 將所有結果匯出成一個 list 回傳\n",
    "    allres = session.query(User).all()\n",
    "    for r in allres:\n",
    "        print(r.id)\n",
    "    print(\"刪除\")\n",
    "    affected_rows = session.query(User).filter_by(id=1).delete()\n",
    "    session.commit()\n",
    "    print('Affected rows:', affected_rows)\n",
    "    if session.query(User).filter_by(id=1).count() == 0:\n",
    "        print('id 1 not found')        \n",
    "    print(\"更新\")\n",
    "    affected_rows = session.query(User).filter_by(id=2).update({'id':20, 'username':\"ysuper\"})\n",
    "    session.commit()\n",
    "    print('Affected rows:', affected_rows)\n",
    "    for r in session.query(User):\n",
    "        print(r.id, r.username)    "
   ]
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}