testbed.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import ssl\n",
    "import urllib.request\n",
    "import json\n",
    "import re\n",
    "\n",
    "hero_re = re.compile(r'HERO_0(\\d)')\n",
    "\n",
    "url = \"https://api.hearthstonejson.com/v1/latest/enUS/cards.json\"\n",
    "def download_hsjson(url):\n",
    "    context = ssl._create_unverified_context()\n",
    "    req = urllib.request.urlopen(url, context=context)\n",
    "    f = req.read()\n",
    "    with open('cards.json', 'wb') as file:\n",
    "        file.write(f)\n",
    "    return f\n",
    "\n",
    "hero_dict = {9: 'priest', 3: 'rogue', 8: 'mage', 4: 'paladin', 1: 'warrior',\n",
    "             7: 'warlock', 5: 'hunter', 2: 'shaman', 6: 'druid'}\n",
    "hero_dict_names = {v: k for k, v in hero_dict.items()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "raw_data = download_hsjson(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'json' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-3-fbb7c9108390>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'cards.json'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'r'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'utf-8'\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m     \u001b[0mcards_json\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'json' is not defined"
     ]
    }
   ],
   "source": [
    "with open('cards.json', 'r', encoding='utf-8') as f:\n",
    "    cards_json = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mech-Bear-Cat\n",
      "Defender of Argus\n",
      "Deathwing, Dragonlord\n",
      "Far Sight\n",
      "Infested Wolf\n",
      "Bouncing Blade\n",
      "Windspeaker\n",
      "Imp-losion\n",
      "Upgrade!\n",
      "Kel'Thuzad\n"
     ]
    }
   ],
   "source": [
    "for i in range(10):\n",
    "    c = cards_json[i]\n",
    "    print(c['name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path_to_db = 'stats.db'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('cards.json', 'w', encoding='utf-8') as f:\n",
    "    json.dump(sort_keys=True, indent=4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tbl_query = r\"SELECT * FROM sqlite_master WHERE type='table'\"\n",
    "\n",
    "create_cards_table_sql = r\"\"\"CREATE TABLE \"cards\" (\n",
    "\t`id`\tTEXT NOT NULL,\n",
    "\t`name`\tTEXT NOT NULL,\n",
    "\t`rarity`\tTEXT NOT NULL,\n",
    "\t`cost`\tINTEGER NOT NULL,\n",
    "\t`attack`\tINTEGER NOT NULL,\n",
    "\t`health`\tINTEGER NOT NULL,\n",
    "\t`set`\tTEXT,\n",
    "\tPRIMARY KEY(id)\n",
    ")\"\"\"\n",
    "\n",
    "create_hero_table_sql = r\"\"\"CREATE TABLE \"hero\" (\n",
    "\t`id`\tINTEGER NOT NULL UNIQUE,\n",
    "\t`name`\tTEXT NOT NULL,\n",
    "\t`cardid`\tTEXT NOT NULL,\n",
    "\t`class`\tINTEGER NOT NULL,\n",
    "\tPRIMARY KEY(id)\n",
    ")\"\"\"\n",
    "\n",
    "create_deck_table_sql = r\"\"\"CREATE TABLE \"deck\" (\n",
    "\t`id`\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "\t`name`\tTEXT NOT NULL,\n",
    "\t`class`\tINTEGER NOT NULL,\n",
    "\t`tag1`\tINTEGER,\n",
    "\t`tag2`\tINTEGER\n",
    ")\"\"\"\n",
    "\n",
    "create_match_table_sql = r\"\"\"CREATE TABLE \"match\" (\n",
    "\t`id`\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "\t`opponent`\tINTEGER NOT NULL,\n",
    "\t`first`\tINTEGER NOT NULL,\n",
    "\t`won`\tINTEGER NOT NULL,\n",
    "\t`duration`\tINTEGER NOT NULL,\n",
    "\t`date`\tDATETIME NOT NULL,\n",
    "\t`opp_hero`\tINTEGER NOT NULL,\n",
    "\t`player_hero`\tINTEGER NOT NULL,\n",
    "\t`deck`\tINTEGER NOT NULL,\n",
    "\tFOREIGN KEY(`opponent`) REFERENCES `player`(`id`),\n",
    "\tFOREIGN KEY(`opp_hero`) REFERENCES `hero`(`id`),\n",
    "\tFOREIGN KEY(`player_hero`) REFERENCES `hero`(`id`),\n",
    "\tFOREIGN KEY(`deck`) REFERENCES deck(id)\n",
    ")\"\"\"\n",
    "\n",
    "create_player_table_sql = r\"\"\"\n",
    "CREATE TABLE `player` (\n",
    "\t`id`\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "\t`name`\tTEXT NOT NULL,\n",
    "\t`high`\tINTEGER NOT NULL,\n",
    "\t`low`\tINTEGER NOT NULL\n",
    ")\"\"\"\n",
    "\n",
    "create_cards_played_sql = r\"\"\"CREATE TABLE \"card_played\" (\n",
    "\t`id`\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "\t`cardid`\tINTEGER NOT NULL,\n",
    "\t`turn`\tINTEGER NOT NULL,\n",
    "\tFOREIGN KEY(`cardid`) REFERENCES `cards`(`id`)\n",
    ")\"\"\"\n",
    "\n",
    "insert_card_sql = \"\"\"INSERT INTO `cards`(`id`,`name`,'rarity', 'cost',`health`,`attack`,`set`,`collectible`,`type`,`player_class`) \n",
    "VALUES (?,?,?,?,?,?,?,?,?,?);\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def update_card_table(cursor, cards):\n",
    "    for card in cards:\n",
    "        result = cursor.execute(r\"SELECT id from cards WHERE name = ?\", (card['name'],))\n",
    "        row = result.fetchone()\n",
    "        if row is None:\n",
    "            print(card['id'])\n",
    "            m = hero_re.match(card['id'])\n",
    "            if m:\n",
    "                sql_str = 'SELECT * from hero where cardid LIKE ?'\n",
    "                row = cursor.execute(sql_str, (\"%\" + card['id'],)).fetchone()\n",
    "                if row is None:\n",
    "                    sql_str2 = 'INSERT INTO hero (name, cardid, class) VALUES (?,?,?)'\n",
    "                    print('Inserting' + card['name'] )\n",
    "                    cursor.execute(sql_str2, (card['name'], card['id'], m.group(1)))\n",
    "            \n",
    "            tmp = card.get('playerClass', None)\n",
    "            player_class = -1 \n",
    "            if tmp is not None:\n",
    "                player_class = hero_dict_names[tmp.lower()]\n",
    "            \n",
    "            ins = (card['id'], card['name'], card['rarity'], card.get('cost', -1),\n",
    "                  card.get('health', -1),card.get('attack', -1), card['set'], card.get('collectible', -1),\n",
    "                  card['type'], player_class)\n",
    "            \n",
    "            cursor.execute(insert_card_sql, ins)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CS2_041e\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "'rarity'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-a30ff9a0cc95>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrow_factory\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msqlite3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mRow\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mcursor\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mupdate_card_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcards_json\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      5\u001b[0m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-7-e9e4cf2a51f0>\u001b[0m in \u001b[0;36mupdate_card_table\u001b[1;34m(cursor, cards)\u001b[0m\n\u001b[0;32m     19\u001b[0m                 \u001b[0mplayer_class\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mhero_dict_names\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mtmp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlower\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     20\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 21\u001b[1;33m             ins = (card['id'], card['name'], card['rarity'], card.get('cost', -1),\n\u001b[0m\u001b[0;32m     22\u001b[0m                   \u001b[0mcard\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'health'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m-\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcard\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'attack'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m-\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcard\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'set'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcard\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'collectible'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m-\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     23\u001b[0m                   card['type'], player_class)\n",
      "\u001b[1;31mKeyError\u001b[0m: 'rarity'"
     ]
    }
   ],
   "source": [
    "db = sqlite3.connect(path_to_db)\n",
    "db.row_factory = sqlite3.Row\n",
    "cursor = db.cursor()\n",
    "update_card_table(cursor, cards_json)\n",
    "db.commit()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "db = sqlite3.connect(path_to_db)\n",
    "db.row_factory = sqlite3.Row\n",
    "cursor = db.cursor()\n",
    "result = cursor.execute(tbl_query)\n",
    "tables = result.fetchall()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db = sqlite3.connect(path_to_db)\n",
    "db.row_factory = sqlite3.Row\n",
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with open('db_sql.sql', 'r') as f:\n",
    "    cursor.executescript(f.read())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def check_table_structure(tables, **kw):\n",
    "    for table in tables:\n",
    "        name = table['name']\n",
    "        print(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "player\n",
      "sqlite_sequence\n",
      "match\n",
      "hero\n",
      "deck\n",
      "cards\n",
      "card_played\n"
     ]
    }
   ],
   "source": [
    "db.row_factory = sqlite3.Row\n",
    "result = cursor.execute(tbl_query)\n",
    "tables = result.fetchall()\n",
    "check_table_structure(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for card in cards_json:\n",
    "    print(card['name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Column, Integer, String\n",
    "\n",
    "Base = declarative_base()\n",
    "engine = create_engine('sqlite:///alchemy.db', echo=True)\n",
    "\n",
    "class Card(Base):\n",
    "    __tablename__ = 'cards'\n",
    "    id = Column(String, primary_key=True)\n",
    "    name = Column(String)\n",
    "    type = Column(String)\n",
    "    rarity = Column(String)\n",
    "    playerClass = Column(String)\n",
    "    cost = Column(Integer)\n",
    "    health = Column(Integer)\n",
    "    attack = Column(Integer)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"<Card(name='%s', id='%s', type='%s')>\" % (\n",
    "                            self.name, self.id, self.type)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "Session = sessionmaker(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for "
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}