{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "################################################################\n", "## ImportingData #1.3\n", "## Atul Singh\n", "## www.datagenx.net\n", "################################################################" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Importing Data #1\n", "### #1.3 Importing data from database" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "# import \n", "import numpy as np\n", "import pandas as pd\n", "\n", "from sqlalchemy import create_engine" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Using sqlite database" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# Create engine: engine\n", "engine = create_engine(\"sqlite:///Chinook.db\")" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['albums', 'artists', 'customers', 'employees', 'genres', 'invoice_items', 'invoices', 'media_types', 'playlist_track', 'playlists', 'sqlite_sequence', 'sqlite_stat1', 'tracks']\n" ] } ], "source": [ "# Save the table names to a list: table_names\n", "table_names = engine.table_names()\n", "\n", "# Print the table names to the shell\n", "print(table_names)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 0 1 2\n", "0 1 For Those About To Rock We Salute You 1\n", "1 2 Balls to the Wall 2\n", "2 3 Restless and Wild 2\n", "3 4 Let There Be Rock 1\n", "4 5 Big Ones 3\n" ] } ], "source": [ "# Open engine connection: con\n", "con = engine.connect()\n", "\n", "# Perform query: rs\n", "rs = con.execute(\"select * from albums\")\n", "\n", "# Save results of the query to DataFrame: df\n", "df = pd.DataFrame(rs.fetchall())\n", "\n", "# Close connection\n", "con.close()\n", "\n", "# Print head of DataFrame df\n", "print(df.head())" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3\n", " LastName Title\n", "0 Adams General Manager\n", "1 Edwards Sales Manager\n", "2 Peacock Sales Support Agent\n" ] } ], "source": [ "# Open engine in context manager\n", "# Perform query and save results to DataFrame: df\n", "with engine.connect() as con:\n", " rs = con.execute(\"select LastName, Title from employees\")\n", " df = pd.DataFrame(rs.fetchmany(3))\n", " df.columns = rs.keys()\n", "\n", "# Print the length of the DataFrame df\n", "print(len(df))\n", "\n", "# Print the head of the DataFrame df\n", "print(df.head())\n" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " EmployeeId LastName FirstName Title ReportsTo BirthDate \\\n", "0 6 Mitchell Michael IT Manager 1 1973-07-01 00:00:00 \n", "1 7 King Robert IT Staff 6 1970-05-29 00:00:00 \n", "2 8 Callahan Laura IT Staff 6 1968-01-09 00:00:00 \n", "\n", " HireDate Address City State Country \\\n", "0 2003-10-17 00:00:00 5827 Bowness Road NW Calgary AB Canada \n", "1 2004-01-02 00:00:00 590 Columbia Boulevard West Lethbridge AB Canada \n", "2 2004-03-04 00:00:00 923 7 ST NW Lethbridge AB Canada \n", "\n", " PostalCode Phone Fax Email \n", "0 T3B 0C5 +1 (403) 246-9887 +1 (403) 246-9899 michael@chinookcorp.com \n", "1 T1K 5N8 +1 (403) 456-9986 +1 (403) 456-8485 robert@chinookcorp.com \n", "2 T1H 1Y8 +1 (403) 467-3351 +1 (403) 467-8772 laura@chinookcorp.com \n" ] } ], "source": [ "with engine.connect() as con:\n", " rs = con.execute(\"select * from employees where EmployeeId >= 6\")\n", " df = pd.DataFrame(rs.fetchall())\n", " df.columns = rs.keys()\n", "\n", "# Print the head of the DataFrame df\n", "print(df.head())" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " EmployeeId LastName FirstName Title ReportsTo \\\n", "0 4 Park Margaret Sales Support Agent 2.0 \n", "1 2 Edwards Nancy Sales Manager 1.0 \n", "2 1 Adams Andrew General Manager NaN \n", "3 5 Johnson Steve Sales Support Agent 2.0 \n", "4 8 Callahan Laura IT Staff 6.0 \n", "\n", " BirthDate HireDate Address City \\\n", "0 1947-09-19 00:00:00 2003-05-03 00:00:00 683 10 Street SW Calgary \n", "1 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW Calgary \n", "2 1962-02-18 00:00:00 2002-08-14 00:00:00 11120 Jasper Ave NW Edmonton \n", "3 1965-03-03 00:00:00 2003-10-17 00:00:00 7727B 41 Ave Calgary \n", "4 1968-01-09 00:00:00 2004-03-04 00:00:00 923 7 ST NW Lethbridge \n", "\n", " State Country PostalCode Phone Fax \\\n", "0 AB Canada T2P 5G3 +1 (403) 263-4423 +1 (403) 263-4289 \n", "1 AB Canada T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322 \n", "2 AB Canada T5K 2N1 +1 (780) 428-9482 +1 (780) 428-3457 \n", "3 AB Canada T3B 1Y7 1 (780) 836-9987 1 (780) 836-9543 \n", "4 AB Canada T1H 1Y8 +1 (403) 467-3351 +1 (403) 467-8772 \n", "\n", " Email \n", "0 margaret@chinookcorp.com \n", "1 nancy@chinookcorp.com \n", "2 andrew@chinookcorp.com \n", "3 steve@chinookcorp.com \n", "4 laura@chinookcorp.com \n" ] } ], "source": [ "# Open engine in context manager\n", "with engine.connect() as con:\n", " rs = con.execute(\"select * from employees order by BirthDate\")\n", " df = pd.DataFrame(rs.fetchall())\n", "\n", " # Set the DataFrame's column names\n", " df.columns = rs.keys()\n", "\n", "# Print head of DataFrame\n", "print(df.head())" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " AlbumId Title ArtistId\n", "0 1 For Those About To Rock We Salute You 1\n", "1 2 Balls to the Wall 2\n", "2 3 Restless and Wild 2\n", "3 4 Let There Be Rock 1\n", "4 5 Big Ones 3\n", "True\n" ] } ], "source": [ "# Create engine: engine\n", "engine = create_engine(\"sqlite:///Chinook.db\")\n", "\n", "# Execute query and store records in DataFrame: df\n", "df = pd.read_sql_query(\"select * from Albums\", engine)\n", "\n", "# Print head of DataFrame\n", "print(df.head())\n", "\n", "# Open engine in context manager\n", "# Perform query and save results to DataFrame: df1\n", "with engine.connect() as con:\n", " rs = con.execute(\"SELECT * FROM Albums\")\n", " df1 = pd.DataFrame(rs.fetchall())\n", " df1.columns = rs.keys()\n", "\n", "# Confirm that both methods yield the same result: does df = df1 ?\n", "print(df.equals(df1))\n" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " EmployeeId LastName FirstName Title ReportsTo BirthDate \\\n", "0 8 Callahan Laura IT Staff 6 1968-01-09 00:00:00 \n", "1 7 King Robert IT Staff 6 1970-05-29 00:00:00 \n", "2 6 Mitchell Michael IT Manager 1 1973-07-01 00:00:00 \n", "\n", " HireDate Address City State Country \\\n", "0 2004-03-04 00:00:00 923 7 ST NW Lethbridge AB Canada \n", "1 2004-01-02 00:00:00 590 Columbia Boulevard West Lethbridge AB Canada \n", "2 2003-10-17 00:00:00 5827 Bowness Road NW Calgary AB Canada \n", "\n", " PostalCode Phone Fax Email \n", "0 T1H 1Y8 +1 (403) 467-3351 +1 (403) 467-8772 laura@chinookcorp.com \n", "1 T1K 5N8 +1 (403) 456-9986 +1 (403) 456-8485 robert@chinookcorp.com \n", "2 T3B 0C5 +1 (403) 246-9887 +1 (403) 246-9899 michael@chinookcorp.com \n" ] } ], "source": [ "# Execute query and store records in DataFrame: df\n", "df = pd.read_sql_query(\"select * from employees where EmployeeId >= 6 order by Birthdate\",engine)\n", "\n", "# Print head of DataFrame\n", "print(df.head())" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Title Name\n", "0 For Those About To Rock We Salute You AC/DC\n", "1 Balls to the Wall Accept\n", "2 Restless and Wild Accept\n", "3 Let There Be Rock AC/DC\n", "4 Big Ones Aerosmith\n" ] } ], "source": [ "# Open engine in context manager\n", "# Perform query and save results to DataFrame: df\n", "with engine.connect() as con:\n", " rs = con.execute(\"select al.Title, ar.Name from Albums al, Artists ar where al.ArtistID=ar.ArtistID\")\n", " df = pd.DataFrame(rs.fetchall())\n", " df.columns = rs.keys()\n", "\n", "# Print head of DataFrame df\n", "print(df.head())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "############################################################\n", "## Atul Singh | www.datagenx.net | lnked.in/atulsingh\n", "############################################################" ] } ], "metadata": { "anaconda-cloud": {}, "kernelspec": { "display_name": "Python [default]", "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 }