social_data/get_db_data.py at master · dagg/social_data · 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
# -*- coding: utf-8 -*-

from dbconnect import connection
import gc
import datetime

from get_fbacebook_followers import FB_Followers_parser

import sys
reload(sys)
sys.setdefaultencoding("utf-8")

def get_fb_data(company, since, until):

	c, conn = connection()

	conn.set_character_set('utf8')
	c.execute('SET NAMES utf8;')
	c.execute('SET CHARACTER SET utf8;')
	c.execute('SET character_set_connection=utf8;')
	c.execute("set session sql_mode='';")
	conn.commit()

	# first delete existing
	if since and until and str(since).strip()!='' and str(until).strip()!='':
		since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
		until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
		res = c.execute("select facebook.id, company, company_name, created, message, char_length(message) as message_chars, type, likes, shares, comments from facebook where  message != '' and company= (%s) and created>=Date((%s)) and created<DATE_ADD(DATE((%s)), INTERVAL 1 DAY) ", [company, since, until])
	else:
		res = c.execute("select facebook.id, company, company_name, created, message, char_length(message) as message_chars, type, likes, shares, comments from facebook where  message != '' and company= (%s)", [company])
	data = list(c.fetchall())
	conn.commit()
	c.close()
	conn.close()
	gc.collect()
	return data, company, FB_Followers_parser(company), get_company_branch('facebook', company)

def get_twitter_data(company, since, until):

	c, conn = connection()

	conn.set_character_set('utf8')
	c.execute('SET NAMES utf8;')
	c.execute('SET CHARACTER SET utf8;')
	c.execute('SET character_set_connection=utf8;')
	c.execute("set session sql_mode='';")
	conn.commit()

	if since and until and str(since).strip()!='' and str(until).strip()!='':
		since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
		until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
		res = c.execute("select twitter.id, company, created, text, char_length(text) as text_chars, type, favorites, retweets, retweeted, inreplytouser from twitter where company= (%s) and created>=Date((%s)) and created<DATE_ADD(DATE((%s)), INTERVAL 1 DAY)", [company, since, until])
	else:
		res = c.execute("select twitter.id, company, created, text, char_length(text) as text_chars, type, favorites, retweets, retweeted, inreplytouser from twitter where company= (%s)", [company])
	data = list(c.fetchall())
	conn.commit()
	c.close()
	conn.close()
	gc.collect()
	return data, company, get_company_branch('twitter', company)


def get_googleplus_data(company, since, until):

	c, conn = connection()

	conn.set_character_set('utf8')
	c.execute('SET NAMES utf8;')
	c.execute('SET CHARACTER SET utf8;')
	c.execute('SET character_set_connection=utf8;')
	c.execute("set session sql_mode='';")
	conn.commit()

	if since and until and str(since).strip()!='' and str(until).strip()!='':
		since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
		until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
		res = c.execute("select googleplus.id, company, company_code, created, googleplus.name, char_length(googleplus.name) as name_chars, content, char_length(content) as content_chars, type, comments, likes, shares from googleplus where company= (%s) and created>=Date((%s)) and created<DATE_ADD(DATE((%s)), INTERVAL 1 DAY)", [company, since, until])
	else:
		res = c.execute("select googleplus.id, company, company_code, created, googleplus.name, char_length(googleplus.name) as name_chars, content, char_length(content) as content_chars, type, comments, likes, shares from googleplus where company= (%s)", [company])

	data = list(c.fetchall())


	conn.commit()
	c.close()
	conn.close()
	gc.collect()
	return data, company, get_company_branch('googleplus', company)


def get_branches():

	c, conn = connection()

	conn.set_character_set('utf8')
	c.execute('SET NAMES utf8;')
	c.execute('SET CHARACTER SET utf8;')
	c.execute('SET character_set_connection=utf8;')
	c.execute("set session sql_mode='';")
	conn.commit()

	res = c.execute("select id, name from branches")
	data = list(c.fetchall())
	conn.commit()
	c.close()
	conn.close()
	gc.collect()
	return data


def get_company_branch(social, company):

	c, conn = connection()

	conn.set_character_set('utf8')
	c.execute('SET NAMES utf8;')
	c.execute('SET CHARACTER SET utf8;')
	c.execute('SET character_set_connection=utf8;')
	c.execute("set session sql_mode='';")
	conn.commit()

	res = c.execute("select branches.id, branches.name from {0}, branches where {0}.branch_id=branches.id and company='{1}'".format(social, company))
	data = list(c.fetchall())
	conn.commit()
	c.close()
	conn.close()
	gc.collect()
	return data