pyMSSQL/sqlQueries.py at master · FoodSentimentObservatory/pyMSSQL · 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
import config
import pymssql
#connecting to database
def connectionToDatabase():
    server = config.dbServer()
    databased = config.databaseName()
    portd = config.databasePort()
    #connection to the database
    conn = pymssql.connect(server,database=databased,port=portd)
    cursor = conn.cursor()
    print ("Connection to Database successfull")

    return cursor

def connectionToDatabaseTest():
    server = config.dbServer()
    databased = config.databaseName()
    portd = config.databasePort()
    #connection to the database
    conn = pymssql.connect(server,database=databased,port=portd)
    #cursor = conn.cursor()
    print ("Connection to Database " +databased+" was successfull")

    return conn

def closeDbConnection():
    conn = connectionToDatabase.conn
    conn.close()
#collecting all tweets from all locations for a given search description
def retrieveAllTweets(cursor, searchDescription):
    cursor.execute("SELECT [Post].[Id],[Post].[hasCreator],\
    [Post].[body], [Post].[createdAt],\
    [Post].[platformPostID], [UserAccount].[platformAccountId], \
    [Search].[Note],[Post].[SearchId]\
    FROM [Post]\
    INNER join [UserAccount] ON [UserAccount].[Id]= [Post].[hasCreator]\
    INNER join [Search] ON [Post].[SearchId]=[Search].Id\
    WHERE [Search].[Note] LIKE '%"+ searchDescription +"%' ORDER BY [Post].[createdAt] ASC")

    row = cursor.fetchall()

    return row
#collecting all tweets containing a keyword from a given search
def retrieveTweetsByKeyword(cursor, keyword, searchDescription):
    cursor.execute("SELECT [Post].[Id],[Post].[hasCreator],\
    [Post].[body], [Post].[createdAt],\
    [Post].[platformPostID], [UserAccount].[platformAccountId], \
    [Search].[Note],[Post].[SearchId]\
    FROM [Post]\
    INNER join [UserAccount] on [UserAccount].[Id]= [Post].[hasCreator]\
    INNER join [Search] ON [Post].[SearchId]=[Search].Id\
    WHERE [Post].[body] LIKE '%"+ keyword +"%' AND  [Search].[Note] LIKE '%"+ searchDescription +"%'\
    ORDER BY [Post].[createdAt] ASC ")

    result = cursor.fetchall()
    return result
#collecting all tweets from a given search from a given location
def locationQuery(cursor,searchDescription, location):
     cursor.execute("SELECT [Post].[Id],[Post].[hasCreator],\
     [Post].[body], [Post].[createdAt],\
     [Post].[platformPostID], [UserAccount].[platformAccountId],\
     [Post].[SearchId] \
     FROM [Post] \
     INNER join [Search] ON [Post].[SearchId]=[Search].Id \
     INNER join [UserAccount] ON [UserAccount].[Id]= [Post].[hasCreator]\
     WHERE [Search].[Note] LIKE '%"+ searchDescription +"%' AND [Search].[Note] LIKE '%"+ location +"%'\
     ORDER BY [Post].[createdAt] ASC")
     row = cursor.fetchall()

     return row
#collecting all tweets from a given search from a given location containing a given keyword
def locationQueryKeyword(cursor, keyword,searchDescription, location):
     cursor.execute("SELECT [Post].[Id],[Post].[hasCreator],\
     [Post].[body], [Post].[createdAt], [Post].[platformPostID],\
     [UserAccount].[platformAccountId],[Search].[Note], [Post].[SearchId] \
     FROM [Post]\
     INNER join [Search] ON [Post].[SearchId]=[Search].Id\
     INNER join [UserAccount] ON [UserAccount].[Id]= [Post].[hasCreator]\
     WHERE [Search].[Note] LIKE '%"+ searchDescription +"%' AND [Search].[Note] LIKE '%"+ location +"%'\
     AND [Post].[body] LIKE '%" + keyword +"%' \
     ORDER BY [Post].[createdAt] ASC")
     row = cursor.fetchall()

     return row