6.2 Store Data Into Database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('3.14.2',)\n"
     ]
    }
   ],
   "source": [
    "import sqlite3 as lite\n",
    "con = lite.connect('test.sqlite')\n",
    "cur = con.cursor()\n",
    "cur.execute('SELECT SQLITE_VERSION()')\n",
    "data = cur.fetchone()\n",
    "print(data)\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('3.14.2',)\n"
     ]
    }
   ],
   "source": [
    "with lite.connect('test.sqlite') as con:\n",
    "    cur = con.cursor()\n",
    "    cur.execute('SELECT SQLITE_VERSION()')\n",
    "    data = cur.fetchone()\n",
    "    print(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0912173381 United State\n",
      "============================\n",
      "0928375018 Tokyo Japan\n",
      "0957209108 Taipei\n"
     ]
    }
   ],
   "source": [
    "import sqlite3 as lite \n",
    "with lite.connect(\"test.sqlite\") as con: \n",
    "    cur = con.cursor() \n",
    "    cur.execute(\"DROP TABLE IF EXISTS PhoneAddress\") \n",
    "    cur.execute(\"CREATE TABLE PhoneAddress(phone CHAR(10) PRIMARY KEY, address TEXT, name TEXT unique, age INT NOT NULL)\") \n",
    "    cur.execute(\"INSERT INTO PhoneAddress VALUES('0912173381','United State','Jhon Doe',53)\") \n",
    "    cur.execute(\"INSERT INTO PhoneAddress(phone, address, name, age) VALUES('0928375018','Tokyo Japan','MuMu Cat',6)\") \n",
    "    cur.execute(\"INSERT INTO PhoneAddress VALUES('0957209108','Taipei','Richard',29)\") \n",
    "    cur.execute('SELECT * FROM PhoneAddress')\n",
    "    data = cur.fetchone()\n",
    "    print(data[0], data[1])\n",
    "    print('============================')\n",
    "    rows = cur.fetchall()\n",
    "    for item in rows:\n",
    "        print(item[0], item[1])\n",
    "    \n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23</td>\n",
       "      <td>F</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>M</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age gender  name\n",
       "0   23      F  Mary\n",
       "1   33      M  John"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3 as lite\n",
    "import pandas\n",
    "employee = [{'name':'Mary', 'age':23 , 'gender': 'F'},{'name':'John', 'age':33 , 'gender': 'M'}]\n",
    "df = pandas.DataFrame(employee)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with lite.connect('test.sqlite') as db:\n",
    "    df.to_sql(name = 'employee', con = db, if_exists='replace', index = None)"
   ]
  }
 ],
 "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}