import sqlite3

conn = sqlite3.connect('election_tweets.sqlite')
cur = conn.cursor()

"""
Explanation: Analyzing Data using Python and SQLite3
SQLite basics

Create a connection
conn = sqlite3.connect('database_file')
cur = conn.curser()
Execute SQL commands
execute: cur.execute('SQL COMMANDS')
commit to save changes made to the database: conn.commit()
Retrieve results
execute the retrieval query cur.execute('SELECT QUERY')
Fetch the results
Fetch all the results at once: cur.fecthall()
Fetch only one result: cur.fetchone()


Close the connection
conn.close()

Data types
|Data Type| Affinity|
|:--|:--:|
|INT INTEGER TINYINT SMALLINT <br> MEDIUMINT BIGINT UNSIGNED<br> BIG INT INT2 INT8| INTEGER|
|CHARACTER(20) VARCHAR(255) <br>VARYING CHARACTER(255) <br>NCHAR(55) NATIVE CHARACTER(70) <br> NVARCHAR(100) TEXT CLOB| TEXT|
|BLOB no datatype specified |NONE|
|REAL DOUBLE <br>DOUBLE PRECISION FLOAT |REAL|
Example: create a table to store data from a textfile

First, create a connection
End of explanation
"""


cur.execute("DROP TABLE IF EXISTS Tweets")

cur.execute("CREATE TABLE Tweets(state VARCHAR(10), party VARCHAR(20), sentiment INT2)")

conn.commit()

"""
Explanation: Setup/create a table
End of explanation
"""


import pandas as pd

reader = pd.read_table('http://vahidmirjalili.com/election-2016/opFromNLP-2.txt', 
                       sep='|', header=None, chunksize=100)

sentiment={'Neutral':0,
           'Positive':1,
           'Negative':-1}

for chunk in reader:
    for i in range(chunk.shape[0]):
        line = chunk.iloc[[i]].values[0]
        cur.execute("INSERT INTO Tweets (state, party, sentiment) \
                    VALUES (?,?,?)",
                  (line[0], line[1], sentiment[line[2]]))
    conn.commit()

"""
Explanation: Read data using pandas and store them in sqlite
End of explanation
"""


cur.execute('SELECT count(*) FROM Tweets')
num_tweets = cur.fetchall()
print('Total number of tweets: %d'%(num_tweets[0]))

cur.execute('SELECT sentiment,COUNT(*) FROM Tweets GROUP BY sentiment')
results = cur.fetchall()

for res in results:
    print("Count of %d tweets: %d"%(res[0], res[1]))

import seaborn as sns
import matplotlib
import numpy as np
import pandas as pd

results = pd.DataFrame(results)

results.columns = ['Mood', 'Freq']
results['Freq'] = results['Freq']/np.sum(results['Freq'])

%matplotlib inline

ax = sns.barplot(x="Mood", y="Freq", data=results)

cur.execute('SELECT state,SUM(sentiment),count(*) \
            FROM Tweets WHERE party="Democrat" GROUP BY state')
dem_results = cur.fetchall()
cur.execute('SELECT state,SUM(sentiment),count(*) \
            FROM Tweets WHERE party="Republican" GROUP BY state')
rep_results = cur.fetchall()

for dem_res,rep_res in zip(dem_results,rep_results):
    if(len(dem_res[0]) == 2):
        print("%s\tDemocrat: %6.2f\tRepublican: %6.2f"%(
                dem_res[0], dem_res[1]/dem_res[2], rep_res[1]/rep_res[2]))

dem_df = pd.DataFrame(dem_results)
rep_df = pd.DataFrame(rep_results)

df = pd.DataFrame({'state':dem_df[0], 'dem':dem_df[2], 'rep':rep_df[2], 'tot':dem_df[2]+rep_df[2]})

df.to_csv('/tmp/res', sep=' ')

ax = sns.barplot(x="state", y="tot", data=df)

for dem_res,rep_res in zip(dem_results,rep_results):
    if(len(dem_res[0]) == 2):
        if (dem_res[1]/dem_res[2] > rep_res[1]/rep_res[2]):
            print("%s\tDemocrat  \t%.3f"%(
                    dem_res[0], dem_res[1]/dem_res[2] -rep_res[1]/rep_res[2]))
        else:
            print("%s\tRepublican\t%.3f"%(
                    rep_res[0], rep_res[1]/rep_res[2] - dem_res[1]/dem_res[2]))

"""
Explanation: Summarizing Queries

Total number of tweets
sql
SELECT COUNT(*) 
FROM Tweets;
Total number of neutral/positive/negative tweets
sql
SELECT sentiment,COUNT(*)
FROM Tweets
GROUP BY sentiment;
Sum of sentiment values in each state for each party
sql
SELECT state,pary,SUM(sentiment)
FROM Tweets
GROUP BY state,party;
End of explanation
"""


