In [1]:
import os
import numpy as np
import pandas as pd
import sqlite3
In [2]:
os.getcwd() #현재 디렉토리 확인
Out[2]:
'/Users/limjongjun/Desktop/JayJay/Growth/Python/Class101_Pandas'
DB에 있는 데이터 가져오기¶
In [150]:
connection = sqlite3.connect('sample.db')
In [151]:
query1 = "SELECT * FROM sqlite_master" #sqlite_mastr : DB내의 테이블 정보
In [152]:
pd.read_sql_query(query1, connection)
Out[152]:
| type | name | tbl_name | rootpage | sql | |
|---|---|---|---|---|---|
| 0 | table | albums | albums | 2 | CREATE TABLE "albums"\r\n(\r\n [AlbumId] IN... |
| 1 | table | sqlite_sequence | sqlite_sequence | 3 | CREATE TABLE sqlite_sequence(name,seq) |
| 2 | table | artists | artists | 4 | CREATE TABLE "artists"\r\n(\r\n [ArtistId] ... |
| 3 | table | customers | customers | 5 | CREATE TABLE "customers"\r\n(\r\n [Customer... |
| 4 | table | employees | employees | 8 | CREATE TABLE "employees"\r\n(\r\n [Employee... |
| 5 | table | genres | genres | 10 | CREATE TABLE "genres"\r\n(\r\n [GenreId] IN... |
| 6 | table | invoices | invoices | 11 | CREATE TABLE "invoices"\r\n(\r\n [InvoiceId... |
| 7 | table | invoice_items | invoice_items | 13 | CREATE TABLE "invoice_items"\r\n(\r\n [Invo... |
| 8 | table | media_types | media_types | 15 | CREATE TABLE "media_types"\r\n(\r\n [MediaT... |
| 9 | table | playlists | playlists | 16 | CREATE TABLE "playlists"\r\n(\r\n [Playlist... |
| 10 | table | playlist_track | playlist_track | 17 | CREATE TABLE "playlist_track"\r\n(\r\n [Pla... |
| 11 | index | sqlite_autoindex_playlist_track_1 | playlist_track | 18 | None |
| 12 | table | tracks | tracks | 20 | CREATE TABLE "tracks"\r\n(\r\n [TrackId] IN... |
| 13 | index | IFK_AlbumArtistId | albums | 22 | CREATE INDEX [IFK_AlbumArtistId] ON "albums" (... |
| 14 | index | IFK_CustomerSupportRepId | customers | 23 | CREATE INDEX [IFK_CustomerSupportRepId] ON "cu... |
| 15 | index | IFK_EmployeeReportsTo | employees | 24 | CREATE INDEX [IFK_EmployeeReportsTo] ON "emplo... |
| 16 | index | IFK_InvoiceCustomerId | invoices | 26 | CREATE INDEX [IFK_InvoiceCustomerId] ON "invoi... |
| 17 | index | IFK_InvoiceLineInvoiceId | invoice_items | 27 | CREATE INDEX [IFK_InvoiceLineInvoiceId] ON "in... |
| 18 | index | IFK_InvoiceLineTrackId | invoice_items | 28 | CREATE INDEX [IFK_InvoiceLineTrackId] ON "invo... |
| 19 | index | IFK_PlaylistTrackTrackId | playlist_track | 29 | CREATE INDEX [IFK_PlaylistTrackTrackId] ON "pl... |
| 20 | index | IFK_TrackAlbumId | tracks | 30 | CREATE INDEX [IFK_TrackAlbumId] ON "tracks" ([... |
| 21 | index | IFK_TrackGenreId | tracks | 31 | CREATE INDEX [IFK_TrackGenreId] ON "tracks" ([... |
| 22 | index | IFK_TrackMediaTypeId | tracks | 32 | CREATE INDEX [IFK_TrackMediaTypeId] ON "tracks... |
| 23 | table | sqlite_stat1 | sqlite_stat1 | 864 | CREATE TABLE sqlite_stat1(tbl,idx,stat) |
| 24 | table | Test | Test | 865 | CREATE TABLE Test (\nidx integer prinary key\n... |
In [153]:
qeury2 = "SELECT * FROM 'customers'" #customer table 조회
pd.read_sql_query(qeury2, connection)
Out[153]:
| CustomerId | FirstName | LastName | Company | Address | City | State | Country | PostalCode | Phone | Fax | SupportRepId | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 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 |
| 1 | 2 | Leonie | Köhler | None | Theodor-Heuss-Straße 34 | Stuttgart | None | Germany | 70174 | +49 0711 2842222 | None | leonekohler@surfeu.de | 5 |
| 2 | 3 | François | Tremblay | None | 1498 rue Bélanger | Montréal | QC | Canada | H2G 1A7 | +1 (514) 721-4711 | None | ftremblay@gmail.com | 3 |
| 3 | 4 | Bjørn | Hansen | None | Ullevålsveien 14 | Oslo | None | Norway | 0171 | +47 22 44 22 22 | None | bjorn.hansen@yahoo.no | 4 |
| 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 |
| 5 | 6 | Helena | Holý | None | Rilská 3174/6 | Prague | None | Czech Republic | 14300 | +420 2 4177 0449 | None | hholy@gmail.com | 5 |
| 6 | 7 | Astrid | Gruber | None | Rotenturmstraße 4, 1010 Innere Stadt | Vienne | None | Austria | 1010 | +43 01 5134505 | None | astrid.gruber@apple.at | 5 |
| 7 | 8 | Daan | Peeters | None | Grétrystraat 63 | Brussels | None | Belgium | 1000 | +32 02 219 03 03 | None | daan_peeters@apple.be | 4 |
| 8 | 9 | Kara | Nielsen | None | Sønder Boulevard 51 | Copenhagen | None | Denmark | 1720 | +453 3331 9991 | None | kara.nielsen@jubii.dk | 4 |
| 9 | 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 |
| 10 | 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 |
| 11 | 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 |
| 12 | 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 |
| 13 | 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 |
| 14 | 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 |
| 15 | 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 |
| 16 | 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 |
| 17 | 18 | Michelle | Brooks | None | 627 Broadway | New York | NY | USA | 10012-2612 | +1 (212) 221-3546 | +1 (212) 221-4679 | michelleb@aol.com | 3 |
| 18 | 19 | Tim | Goyer | Apple Inc. | 1 Infinite Loop | Cupertino | CA | USA | 95014 | +1 (408) 996-1010 | +1 (408) 996-1011 | tgoyer@apple.com | 3 |
| 19 | 20 | Dan | Miller | None | 541 Del Medio Avenue | Mountain View | CA | USA | 94040-111 | +1 (650) 644-3358 | None | dmiller@comcast.com | 4 |
| 20 | 21 | Kathy | Chase | None | 801 W 4th Street | Reno | NV | USA | 89503 | +1 (775) 223-7665 | None | kachase@hotmail.com | 5 |
| 21 | 22 | Heather | Leacock | None | 120 S Orange Ave | Orlando | FL | USA | 32801 | +1 (407) 999-7788 | None | hleacock@gmail.com | 4 |
| 22 | 23 | John | Gordon | None | 69 Salem Street | Boston | MA | USA | 2113 | +1 (617) 522-1333 | None | johngordon22@yahoo.com | 4 |
| 23 | 24 | Frank | Ralston | None | 162 E Superior Street | Chicago | IL | USA | 60611 | +1 (312) 332-3232 | None | fralston@gmail.com | 3 |
| 24 | 25 | Victor | Stevens | None | 319 N. Frances Street | Madison | WI | USA | 53703 | +1 (608) 257-0597 | None | vstevens@yahoo.com | 5 |
| 25 | 26 | Richard | Cunningham | None | 2211 W Berry Street | Fort Worth | TX | USA | 76110 | +1 (817) 924-7272 | None | ricunningham@hotmail.com | 4 |
| 26 | 27 | Patrick | Gray | None | 1033 N Park Ave | Tucson | AZ | USA | 85719 | +1 (520) 622-4200 | None | patrick.gray@aol.com | 4 |
| 27 | 28 | Julia | Barnett | None | 302 S 700 E | Salt Lake City | UT | USA | 84102 | +1 (801) 531-7272 | None | jubarnett@gmail.com | 5 |
| 28 | 29 | Robert | Brown | None | 796 Dundas Street West | Toronto | ON | Canada | M6J 1V1 | +1 (416) 363-8888 | None | robbrown@shaw.ca | 3 |
| 29 | 30 | Edward | Francis | None | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | +1 (613) 234-3322 | None | edfrancis@yachoo.ca | 3 |
| 30 | 31 | Martha | Silk | None | 194A Chain Lake Drive | Halifax | NS | Canada | B3S 1C5 | +1 (902) 450-0450 | None | marthasilk@gmail.com | 5 |
| 31 | 32 | Aaron | Mitchell | None | 696 Osborne Street | Winnipeg | MB | Canada | R3L 2B9 | +1 (204) 452-6452 | None | aaronmitchell@yahoo.ca | 4 |
| 32 | 33 | Ellie | Sullivan | None | 5112 48 Street | Yellowknife | NT | Canada | X1A 1N6 | +1 (867) 920-2233 | None | ellie.sullivan@shaw.ca | 3 |
| 33 | 34 | João | Fernandes | None | Rua da Assunção 53 | Lisbon | None | Portugal | None | +351 (213) 466-111 | None | jfernandes@yahoo.pt | 4 |
| 34 | 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 |
| 35 | 36 | Hannah | Schneider | None | Tauentzienstraße 8 | Berlin | None | Germany | 10789 | +49 030 26550280 | None | hannah.schneider@yahoo.de | 5 |
| 36 | 37 | Fynn | Zimmermann | None | Berger Straße 10 | Frankfurt | None | Germany | 60316 | +49 069 40598889 | None | fzimmermann@yahoo.de | 3 |
| 37 | 38 | Niklas | Schröder | None | Barbarossastraße 19 | Berlin | None | Germany | 10779 | +49 030 2141444 | None | nschroder@surfeu.de | 3 |
| 38 | 39 | Camille | Bernard | None | 4, Rue Milton | Paris | None | France | 75009 | +33 01 49 70 65 65 | None | camille.bernard@yahoo.fr | 4 |
| 39 | 40 | Dominique | Lefebvre | None | 8, Rue Hanovre | Paris | None | France | 75002 | +33 01 47 42 71 71 | None | dominiquelefebvre@gmail.com | 4 |
| 40 | 41 | Marc | Dubois | None | 11, Place Bellecour | Lyon | None | France | 69002 | +33 04 78 30 30 30 | None | marc.dubois@hotmail.com | 5 |
| 41 | 42 | Wyatt | Girard | None | 9, Place Louis Barthou | Bordeaux | None | France | 33000 | +33 05 56 96 96 96 | None | wyatt.girard@yahoo.fr | 3 |
| 42 | 43 | Isabelle | Mercier | None | 68, Rue Jouvence | Dijon | None | France | 21000 | +33 03 80 73 66 99 | None | isabelle_mercier@apple.fr | 3 |
| 43 | 44 | Terhi | Hämäläinen | None | Porthaninkatu 9 | Helsinki | None | Finland | 00530 | +358 09 870 2000 | None | terhi.hamalainen@apple.fi | 3 |
| 44 | 45 | Ladislav | Kovács | None | Erzsébet krt. 58. | Budapest | None | Hungary | H-1073 | None | None | ladislav_kovacs@apple.hu | 3 |
| 45 | 46 | Hugh | O'Reilly | None | 3 Chatham Street | Dublin | Dublin | Ireland | None | +353 01 6792424 | None | hughoreilly@apple.ie | 3 |
| 46 | 47 | Lucas | Mancini | None | Via Degli Scipioni, 43 | Rome | RM | Italy | 00192 | +39 06 39733434 | None | lucas.mancini@yahoo.it | 5 |
| 47 | 48 | Johannes | Van der Berg | None | Lijnbaansgracht 120bg | Amsterdam | VV | Netherlands | 1016 | +31 020 6223130 | None | johavanderberg@yahoo.nl | 5 |
| 48 | 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 |
| 49 | 50 | Enrique | Muñoz | None | C/ San Bernardo 85 | Madrid | None | Spain | 28015 | +34 914 454 454 | None | enrique_munoz@yahoo.es | 5 |
| 50 | 51 | Joakim | Johansson | None | Celsiusg. 9 | Stockholm | None | Sweden | 11230 | +46 08-651 52 52 | None | joakim.johansson@yahoo.se | 5 |
| 51 | 52 | Emma | Jones | None | 202 Hoxton Street | London | None | United Kingdom | N1 5LH | +44 020 7707 0707 | None | emma_jones@hotmail.com | 3 |
| 52 | 53 | Phil | Hughes | None | 113 Lupus St | London | None | United Kingdom | SW1V 3EN | +44 020 7976 5722 | None | phil.hughes@gmail.com | 3 |
| 53 | 54 | Steve | Murray | None | 110 Raeburn Pl | Edinburgh | None | United Kingdom | EH4 1HH | +44 0131 315 3300 | None | steve.murray@yahoo.uk | 5 |
| 54 | 55 | Mark | Taylor | None | 421 Bourke Street | Sidney | NSW | Australia | 2010 | +61 (02) 9332 3633 | None | mark.taylor@yahoo.au | 4 |
| 55 | 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 |
| 56 | 57 | Luis | Rojas | None | Calle Lira, 198 | Santiago | None | Chile | None | +56 (0)2 635 4444 | None | luisrojas@yahoo.cl | 5 |
| 57 | 58 | Manoj | Pareek | None | 12,Community Centre | Delhi | None | India | 110017 | +91 0124 39883988 | None | manoj.pareek@rediff.com | 3 |
| 58 | 59 | Puja | Srivastava | None | 3,Raj Bhavan Road | Bangalore | None | India | 560001 | +91 080 22289999 | None | puja_srivastava@yahoo.in | 3 |
