Пример #1
0
def authors():
    
    db_connection = connection.get_connection()
    cursor = db_connection.cursor()
    popular_authors = """
        SELECT name,title,path,count(title) AS views
        FROM articles,log,authors
        WHERE log.path = CONCAT('/article/', articles.slug)
        AND articles.author = authors.id
        GROUP BY path, title, name ORDER BY views DESC LIMIT 3;"""
    
    cursor.execute(popular_authors)
    return cursor.fetchall()
Пример #2
0
def get_top_authors():
    conn = connection.get_connection()
    conn.execute('''
        SELECT
        COUNT(authors.id) AS totalViews,
        authors.name
        FROM log, authors, articles
        WHERE log.path != '/' AND
        articles.slug =
        SUBSTR(log.path, LENGTH('/article/') + 1)
        AND articles.author = authors.id
        GROUP BY
        authors.name,
        authors.id
        ORDER BY totalViews DESC
    ''')

    return conn
Пример #3
0
def articles():
    #print("data{}".format(connection)) # string interpolation !/(comprehension/literals)
    #i CAN Add more objects bracket notation, separated by comma
    db_connection = connection.get_connection()
    #print("data{}".format(cursor))
    cursor = db_connection.cursor()
    #python uses indentation to identify code blocks
    popular_articles = ("""
        SELECT title, path, COUNT(path) AS hits, authors.name
        FROM articles
        JOIN authors
        ON articles.author = authors.id
        JOIN log
        ON log.path = concat('/article/', articles.slug)
        GROUP BY title, path, authors.name
        ORDER BY hits DESC LIMIT 3;
    """)
    cursor.execute(popular_articles)
    return cursor
Пример #4
0
def error_status():
    db_connection = connection.get_connection()
    # You need to join the logs to the path
    cursor = db_connection.cursor()
    error_reports = """
        SELECT time::DATE,
        SUM (
            CASE WHEN status = '200 OK'
            THEN 1
            ELSE 0
            END) AS success,
        SUM (
            CASE WHEN status  =  '404 NOT FOUND.'
            THEN 1
            ELSE 0
            END) AS error
        FROM log GROUP BY time::DATE;
    """
    cursor.execute(error_reports)
    return cursor.fetchall()
Пример #5
0
def get_errors_per_day():
    conn = connection.get_connection()
    conn.execute('''
    WITH t AS
        (SELECT DATE(log.time) AS failureDate,
        ROUND((SUM(CASE WHEN
            SUBSTRING(log.status, 0, 4)::INTEGER >= 400
            THEN 1
            ELSE 0
            END
        )  * 100.0)::DECIMAL /
        (COUNT(log.status)), 1) AS totalFailures
        FROM log GROUP BY DATE(log.time)
        )
    SELECT CONCAT(t.totalFailures, '%') AS failure,
    TO_CHAR(t.failureDate, 'Month DD, YYYY') AS date
    FROM t
    GROUP BY t.totalFailures, t.failureDate
    HAVING t.totalFailures > 1
    ''')

    return conn