def get_user(session_id):
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
user_dict = {
'username': "",
'user_id': "",
'name': ""
}
try:
#use session id associatd with user to pull additional data
curs.execute("select * from usersessions where sessionkey=%s",
(session_id,))
row = curs.fetchone()
username = row['username']
user_dict['username'] = username
#use username pulled from usersessions table to query more data
curs.execute("select * from user where username=%s", (username,))
user_row = curs.fetchone()
user_dict['user_id'] = user_row['userId']
user_dict['name'] = user_row['name']
print user_dict['name']
except:
pass
return user_dict
def searchByDate(searchDate):
#Connect to Database
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
#format the given date for the SQL query
formatSearchDate=str("%"+searchDate+"%")
curs.execute("select title, content, name, created from form inner join board where form.boardId=board.boardId and form.type!='feedback' and created like %s", (formatSearchDate,))
posts = []
#HTML to format results
start_post = "<p class='text'>"
post_html = "<h4 class='text'>{title}</h4><p>{content}</p><p>Date: {created}</p>"
end_post = "</p> </p>"
while True:
row = curs.fetchone()
if row == None:
return "\n".join(posts)
else:
posts.append(start_post.format(**row))
posts.append(post_html.format(**row))
posts.append(end_post.format(**row))
def main(actor, movie, insertactor):
DSN['database'] = 'wmdb' # the database we want to connect to
conn = dbconn.connect(DSN)
if insertactor: #calls insertactor function
insertName(conn,actor['nm'],actor['name'],actor['bd'])
else: #calls insertmovie function
insertMovie(conn, movie['tt'], movie['title'], movie['release'], movie['index'], actor['nm'])
def signPetition(petitionName, dateCreated, signerId):
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select formId as id from form where type='petition' and title=%s and created=%s", (petitionName, dateCreated))
sent = ""
petitionIDS = []
numpetitions = curs.rowcount
while True:
row=curs.fetchone()
if row!=None:
petitionIDS.append(row['id'])
else:
break
numsig = 0
while numsig<numpetitions:
curs.execute('select max(sigId) as id from signature')
signature_row=curs.fetchone()
signatureId=signature_row['id']+1
curs.execute("insert into signature values(%s, %s, %s)", (signatureId, petitionIDS[numsig], signerId,))
numsig = numsig+1
def load_data(keyword, site, post_date):
try:
conn = dbconn.connect('crawldb')
except psycopg2.DatabaseError as e:
print("DB Connection Failed. Try Again. %s" % e)
sys.exit(1)
news_data = []
cur = conn.cursor()
query = "select keyword, link, title, press, post_date, site" +\
" from TB_CR_NEWS" +\
" where keyword like '%%s%'" +\
" and site like '%'" +\
" and post_date >="
try:
cur.execute(query)
except psycopg2.DatabaseError as e:
print('Error Occured while Execute Query.\n%s' % e2)
else:
try:
news_data = cur.fetchall()
except psycopg2.DatabaseError as e:
print('Error Occured while Fetch Data.\n%s' % e)
finally:
conn.close()
return news_data
def searchByTags(tagValues):
#Connect to database
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
#Execute query
curs.execute("select formId, name, title, content, created, value from form inner join tag inner join board where formId=postId and form.boardId=board.boardId and value=%s and form.type!='feedback'", (tagValues,))
posts = []
#HTML code to format results
start_post = "<p class='text'>"
post_html = "<h4 class='text'>{title}</h4><p>Board: {name}</p><p>{content}</p><p>Date: {created}</p>"
end_post = "</p> </p>"
while True:
row = curs.fetchone()
if row == None:
return "\n".join(posts)
else:
posts.append(start_post.format(**row))
posts.append(post_html.format(**row))
posts.append(end_post.format(**row))
def get_user(session_id):
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
user_dict = {
'username': "******",
'user_id': "null",
'name': "null"
}
try:
curs.execute("select * from usersessions where sessionkey=%s",
(session_id,))
row = curs.fetchone()
username = row['username']
user_dict['username'] = username
curs.execute("select * from user where username=%s", (username,))
user_row = curs.fetchone()
user_dict['user_id'] = user_row['userId']
user_dict['name'] = user_row['name']
except:
pass
return user_dict
def main(actor, movie, insertactor):
DSN['database'] = 'wmdb' # the database we want to connect to
conn = dbconn.connect(DSN)
if insertactor: #calls insertactor function
insertName(conn, actor['nm'], actor['name'], actor['bd'])
else: #calls insertmovie function
insertMovie(conn, movie['tt'], movie['title'], movie['release'],
movie['index'], actor['nm'])
def getConn():
#dsn = dsn.dsn
dsn = dbconn.read_cnf('my.cnf') # changed by Scott
dsn['db'] = 'eventscal_db'
conn = dbconn.connect(dsn)
conn.autocommit(True)
#curs = conn.cursor((MySQLdb.cursors.DictCursor)
return conn
def main():
config = configparser.ConfigParser()
config.read('/home/andresg3/PycharmProjects/redshift_dw/dwh.cfg')
conn, cur = connect()
drop_tables(cur, conn)
create_tables(cur, conn)
conn.close()
def main(submit, fillers, tutor_list): dsn = wendy_dsn.DSN dsn['database'] = 'wli2_db' conn = dbconn.connect(dsn) if submit == 'StoreInfo': return addTutor(conn, fillers, tutor_list) elif submit == 'LookupTutor': return searchTutor(conn, fillers) elif submit == 'LookupClass': return searchClass(conn, fillers)
def save_data(data):
try:
conn = dbconn.connect('crawldb')
except psycopg2.DatabaseError as e:
print("DB Connection Failed. Try Again. %s" % e)
sys.exit(1)
cur = conn.cursor()
query = "insert into TB_CR_NEWS (keyword, link, title, press, post_date, site)"\
+ "values(%s, %s, %s, %s, %s, %s)"
overlaped_link = []
for i in range(0, len(data)):
try:
cur.execute(query, data[i])
except psycopg2.IntegrityError as e1:
overlaped_link.append(data[i])
conn.rollback()
except psycopg2.DatabaseError as e2:
print('DB Error %s' % e2)
conn.rollback()
else:
conn.commit()
conn.close()
file_path = os.path.join(dbconn.BASE_PATH, 'news', data[0][5])
file_name = os.path.join(file_path, data[0][0] + '.txt')
if not os.path.exists(file_path):
os.makedirs(file_path)
f = open(file_name, 'w')
for idx, news in enumerate(data):
overlaped_str = ""
if news in overlaped_link:
overlaped_str = " - 중복"
index = '[' + str(idx + 1) + ']' + overlaped_str
f.write(index + '\n')
f.write('제목 : ' + news[2] + '\n')
f.write('링크 : ' + news[1] + '\n')
f.write('언론 : ' + news[3] + '\n')
f.write('등록 : ' + news[4] + '\n')
print(index)
print(news)
print()
f.close()
if overlaped_link:
new_data = len(data) - len(overlaped_link)
print('신규 : ' + str(new_data) + '건 저장. 중복 : ' +
str(len(overlaped_link)) + '건 제외')
def getBoardNames():
DSN['database'] = 'cgallag2_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select boardId, name from board where type='board'");
names = []
while True:
row = curs.fetchone()
if row == None:
return "\n".join(names)
names.append("<li id=\"{boardId}-nav\"><a href=\"#\">{name}</a></li>".format(**row))
def retrieve_class_list(self, module_id):
cursor = connect()
class_list = []
query = 'SELECT {}.MatricNum, {}.Attendance, Students.FirstName,Students.LastName, Students.ModuleString FROM {} INNER JOIN Students ON {}.MatricNum=Students.MatricNum;'.format(
module_id, module_id, module_id, module_id)
result = pd.read_sql(query, conn)
students = result.to_dict('records')
return students
def main():
try:
conn, cur = connect()
load_staging_tables(conn, cur)
insert_tables(conn, cur)
conn.close()
logging.info('Closing connection')
except Exception as e:
logging.error("exception occurred", exc_info=True)
def getAdmin():
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select userId, username, name from user where category='staff'")
names = []
while True:
row = curs.fetchone()
if row == None:
return "\n".join(names)
names.append("<option>{username}</option>".format(**row))
def displayPetition(petitionName):
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select title as title, content as content, created as date, name from form inner join board where form.boardId=board.boardId and form.type='petition' and title=%s", (petitionName,))
petition = []
while True:
row = curs.fetchone()
if row == None:
return "\n".join(petition)
petition.append("<h4>{title}</h4>".format(**row))
petition.append("<p class='text'>{content}</p>".format(**row))
petition.append("<p class='text'>Posted To: {name}</p>".format(**row))
petition.append("<p class='text'>Created On: {date}</p>".format(**row))
def getPetitionNames():
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select formId, title, name, created from form inner join board where form.type='petition' and form.boardId=board.boardId order by created desc")
names = []
numpetitions = 0
while (True and numpetitions<4):
row = curs.fetchone()
if row == None:
return "\n".join(names)
names.append("<li id=\"{formId}\"><h6>{title}</h6><p>Sent To: {name} at {created}</p></li>".format(**row))
numpetitions = numpetitions+1
if numpetitions==4:
return "\n".join(names)
def getPetitionOptions():
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select title from form where form.type='petition'")
names = []
previousTitle=""
while True:
row = curs.fetchone()
if row == None:
return "\n".join(names)
if row['title']!=previousTitle:
names.append("<option>{title}</option>".format(**row))
previousTitle = row['title']
def getDateCreated():
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select created from form where form.type='petition'")
dates = []
previousDate=""
while True:
row = curs.fetchone()
if row == None:
return "\n".join(dates)
if row['created']!=previousDate:
dates.append("<option>{created}</option>".format(**row))
previousDate = row['created']
def getTags():
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select value from tag")
repeatnames = []
names = []
while True:
row = curs.fetchone()
if row == None:
return "\n".join(names)
if row['value'] not in repeatnames:
names.append("<option>{value}</option>".format(**row))
repeatnames.append(row['value'])
def vacuum_catalog(dburl,conn,full=False,utility=False):
""" Will use the provided connection to enumerate the list of databases
and then connect to each one in turn and vacuum full all of the
catalog files
TODO: There are a few tables that are cluster-wide that strictly speaking
don't need to be vacuumed for each database. These are most likely
small and so perhaps isn't worth the added complexity to optimize them.
WARNING: doing a vacuum full on the catalog requires that
there aren't any users idle in a transaction as they typically
hold catalog share locks. The result is this vacuum will wait forever on
getting the lock. This method is best called when no one else
is connected to the system. Our own connections are typically idle
in transactions and so are especially bad.
"""
dblist = getDatabaseList(conn)
catlist = get_catalogtable_list(conn)
conn.commit()
for db in dblist:
test_url = copy.deepcopy(dburl)
test_url.pgdb = db[0]
if db[0] == 'template0' or db[0] == 'postgres':
continue
vac_conn = dbconn.connect(test_url,utility)
vac_curs = vac_conn.cursor()
vac_curs.execute("COMMIT")
vac_curs.execute("SET CLIENT_MIN_MESSAGES='ERROR'")
for table in catlist:
logger.debug('Vacuuming %s %s' % (db[0],table[0]) )
if full:
sql = "VACUUM FULL %s" % table[0]
else:
sql = "VACUUM %s" % table[0]
vac_curs.execute(sql)
vac_curs.execute(sql)
vac_conn.commit()
vac_conn.close()
def vacuum_catalog(dburl,conn,full=False,utility=False):
""" Will use the provided connection to enumerate the list of databases
and then connect to each one in turn and vacuum full all of the
catalog files
TODO: There are a few tables that are cluster-wide that strictly speaking
don't need to be vacuumed for each database. These are most likely
small and so perhaps isn't worth the added complexity to optimize them.
WARNING: doing a vacuum full on the catalog requires that
there aren't any users idle in a transaction as they typically
hold catalog share locks. The result is this vacuum will wait forever on
getting the lock. This method is best called when no one else
is connected to the system. our own connections are typically idle
in transactiona and so are especially bad.
"""
dblist = getDatabaseList(conn)
catlist = get_catalogtable_list(conn)
conn.commit()
for db in dblist:
test_url = copy.deepcopy(dburl)
test_url.pgdb = db[0]
if db[0] == 'template0' or db[0] == 'postgres':
continue
vac_conn = dbconn.connect(test_url,utility)
vac_curs = vac_conn.cursor()
vac_curs.execute("COMMIT")
vac_curs.execute("SET CLIENT_MIN_MESSAGES='ERROR'")
for table in catlist:
logger.debug('Vacuuming %s %s' % (db[0],table[0]) )
if full:
sql = "VACUUM FULL %s" % table[0]
else:
sql = "VACUUM %s" % table[0]
vac_curs.execute(sql)
vac_curs.execute(sql)
vac_conn.commit()
vac_conn.close()
def displayBoards():
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute(
"select boardId, name, mailname from board where type='board' or type='petition'")
total_boards = curs.rowcount
boards_printed = 0
boards_1 = []
boards_2 = []
panel_html_heading = """
<div class=\"panel panel-default\">
<div class=\"panel-heading\">{name}<span class=\"badge pull-right\">"""
panel_html_posts = """</span></div>
<div class=\"list-group\" id=\"{mailname}-board\">"""
panel_html_end = """
</div>
</div>"""
while True:
row = curs.fetchone()
if row is None:
return ["\n".join(boards_1), "\n".join(boards_2)]
boardId = row['boardId']
[posts, numposts] = displayPosts(boardId, conn)
board_html = (
panel_html_heading + str(numposts) + panel_html_posts).format(
**row)
board_html += posts
board_html += panel_html_end
if (boards_printed < math.ceil(total_boards / 2)):
boards_1.append(board_html)
else:
boards_2.append(board_html)
boards_printed += 1
def searchByDate(searchDate):
DSN['database'] = 'scusack_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
formatSearchDate=str("%"+searchDate+"%")
print formatSearchDate
curs.execute("select title, content, formId from form where created like %s", (formatSearchDate,))
numposts = curs.rowcount
print numposts
isFirst = True
posts = []
start_post_active = """<a href="#" class="list-group-item active">"""
start_post = """<a href="#" class="list-group-item">"""
post_html = """
<h4 class="list-group-item-heading">{title}</h4>
<p>{content}</p>
<h4><small>"""
end_post = "</small></h4> </a>"
while True:
row = curs.fetchone()
if row == None:
return ["\n".join(posts), numposts]
postId = row['formId']
post_tags = displayTags(postId, conn)
if isFirst:
posts.append(start_post_active + post_html.format(**row) + post_tags + end_post)
isFirst = False
else:
posts.append(start_post + post_html.format(**row) + post_tags + end_post)
def is_user(session_id):
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute("select * from usersessions where sessionkey=%s",
(session_id,))
row = curs.fetchone()
username = row['username']
curs.execute("select * from user where username=%s", (username,))
user_row = curs.fetchone()
if user_row is None:
return False
else:
return True
def main(name, description, location, category, contact, imgpath):
if name is None:
name = ""
if description is None:
description = ""
if location is None:
location = ""
if category is None:
category = "other"
if contact is None:
contact = ""
if imgpath is None:
imgpath = ""
DSN['database'] = 'vshaw_db' # the database we want to connect to
conn = dbconn.connect(DSN)
result = insertItem(conn, name, description, location, category, contact, imgpath)
return result
def addBoard(name, privacy_level, category):
DSN['database'] = 'cgallag2_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
mailname = name.strip().lower().replace(" ", "-")
curs.execute("select * from board where mailname=%s", (mailname,))
row = curs.fetchone()
if row == None:
curs.execute("insert into board (name, mailname, type, privacyLevel, category) values (%s, %s, 'board', %s, %s)",
(name, mailname, privacy_level, category))
return "Board " + name + " created successfully."
else:
return "Board " + name + " already exists."
def main(name, description, location, category, contact, imgpath):
if name is None:
name = ""
if description is None:
description = ""
if location is None:
location = ""
if category is None:
category = "other"
if contact is None:
contact = ""
if imgpath is None:
imgpath = ""
DSN['database'] = 'vshaw_db' # the database we want to connect to
conn = dbconn.connect(DSN)
result = insertItem(conn, name, description, location, category, contact,
imgpath)
return result
def addPetition(boards, title, message, tags, creator):
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
# For feedback.
sent = ""
failed_to_send = ""
for board in boards:
boardname = board.strip().lower().replace(" ", "-")
curs.execute("select boardId from board where name=%s", (boardname,))
board_row = curs.fetchone()
if board_row != None:
boardId = board_row['boardId']
curs.execute('select max(formId) as id from form')
petition_row=curs.fetchone()
petitionId=petition_row['id']+1
# I can't get the created timestamp from mysql without causing some timestamp issues later on,
# so I have to calculate it myself in python beforehand.
current_time = str(datetime.now())
curs.execute("insert into form (formId, boardId, created, title, content, creator, type) values (%s, %s, %s, %s, %s, %s, 'petition')",
(petitionId, boardId, current_time, title, message, creator,))
addTags(petitionId, tags, conn)
print 'petition added'
sent += board + ","
else:
failed_to_send += board + ","
if failed_to_send != "":
unsent = "Post could not be sent to " + failed_to_send.rstrip(",")
else:
unsent = ""
return "Post sent to " + sent.rstrip(",") + "<br>" + unsent
def addFeedback(boardname, subject, message, creator):
DSN['database'] = 'neighbrd_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
sent = ""
failed_to_send = ""
#format for all private feedback board names
boardnameStr = boardname+"feedback"
curs.execute('select max(formId) as id from form')
feedback_row=curs.fetchone()
feedbackId=feedback_row['id']+1
boardID=0
curs.execute("select boardId as id from board where privacyLevel='private' and name=%s", (boardnameStr,))
board_row = curs.fetchone()
numrows = curs.rowcount
#if search returns result, board already exists
if numrows!=0:
boardID=board_row['id']
print boardID
current_time = str(datetime.now())
#create board if necessary
if boardID==0:
curs.execute('select max(boardId) as id from board')
board_row=curs.fetchone()
boardID=board_row['id']+1
curs.execute("insert into board values(%s, %s, %s, %s, 'feedback', 'private', 'staff')", (boardID, boardnameStr, boardname,creator,))
#insert feedback
curs.execute("insert into form values (%s, %s, %s, %s, %s, %s, 'feedback')", (feedbackId, boardID, current_time, subject, message, creator,))
#feedback for CGI script
sent = sent+boardnameStr
if failed_to_send != "":
unsent = "Post could not be sent to " + failed_to_send.rstrip(",")
else:
unsent = ""
return "Post sent to " + sent + "<br>" + unsent
def addBoard(name, privacy_level, category, owner_id):
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
# If the board's name is CS department, then the board's mailname
# is cs-department. This allows the system to recognize a board name
# even if the user does not type the correct caps, and provides
# standardization.
mailname = name.strip().lower().replace(" ", "-")
curs.execute("select * from board where mailname=%s", (mailname,))
row = curs.fetchone()
if row is None:
curs.execute(
"insert into board (name, mailname, owner, type, privacyLevel, category) values (%s, %s, %s, 'board', %s, %s)",
(name, mailname, owner_id, privacy_level, category))
return "Board " + name + " created successfully."
else:
return "Board " + name + " already exists."
def addPost(boards, subject, message, tags):
DSN['database'] = 'cgallag2_db'
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
# For feedback.
sent = ""
failed_to_send = ""
for board in boards:
mailname = board.strip().lower().replace(" ", "-")
#print 'Searching for board ' + mailname
curs.execute("select boardId from board where mailname=%s", (mailname,))
board_row = curs.fetchone()
if board_row != None:
boardId = board_row['boardId']
# I can't get the created timestamp from mysql without causing some timestamp issues later on,
# so I have to calculate it myself in python beforehand.
current_time = str(datetime.now())
curs.execute("insert into form (boardId, created, title, content, type) values (%s, %s, %s, %s, 'post')",
(boardId, current_time, subject, message))
addTags(boardId, current_time, tags, conn)
sent += board + ","
else:
failed_to_send += board + ","
if failed_to_send != "":
unsent = "Post could not be sent to " + failed_to_send.rstrip(",")
else:
unsent = ""
return "Post sent to " + sent.rstrip(",") + "<br>" + unsent
def main(choice, searchType):
DSN['database'] = 'vshaw_db'
conn = dbconn.connect(DSN)
items = getRows(conn, choice, searchType)
return items
def cursor():
global database_connection, cursor
DSN['database'] = 'wmdb'
database_connection = dbconn.connect(DSN)
cursor = database_connection.cursor(MySQLdb.cursors.DictCursor) # results as dictionaries
return cursor
def connect():
DSN['database']= 'rugsbee_db' #change later to rugsbee
conn = dbconn.connect(DSN)
conn.autocommit(True)
return conn
def main():
'''Returns a listing all actors and birthdates'''
DSN['database'] = 'vshaw_db' # the database we want to connect to
conn = dbconn.connect(DSN)
actorlist = getItems(conn)
return actorlist
def main(fillers): dsn = wendy_dsn.DSN dsn['database'] = 'wli2_db' conn = dbconn.connect(dsn) return register(conn, fillers)
from sqlite3 import Error
import pandas as pd
from flask import Flask, render_template, request, redirect, url_for, flash, session, g, json
import flask
import requests
import unittest
import logging
import pyodbc
import requests
from unittest import mock
from dbconn import connect
from main import app
from create_tables import create_all_tables
app.testing = True
conn = connect()
create_all_tables()
# Testing that each page returns the required response.
class BasicTests(unittest.TestCase):
def setUp(self):
self.app = app.test_client()
self.app.testing = True
pass
def test_main_pages(self):
# These are basic tests to make sure that there are no crashes as pages are loaded.
def getConn():
dsn = dbconn.read_cnf('my.cnf') # changed by Scott
dsn['db'] = 'eventscal_db'
conn = dbconn.connect(dsn)
conn.autocommit(True)
return conn
def main(choice, searchType):
DSN['database'] = 'vshaw_db'
conn = dbconn.connect(DSN)
items = getRows(conn,choice,searchType)
return items
def main(fillers, movie_list): dsn = wendy_dsn.DSN dsn['database'] = 'wli2_db' conn = dbconn.connect(dsn) return addSession(conn,fillers, work_list)
def addPost(boards, subject, message, tags, image, owner_id):
conn = dbconn.connect(DSN)
curs = conn.cursor(MySQLdb.cursors.DictCursor)
# For feedback.
sent = ""
failed_to_send = ""
# For keeping track of posts that should have images with them.
postIds = []
for board in boards:
mailname = board.strip().lower().replace(" ", "-")
#print 'Searching for board ' + mailname
curs.execute("select boardId from board where mailname=%s",
(mailname,))
board_row = curs.fetchone()
if board_row is not None:
boardId = board_row['boardId']
# I can't get the created timestamp from mysql without causing some timestamp issues later on,
# so I have to calculate it myself in python beforehand.
current_time = str(datetime.now())
curs.execute(
"insert into form (boardId, created, title, content, creator, type) values (%s, %s, %s, %s, %s, 'post')",
(boardId, current_time, subject, message, owner_id))
addTags(boardId, current_time, tags, conn)
curs.execute("select formId from form where boardId=%s and created=%s and type='post'",
(boardId, current_time))
post_row = curs.fetchone()
if post_row is not None:
postIds.append(post_row['formId'])
sent += board
else:
failed_to_send += board + ","
# imghdr.what(image.file) returns the image file type, and None otherwise.
# It does not work for all images, such as svg and xcf, but it works for most.
# We thought that the security implications of allowing someone to upload
# a malicious non-image file was worse than omitting a few image files.
if image.file is not None and imghdr.what(image.file) is not None:
image_filename = binascii.b2a_hex(os.urandom(15))
did_upload = process_file_upload(image_filename, image.file)
else:
did_upload = False
if did_upload:
for post in postIds:
add_image_to_post(post, image_filename, curs)
if failed_to_send != "":
unsent = "Post could not be sent to " + failed_to_send.rstrip(",")
else:
unsent = ""
return "Post sent to " + sent.rstrip(",") + "<br>" + unsent