Python Adventures

Here you will find a very basic program that stores data in SQLite. It is a simple example. If I need to write a program that needs sqlite, it can serve as a basis and I won’t need to write it from scratch.

Problem
Visit the page /r/earthporn, extract the image URLs and save the URLs of images stored on imgur in an sqlite table.

Solution
earthporn.py:

#!/usr/bin/env python
# encoding: utf-8

import requests
import database as db    # see below

subreddit = "earthporn"

def main():
    db.init()    # Important! It will connect to the DB.

    r = requests.get('http://www.reddit.com/r/{sr}/.json'.format(sr=subreddit))
    d = r.json()
    children = d["data"]["children"]
    for e in children:
        url = e["data"]["url"]
        if "imgur.com" in url:
#            print url
            db.add_image(url, subreddit)

####################

if __name__ == "__main__":
    main()

database.py:

#!/usr/bin/env python

"""
Sqlite database handler.
"""

import os
import sqlite3
import atexit
import random
import termcolor

PATH = os.path.dirname(os.path.abspath(__file__))
SCHEMA = """
CREATE TABLE "images" (
    "url" TEXT PRIMARY KEY NOT NULL,
    "subreddit" TEXT,
    "insert_date" DEFAULT CURRENT_TIMESTAMP
)
"""
SQLITE_DB = PATH + '/images.sqlite'

conn = None

def get_random_image():
    query = "SELECT url FROM images"
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    return random.choice(result)[0]

def add_image(url, subreddit=None):
    try:
        if subreddit:
            query = "INSERT INTO images (url, subreddit) VALUES (?, ?)"
            conn.execute(query, (url, subreddit))
        else:
            query = "INSERT INTO images (url, subreddit) VALUES (?, NULL)"
            conn.execute(query, (url,))
        #
        print termcolor.colored("# {url} added to DB".format(url=url), "cyan")
    except sqlite3.IntegrityError:
        print termcolor.colored("# the image {0} is already in the DB...".format(url), "red")

def get_all_images():
    query = "SELECT url FROM images"
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    return result

def create_db():
    """
    Create the DB if not exists.
    """
    global conn
    conn = sqlite3.connect(SQLITE_DB)
    conn.executescript(SCHEMA)

def init(commit=True):
    """
    Initialize the DB.
    """
    global conn
    if commit:
        atexit.register(commit_and_close)
    else:
        atexit.register(close)

    if not os.path.exists(SQLITE_DB):
        create_db()
    if not conn:
        conn = sqlite3.connect(SQLITE_DB)

def commit():
    """
    Commit.
    """
    if conn:
        conn.commit()

def close():
    """
    Close.
    """
    if conn:
        conn.close()

def commit_and_close():
    """
    Commit and close DB connection.

    As I noticed, commit() must be called, otherwise changes
    are not committed automatically when the program terminates.
    """
    if conn:
        conn.commit()
        conn.close()

####################

if __name__ == "__main__":
    init()

The module database.py is responsible for handling the database. When you need to work with the database, just include this file and call the appropriate function(s).