{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "################################################################\n", "## Introduction to Database #1 - Basics of RDBMS\n", "## Atul Singh\n", "## www.datagenx.net\n", "################################################################" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# import\n", "from sqlalchemy import create_engine, Table, MetaData" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Connecting SQLite" ] }, { "cell_type": "code", "execution_count": 5, "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": [ "# creating engine\n", "engine = create_engine(\"sqlite:///chinook.db\")\n", "\n", "# reading table names from database\n", "print(engine.table_names())" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": true }, "outputs": [], "source": [ "### Importing metadata with Table and MetaData object\n", "metadata = MetaData()\n", "\n", "# getting albums metadata\n", "albums = Table('albums', metadata, autoload=True, autoload_with=engine)" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Table('albums', MetaData(bind=None), Column('AlbumId', INTEGER(), table=, primary_key=True, nullable=False), Column('Title', NVARCHAR(length=160), table=, nullable=False), Column('ArtistId', INTEGER(), ForeignKey('artists.ArtistId'), table=, nullable=False), schema=None)\n" ] } ], "source": [ "print(repr(albums))" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Table('albums', MetaData(bind=None), Column('AlbumId', INTEGER(), table=, primary_key=True, nullable=False), Column('Title', NVARCHAR(length=160), table=, nullable=False), Column('ArtistId', INTEGER(), ForeignKey('artists.ArtistId'), table=, nullable=False), schema=None)\n" ] } ], "source": [ "# we can get the same metadata details from metadata.tables dictionaty\n", "print(repr(metadata.tables['albums']))" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "collapsed": false }, "outputs": [], "source": [ "## Let's query on table albums\n", "stmt = 'select * from albums'\n", "connection = engine.connect()" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n" ] } ], "source": [ "#results = connection.execute(stmt).fetchall()\n", "result_proxy = connection.execute(stmt)\n", "print(result_proxy)" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(1, 'For Those About To Rock We Salute You', 1), (2, 'Balls to the Wall', 2), (3, 'Restless and Wild', 2), (4, 'Let There Be Rock', 1), (5, 'Big Ones', 3), (6, 'Jagged Little Pill', 4), (7, 'Facelift', 5), (8, 'Warner 25 Anos', 6), (9, 'Plays Metallica By Four Cellos', 7), (10, 'Audioslave', 8), (11, 'Out Of Exile', 8), (12, 'BackBeat Soundtrack', 9), (13, 'The Best Of Billy Cobham', 10), (14, 'Alcohol Fueled Brewtality Live! [Disc 1]', 11), (15, 'Alcohol Fueled Brewtality Live! [Disc 2]', 11), (16, 'Black Sabbath', 12), (17, 'Black Sabbath Vol. 4 (Remaster)', 12), (18, 'Body Count', 13), (19, 'Chemical Wedding', 14), (20, 'The Best Of Buddy Guy - The Millenium Collection', 15), (21, 'Prenda Minha', 16), (22, 'Sozinho Remix Ao Vivo', 16), (23, 'Minha Historia', 17), (24, 'Afrociberdelia', 18), (25, 'Da Lama Ao Caos', 18), (26, 'Acústico MTV [Live]', 19), (27, 'Cidade Negra - Hits', 19), (28, 'Na Pista', 20), (29, 'Axé Bahia 2001', 21), (30, 'BBC Sessions [Disc 1] [Live]', 22), (31, 'Bongo Fury', 23), (32, 'Carnaval 2001', 21), (33, 'Chill: Brazil (Disc 1)', 24), (34, 'Chill: Brazil (Disc 2)', 6), (35, 'Garage Inc. (Disc 1)', 50), (36, 'Greatest Hits II', 51), (37, 'Greatest Kiss', 52), (38, 'Heart of the Night', 53), (39, 'International Superhits', 54), (40, 'Into The Light', 55), (41, 'Meus Momentos', 56), (42, 'Minha História', 57), (43, 'MK III The Final Concerts [Disc 1]', 58), (44, 'Physical Graffiti [Disc 1]', 22), (45, 'Sambas De Enredo 2001', 21), (46, 'Supernatural', 59), (47, 'The Best of Ed Motta', 37), (48, 'The Essential Miles Davis [Disc 1]', 68), (49, 'The Essential Miles Davis [Disc 2]', 68), (50, 'The Final Concerts (Disc 2)', 58), (51, \"Up An' Atom\", 69), (52, 'Vinícius De Moraes - Sem Limite', 70), (53, 'Vozes do MPB', 21), (54, 'Chronicle, Vol. 1', 76), (55, 'Chronicle, Vol. 2', 76), (56, 'Cássia Eller - Coleção Sem Limite [Disc 2]', 77), (57, 'Cássia Eller - Sem Limite [Disc 1]', 77), (58, 'Come Taste The Band', 58), (59, 'Deep Purple In Rock', 58), (60, 'Fireball', 58), (61, \"Knocking at Your Back Door: The Best Of Deep Purple in the 80's\", 58), (62, 'Machine Head', 58), (63, 'Purpendicular', 58), (64, 'Slaves And Masters', 58), (65, 'Stormbringer', 58), (66, 'The Battle Rages On', 58), (67, \"Vault: Def Leppard's Greatest Hits\", 78), (68, 'Outbreak', 79), (69, 'Djavan Ao Vivo - Vol. 02', 80), (70, 'Djavan Ao Vivo - Vol. 1', 80), (71, 'Elis Regina-Minha História', 41), (72, 'The Cream Of Clapton', 81), (73, 'Unplugged', 81), (74, 'Album Of The Year', 82), (75, 'Angel Dust', 82), (76, 'King For A Day Fool For A Lifetime', 82), (77, 'The Real Thing', 82), (78, 'Deixa Entrar', 83), (79, 'In Your Honor [Disc 1]', 84), (80, 'In Your Honor [Disc 2]', 84), (81, 'One By One', 84), (82, 'The Colour And The Shape', 84), (83, 'My Way: The Best Of Frank Sinatra [Disc 1]', 85), (84, 'Roda De Funk', 86), (85, 'As Canções de Eu Tu Eles', 27), (86, 'Quanta Gente Veio Ver (Live)', 27), (87, 'Quanta Gente Veio ver--Bônus De Carnaval', 27), (88, 'Faceless', 87), (89, 'American Idiot', 54), (90, 'Appetite for Destruction', 88), (91, 'Use Your Illusion I', 88), (92, 'Use Your Illusion II', 88), (93, 'Blue Moods', 89), (94, 'A Matter of Life and Death', 90), (95, 'A Real Dead One', 90), (96, 'A Real Live One', 90), (97, 'Brave New World', 90), (98, 'Dance Of Death', 90), (99, 'Fear Of The Dark', 90), (100, 'Iron Maiden', 90), (101, 'Killers', 90), (102, 'Live After Death', 90), (103, 'Live At Donington 1992 (Disc 1)', 90), (104, 'Live At Donington 1992 (Disc 2)', 90), (105, 'No Prayer For The Dying', 90), (106, 'Piece Of Mind', 90), (107, 'Powerslave', 90), (108, 'Rock In Rio [CD1]', 90), (109, 'Rock In Rio [CD2]', 90), (110, 'Seventh Son of a Seventh Son', 90), (111, 'Somewhere in Time', 90), (112, 'The Number of The Beast', 90), (113, 'The X Factor', 90), (114, 'Virtual XI', 90), (115, 'Sex Machine', 91), (116, 'Emergency On Planet Earth', 92), (117, 'Synkronized', 92), (118, 'The Return Of The Space Cowboy', 92), (119, 'Get Born', 93), (120, 'Are You Experienced?', 94), (121, 'Surfing with the Alien (Remastered)', 95), (122, 'Jorge Ben Jor 25 Anos', 46), (123, 'Jota Quest-1995', 96), (124, 'Cafezinho', 97), (125, 'Living After Midnight', 98), (126, 'Unplugged [Live]', 52), (127, 'BBC Sessions [Disc 2] [Live]', 22), (128, 'Coda', 22), (129, 'Houses Of The Holy', 22), (130, 'In Through The Out Door', 22), (131, 'IV', 22), (132, 'Led Zeppelin I', 22), (133, 'Led Zeppelin II', 22), (134, 'Led Zeppelin III', 22), (135, 'Physical Graffiti [Disc 2]', 22), (136, 'Presence', 22), (137, 'The Song Remains The Same (Disc 1)', 22), (138, 'The Song Remains The Same (Disc 2)', 22), (139, 'A TempestadeTempestade Ou O Livro Dos Dias', 99), (140, 'Mais Do Mesmo', 99), (141, 'Greatest Hits', 100), (142, 'Lulu Santos - RCA 100 Anos De Música - Álbum 01', 101), (143, 'Lulu Santos - RCA 100 Anos De Música - Álbum 02', 101), (144, 'Misplaced Childhood', 102), (145, 'Barulhinho Bom', 103), (146, 'Seek And Shall Find: More Of The Best (1963-1981)', 104), (147, 'The Best Of Men At Work', 105), (148, 'Black Album', 50), (149, 'Garage Inc. (Disc 2)', 50), (150, \"Kill 'Em All\", 50), (151, 'Load', 50), (152, 'Master Of Puppets', 50), (153, 'ReLoad', 50), (154, 'Ride The Lightning', 50), (155, 'St. Anger', 50), (156, '...And Justice For All', 50), (157, 'Miles Ahead', 68), (158, 'Milton Nascimento Ao Vivo', 42), (159, 'Minas', 42), (160, 'Ace Of Spades', 106), (161, 'Demorou...', 108), (162, 'Motley Crue Greatest Hits', 109), (163, 'From The Muddy Banks Of The Wishkah [Live]', 110), (164, 'Nevermind', 110), (165, 'Compositores', 111), (166, 'Olodum', 112), (167, 'Acústico MTV', 113), (168, 'Arquivo II', 113), (169, 'Arquivo Os Paralamas Do Sucesso', 113), (170, 'Bark at the Moon (Remastered)', 114), (171, 'Blizzard of Ozz', 114), (172, 'Diary of a Madman (Remastered)', 114), (173, 'No More Tears (Remastered)', 114), (174, 'Tribute', 114), (175, 'Walking Into Clarksdale', 115), (176, 'Original Soundtracks 1', 116), (177, 'The Beast Live', 117), (178, 'Live On Two Legs [Live]', 118), (179, 'Pearl Jam', 118), (180, 'Riot Act', 118), (181, 'Ten', 118), (182, 'Vs.', 118), (183, 'Dark Side Of The Moon', 120), (184, 'Os Cães Ladram Mas A Caravana Não Pára', 121), (185, 'Greatest Hits I', 51), (186, 'News Of The World', 51), (187, 'Out Of Time', 122), (188, 'Green', 124), (189, 'New Adventures In Hi-Fi', 124), (190, 'The Best Of R.E.M.: The IRS Years', 124), (191, 'Cesta Básica', 125), (192, 'Raul Seixas', 126), (193, 'Blood Sugar Sex Magik', 127), (194, 'By The Way', 127), (195, 'Californication', 127), (196, 'Retrospective I (1974-1980)', 128), (197, 'Santana - As Years Go By', 59), (198, 'Santana Live', 59), (199, 'Maquinarama', 130), (200, 'O Samba Poconé', 130), (201, 'Judas 0: B-Sides and Rarities', 131), (202, 'Rotten Apples: Greatest Hits', 131), (203, 'A-Sides', 132), (204, 'Morning Dance', 53), (205, 'In Step', 133), (206, 'Core', 134), (207, 'Mezmerize', 135), (208, '[1997] Black Light Syndrome', 136), (209, 'Live [Disc 1]', 137), (210, 'Live [Disc 2]', 137), (211, 'The Singles', 138), (212, 'Beyond Good And Evil', 139), (213, 'Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]', 139), (214, 'The Doors', 140), (215, 'The Police Greatest Hits', 141), (216, 'Hot Rocks, 1964-1971 (Disc 1)', 142), (217, 'No Security', 142), (218, 'Voodoo Lounge', 142), (219, 'Tangents', 143), (220, 'Transmission', 143), (221, 'My Generation - The Very Best Of The Who', 144), (222, 'Serie Sem Limite (Disc 1)', 145), (223, 'Serie Sem Limite (Disc 2)', 145), (224, 'Acústico', 146), (225, 'Volume Dois', 146), (226, 'Battlestar Galactica: The Story So Far', 147), (227, 'Battlestar Galactica, Season 3', 147), (228, 'Heroes, Season 1', 148), (229, 'Lost, Season 3', 149), (230, 'Lost, Season 1', 149), (231, 'Lost, Season 2', 149), (232, 'Achtung Baby', 150), (233, \"All That You Can't Leave Behind\", 150), (234, 'B-Sides 1980-1990', 150), (235, 'How To Dismantle An Atomic Bomb', 150), (236, 'Pop', 150), (237, 'Rattle And Hum', 150), (238, 'The Best Of 1980-1990', 150), (239, 'War', 150), (240, 'Zooropa', 150), (241, 'UB40 The Best Of - Volume Two [UK]', 151), (242, 'Diver Down', 152), (243, 'The Best Of Van Halen, Vol. I', 152), (244, 'Van Halen', 152), (245, 'Van Halen III', 152), (246, 'Contraband', 153), (247, 'Vinicius De Moraes', 72), (248, 'Ao Vivo [IMPORT]', 155), (249, 'The Office, Season 1', 156), (250, 'The Office, Season 2', 156), (251, 'The Office, Season 3', 156), (252, 'Un-Led-Ed', 157), (253, 'Battlestar Galactica (Classic), Season 1', 158), (254, 'Aquaman', 159), (255, 'Instant Karma: The Amnesty International Campaign to Save Darfur', 150), (256, 'Speak of the Devil', 114), (257, '20th Century Masters - The Millennium Collection: The Best of Scorpions', 179), (258, 'House of Pain', 180), (259, 'Radio Brasil (O Som da Jovem Vanguarda) - Seleccao de Henrique Amaro', 36), (260, 'Cake: B-Sides and Rarities', 196), (261, 'LOST, Season 4', 149), (262, 'Quiet Songs', 197), (263, 'Muso Ko', 198), (264, 'Realize', 199), (265, 'Every Kind of Light', 200), (266, 'Duos II', 201), (267, 'Worlds', 202), (268, 'The Best of Beethoven', 203), (269, 'Temple of the Dog', 204), (270, 'Carry On', 205), (271, 'Revelations', 8), (272, 'Adorate Deum: Gregorian Chant from the Proper of the Mass', 206), (273, 'Allegri: Miserere', 207), (274, 'Pachelbel: Canon & Gigue', 208), (275, 'Vivaldi: The Four Seasons', 209), (276, 'Bach: Violin Concertos', 210), (277, 'Bach: Goldberg Variations', 211), (278, 'Bach: The Cello Suites', 212), (279, 'Handel: The Messiah (Highlights)', 213), (280, 'The World of Classical Favourites', 214), (281, 'Sir Neville Marriner: A Celebration', 215), (282, 'Mozart: Wind Concertos', 216), (283, 'Haydn: Symphonies 99 - 104', 217), (284, 'Beethoven: Symhonies Nos. 5 & 6', 218), (285, 'A Soprano Inspired', 219), (286, 'Great Opera Choruses', 220), (287, 'Wagner: Favourite Overtures', 221), (288, 'Fauré: Requiem, Ravel: Pavane & Others', 222), (289, 'Tchaikovsky: The Nutcracker', 223), (290, 'The Last Night of the Proms', 224), (291, 'Puccini: Madama Butterfly - Highlights', 225), (292, 'Holst: The Planets, Op. 32 & Vaughan Williams: Fantasies', 226), (293, \"Pavarotti's Opera Made Easy\", 227), (294, \"Great Performances - Barber's Adagio and Other Romantic Favorites for Strings\", 228), (295, 'Carmina Burana', 229), (296, 'A Copland Celebration, Vol. I', 230), (297, 'Bach: Toccata & Fugue in D Minor', 231), (298, 'Prokofiev: Symphony No.1', 232), (299, 'Scheherazade', 233), (300, 'Bach: The Brandenburg Concertos', 234), (301, 'Chopin: Piano Concertos Nos. 1 & 2', 235), (302, 'Mascagni: Cavalleria Rusticana', 236), (303, 'Sibelius: Finlandia', 237), (304, 'Beethoven Piano Sonatas: Moonlight & Pastorale', 238), (305, 'Great Recordings of the Century - Mahler: Das Lied von der Erde', 240), (306, 'Elgar: Cello Concerto & Vaughan Williams: Fantasias', 241), (307, 'Adams, John: The Chairman Dances', 242), (308, \"Tchaikovsky: 1812 Festival Overture, Op.49, Capriccio Italien & Beethoven: Wellington's Victory\", 243), (309, 'Palestrina: Missa Papae Marcelli & Allegri: Miserere', 244), (310, 'Prokofiev: Romeo & Juliet', 245), (311, 'Strauss: Waltzes', 226), (312, 'Berlioz: Symphonie Fantastique', 245), (313, 'Bizet: Carmen Highlights', 246), (314, 'English Renaissance', 247), (315, 'Handel: Music for the Royal Fireworks (Original Version 1749)', 208), (316, 'Grieg: Peer Gynt Suites & Sibelius: Pelléas et Mélisande', 248), (317, 'Mozart Gala: Famous Arias', 249), (318, 'SCRIABIN: Vers la flamme', 250), (319, 'Armada: Music from the Courts of England and Spain', 251), (320, 'Mozart: Symphonies Nos. 40 & 41', 248), (321, 'Back to Black', 252), (322, 'Frank', 252), (323, 'Carried to Dust (Bonus Track Version)', 253), (324, \"Beethoven: Symphony No. 6 'Pastoral' Etc.\", 254), (325, 'Bartok: Violin & Viola Concertos', 255), (326, \"Mendelssohn: A Midsummer Night's Dream\", 256), (327, 'Bach: Orchestral Suites Nos. 1 - 4', 257), (328, 'Charpentier: Divertissements, Airs & Concerts', 258), (329, 'South American Getaway', 259), (330, 'Górecki: Symphony No. 3', 260), (331, 'Purcell: The Fairy Queen', 261), (332, 'The Ultimate Relexation Album', 262), (333, 'Purcell: Music for the Queen Mary', 263), (334, 'Weill: The Seven Deadly Sins', 264), (335, 'J.S. Bach: Chaconne, Suite in E Minor, Partita in E Major & Prelude, Fugue and Allegro', 265), (336, 'Prokofiev: Symphony No.5 & Stravinksy: Le Sacre Du Printemps', 248), (337, 'Szymanowski: Piano Works, Vol. 1', 266), (338, 'Nielsen: The Six Symphonies', 267), (339, \"Great Recordings of the Century: Paganini's 24 Caprices\", 268), (340, \"Liszt - 12 Études D'Execution Transcendante\", 269), (341, 'Great Recordings of the Century - Shubert: Schwanengesang, 4 Lieder', 270), (342, 'Locatelli: Concertos for Violin, Strings and Continuo, Vol. 3', 271), (343, 'Respighi:Pines of Rome', 226), (344, \"Schubert: The Late String Quartets & String Quintet (3 CD's)\", 272), (345, \"Monteverdi: L'Orfeo\", 273), (346, 'Mozart: Chamber Music', 274), (347, 'Koyaanisqatsi (Soundtrack from the Motion Picture)', 275)]\n" ] } ], "source": [ "results = result_proxy.fetchall()\n", "print(results)" ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['AlbumId', 'Title', 'ArtistId']\n" ] } ], "source": [ "# get all the column names\n", "print(result_proxy.keys())" ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "collapsed": false }, "outputs": [ { "ename": "AttributeError", "evalue": "'list' object has no attribute 'size'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mAttributeError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[1;31m# get total no of rows in tables\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresults\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msize\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;31mAttributeError\u001b[0m: 'list' object has no attribute 'size'" ] } ], "source": [ "# get total no of rows in tables\n", "print(results.size())" ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1, 'For Those About To Rock We Salute You', 1)\n" ] } ], "source": [ "# get first row\n", "result1 = results[0]\n", "print(result1)" ] }, { "cell_type": "code", "execution_count": 35, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['AlbumId', 'Title', 'ArtistId']\n" ] } ], "source": [ "# print column names\n", "print(result1.keys())" ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "For Those About To Rock We Salute You\n" ] } ], "source": [ "# access particulat column data\n", "print(result1.Title)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Creating SQL statement" ] }, { "cell_type": "code", "execution_count": 40, "metadata": { "collapsed": true }, "outputs": [], "source": [ "# importing select\n", "from sqlalchemy import select" ] }, { "cell_type": "code", "execution_count": 38, "metadata": { "collapsed": false }, "outputs": [], "source": [ "metadata = MetaData()\n", "customers = Table('customers', metadata, autoload=True, autoload_with=engine)" ] }, { "cell_type": "code", "execution_count": 41, "metadata": { "collapsed": false }, "outputs": [], "source": [ "stmt = select([customers])" ] }, { "cell_type": "code", "execution_count": 42, "metadata": { "collapsed": true }, "outputs": [], "source": [ "results= connection.execute(stmt).fetchall()" ] }, { "cell_type": "code", "execution_count": 43, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(1, 'Luís', 'Gonçalves', 'Embraer - Empresa Brasileira de Aeronáutica S.A.', 'Av. Brigadeiro Faria Lima, 2170', 'São José dos Campos', 'SP', 'Brazil', '12227-000', '+55 (12) 3923-5555', '+55 (12) 3923-5566', 'luisg@embraer.com.br', 3), (2, 'Leonie', 'Köhler', None, 'Theodor-Heuss-Straße 34', 'Stuttgart', None, 'Germany', '70174', '+49 0711 2842222', None, 'leonekohler@surfeu.de', 5), (3, 'François', 'Tremblay', None, '1498 rue Bélanger', 'Montréal', 'QC', 'Canada', 'H2G 1A7', '+1 (514) 721-4711', None, 'ftremblay@gmail.com', 3), (4, 'Bjørn', 'Hansen', None, 'Ullevålsveien 14', 'Oslo', None, 'Norway', '0171', '+47 22 44 22 22', None, 'bjorn.hansen@yahoo.no', 4), (5, 'František', 'Wichterlová', 'JetBrains s.r.o.', 'Klanova 9/506', 'Prague', None, 'Czech Republic', '14700', '+420 2 4172 5555', '+420 2 4172 5555', 'frantisekw@jetbrains.com', 4), (6, 'Helena', 'Holý', None, 'Rilská 3174/6', 'Prague', None, 'Czech Republic', '14300', '+420 2 4177 0449', None, 'hholy@gmail.com', 5), (7, 'Astrid', 'Gruber', None, 'Rotenturmstraße 4, 1010 Innere Stadt', 'Vienne', None, 'Austria', '1010', '+43 01 5134505', None, 'astrid.gruber@apple.at', 5), (8, 'Daan', 'Peeters', None, 'Grétrystraat 63', 'Brussels', None, 'Belgium', '1000', '+32 02 219 03 03', None, 'daan_peeters@apple.be', 4), (9, 'Kara', 'Nielsen', None, 'Sønder Boulevard 51', 'Copenhagen', None, 'Denmark', '1720', '+453 3331 9991', None, 'kara.nielsen@jubii.dk', 4), (10, 'Eduardo', 'Martins', 'Woodstock Discos', 'Rua Dr. Falcão Filho, 155', 'São Paulo', 'SP', 'Brazil', '01007-010', '+55 (11) 3033-5446', '+55 (11) 3033-4564', 'eduardo@woodstock.com.br', 4), (11, 'Alexandre', 'Rocha', 'Banco do Brasil S.A.', 'Av. Paulista, 2022', 'São Paulo', 'SP', 'Brazil', '01310-200', '+55 (11) 3055-3278', '+55 (11) 3055-8131', 'alero@uol.com.br', 5), (12, 'Roberto', 'Almeida', 'Riotur', 'Praça Pio X, 119', 'Rio de Janeiro', 'RJ', 'Brazil', '20040-020', '+55 (21) 2271-7000', '+55 (21) 2271-7070', 'roberto.almeida@riotur.gov.br', 3), (13, 'Fernanda', 'Ramos', None, 'Qe 7 Bloco G', 'Brasília', 'DF', 'Brazil', '71020-677', '+55 (61) 3363-5547', '+55 (61) 3363-7855', 'fernadaramos4@uol.com.br', 4), (14, 'Mark', 'Philips', 'Telus', '8210 111 ST NW', 'Edmonton', 'AB', 'Canada', 'T6G 2C7', '+1 (780) 434-4554', '+1 (780) 434-5565', 'mphilips12@shaw.ca', 5), (15, 'Jennifer', 'Peterson', 'Rogers Canada', '700 W Pender Street', 'Vancouver', 'BC', 'Canada', 'V6C 1G8', '+1 (604) 688-2255', '+1 (604) 688-8756', 'jenniferp@rogers.ca', 3), (16, 'Frank', 'Harris', 'Google Inc.', '1600 Amphitheatre Parkway', 'Mountain View', 'CA', 'USA', '94043-1351', '+1 (650) 253-0000', '+1 (650) 253-0000', 'fharris@google.com', 4), (17, 'Jack', 'Smith', 'Microsoft Corporation', '1 Microsoft Way', 'Redmond', 'WA', 'USA', '98052-8300', '+1 (425) 882-8080', '+1 (425) 882-8081', 'jacksmith@microsoft.com', 5), (18, 'Michelle', 'Brooks', None, '627 Broadway', 'New York', 'NY', 'USA', '10012-2612', '+1 (212) 221-3546', '+1 (212) 221-4679', 'michelleb@aol.com', 3), (19, 'Tim', 'Goyer', 'Apple Inc.', '1 Infinite Loop', 'Cupertino', 'CA', 'USA', '95014', '+1 (408) 996-1010', '+1 (408) 996-1011', 'tgoyer@apple.com', 3), (20, 'Dan', 'Miller', None, '541 Del Medio Avenue', 'Mountain View', 'CA', 'USA', '94040-111', '+1 (650) 644-3358', None, 'dmiller@comcast.com', 4), (21, 'Kathy', 'Chase', None, '801 W 4th Street', 'Reno', 'NV', 'USA', '89503', '+1 (775) 223-7665', None, 'kachase@hotmail.com', 5), (22, 'Heather', 'Leacock', None, '120 S Orange Ave', 'Orlando', 'FL', 'USA', '32801', '+1 (407) 999-7788', None, 'hleacock@gmail.com', 4), (23, 'John', 'Gordon', None, '69 Salem Street', 'Boston', 'MA', 'USA', '2113', '+1 (617) 522-1333', None, 'johngordon22@yahoo.com', 4), (24, 'Frank', 'Ralston', None, '162 E Superior Street', 'Chicago', 'IL', 'USA', '60611', '+1 (312) 332-3232', None, 'fralston@gmail.com', 3), (25, 'Victor', 'Stevens', None, '319 N. Frances Street', 'Madison', 'WI', 'USA', '53703', '+1 (608) 257-0597', None, 'vstevens@yahoo.com', 5), (26, 'Richard', 'Cunningham', None, '2211 W Berry Street', 'Fort Worth', 'TX', 'USA', '76110', '+1 (817) 924-7272', None, 'ricunningham@hotmail.com', 4), (27, 'Patrick', 'Gray', None, '1033 N Park Ave', 'Tucson', 'AZ', 'USA', '85719', '+1 (520) 622-4200', None, 'patrick.gray@aol.com', 4), (28, 'Julia', 'Barnett', None, '302 S 700 E', 'Salt Lake City', 'UT', 'USA', '84102', '+1 (801) 531-7272', None, 'jubarnett@gmail.com', 5), (29, 'Robert', 'Brown', None, '796 Dundas Street West', 'Toronto', 'ON', 'Canada', 'M6J 1V1', '+1 (416) 363-8888', None, 'robbrown@shaw.ca', 3), (30, 'Edward', 'Francis', None, '230 Elgin Street', 'Ottawa', 'ON', 'Canada', 'K2P 1L7', '+1 (613) 234-3322', None, 'edfrancis@yachoo.ca', 3), (31, 'Martha', 'Silk', None, '194A Chain Lake Drive', 'Halifax', 'NS', 'Canada', 'B3S 1C5', '+1 (902) 450-0450', None, 'marthasilk@gmail.com', 5), (32, 'Aaron', 'Mitchell', None, '696 Osborne Street', 'Winnipeg', 'MB', 'Canada', 'R3L 2B9', '+1 (204) 452-6452', None, 'aaronmitchell@yahoo.ca', 4), (33, 'Ellie', 'Sullivan', None, '5112 48 Street', 'Yellowknife', 'NT', 'Canada', 'X1A 1N6', '+1 (867) 920-2233', None, 'ellie.sullivan@shaw.ca', 3), (34, 'João', 'Fernandes', None, 'Rua da Assunção 53', 'Lisbon', None, 'Portugal', None, '+351 (213) 466-111', None, 'jfernandes@yahoo.pt', 4), (35, 'Madalena', 'Sampaio', None, 'Rua dos Campeões Europeus de Viena, 4350', 'Porto', None, 'Portugal', None, '+351 (225) 022-448', None, 'masampaio@sapo.pt', 4), (36, 'Hannah', 'Schneider', None, 'Tauentzienstraße 8', 'Berlin', None, 'Germany', '10789', '+49 030 26550280', None, 'hannah.schneider@yahoo.de', 5), (37, 'Fynn', 'Zimmermann', None, 'Berger Straße 10', 'Frankfurt', None, 'Germany', '60316', '+49 069 40598889', None, 'fzimmermann@yahoo.de', 3), (38, 'Niklas', 'Schröder', None, 'Barbarossastraße 19', 'Berlin', None, 'Germany', '10779', '+49 030 2141444', None, 'nschroder@surfeu.de', 3), (39, 'Camille', 'Bernard', None, '4, Rue Milton', 'Paris', None, 'France', '75009', '+33 01 49 70 65 65', None, 'camille.bernard@yahoo.fr', 4), (40, 'Dominique', 'Lefebvre', None, '8, Rue Hanovre', 'Paris', None, 'France', '75002', '+33 01 47 42 71 71', None, 'dominiquelefebvre@gmail.com', 4), (41, 'Marc', 'Dubois', None, '11, Place Bellecour', 'Lyon', None, 'France', '69002', '+33 04 78 30 30 30', None, 'marc.dubois@hotmail.com', 5), (42, 'Wyatt', 'Girard', None, '9, Place Louis Barthou', 'Bordeaux', None, 'France', '33000', '+33 05 56 96 96 96', None, 'wyatt.girard@yahoo.fr', 3), (43, 'Isabelle', 'Mercier', None, '68, Rue Jouvence', 'Dijon', None, 'France', '21000', '+33 03 80 73 66 99', None, 'isabelle_mercier@apple.fr', 3), (44, 'Terhi', 'Hämäläinen', None, 'Porthaninkatu 9', 'Helsinki', None, 'Finland', '00530', '+358 09 870 2000', None, 'terhi.hamalainen@apple.fi', 3), (45, 'Ladislav', 'Kovács', None, 'Erzsébet krt. 58.', 'Budapest', None, 'Hungary', 'H-1073', None, None, 'ladislav_kovacs@apple.hu', 3), (46, 'Hugh', \"O'Reilly\", None, '3 Chatham Street', 'Dublin', 'Dublin', 'Ireland', None, '+353 01 6792424', None, 'hughoreilly@apple.ie', 3), (47, 'Lucas', 'Mancini', None, 'Via Degli Scipioni, 43', 'Rome', 'RM', 'Italy', '00192', '+39 06 39733434', None, 'lucas.mancini@yahoo.it', 5), (48, 'Johannes', 'Van der Berg', None, 'Lijnbaansgracht 120bg', 'Amsterdam', 'VV', 'Netherlands', '1016', '+31 020 6223130', None, 'johavanderberg@yahoo.nl', 5), (49, 'Stanisław', 'Wójcik', None, 'Ordynacka 10', 'Warsaw', None, 'Poland', '00-358', '+48 22 828 37 39', None, 'stanisław.wójcik@wp.pl', 4), (50, 'Enrique', 'Muñoz', None, 'C/ San Bernardo 85', 'Madrid', None, 'Spain', '28015', '+34 914 454 454', None, 'enrique_munoz@yahoo.es', 5), (51, 'Joakim', 'Johansson', None, 'Celsiusg. 9', 'Stockholm', None, 'Sweden', '11230', '+46 08-651 52 52', None, 'joakim.johansson@yahoo.se', 5), (52, 'Emma', 'Jones', None, '202 Hoxton Street', 'London', None, 'United Kingdom', 'N1 5LH', '+44 020 7707 0707', None, 'emma_jones@hotmail.com', 3), (53, 'Phil', 'Hughes', None, '113 Lupus St', 'London', None, 'United Kingdom', 'SW1V 3EN', '+44 020 7976 5722', None, 'phil.hughes@gmail.com', 3), (54, 'Steve', 'Murray', None, '110 Raeburn Pl', 'Edinburgh ', None, 'United Kingdom', 'EH4 1HH', '+44 0131 315 3300', None, 'steve.murray@yahoo.uk', 5), (55, 'Mark', 'Taylor', None, '421 Bourke Street', 'Sidney', 'NSW', 'Australia', '2010', '+61 (02) 9332 3633', None, 'mark.taylor@yahoo.au', 4), (56, 'Diego', 'Gutiérrez', None, '307 Macacha Güemes', 'Buenos Aires', None, 'Argentina', '1106', '+54 (0)11 4311 4333', None, 'diego.gutierrez@yahoo.ar', 4), (57, 'Luis', 'Rojas', None, 'Calle Lira, 198', 'Santiago', None, 'Chile', None, '+56 (0)2 635 4444', None, 'luisrojas@yahoo.cl', 5), (58, 'Manoj', 'Pareek', None, '12,Community Centre', 'Delhi', None, 'India', '110017', '+91 0124 39883988', None, 'manoj.pareek@rediff.com', 3), (59, 'Puja', 'Srivastava', None, '3,Raj Bhavan Road', 'Bangalore', None, 'India', '560001', '+91 080 22289999', None, 'puja_srivastava@yahoo.in', 3)]\n" ] } ], "source": [ "print(results)" ] }, { "cell_type": "code", "execution_count": 44, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(2, 'Leonie', 'Köhler', None, 'Theodor-Heuss-Straße 34', 'Stuttgart', None, 'Germany', '70174', '+49 0711 2842222', None, 'leonekohler@surfeu.de', 5)\n" ] } ], "source": [ "# fetching 2nd row\n", "sec_row = results[1]\n", "print(sec_row)" ] }, { "cell_type": "code", "execution_count": 45, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['CustomerId', 'FirstName', 'LastName', 'Company', 'Address', 'City', 'State', 'Country', 'PostalCode', 'Phone', 'Fax', 'Email', 'SupportRepId']\n" ] } ], "source": [ "# fetching the column names\n", "print(sec_row.keys())" ] }, { "cell_type": "code", "execution_count": 47, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Leonie\n", "Köhler\n", "Stuttgart\n" ] } ], "source": [ "# fetching column by index and name\n", "print(sec_row[1])\n", "print(sec_row['LastName'])\n", "print(sec_row.City)" ] }, { "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 [conda root]", "language": "python", "name": "conda-root-py" }, "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": 1 }