lecture9.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_file = \"example.db\"\n",
    "if os.path.exists(db_file):\n",
    "    os.remove(db_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(db_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f3d29f70ea0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute('''CREATE TABLE stocks\n",
    "          (date text, trans text, symbol text, qty real, price real)''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f3d29f70ea0>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"\"\"INSERT INTO stocks VALUES \n",
    "          ('2006-01-05','BUY','RHAT',100,35.14)\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f3d29f70ea0>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"\"\"INSERT INTO stocks VALUES \n",
    "          ('2006-02-05','BUY','FED',200,35.24)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)\n",
      "('2006-02-05', 'BUY', 'FED', 200.0, 35.24)\n"
     ]
    }
   ],
   "source": [
    "for item in cursor.execute(\"select * from stocks\"): # 直接用循环遍历查询结果\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)\n"
     ]
    }
   ],
   "source": [
    "# Never do this -- insecure!\n",
    "# symbol = 'RHAT'\n",
    "# cursor.execute(\"SELECT * FROM stocks WHERE symbol = '%s'\" % symbol)\n",
    "\n",
    "# Do this instead\n",
    "t = ('RHAT',)\n",
    "cursor.execute('SELECT * FROM stocks WHERE symbol=?', t) # 使用内置的替换，减少安全风险\n",
    "print(cursor.fetchone())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f3d29f70ea0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 使用executemany执行多条操作\n",
    "# Larger example that inserts many records at a time\n",
    "purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),\n",
    "             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),\n",
    "             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),\n",
    "            ]\n",
    "cursor.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f3d29f70ea0>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 执行脚本\n",
    "sql = \"\"\"create table news (id integer, score integer, title text, href text);\n",
    "insert into news values (1, 8, \"hello world\", \"http://oscar-lab.org\");\n",
    "insert into news values (2, 2, \"hello charlie\", \"http://www.dlut.edu.cn\");\"\"\" # sql语句间要用分号分隔\n",
    "cursor.executescript(sql)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 8, 'hello world', 'http://oscar-lab.org'), (2, 2, 'hello charlie', 'http://www.dlut.edu.cn')]\n"
     ]
    }
   ],
   "source": [
    "print(list(cursor.execute(\"select * from news\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f3d29f72420>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.row_factory = sqlite3.Row\n",
    "c = con.cursor()\n",
    "c.execute('select * from stocks')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = c.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sqlite3.Row"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['date', 'trans', 'symbol', 'qty', 'price']"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2006-01-05'"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r['date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2006-01-05\n",
      "BUY\n",
      "RHAT\n",
      "100.0\n",
      "35.14\n"
     ]
    }
   ],
   "source": [
    "for field in r:\n",
    "    print(field)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}