05-advanced-python/05.06-sql-databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL 数据库"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Python` 提供了一系列标准的数据库的 API，这里我们介绍 sqlite 数据库的用法，其他的数据库的用法大同小异："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3 as db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "首先我们要建立或者连接到一个数据库上："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "connection = db.connect(\"my_database.sqlite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "不同的数据库有着不同的连接方法，例如 cx-oracle 数据库的链接方式为：\n",
    "\n",
    "    connection = db.connect(username, password, host, port,  'XE')\n",
    "\n",
    "一旦建立连接，我们可以利用它的 `cursor()` 来执行 SQL 语句："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = connection.cursor()\n",
    "cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS orders(\n",
    "        order_id TEXT PRIMARY KEY,\n",
    "        date TEXT,\n",
    "        symbol TEXT,\n",
    "        quantity INTEGER,\n",
    "        price NUMBER)\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO orders VALUES\n",
    "        ('A0001', '2013-12-01', 'AAPL', 1000, 203.4)\"\"\")\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "不过为了安全起见，一般不将数据内容写入字符串再传入，而是使用这样的方式："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "orders = [\n",
    "          (\"A0002\",\"2013-12-01\",\"MSFT\",1500,167.5),\n",
    "          (\"A0003\",\"2013-12-02\",\"GOOG\",1500,167.5)\n",
    "]\n",
    "cursor.executemany(\"\"\"INSERT INTO orders VALUES\n",
    "    (?, ?, ?, ?, ?)\"\"\", orders)\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "cx-oracle 数据库使用不同的方式：\n",
    "\n",
    "    cursor.executemany(\"\"\"INSERT INTO orders VALUES\n",
    "    (:order_id, :date, :symbol, :quantity, :price)\"\"\",\n",
    "    orders)\n",
    "\n",
    "查看支持的数据库格式："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'qmark'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.paramstyle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在 `query` 语句执行之后，我们需要进行 `commit`，否则数据库将不会接受这些变化，如果想撤销某个 `commit`，可以使用 `rollback()` 方法撤销到上一次 `commit()` 的结果：\n",
    "\n",
    "    try:\n",
    "        ... # perform some operations\n",
    "    except:\n",
    "        connection.rollback()\n",
    "        raise\n",
    "    else:\n",
    "        connection.commit()\n",
    "\n",
    "使用 `SELECT` 语句对数据库进行查询："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'A0002', u'2013-12-01', u'MSFT', 1500, 167.5)\n"
     ]
    }
   ],
   "source": [
    "stock = 'MSFT'\n",
    "cursor.execute(\"\"\"SELECT *\n",
    "    FROM orders\n",
    "    WHERE symbol=?\n",
    "    ORDER BY quantity\"\"\", (stock,))\n",
    "for row in cursor:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`cursor.fetchone()` 返回下一条内容， `cursor.fetchall()` 返回所有查询到的内容组成的列表（可能非常大）："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(u'A0001', u'2013-12-01', u'AAPL', 1000, 203.4)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stock = 'AAPL'\n",
    "cursor.execute(\"\"\"SELECT *\n",
    "    FROM orders\n",
    "    WHERE symbol=?\n",
    "    ORDER BY quantity\"\"\", (stock,))\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "关闭数据库："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}