def test_normal_select_with_where_condition_As_Null(self):
print('test_normal_select_with_column_simple_where - Begin')
query = Query('sample-1')
print('TableName : {}, Query Built:{}'.format(query.table_name, query.query_built))
query.select().columns('a, b, c').where_null("a")
print('TableName : {}, Query Built:{}'.format(query.table_name, query.query_built))
self.assertEqual("Select a, b, c where a is Null".upper().strip(), query.query_built.upper().strip())
print('test_normal_select_with_column_simple_where - End')
def test_normal_select_with_column(self):
print('test_normal_select_with_column - Begin')
query = Query('sample-1')
print('TableName : {}, Query Built:{}'.format(query.table_name, query.query_built))
query.select().columns('a, b, c')
print('TableName : {}, Query Built:{}'.format(query.table_name, query.query_built))
self.assertEqual("Select a, b, c".upper().strip(), query.query_built.upper().strip())
print('test_normal_select_with_column - End')
def test_select_where_column_relates_another_column(self):
print('test_select_where_column_relates_another_column - Begin')
query = Query('aSample')
query.select().from_table().alias('a').join('bSample', 'b').on().join_column('bSample', 'field-1').equals() \
.join_column('aSample', 'field-1').where_column_relates_another_column('a.field2', ' = ', 'b.field2')
print(query.query_built)
self.assertEqual("Select From aSample as a join bSample as b on b.field-1 = a.field-1 where a.field2 = "
"b.field2".upper().strip(), query.query_built.upper().strip())
print('test_select_where_column_relates_another_column - End')
def test_join_select_Without_Condition(self):
print('test_normal_select_with_column_Join - Begin')
query = Query('sample-1')
print('TableName : {}, Query Built:{}'.format(query.table_name, query.query_built))
query.select().columns('a.sample-1_2, a.sample-1_3, b.sample-1_2, b.sample-1_4').from_table().alias("a") \
.join("sample-2", "b").on().join_column("sample-1", "sample-1_2").equals() \
.join_column("sample-2", "sample-1_2")
print(query.query_built.upper().strip())
self.assertEqual("Select a.sample-1_2, a.sample-1_3, b.sample-1_2, b.sample-1_4 From sample-1 as a join "
"sample-2 as b on a.sample-1_2 = b.sample-1_2".upper().strip(), query.query_built.upper()
.strip())
print('test_normal_select_with_column_Join - End')
def __init__(self,submission):
super(SubmissionQuery,self).__init__(submission)
if self.author is None:
self.author = 'None'
self.title = Query.addquote(self.title)
self.label = LabelMaker().create(self.submission)
self.created = datetime.fromtimestamp(
self.created, tz=timezone('US/Eastern')).strftime("%Y-%m-%d")
def test_select_union_query(self):
print('test_select_union_query - Begin')
query = Query('Sample-1').select().columns('*').from_table().union().select().columns('*') \
.from_table('Sample-2')
print(query.query_built)
self.assertEqual("Select * From Sample-1 Union Select * From Sample-2".upper().strip(),
query.query_built.upper().strip())
print('test_select_union_query - End')
def test_select_complex_condition_where(self):
print('test_select_complex_condition_where - Begin')
query = Query('aSample').select().from_table().where_column_relates_another_column('field-1', '=', 10) \
.and_op() \
.column("field-2").in_op().open_brackets().select().column('id').from_table('bSample').where_column('id') \
.equals().value(10).close_brackets()
print(query.query_built)
self.assertEqual("Select From aSample where field-1 = 10 And field-2 in (Select id From bSample Where id = 10 )"
.upper().strip(), query.query_built.upper().strip())
print('test_select_complex_condition_where - End')
def test_Whether_DB_Is_Connecting_And_Returning_Values(self):
conn = PostgresConnection()
conn.init_from_file('simple.json', 'dev', 'dbConfig')
conn.open_connection()
query = Query('public."application"').select().column('*').from_table()
db = Postgres(conn)
rows = db.fetch_all(query.query_built)
print(rows)
print(len(rows))
self.assertTrue(len(rows) > 0)
conn.close_connection()
def __init__(self,comment):
super(CommentQuery,self).__init__(comment)
if self.author is None:
self.author = 'None'
if not self.edited:
self.edited = str(0)
else:
self.edited = str(1)
if self.body == '[removed]' or self.body == '[deleted]':
self.deleted = str(1)
else:
self.deleted = str(0)
self.created = datetime.fromtimestamp(
self.created, tz=timezone('US/Eastern')).strftime("%Y-%m-%d")
self.body = Query.addquote(self.body)
import sqlite3
from Database import Query
from Labels import LabelMaker
# Need a "fake" submission in order to work with new stupid stuff
class fakeSubmis:
def __init__(self,name,domain):
self.name = name
self.domain = domain
whichDatabase = r"canada_subreddit.db"
db = Query(whichDatabase)
db.connect()
cur = db.cursor()
cur.execute('SELECT title, domain, submission_id FROM submissions')
lm = LabelMaker()
labels = []
for c in cur:
fs = fakeSubmis(c[0],c[1])
cur_label = lm.create(fs)
labels.append((cur_label, c[2]))
cur.executemany("UPDATE submissions SET label = ? WHERE submission_id = ?", labels)
db.commit()
db.close()
def test_insert_query_built_successful(self):
key = [{'a': 'a', 'b': 'b'}, {'c': 'c', 'd': 'd'}]
query_builder = Query('')
query_builder.insert_multiple('sample', key)
self.assertEqual("Insert into sample (a, b) values ('a', 'b'), ('c', 'd')", query_builder.query_built)
from Database import Query
from Body import FeaturePipeline
import pickle
from nltk import NaiveBayesClassifier
from nltk import classify
db = Query('canada_subreddit.db')
db.connect()
cur = db.cursor()
cur.execute('''
SELECT c.body, s.label
FROM submissions as s, comments as c
WHERE s.submission_id = c.submission_id
AND (s.label = "Climate"
OR s.label = "Housing");
''')
data = cur.fetchall()
feature_set = FeaturePipeline().create_set(data)
split = lambda x: - int(len(x) / 5)
k = split(feature_set)
training_set = feature_set[:k]
testing_set = feature_set[k:]
print('Now training...')
Naive_classifier = NaiveBayesClassifier.train(training_set)
print("Naive Bayes Algo accuracy percent:", (classify.accuracy(Naive_classifier, testing_set)))
Naive_classifier.show_most_informative_features(30)
# Connect to reddit
reddit = praw.Reddit(user_agent='CanadaGather',
client_id='tuM5-a7hYWtsQw',
client_secret='m5IziOvCOw3-GqyhrePtSFnj858',
username='******',
password='******')
canada = reddit.subreddit('canada')
# Extract submisions
allSubmissions = canada.submissions(int(time.mktime(ext_from.timetuple())),
int(time.mktime(ext_to.timetuple())))
# Load the data into sqlite3
for submis in allSubmissions:
db = Query(whichDatabase)
db.connect()
if submis.num_comments >= commentMin:
# Enter submission:
sq = SubmissionQuery(submis)
try:
db.execute(sq.insert())
except sqlite3.IntegrityError:
db.execute(sq.update())
# Enter users:
uq = UserQuery(submis)
if uq.id is not None:
try:
db.execute(uq.insert())