Twit2Blog

TwittoBlog.py

# -*- coding: utf-8 -*-
"""
Created on Sun Jan 14 13:08:30 2018

@author: garym
"""
#run imports 
import pandas as pd
from twitterscraper import query_tweets
import re
from sqlalchemy import create_engine
import pyodbc as py
import urllib 
import codecs
__author__ = 'garymanley@gmail.com (Gary Manley)'
from oauth2client import client
from googleapiclient import sample_tools

# Authenticate and construct service.
argv ='P'
service, flags = sample_tools.init(argv, 'blogger', 'v3', __doc__, __file__, scope='https://www.googleapis.com/auth/blogger')
### set up connection string for database
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()

## create function to download and save tweets
def download(twittersearch,number):
    #### download tweets and write into a file
    file = open(r'C:\Users\garym\Documents\New folder (2)\output.txt','w') 
    file.write('Date\tUser\tTweet\n') #\t Likes \t Retweets \n')
    print('Collecting Data')
    for tweet in query_tweets(twittersearch, number):
        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()
    ### open the file as a dateframe
    df = pd.read_table(r'C:\Users\garym\Documents\New folder (2)\output.txt' , encoding='latin-1')
    print('Inserting Data into Tweets')   
   ### import to database
    df.to_sql(name='Tweets', con=engine, if_exists = 'replace')

### function to create HTML
def createHTML(): 
    print('Creating HTML')

    df = pd.read_sql('SELECT TOP 50 [USER], count(*) TOTAL FROM [Twitter].[dbo].[Tweets] GROUP BY [USER] ORDER BY 2 desc',cnxn)
    df.to_html(r'C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\top50.html')
    
    df = pd.read_sql('select year([date]) Yr , month([date]) mon , day([date]) dy , datepart(hour , [date]) hr , count(*) total from tweets group by year([date])  , month([date])  , day([date])  , datepart(hour , [date])',cnxn) 
   # df.to_html(r'C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\timeTweets.html')
    
    html = (
    df.style
    .set_properties(**{'font-size': '9pt', 'font-family': 'Calibri'})
    .bar(subset=['total'], color='lightblue')
    .render()
    )
    #print(html)
    
    fh = open(r"C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\output.html","w")
    fh.write(html)
    fh.close()

## Create post from linking HTML together     
def post(title,blogid,labels):
          html = 'This is an automatically generated post using python!! <br><br><br>'
          f=codecs.open(r"C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\output.html", 'r')
          html = html + f.read()
          html = html + '<br><br>'
          f=codecs.open(r'C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\top50.html', 'r')
          html = html + f.read()
          posts = service.posts()
          body = {
                  "kind": "blogger#post",
                  "id": blogid,
                  "title": "Twitter Analysis on " + title,
                  "content":html, 
                  "labels":labels
                  }
          insert = posts.insert(blogId=blogid, body=body)
          insert.execute()

def HTMLTEST():
    html = 'This is an automatically generated post using python!! <br><br><br>'
    f=codecs.open(r"C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\output.html", 'r')
    html = html + f.read()
    html = html + '<br><br>'
    f=codecs.open(r'C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\top50.html', 'r')
    html = html + f.read()
    
    fh = open(r"C:\Users\garym\Documents\PyWinAutoBlog\AutoAutoBlog\postSample.html","w")
    fh.write(html)
    fh.close()

#### run the above functions     
download('Low Carb',1000)
createHTML() 
post('Low Carb',"3722853969266245324",['low carb','twitter'])