Create an wonderful web page with a #RaspberryPi and #Reddit.

I saw on Reddit that user /u/tvm78 made a web page that displays the top posts in /r/ShowerThoughts and /r/EarthPorn into pretty pictures with thought-provoking texts.

Since I’ve been tinkering with Python a little and this seemed doable, I gave it a shot.

There were several hurdles to overcome:

  • How on earth do I scrape content from those sub-reddits
  • I want to put the scraped stuff in a MySQL database, but I’v never worked with databases and Python…
  • I want to use pretty fonts on the page, never worked with pretty Google fonts either.

What I did have was a Raspberry Pi2 configured as a webserver (yup, it is also serving up the very page you’re looking at) with php, MySQL and Python.

So, step one, make something that scrapes the top-x from the afore mentioned sub reddits.

Apologies for the horrible code, but hey, it works!

#!/usr/bin python

import praw
import mysql.connector
import datetime

r = praw.Reddit(user_agent='my_Reddit_scraper')
cnx = mysql.connector.connect(user='DATABASE_USER', password='DATABASE_PASSWORD',
                              host='127.0.0.1',
                              database='Reddit')
subreddit_name = "showerthoughts"
submissions = r.get_subreddit(subreddit_name).get_hot(limit=11)

#Set database cursor
cursor = cnx.cursor()

for x in submissions:
    if (x.title[0:6] == 'Follow'):
        continue
    ts = datetime.datetime.fromtimestamp(x.created_utc).strftime('%Y-%m-%d %H:%M:%S')
    add_title = ("INSERT INTO titles "
                "(username, text, date, subreddit) "
                "VALUES (%s, %s, %s, %s)")
    data_title = (x.author.name, x.title, ts, subreddit_name)
    cursor.execute(add_title, data_title)
    cnx.commit()
#Close database connection
cnx.close()

cnx = mysql.connector.connect(user='DATABASE_USER', password='DATABASE_PASSWORD',
                              host='127.0.0.1',
                              database='Reddit')
subreddit_name = "earthporn"
submissions = r.get_subreddit(subreddit_name).get_hot(limit=30)

#Set database cursor
cursor = cnx.cursor()

for x in submissions:
    if ( x.url[-3:] == 'jpg'):
        ts = datetime.datetime.fromtimestamp(x.created_utc).strftime('%Y-%m-%d %H:%M:%S')
        add_title = ("INSERT INTO pictures "
                 "(title, url, username, date, subreddit) "
                 "VALUES (%s, %s, %s, %s, %s)")
        data_title = (x.title, x.url, x.author.name, ts, subreddit_name)
        cursor.execute(add_title, data_title)
        cnx.commit()
#Close database connection
cnx.close()

As you can see, the comments are stored in a database. If you cut-and paste this sql in phpmyadmin, the correct database structure will be created

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `Reddit`
--
CREATE DATABASE IF NOT EXISTS `Reddit` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `Reddit`;

-- --------------------------------------------------------

--
-- Table structure for table `pictures`
--

CREATE TABLE IF NOT EXISTS `pictures` (
`id` int(11) NOT NULL,
  `title` text NOT NULL,
  `url` varchar(256) NOT NULL,
  `username` text NOT NULL,
  `date` datetime NOT NULL,
  `subreddit` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `titles`
--

CREATE TABLE IF NOT EXISTS `titles` (
`id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `text` text NOT NULL,
  `date` datetime NOT NULL,
  `subreddit` varchar(256) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `pictures`
--
ALTER TABLE `pictures`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `titles`
--
ALTER TABLE `titles`
 ADD PRIMARY KEY (`id`), ADD KEY `id` (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `pictures`
--
ALTER TABLE `pictures`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=61;
--
-- AUTO_INCREMENT for table `titles`
--
ALTER TABLE `titles`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=179;

When you run the Python-script it will probably moan that it can’t understand the PRAW thingy in the import-line. Go to the command line and enter:

sudo pip install PRAW

and also install the MySQL-connector:

sudo apt-get install python-mysql.connector

Done! Now run the script. Or better, run it every 12 hrs via a cron-job, this will fill up the database nicely.

Time for the web page to display all that database-content:

create an index.php file and paste the following:

<?php
$font=array("font-family: 'Raleway', sans-serif;",
        "font-family: 'Lobster', cursive;",
        "font-family: 'Pacifico', cursive;",
        "font-family: 'Dancing Script', cursive;",
        "font-family: 'Amatic SC', cursive;",
        "font-family: 'Special Elite', cursive;",
        "font-family: 'Rock Salt', cursive;");

$selected_font=$font[array_rand($font)];
$dbhost = 'localhost';
$dbuser = 'DATABASE_USER';
$dbpass = 'DATABASE_PASSWORD';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql1 = 'SELECT *
        FROM pictures
        ORDER BY RAND()
        LIMIT 1';

$sql2= 'SELECT *
        FROM titles
        ORDER BY RAND()
        LIMIT 1';

mysql_select_db('Reddit');
$retval1 = mysql_query( $sql1, $conn );
$retval2 = mysql_query( $sql2, $conn );


if(! $retval1 )
{
  die('Could not get data: ' . mysql_error());
}
while($row1 = mysql_fetch_array($retval1, MYSQL_ASSOC))
{
$image = $row1['url'];
}

if(! $retval2 )
{
  die('Could not get data: ' . mysql_error());
}
while($row2 = mysql_fetch_array($retval2, MYSQL_ASSOC))
{
$text = $row2['text'];
$username = $row2['username'];
}

mysql_close($conn);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<html>
    <head>
        <meta http-equiv="content-type" content="text/php; charset=utf-8" />
        <meta http-equiv="refresh" content="10" >
        <title>Inspire</title>
<style>
@import url(https://fonts.googleapis.com/css?family=Raleway|Lobster|Pacifico|Dancing+Script|Amatic+SC|Special+Elite|Rock+Salt);
* { margin: 0; padding: 0; }

body {
  background: url(<?php echo $image; ?>) no-repeat center center fixed;
  -webkit-background-size: cover;
  -moz-background-size: cover;
  -o-background-size: cover;
  background-size: cover;
}
.content {
  text-align: center;
  /* font-family: 'Open Sans', sans-serif; */
  /* color: #fff; */
  margin: 40px auto;
  /* background: rgba(22,22,22, 0.0); */
  width: 100%;
  /* max-width: 960px; */
  border-radius: 5px;
  padding-bottom: 32px;
}

h1, h1 a {
  <?php echo $selected_font;  ?>
  min-height: 120px;
  width: 90%;
  max-width: 700px;
  vertical-align: middle;
  text-align: center;
  margin: 0 auto;
  text-decoration: none;
  text-shadow: 1px 1px 2px black, 0 0 25px grey, 0 0 5px darkgrey;
  color: #ffffff;
  padding-top: 80px;
}
p {
  width: 90%;
  max-width: 700px;
  text-align: left;
  margin: 0 auto;
  padding-bottom: 32px;
}
</style>

</head>

<body>
        <div class="content">
        <h1><?php echo $text;  ?></br></h1>
</div>

</body>

</html>