import pandas as pd
import sqlite3 as sql
wikipedia = pd.read_excel('wikipedia_dataset_flat.xlsx')
wikipedia.head()
Unnamed: 0 | date | page | visits | |
---|---|---|---|---|
0 | 0 | 2016-01-01 | .xxx_en.wikipedia.org_all-access_all-agents | 7089.0 |
1 | 1 | 2016-01-01 | .xxx_en.wikipedia.org_mobile-web_all-agents | 6182.0 |
2 | 2 | 2016-01-01 | 1._Juli_de.wikipedia.org_desktop_all-agents | 20.0 |
3 | 3 | 2016-01-01 | 2014_FIFA_World_Cup_en.wikipedia.org_all-acces... | 5363.0 |
4 | 4 | 2016-01-01 | 2014_FIFA_World_Cup_en.wikipedia.org_desktop_a... | 3186.0 |
##Creating a SQLite Database
## STEP 1: Assigning the new SQLte database we are about to create to the variable db_conn.
db_conn = sql.connect("wikipedia_dataset.db")
##Since the new database file is empty,(i.e, it has no tables), we need to establish a cursor object that will execute the SQL codes to create the data tables
## STEP 2: The cursor is assigned to the variable c
c = db_conn.cursor()
## STEP 4: Create the table that will be included in the database. the will result in empty tables
c.execute(
"""
CREATE TABLE wikipedia_pages (
date INTEGER,
page TEXT NOT NULL,
visits INTEGER
);
"""
)
<sqlite3.Cursor at 0x1bca536c340>
##STEP 5: Populate the table in the database with the relevant data
wikipedia.to_sql('wikipedia', db_conn)
wikipedia
Unnamed: 0 | date | page | visits | |
---|---|---|---|---|
0 | 0 | 2016-01-01 | .xxx_en.wikipedia.org_all-access_all-agents | 7089.0 |
1 | 1 | 2016-01-01 | .xxx_en.wikipedia.org_mobile-web_all-agents | 6182.0 |
2 | 2 | 2016-01-01 | 1._Juli_de.wikipedia.org_desktop_all-agents | 20.0 |
3 | 3 | 2016-01-01 | 2014_FIFA_World_Cup_en.wikipedia.org_all-acces... | 5363.0 |
4 | 4 | 2016-01-01 | 2014_FIFA_World_Cup_en.wikipedia.org_desktop_a... | 3186.0 |
... | ... | ... | ... | ... |
548995 | 548995 | 2016-12-31 | Zac_Efron_en.wikipedia.org_all-access_all-agents | 13146.0 |
548996 | 548996 | 2016-12-31 | Zayn_Malik_en.wikipedia.org_all-access_all-agents | 12597.0 |
548997 | 548997 | 2016-12-31 | Zendaya_en.wikipedia.org_all-access_all-agents | 6217.0 |
548998 | 548998 | 2016-12-31 | Zendaya_en.wikipedia.org_mobile-web_all-agents | 4650.0 |
548999 | 548999 | 2016-12-31 | Zodiac_Killer_en.wikipedia.org_all-access_all-... | 6659.0 |
549000 rows × 4 columns
pd.read_sql("SELECT * FROM wikipedia LIMIT 5", db_conn)
index | Unnamed: 0 | date | page | visits | |
---|---|---|---|---|---|
0 | 0 | 0 | 2016-01-01 00:00:00 | .xxx_en.wikipedia.org_all-access_all-agents | 7089.0 |
1 | 1 | 1 | 2016-01-01 00:00:00 | .xxx_en.wikipedia.org_mobile-web_all-agents | 6182.0 |
2 | 2 | 2 | 2016-01-01 00:00:00 | 1._Juli_de.wikipedia.org_desktop_all-agents | 20.0 |
3 | 3 | 3 | 2016-01-01 00:00:00 | 2014_FIFA_World_Cup_en.wikipedia.org_all-acces... | 5363.0 |
4 | 4 | 4 | 2016-01-01 00:00:00 | 2014_FIFA_World_Cup_en.wikipedia.org_desktop_a... | 3186.0 |