Beispiel #1
0
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
Beispiel #2
0
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))
Beispiel #3
0
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'])
Beispiel #4
0
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
Beispiel #5
0
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
Beispiel #6
0
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))
Beispiel #7
0
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
Beispiel #8
0
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'])
Beispiel #9
0
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()
Beispiel #11
0
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)
Beispiel #12
0
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)) + '건 제외')
Beispiel #13
0
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))
Beispiel #14
0
    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
Beispiel #15
0
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)
Beispiel #16
0
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))
Beispiel #17
0
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))
Beispiel #18
0
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)
Beispiel #19
0
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']
Beispiel #20
0
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']
Beispiel #21
0
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'])
Beispiel #22
0
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()
Beispiel #23
0
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()
Beispiel #24
0
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
Beispiel #25
0
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)
Beispiel #26
0
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
Beispiel #27
0
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     
Beispiel #28
0
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."
Beispiel #29
0
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
Beispiel #30
0
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
Beispiel #31
0
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
Beispiel #32
0
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."
Beispiel #33
0
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
Beispiel #34
0
def main(choice, searchType):
    DSN['database'] = 'vshaw_db'
    conn = dbconn.connect(DSN)
    items = getRows(conn, choice, searchType)
    return items
Beispiel #35
0
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
Beispiel #36
0
def connect():
    DSN['database']= 'rugsbee_db' #change later to rugsbee
    conn = dbconn.connect(DSN)
    conn.autocommit(True)
    return conn
Beispiel #37
0
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
Beispiel #38
0
def main(fillers):
	dsn = wendy_dsn.DSN
	dsn['database'] = 'wli2_db'
	conn = dbconn.connect(dsn)
	return register(conn, fillers)
Beispiel #39
0
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
Beispiel #41
0
def main(choice, searchType):
    DSN['database'] = 'vshaw_db'
    conn = dbconn.connect(DSN)
    items = getRows(conn,choice,searchType)
    return items
Beispiel #42
0
def main(fillers, movie_list):
	dsn = wendy_dsn.DSN
	dsn['database'] = 'wli2_db'
	conn = dbconn.connect(dsn)
	return addSession(conn,fillers, work_list)
Beispiel #43
0
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