Twitter Excel Report

TwitterReport.py

# -*- coding: utf-8 -*-
"""
Created on Sat Jan 06 20:36:17 2017

@author: garym
"""

import pandas as pd
from twitterscraper import query_tweets
import re
from sqlalchemy import create_engine
import pyodbc as py
import urllib 
import xlwings as xw

conn_str = (
                   r'Driver={SQL Server};'
                   r'Server=localhost\SQLEXPRESS;'
                   r'Database=Twitter;'
                   r'Trusted_Connection=yes;'
                       )   
quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
cnxn = py.connect(conn_str)
cursor = cnxn.cursor()


download = True
database = True
Excel = True 

if download:
    file = open(r'C:\Users\garym\Documents\New folder (2)\output.txt','w') 
    file.write('Date\tUser\tTweet\n') #\t Likes \t Retweets \n')
    for tweet in query_tweets("Dupuytren", 1000):
        try:
            file.write(str(tweet.timestamp) + '\t' + re.sub(r"[\n\t]*", "",tweet.user) + '\t' + re.sub(r"[\n\t]*", "",tweet.text) + '\n' ) #'\t' +  tweet.likes + '\t' + tweet.retweets + '\n')
        except: 
            print('error on tweet')
    file.close()

    df = pd.read_table(r'C:\Users\garym\Documents\New folder (2)\output.txt' , encoding='latin-1')
    #print(df)

    df.to_csv(r'C:\Users\garym\Documents\New folder (2)\output.csv')

if database:
    df = pd.read_csv(r'C:\Users\garym\Documents\New folder (2)\output.csv' , encoding='latin-1')
    df.to_sql(name='Dupuytrens', con=engine, if_exists = 'replace')

if Excel:
    df10 = pd.read_sql('select top 10 [User], count([Tweet]) Total from Dupuytrens group by [User] order by 2 desc' , cnxn)
    dfMon = pd.read_sql('select year([Date]) [Year], month([Date]) [Month] , count([Tweet]) Total from Dupuytrens group by  year([Date]) , month([Date]) order by 1, 2 ' , cnxn)
    wb = xw.Book(r'C:\Users\garym\Documents\PyWinAutoBlog\File\DupReport.xlsx')
    wb.sheets['Top10'].select()
    xw.Range('A1', index=False,header = True).value = df10
    wb.sheets['ByMonth'].select()
    xw.Range('A1', index=False,header = True).value = dfMon
    wb.save()
    wb.close()