{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating and Manipulating DataBase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Import Table, Column, String, Integer, Float, Boolean from sqlalchemy.\n",
    "Build a new table called data with columns 'name' (String(255)), 'count' (Integer), 'amount'(Float), and 'valid' (Boolean) columns. The second argument of Table needs to be metadata, which is already initialized.\n",
    "Create the table in the database by passing data to metadata.create_all().\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Import Table, Column, String, Integer, Float, Boolean from sqlalchemy\n",
    "# from sqlalchemy import Table, Column, String, Integer, Float, Boolean\n",
    "\n",
    "# # Define a new table with a name, count, amount, and valid column: data\n",
    "# data = Table('data', metadata,\n",
    "#              Column('name', String(255)),\n",
    "#              Column('count', Integer()),\n",
    "#              Column('amount', Float()),\n",
    "#              Column('valid', Boolean())\n",
    "# )\n",
    "\n",
    "# # Use the metadata to create the table\n",
    "# metadata.create_all(engine)\n",
    "\n",
    "# # Print table repr\n",
    "# print(repr(data))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "-Table, Column, String, Integer, Float, Boolean are already imported from sqlalchemy. - Build a new table called data with a unique name (String), count (Integer) defaulted to 1, amount (Float), and valid (Boolean) defaulted to False. - Hit submit to create the table in the database and to print the table details for data.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Import Table, Column, String, Integer, Float, Boolean from sqlalchemy\n",
    "# from sqlalchemy import Table, Column, String, Integer, Float, Boolean\n",
    "\n",
    "# # Define a new table with a name, count, amount, and valid column: data\n",
    "# data = Table('data', metadata,\n",
    "#              Column('name', String(255), unique=True),\n",
    "#              Column('count', Integer(), default=1),\n",
    "#              Column('amount', Float()),\n",
    "#              Column('valid', Boolean(), default=False)\n",
    "# )\n",
    "\n",
    "# # Use the metadata to create the table\n",
    "# metadata.create_all(engine)\n",
    "\n",
    "# # Print the table details\n",
    "# print(repr(metadata.tables['data']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Import insert and select from the sqlalchemy module.\n",
    "Build an insert statement for the data table to set name to 'Anna', count to 1, amount to 1000.00, and valid to True. Save the statement as stmt.\n",
    "Execute stmt with the connection and store the results.\n",
    "Print the rowcount attribute of results to see how many records were inserted.\n",
    "Build a select statement to query for the record with the name of Anna.\n",
    "Hit submit to print the results of executing the select statement.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Import insert and select from sqlalchemy\n",
    "# from sqlalchemy import insert, select\n",
    "\n",
    "# # Build an insert statement to insert a record into the data table: stmt\n",
    "# stmt = insert(data).values(name='Anna', count=1, amount=1000.00, valid=True)\n",
    "\n",
    "# # Execute the statement via the connection: results\n",
    "# results = connection.execute(stmt)\n",
    "\n",
    "# # Print result rowcount\n",
    "# print(results.rowcount)\n",
    "\n",
    "# # Build a select statement to validate the insert\n",
    "# stmt = select([data]).where(data.columns.name == 'Anna')\n",
    "\n",
    "# # Print the result of executing the query.\n",
    "# print(connection.execute(stmt).first())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Build a list of dictionaries called values_list with two dictionaries. In the first dictionary set name to 'Anna', count to 1, amount to 1000.00, and valid to True. In the second dictionary of the list, set name to 'Taylor', count to 1, amount to 750.00, and valid to False.\n",
    "Build an insert statement for the data table for a multiple insert, save it as stmt.\n",
    "Execute stmt with the values_list via connection and store the results.\n",
    "Print the rowcount of the results.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Build a list of dictionaries: values_list\n",
    "# values_list = [\n",
    "#     {'name': 'Anna', 'count': 1, 'amount': 1000.00, 'valid': True},\n",
    "#     {'name': 'Taylor', 'count': 1, 'amount': 750.00, 'valid': False}\n",
    "# ]\n",
    "\n",
    "# # Build an insert statement for the data table: stmt\n",
    "# stmt = insert(data)\n",
    "\n",
    "# # Execute stmt with the values_list: results\n",
    "# results = connection.execute(stmt, values_list)\n",
    "\n",
    "# # Print rowcount\n",
    "# print(results.rowcount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Create a statement for bulk insert into the census table and save it as stmt.\n",
    "Create an empty list called values_list, create a variable called total_rowcount set to 0 (an empty list can be created with []).\n",
    "Within the for loop, create a dictionary data for each row and append it to values_list. Within the for loop, row will be a list whose entries are state , sex, age, pop2000 and pop2008 (in that order);\n",
    "Recall that, in the for loop, idx will be the csv line number. If 51 will cleanly divide into the current idx (NOTE: use the % operator and make sure it is 0), execute stmt with the values_list. Save the result as results. The results rowcount is then added to total_rowcount, and values_list is set back to an empty list.\n",
    "Hit submit to print total_rowcount when done with all the records.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Create a insert statement for census: stmt\n",
    "# stmt = insert(census)\n",
    "\n",
    "# # Create an empty list and zeroed row count: values_list, total_rowcount\n",
    "# values_list = []\n",
    "# total_rowcount = 0\n",
    "\n",
    "# # Enumerate the rows of csv_reader\n",
    "# for idx, row in enumerate(csv_reader):\n",
    "#     #create data and append to values_list\n",
    "#     data = {'state': row[0], 'sex': row[1], 'age': row[2], 'pop2000': row[3],\n",
    "#             'pop2008': row[4]}\n",
    "#     values_list.append(data)\n",
    "\n",
    "#     # Check to see if divisible by 51\n",
    "#     if idx % 51 == 0:\n",
    "#         results = connection.execute(stmt, values_list)\n",
    "#         total_rowcount += results.rowcount\n",
    "#         values_list = []\n",
    "\n",
    "# # Print total rowcount\n",
    "# print(total_rowcount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Updating individual records\n",
    "```\n",
    "Build a statement to select all columns from the state_fact table where the name column is New York. Call it select_stmt.\n",
    "Print the results of executing the select_stmt and fetching all records.\n",
    "Build an update statement to change the fips_state column code to 36, save it as stmt.\n",
    "Append a where clause to filter for states states with the name of 'New York' in the state_fact table.\n",
    "Execute stmt via the connection and save the output as results.\n",
    "Hit Submit to print the rowcount of the results and to print the results of executing the select_stmt. This will verify the fips_state code is now 36.\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Build a select statement: select_stmt\n",
    "# select_stmt = select([state_fact]).where(state_fact.columns.name == 'New York')\n",
    "\n",
    "# # Print the results of executing the select_stmt\n",
    "# print(connection.execute(select_stmt).fetchall())\n",
    "\n",
    "# # Build a statement to update the fips_state to 36: stmt\n",
    "# stmt = update(state_fact).values(fips_state=36)\n",
    "\n",
    "# # Append a where clause to limit it to records for New York state\n",
    "# stmt = stmt.where(state_fact.columns.name == 'New York')\n",
    "\n",
    "# # Execute the statement: results\n",
    "# results = connection.execute(stmt)\n",
    "\n",
    "# # Print rowcount\n",
    "# print(results.rowcount)\n",
    "\n",
    "# # Execute the select_stmt again to view the changes\n",
    "# print(connection.execute(select_stmt).fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Build an update statement to update the notes column in the state_fact table to The Wild West. Save it as stmt.\n",
    "Append a where clause to to filter for records have 'West' in the census_region_name column.\n",
    "Execute stmt via the connection and save the output as results.\n",
    "Hit submit to print rowcount of the results.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Build a statement to update the notes to 'The Wild West': stmt\n",
    "# stmt = update(state_fact).values(notes='The Wild West')\n",
    "\n",
    "# # Append a where clause to match the West census region records\n",
    "# stmt = stmt.where(state_fact.columns.census_region_name == 'West')\n",
    "\n",
    "# # Execute the statement: results\n",
    "# results = connection.execute(stmt)\n",
    "\n",
    "# # Print rowcount\n",
    "# print(results.rowcount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Build a statement to select the name column from state_fact. Save the statement as fips_stmt.\n",
    "Append a where clause to fips_stmt that matches fips_state from the state_fact table with fips_code in the flat_census table.\n",
    "Build an update statement to set the state_name in flat_census to fips_stmt. Save the statement as update_stmt.\n",
    "Hit Submit to execute update_stmt, store the results and print the rowcount of results.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Build a statement to select name from state_fact: stmt\n",
    "# fips_stmt = select([state_fact.columns.name])\n",
    "\n",
    "# # Append a where clause to Match the fips_state to flat_census fips_code\n",
    "# fips_stmt = fips_stmt.where(\n",
    "#     state_fact.columns.fips_state == flat_census.columns.fips_code)\n",
    "\n",
    "# # Build an update statement to set the name to fips_stmt: update_stmt\n",
    "# update_stmt = update(flat_census).values(state_name=fips_stmt)\n",
    "\n",
    "# # Execute update_stmt: results\n",
    "# results = connection.execute(update_stmt)\n",
    "\n",
    "# # Print rowcount\n",
    "# print(results.rowcount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting objects from DataBase\n",
    "```\n",
    "Import delete and select from sqlalchemy.\n",
    "Build a delete statement to remove all the data from the census table; saved it as stmt.\n",
    "Execute stmt via the connection and save the results.\n",
    "Hit 'Submit Answer' to select all remaining rows from the census table and print the result to confirm that the table is now empty!\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Import delete, select\n",
    "# from sqlalchemy import delete, select\n",
    "\n",
    "# # Build a statement to empty the census table: stmt\n",
    "# stmt = delete(census)\n",
    "\n",
    "# # Execute the statement: results\n",
    "# results = connection.execute(stmt)\n",
    "\n",
    "# # Print affected rowcount\n",
    "# print(results.rowcount)\n",
    "\n",
    "# # Build a statement to select all records from the census table\n",
    "# stmt = select([census])\n",
    "\n",
    "# # Print the results of executing the statement to verify there are no rows\n",
    "# print(connection.execute(stmt).fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Build a delete statement to remove data from the census table; save it as stmt.\n",
    "Execute stmt via the connection; save the results.\n",
    "Append a where clause to stmt_del that filters for rows which have 'M' in the sex column AND 36 in the age column.\n",
    "Hit Submit to print the rowcount of the results, as well as to_delete, which returns the number of rows that should be deleted. These should match and this is an important sanity check!\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Build a statement to count records using the sex column for Men (M) age 36: stmt\n",
    "# stmt = select([func.count(census.columns.sex)]).where(\n",
    "#     and_(census.columns.sex == 'M',\n",
    "#          census.columns.age == 36)\n",
    "# )\n",
    "\n",
    "# # Execute the select statement and use the scalar() fetch method to save the record count\n",
    "# to_delete = connection.execute(stmt).scalar()\n",
    "\n",
    "# # Build a statement to delete records from the census table: stmt_del\n",
    "# stmt_del = delete(census)\n",
    "\n",
    "# # Append a where clause to target man age 36\n",
    "# stmt_del = stmt_del.where(\n",
    "#     and_(census.columns.sex == 'M',\n",
    "#          census.columns.age == 36)\n",
    "# )\n",
    "\n",
    "# # Execute the statement: results\n",
    "# results = connection.execute(stmt_del)\n",
    "\n",
    "# # Print affected rowcount and to_delete record count, make sure they match\n",
    "# print(results.rowcount, to_delete)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Drop the state_fact table by applying the method drop() to it and passing it the argument engine (in fact, engine will be the sole argument for every function/method in this exercises!)\n",
    "Check to see if state_fact exists via print.\n",
    "Drop all the tables via the metadata.\n",
    "Check to see if census exists via print.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # Drop the state_fact table\n",
    "# state_fact.drop(engine)\n",
    "\n",
    "# # Check to see if state_fact exists\n",
    "# print(state_fact.exists(engine))\n",
    "\n",
    "# # Drop all tables\n",
    "# metadata.drop_all(engine)\n",
    "\n",
    "# # Check to see if census exists\n",
    "# print(census.exists(engine))\n",
    "\n",
    "# print(engine.table_names())"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}