Data-Tech-Final-Project/word_count_postgres.py at main · ysui7/Data-Tech-Final-Project · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
import psycopg2
from server_postgres import execute_sql
from server_postgres import close_db_connection
import server_postgres as sp
import time
import argparse


def connect_db():
    try:
        conn = psycopg2.connect(database='Milestone2', user='postgres',
                                password='123', host='127.0.0.1', port=5432)
    except Exception as e:

        print("fail")
    else:
        return conn
    return None


def execute_select(sql):
    conn = connect_db()
    cur = conn.cursor()
    cur.execute(sql)
    list_header = [row[0] for row in cur.description]
    list_result = [[str(item) for item in row] for row in cur.fetchall()]
    res = [dict(zip(list_header, row)) for row in list_result]
    # print(list_header)
    # print(list_result)
    # print(res)
    return res


def word_table():
    # Creating a table
    sql = "drop table if exists words;"
    execute_sql(sql)
    sql = "CREATE TABLE WORDS(word_id SERIAL PRIMARY KEY,timestamp TIMESTAMP NOT NULL, tweet_id NUMERIC NOT NULL," \
          " word varchar(1000) NOT NULL, phrase_yn NUMERIC NOT NULL);"

    execute_sql(sql)

    print("Table created successfully........")

    # Preparing query to create a database
    conn = connect_db()
    cur = conn.cursor()

    sql = "select * from phrases"
    tweet_table = execute_select(sql)
    # print(ret)
    # print(tweet_table[0]['timestamp'])
    j = 0

    conn = connect_db()
    cur = conn.cursor()
    for row in tweet_table:
        minute_timestamp = str(row['timestamp'])
        # minute_timestamp = minute_timestamp[:16]
        tweet_id = str(row['tweet_id'])
        row['text'] = row['text'].replace("'", '')
        # minute_timestamp = 1
        tweet_text = row['text'].split()
        phrases = [tweet_text[i] + ' ' + tweet_text[i + 1] for i in range(len(tweet_text) - 1)]

        j += 100

        for i in tweet_text:
            # print(tweet_id)

            cur.execute("""insert into words(timestamp, tweet_id, word,  phrase_yn) values(%s,%s,%s,0); """,
                        (minute_timestamp, tweet_id, i))

            sql = ("insert into words(timestamp, tweet_id, word_id, word,  phrase_yn) " + "values(%s,%s,%s,0)" % (
            minute_timestamp, tweet_id, i) + ";")
            # print(sql)

            # execute_sql(sql)
            j += 1

        for k in phrases:
            cur.execute("""insert into words(timestamp, tweet_id, word, phrase_yn) values(%s,%s,%s,1); """,
                        (minute_timestamp, tweet_id, k))

            sql = ("insert into words(timestamp, tweet_id, word,  phrase_yn) " + "values(%s,%s,%s,1)" % (
                minute_timestamp, tweet_id, k) + ";")
            # print(sql)

            # execute_sql(sql)
            j += 1

    close_db_connection(conn)


def word_count_in_current_minute(single_word):
   conn = connect_db()
   cur = conn.cursor()
   sql = """select
            count(word) from words
            where
            timestamp >= date_trunc('minute', localtimestamp) + interval '6 hours'
            and timestamp <= localtimestamp + interval '6 hours'
            and word LIKE %s """
   cur.execute(sql , (single_word,))
   list_header = [row[0] for row in cur.description][0]
   list_result = [[str(item) for item in row] for row in cur.fetchall()][0][0]
   res = [list_header, list_result]

   close_db_connection(conn)

   print('word_count_in_current_minute:')
   print(int(res[1]))
   return res


def main():

    timeout = 0

    parser = argparse.ArgumentParser(description='word count')
    parser.add_argument('--word', dest='words', type=str)

    args = parser.parse_args()


    if args.words:
        word_table()

        try:
            word_count_in_current_minute(args.words.lower())

        except Exception:
            print("Error accessing Database")

    else:
        word_table()
        try:
            word_count_in_current_minute('the')

        except Exception:
            print("Error accessing database")


if __name__ == "__main__":
    main()