def send_message(messageJson):
third = messageJson['trd']
seq = messageJson['seq']
sqli = "select openid,sequence from session where third_session=%s;"
sql = "select sendtime,content,sessionid from log natural join userid where openid=%s;"
sqld = "delete from log where openid=%s;"
try:
cur.execute(sqli, [third])
temp = cur.fetchall()
if temp:
openid = temp[0][0]
seqs = temp[0][1]
if seq != seqs:
print seq, " ", seqs
return [], 0
else:
seq = seq + 1
seqs = seqs + 2
sqls = "update session set sequence=%s where third_session=%s"
cur.execute(sqls, [seqs, third])
conn.commit()
cur.execute(sql, [openid])
log = cur.fetchall()
cur.execute(sqld, [openid])
conn.commit()
if log:
return log, seq
else:
return [], seq
else:
print "no user"
return [], 0
except:
print "sql error"
return [], 0
def updateFlight(flight_num, dept_time):
#get airline name
airline_name = session['airline_name']
if request.method == "POST":
#fetch selection
new_status = request.form.get('statusSelect')
print("new-status", new_status)
#update database
cursor = conn.cursor()
query = "UPDATE flight SET flight_status = %s WHERE (airline_name, flight_num, dept_time) = (%s, %s, %s)"
cursor.execute(query,
(new_status, airline_name, flight_num, dept_time))
cursor.close()
conn.commit()
message = "Update Flights Success"
session["flight_updated"] = True
return redirect("/flightManage")
else:
cursor = conn.cursor()
#check if such flight exits
query = "SELECT * FROM flight WHERE airline_name = %s AND flight_num = %s AND dept_time = %s"
cursor.execute(query, (airline_name, flight_num, dept_time))
data = cursor.fetchone()
cursor.close()
if data:
print(data)
return render_template("updateFlight.html", flight=data)
else:
noFound = "There's an issue in updating the flight: such flight does not exist"
return render_template('flightManage.html', noFound=noFound)
def add_user(cls, username, password, email):
query = f"INSERT INTO users (username, password, email) VALUES ('{username}', '{password}', '{email}')"
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
cursor.close()
return True
def viewMyFlights():
if session['role'] == 'customer':
email = session['email']
username = session['username']
cursor = conn.cursor()
current_date = datetime.datetime.now()
query = "select * from ticket natural join flight natural join airport as A, airport as B where cust_email = %s and dept_time > %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (email, current_date))
data1 = cursor.fetchall()
conn.commit()
cursor.close()
return render_template('viewMyFlights.html',
flights=data1,
role='customer')
elif session['role'] == 'agent':
email = session['email']
cursor = conn.cursor()
current_date = datetime.datetime.now()
query = "select * from ticket natural join flight natural join airport as A, airport as B where agent_email = %s and dept_time > %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (email, current_date))
data1 = cursor.fetchall()
conn.commit()
cursor.close()
return render_template('viewMyFlights.html',
flights=data1,
role='agent')
def saveToDatabase(nvdResult, productResult, referenceResult, logicalTestResult):
cur.execute(INSERT_NVD % (
nvdResult['cve_id'],
nvdResult['published_datetime'],
nvdResult['last_modified_datetime'],
nvdResult['score'],
nvdResult['access_vector'],
nvdResult['access_complexity'],
nvdResult['authentication'],
nvdResult['confidentiality_impact'],
nvdResult['integrity_impact'],
nvdResult['availability_impact'],
nvdResult['source'],
nvdResult['generated_on_datetime'],
nvdResult['cwe_id'],
nvdResult['summary'],
))
id = cur.lastrowid
for product in productResult:
cur.execute(INSERT_PRODUCT % (id, product))
for reference in referenceResult:
cur.execute(INSERT_REFERENCE % (id, reference['type'], reference['source'], reference['reference'], reference['url']))
for logicalTest in logicalTestResult:
cur.execute(INSERT_LOGICAL_TEST % (id, logicalTest))
conn.commit()
def change_password(email):
user = cursor.execute(
f'SELECT * FROM USER WHERE EMAIL="{email}";').fetchone()
if not user:
print(f"***The user with '{email}' email address does not exist***")
return
user_old_pass = user[2]
old_pass_valid = False
while True:
if not old_pass_valid:
old_password = getpass.getpass(prompt="Old password: "******"***Invalid old password***")
continue
else:
old_pass_valid = True
new_password = getpass.getpass(prompt='New password:'******'{generate_password_hash(new_password)}' WHERE email='{email}';"
)
conn.commit()
print("***Password has been changed successfully!***")
break
def editPwd():
#cursor.execute("SELECT password FROM users where email = %s;", (session['logged_email'],))
#u = cursor.fetchone()
error = None
if request.method == 'POST':
oldPassword = request.form['oldPassword'].strip()
newPassword = request.form['newPassword'].strip()
newPassword2 = request.form['newPassword2'].strip()
cursor.execute("SELECT user_id FROM users WHERE email = %s AND password = crypt(%s, password);", \
(session['logged_email'], oldPassword))
u = cursor.fetchone()
if u is None:
error = "Your old password is not right."
#if not bcrypt.check_password_hash(u[0], oldPassword):
# error = 'Your old password is not right.'
elif newPassword != newPassword2:
error = 'The password is not repeated correctly'
elif len(newPassword) < 6:
error = 'The password has at least 6 characters'
else:
#password = bcrypt.generate_password_hash(newPassword)
password = newPassword
cursor.execute("UPDATE users SET password = crypt(%s, gen_salt('bf', 8)) where email = %s", \
(password, session['logged_email']))
conn.commit()
flash('Edit Password Success!')
return render_template('users/edit.html', u=u, error=error)
def create_user():
email_valid = False
while True:
if not email_valid:
email = input("Email: ")
email_valid = check_email(email)
if not email_valid:
print("***Invalid email address***")
continue
user = cursor.execute(
f'SELECT * FROM USER WHERE EMAIL="{email}";').fetchone()
if user:
print("***Email address already exists***")
email_valid = False
continue
email_valid = True
password = getpass.getpass()
if len(password) < 6:
print("***Password must have min 6 characters***")
continue
confirm_pass = getpass.getpass(prompt="Confirm password: "******"***Passwords do not match***")
continue
cursor.execute(
f"Insert Into USER ('EMAIL','PASSWORD') Values ('{email}', '{generate_password_hash(password)}');"
)
conn.commit()
print("***User has been created successfully!***")
break
def registerAuthCustomer():
#grabs information from the forms
email = request.form['email']
password = sha256_crypt.encrypt(request.form['password'])
name = request.form['name']
building_num = request.form['building_num']
street = request.form['street']
city = request.form['city']
state = request.form['state']
phone_num = request.form['phone_num']
passport_num = request.form['passport_num']
passport_expr = request.form['passport_expr']
passport_country = request.form['passport_country']
DOB = request.form['DOB']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM customer WHERE email = %s'
cursor.execute(query, (email))
#stores the results in a variable
data = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
error = None
if(data):
#If the previous query returns data, then user exists
error = "This user already exists"
cursor.close()
return render_template('register.html', error = error)
else:
ins = 'INSERT INTO customer VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
cursor.execute(ins, (email, password, name, building_num, street, city, state, phone_num, passport_num, passport_expr, passport_country, DOB, 0))
conn.commit()
cursor.close()
return render_template('index.html')
def storeFile(self, commit):
try:
#Need to check if we are saving an image or a document.
if self.file_type == FILE_TYPE_Images:
#This means that we are saving an image.
image_id = returnNextSerialID('images', 'image_id')
File.checkAndStoreDocType(self, False)
if self.image_date_taken is not None:
insert_image_date_taken = f"to_timestamp( '{convertJSONDate(self.image_date_taken)}', 'YYYY-MM-DD HH24:MI:SS' )"
else:
insert_image_date_taken = "NULL"
images_insert_statement = f""" INSERT into images( advert_id, date_taken, url )
VALUES( {ifNotNone(self.image_advert_id, "NULL")}, {insert_image_date_taken}, '{self.image_images_url}' )"""
cur.execute(images_insert_statement, "")
cur.execute(
f""" INSERT INTO files( file_id, file_type, object_type, keyval1, keyval2, document_type, entered_when )
VALUES( {image_id}, '{FILE_TYPE_Images}', {self.object_type}, '{self.keyval1}', '{self.keyval2}', {self.document_type}, current_timestamp ) """,
"")
if commit:
conn.commit()
return True
elif self.file_type == FILE_TYPE_Documents:
pass
else:
raise ValueError(str(self) + " has an invalid file type")
except (ValueError, Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING File with File ID " + str(image_id) +
"\n" + "Error " + error)
return False
def checkFlight():
#grabs information from the forms
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
dept_date = request.form['dept_date']
arr_date = request.form['arr_date']
#open cursor
cursor = conn.cursor()
#excutes query
if dept_date and arr_date:
query = "select * from flight where airline_name = %s and flight_num = %s and date(dept_time) = %s and date(arr_time) = %s"
cursor.execute(query, (airline_name, flight_num, dept_date, arr_date))
elif dept_date:
query = "select * from flight where airline_name = %s and flight_num = %s and date(dept_time) = %s"
cursor.execute(query, (airline_name, flight_num, dept_date))
elif arr_date:
query = "select * from flight where airline_name = %s and flight_num = %s and date(arr_time) = %s"
cursor.execute(query, (airline_name, flight_num, arr_date))
else:
pass
#store the results
data3 = cursor.fetchall()
cursor.close()
conn.commit()
error = None
if data3:
return render_template("check.html", status=data3)
else:
error = "The Flight You are Searching Is Empty"
return render_template("check.html", error=error)
def purchaseTickets():
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
dept_time = request.form['dept_time']
current_price = request.form['price']
airline_name2 = request.form['airline_name2']
flight_num2 = request.form['flight_num2']
dept_time2 = request.form['dept_time2']
current_price2 = request.form['price2']
total = float(current_price) + float(current_price2)
#open cursor
cursor = conn.cursor()
#excutes query for flight
query = "select * from flight natural join airplane, airport as A, airport as B where airline_name = %s and flight_num = %s and dept_time = %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (airline_name, flight_num, dept_time))
#store the results
data = cursor.fetchone()
data['current_price'] = current_price
data2 = 0
if airline_name2 != '':
query = "select * from flight natural join airplane, airport as A, airport as B where airline_name = %s and flight_num = %s and dept_time = %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (airline_name2, flight_num2, dept_time2))
data2 = cursor.fetchone()
data2['current_price'] = current_price2
conn.commit()
cursor.close()
print(airline_name)
return render_template("purchaseTickets.html",
flight=data,
return_flight=data2,
total=total,
role=session['role'])
def adminRegistration(admin):
print('\nDigite os dados para a criação de um novo administrador!')
name = input('\nUsuário: ')
while True:
password = input('Senha: ')
if validatePassword(password):
salt = os.urandom(32)
key = hashPassword(password, salt)
cur.execute(
'''INSERT INTO administrator (name, salt, key_pass) VALUES (%s, %s, %s)''',
(name, salt, key))
conn.commit()
print('\nAdministrador cadastrado com sucesso!')
break
else:
print(
'\nA senha deve conter mais que 6 dígitos! Tente novamente.\n')
getOption(admin)
def set_host_profile(host, new_profile):
with get_cursor() as cur:
cur.execute(
f"INSERT INTO pp_host (name) VALUES (%s) ON CONFLICT DO NOTHING;",
(host, ))
cur.execute(
f"""
SELECT pp_profile_link.profile FROM pp_profile_link
JOIN pp_host ON pp_profile_link.host = pp_host.id
WHERE pp_host.name = %s
ORDER BY pp_profile_link.id DESC LIMIT 1;
""", (host, ))
result = cur.fetchone()
if result:
old_profile = result[0]
if new_profile == old_profile:
return
cur.execute(
f"""
INSERT INTO pp_profile_link (host, profile, date)
SELECT pp_host.id, %s, now() FROM pp_host
WHERE pp_host.name = %s;
""", (new_profile, host))
conn.commit()
def checkRatings(flight_num, dept_time):
#get airline name
airline_name = session['airline_name']
#fetch data
cursor = conn.cursor()
query = "SELECT airline_name,flight_num, dept_time, AVG(rate) as avg_rate \
FROM rates \
WHERE (airline_name,flight_num, dept_time) = (%s, %s, %s)"
cursor.execute(query, (airline_name, flight_num, dept_time))
data1 = cursor.fetchone()
if data1["avg_rate"]:
avg_rate = "{0:.2f}".format(float(data1["avg_rate"]))
else:
noFound = "This Flight has no ratings yet"
return render_template("report.html", noFound=noFound)
query = "SELECT airline_name,flight_num, dept_time, cust_email, rate, comments \
FROM rates \
WHERE (airline_name,flight_num, dept_time) = (%s, %s, %s) "
cursor.execute(query, (airline_name, flight_num, dept_time))
data = cursor.fetchall()
cursor.close()
conn.commit()
if data:
for each in data:
print(each)
return render_template("report.html", avg_rate=avg_rate, ratings=data)
else:
noFound = "This Flight has no ratings yet"
return render_template("report.html", noFound=noFound)
def registerAuthAgent():
email = request.form['email']
password = sha256_crypt.encrypt(request.form['password'])
agent_id = request.form['id']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM booking_agent WHERE email = %s'
cursor.execute(query, (email))
#stores the results in a variable
data = cursor.fetchone()
query = 'SELECT * FROM booking_agent WHERE agent_id = %s'
cursor.execute(query, (agent_id))
data2 = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
error = None
if(data):
#If the previous query returns data, then user exists
error = "This user already exists"
cursor.close()
return render_template('register.html', error = error)
elif (data2):
#If the previous query returns data, then user exists
error = "This agent id already exists"
cursor.close()
return render_template('register.html', error = error)
else:
ins = 'INSERT INTO booking_agent VALUES(%s, %s, %s, %s)'
cursor.execute(ins, (email, password, agent_id, 0))
conn.commit()
cursor.close()
return render_template('index.html')
def checkAndStoreDocType(self, commit):
try:
if not checkRowExists(
f"SELECT 1 FROM document_type WHERE UPPER(description) = '{self.document_type_description}'"
):
document_type = returnNextSerialID('document_type',
'document_type')
cur.execute(
f""" INSERT INTO document_type(description)
VALUES( '{self.document_type_description}' )""",
"")
if commit:
conn.commit()
self.document_type = document_type
else:
cur.execute(
f"SELECT document_type FROM document_type WHERE UPPER(description) = '{self.document_type_description}'",
"")
result = cur.fetchone()
self.document_type = result[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error in Checking Description of File with File ID " +
str(self.file_id) + " with Document Type Description " +
self.document_type_description + "\n" + "Error: " + error)
return None
def delete(cmt_id):
if request.method == 'GET':
cursor.execute("SELECT msg_id FROM comment where cmt_id = %s;", (cmt_id,))
m = cursor.fetchone()
cursor.execute("DELETE FROM comment where cmt_id = %s;", (cmt_id,))
conn.commit()
flash('Delete Success!')
return redirect(url_for('comment.show', msg_id=m[0]))
def deleteInvite(inviteCode):
sql = "delete from invite where inviteCode=%s"
try:
cur.execute(sql, [inviteCode])
conn.commit()
except:
pass
return
def unlike(msg_id):
if request.method == 'GET':
user_id = session['logged_id']
cursor.execute(
"DELETE FROM like_msg where msg_id = %s AND user_id = %s;",
(msg_id, user_id))
conn.commit()
return redirect(url_for('show_entries'))
def register(userid):
sqli = "insert into user(iduser) values(%s);"
try:
cur.execute(sqli, (userid))
conn.commit()
return 1
except:
return 0
def saveInvite(inviteCode, trd, userid):
sql = "insert into invite values(%s,%s,%s)"
try:
cur.execute(sql, [inviteCode, trd, userid])
conn.commit()
except:
pass
return
def add():
if request.method == 'POST':
msg_id = int(request.form['msg_id'])
user_id = session['logged_id']
content = request.form['content']
c_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute("INSERT INTO comment(msg_id,user_id,content,c_time) VALUES(%s,%s,%s,%s);", (msg_id, user_id, content, c_time))
conn.commit()
return redirect(url_for('comment.show', msg_id=msg_id))
def like(msg_id):
if request.method == 'GET':
user_id = session['logged_id']
c_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute(
"INSERT INTO like_msg(msg_id, user_id,c_time) VALUES(%s,%s,%s);",
(msg_id, user_id, c_time))
conn.commit()
return redirect(url_for('show_entries'))
def purchaseDetails():
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
dept_time = request.form['dept_time']
current_price = request.form['price']
airline_name2 = request.form['airline_name2']
flight_num2 = request.form['flight_num2']
dept_time2 = request.form['dept_time2']
current_price2 = request.form['price2']
card_type = request.form['card_type']
card_num = request.form['card_num']
name_on_card = request.form['name_on_card']
expr_date = request.form['expr_date']
cursor = conn.cursor()
time = datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
if session['role'] == 'agent':
cust_email = request.form['cust_email']
query = "select count(*) from customer where email = %s"
cursor.execute(query, cust_email)
num = cursor.fetchone()
print(num)
if num['count(*)'] == 0:
return "Failure"
#excutes query for flight
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(query,
(str(datetime.datetime.now().timestamp()),
current_price, card_type, card_num, name_on_card,
expr_date, time, session['email'], cust_email,
airline_name, flight_num, dept_time))
#store the results
if len(airline_name2) > 0:
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(query,
(str(datetime.datetime.now().timestamp()),
current_price2, card_type, card_num, name_on_card,
expr_date, time, session['email'], cust_email,
airline_name2, flight_num2, dept_time2))
else:
#excutes query for flight
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, null, %s, %s, %s, %s)"
cursor.execute(
query, (str(datetime.datetime.now().timestamp()), current_price,
card_type, card_num, name_on_card, expr_date, time,
session['email'], airline_name, flight_num, dept_time))
#store the results
if len(airline_name2) > 0:
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, null, %s, %s, %s, %s)"
cursor.execute(
query,
(str(datetime.datetime.now().timestamp()), current_price2,
card_type, card_num, name_on_card, expr_date, time,
session['email'], airline_name2, flight_num2, dept_time2))
conn.commit()
cursor.close()
return "Success"
def add():
if request.method == 'POST':
user_id = session['logged_id']
content = request.form['content']
c_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute(
"INSERT INTO message(user_id,content,c_time) VALUES(%s,%s,%s);",
(user_id, content, c_time))
conn.commit()
return redirect(url_for('show_entries'))
def delete_user(email):
user = cursor.execute(
f'SELECT * FROM USER WHERE EMAIL="{email}";').fetchone()
if not user:
print(f"***The user with '{email}' email address does not exist***")
return
cursor.execute(f"DELETE FROM USER WHERE email='{email}';")
conn.commit()
print("***User has been deleted successfully!***")
def find_user_id(id_number):
cur.execute(
"""
SELECT * FROM users WHERE id_number='{}' """.format(id_number))
conn.commit()
rows = cur.fetchone()
if rows :
return rows['id']
return 0
def find_address_id(user_id):
cur.execute(
"""
SELECT * FROM address WHERE user_id='{}' """.format(user_id))
conn.commit()
rows = cur.fetchone()
if rows :
return rows['estate']
return 0
def unlike(cmt_id):
if request.method == 'GET':
user_id = session['logged_id']
cursor.execute(
"DELETE FROM like_cmt where cmt_id = %s AND user_id = %s;",
(cmt_id, user_id))
conn.commit()
cursor.execute("SELECT msg_id FROM comment WHERE cmt_id = %s",
(cmt_id, ))
c = cursor.fetchone()
return redirect(url_for('comment.show', msg_id=c[0]))
def comments():
#open cursor
cursor = conn.cursor()
#excutes query for flight
query = "select * from ticket natural join flight natural join airport as A, airport as B where cust_email = %s and dept_time < %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (session['email'], datetime.datetime.now()))
#store the results
data = cursor.fetchall()
conn.commit()
cursor.close()
return render_template("comments.html", flights=data)
def load_followers_for_users_from_file():
huff_twitt_matches = open("matching_users.txt", "r")
failed_users_file = open("failed_users3.txt", "a")
cur = conn.cursor()
start_time = time.time()
line_no = 0
LINES_TO_SKIP = 0
for line in huff_twitt_matches:
curr_time = time.time()
print("Elapsed time: ", curr_time - start_time)
line_no += 1
if line_no < LINES_TO_SKIP:
continue
try:
huff_name, twitter_names = split_huff_and_twitter_line_from_list(line)
for twitter_name in twitter_names:
twitter_user = get_api_and_move_to_end().get_user(twitter_name) # 1 CALL
print("Working on ", twitter_name, " having ", twitter_user.followers_count, "followers")
save_user_in_db(twitter_user, cur)
link_huff_user_with_twitter(huff_name, twitter_user.id, cur)
next_cursor = -1
followers = get_api_and_move_to_end().followers(twitter_user.id, next_cursor) # 1 CALL
for follower in followers:
save_user_in_db(follower, cur)
save_a_follower(twitter_user.id, follower.id, cur)
try:
conn.commit()
except:
print("### SOMETHING BAD HAS HAPPENED WHEN WORKING ON {}, BUT WE GO FORWARD".format(twitter_name))
conn.rollback()
failed_users_file.write(line + "\n")
failed_users_file.flush()
except:
print("### SOMETHING HAS BROKEN INCORRECTLY ", str(sys.exc_info()), traceback.print_exc())
conn.rollback()
failed_users_file.write(line)
failed_users_file.flush()
failed_users_file.close()
def extract_hashtags_from_tweets():
cur = conn.cursor()
cur.execute("SELECT t.id, t.text FROM tweets t")
for row in cur.fetchall():
tweet_id = row[0]
hashtags = re.findall("#([a-zA-Z]+)", row[1])
print(hashtags)
for hashtag in set(hashtags):
hashtag = hashtag.lower()
cur.execute("SELECT tweet_id FROM twitter_hashtags WHERE tweet_id = %s AND hashtag = %s",
(tweet_id, hashtag))
if not cur.fetchone():
cur.execute("INSERT INTO twitter_hashtags (tweet_id, hashtag) VALUES (%s, %s)", (tweet_id, hashtag))
conn.commit()
def transform_huff_tags():
cur = conn.cursor()
cur.execute("SELECT id, name FROM tags")
i = 0
for tag_id, name in cur.fetchall():
i += 1
if i % 1000 == 0:
print(i)
conn.commit()
new_name = name.replace(" ", "").lower()
cur.execute("INSERT INTO cleaned_tags (tag_id, name) VALUES (%s, %s)", (tag_id, new_name))
conn.commit()
def update_followers_and_followed_count_for_fetched(cur):
i = 0
for (twitter_user_id, twitter_user_name) in cur.fetchall():
i += 1
try:
twitter_user = get_api_and_move_to_end().get_user(twitter_user_id)
followers = twitter_user.followers_count
followed = twitter_user.friends_count
cur.execute("UPDATE twitter_users SET followers = %s, followed = %s WHERE id = %s",
(followers, followed, twitter_user_id))
if i % 100 == 0:
print(i)
conn.commit()
except:
print("### SOMETHING HAS BROKEN INCORRECTLY ", str(sys.exc_info()), traceback.print_exc())
conn.rollback()
def top_hashtags():
top_tags = open("data/huff_top_tags.csv", "r")
failed_tags_file = open("failed_tags.txt", "a")
cur = conn.cursor()
start_time = time.time()
LINES_TO_SKIP = 0
line_no = 0
for tag in top_tags:
line_no += 1
if line_no < LINES_TO_SKIP:
continue
tag = tag.strip()
tag = tag.replace(" ", "")
print("Search for tag ", tag)
print("Time elapsed: ", (time.time() - start_time))
try:
search_results = get_api_and_move_to_end().search(q="#" + tag, lang="en", rpp=100)
for result in search_results:
if not is_user_in_db(result.author.id, cur):
save_user_in_db(result.author, cur)
cur.execute("""INSERT INTO tweets (id, author_id, text, date)
VALUES (%s, %s, %s, %s)""", (result.id, result.author.id, result.text, result.created_at))
conn.commit()
except:
print("### SOMETHING HAS BROKEN INCORRECTLY ", str(sys.exc_info()), traceback.print_exc())
conn.rollback()
failed_tags_file.write(tag)
failed_tags_file.flush()
def load_following_for_users_from_file():
huff_twitt_matches = open("matching_users.txt", "r")
failed_users_file = open("failed_users4.txt", "a")
cur = conn.cursor()
start_time = time.time()
existing_users = 0
not_existing_users = 0
line_no = 0
LINES_TO_SKIP = 124
for line in huff_twitt_matches:
curr_time = time.time()
print("Elapsed time: ", curr_time - start_time, "existed:", existing_users, "not existed:", not_existing_users)
line_no += 1
if line_no < LINES_TO_SKIP:
continue
try:
huff_name, twitter_names = split_huff_and_twitter_line_from_list(line)
for twitter_name in twitter_names:
print("Working on ", twitter_name)
if not is_user_in_db(twitter_name, cur):
twitter_user = get_api_and_move_to_end().get_user(twitter_name) # 1 CALL
print("who wasn't in db")
save_user_in_db(twitter_user, cur)
link_huff_user_with_twitter(huff_name, twitter_user.id, cur)
next_cursor = -1
response = get_api_and_move_to_end().friends_ids(screen_name=twitter_name,
cursor=next_cursor) # 1 CALL
followed_ids = response[0]
print("Has ", len(followed_ids), " follows")
if len(followed_ids) > 100: # limit to 100
followed_ids = followed_ids[:100]
for followed_id in followed_ids:
if not is_user_in_db(followed_id, cur):
save_user_in_db(followed_id, cur)
not_existing_users += 1
else:
existing_users += 1
save_a_follower(followed_id, get_id_by_name(twitter_name, cur), cur)
try:
conn.commit()
except:
print("### SOMETHING BAD HAS HAPPENED WHEN WORKING ON {}, BUT WE GO FORWARD".format(twitter_name))
conn.rollback()
failed_users_file.write(line + "\n")
failed_users_file.flush()
except:
print("### SOMETHING HAS BROKEN INCORRECTLY ", str(sys.exc_info()), traceback.print_exc())
conn.rollback()
failed_users_file.write(line)
failed_users_file.flush()
failed_users_file.close()
def save_list(self):
for item in self.todo_items:
item.save_item(self.name)
SQL = "INSERT INTO todolist (NAME, DESCRIPTION) VALUES ('{0}', '{1}')"
c.execute(SQL.format(self.name, self.description))
conn.commit()
def delete_list(self):
SQL = "DELETE FROM todolist WHERE NAME = '{0}'".format(self.name)
todo_item.ToDoItem('').delete_all(self.name)
c.execute(SQL)
conn.commit()
huff_author_id integer NOT NULL,
twitter_user_id bigint NOT NULL,
CONSTRAINT huff_twitter_users_junction_pkey PRIMARY KEY (huff_author_id, twitter_user_id)
)
WITH (
OIDS=FALSE
);
""")
cur.execute("""CREATE TABLE IF NOT EXISTS retweets
(
tweet_id bigint NOT NULL,
user_id bigint NOT NULL,
CONSTRAINT retweets_pkey PRIMARY KEY (tweet_id, user_id)
)
WITH (
OIDS=FALSE
);""")
cur.execute("""CREATE TABLE IF NOT EXISTS tweet_hashtags
(
tweet_id bigint NOT NULL,
hashtag character(1) NOT NULL,
CONSTRAINT tweet_hashtags_pkey PRIMARY KEY (tweet_id, hashtag)
)
WITH (
OIDS=FALSE
);""")
conn.commit()