Mensabot

CREATE TABLE "hildesheim_uni" (
entry_date text,
date text,
mensa_type text,
meal_type text,
meal_name text,
price_stud text,
price_bed text,
price_gaeste text,
UNIQUE(date, mensa_type, meal_type, meal_name, price_stud, price_bed, price_gaeste)
);

import lxml.html
import datetime
import sqlite3
 
 
# url to parse
mensaSite = lxml.html.parse("http://www.sw-bs.de/hildesheim/essen/menus/mensa-uni")
 
# db-stuff, path to .sqlite-file
con = sqlite3.connect('/home/.scripts/mensabot/mensabot.sqlite')
table_name = "hildesheim_uni"
c = con.cursor()
 
# days in week and their dates, replace with something smarter
days = ["mo","di","mi","do","fr","sa","so"]
dates = {"mo":None,"di":None,"mi":None,"do":None,"fr":None}
monday = datetime.date.today()
 
dates["mo"] = monday.strftime("%Y-%m-%d")
dates["di"] = (monday + datetime.timedelta(hours=24)).strftime("%Y-%m-%d")
dates["mi"] = (monday + datetime.timedelta(hours=48)).strftime("%Y-%m-%d")
dates["do"] = (monday + datetime.timedelta(hours=72)).strftime("%Y-%m-%d")
dates["fr"] = (monday + datetime.timedelta(hours=96)).strftime("%Y-%m-%d")
dates["sa"] = (monday + datetime.timedelta(hours=120)).strftime("%Y-%m-%d")
dates["so"] = (monday + datetime.timedelta(hours=144)).strftime("%Y-%m-%d")
 
# static value in database
mensa_type = "mittagsmensa"
 
counter = 0
 
for day in days:
	# first column
	meal_type = mensaSite.xpath("//table[@id='swbs_speiseplan_" + day + "']/tr/td/nobr")
 
	# mjam mjam
	table_content = mensaSite.xpath("//table[@id='swbs_speiseplan_" + day + "']/tr/td")
 
	# ceate and execute INSERT-statement
	for i in range(0,len(meal_type)):
 
		insertvalues = (
			table_name,
			datetime.date.today().strftime("%Y-%m-%d"),
			dates[day],
			mensa_type,
			meal_type[i].text, 
			table_content[(5*i+1)].text,
			table_content[(5*i+2)].text,
			table_content[(5*i+3)].text,
			table_content[(5*i+4)].text)
 
		try: 
			c.execute("INSERT INTO ? \
				('entry_date','date','mensa_type','meal_type','meal_name','price_stud','price_bed','price_gaeste') \
				VALUES (?,?,?,?,?,?,?,?)", insertvalues)
			counter = counter + 1
		except sqlite3.IntegrityError:
			pass			
 
		con.commit()
 
print datetime.datetime.today()
print str(counter) + " new datasets inserted."
 
c.close()
con.close()

#!/usr/bin/env python
# -*- coding: utf-8 -*-
 
import time
import datetime
import sqlite3
import re
import tweepy
 
# if 'DEBUG = True' tweets will only be shown on console
DEBUG = False
 
# twitter-stuff
mensahildesheim = {"access_token":"XXX","access_token_secret":"XXX","consumer_key":"XXX", consumer_secret":"XXX"}
auth = tweepy.OAuthHandler(mensahildesheim["consumer_key"], mensahildesheim["consumer_secret"])
auth.set_access_token(mensahildesheim["access_token"], mensahildesheim["access_token_secret"])
api = tweepy.API(auth)
 
# db-stuff
con = sqlite3.connect('/home/.scripts/mensabot/mensabot.sqlite')
table_name = "hildesheim_uni"
c1 = con.cursor()
 
today =(datetime.date.today().strftime("%Y-%m-%d"),)
 
c1.execute("SELECT meal_type, meal_name FROM hildesheim_uni WHERE date=? AND meal_type LIKE 'Essen%' ORDER BY meal_type DESC;", today)
 
for entry in c1:
	meal_type = entry[0]
	meal_name = entry[1]
	# deletes all brackets with numbers, such as (1,2,3)
	meal_name = re.sub(r'/ \(\d(,\d)*\)/', '', meal_name)
 
	c2 = con.cursor()
	selectLastDate = (meal_name + "%",)
	c2.execute("SELECT date FROM hildesheim_uni WHERE meal_name LIKE ? ORDER BY date DESC;", selectLastDate)
 
	# jump over first entry
	c2.fetchone()
 
	try:
		lastdatestring = c2.fetchone()[0]
		lastdate = time.strptime(lastdatestring, "%Y-%m-%d")
		outputlastdate = ", Zuletzt: " + time.strftime("%d.%m.%Y", lastdate) + " "
	except TypeError:
		outputlastdate = ""
 
	tweet = meal_type + ": " + meal_name + outputlastdate + " http://unihi.de/mensa#heute"
 
	if DEBUG:
		print tweet
	else:
		api.update_status(tweet)
 
	c2.close()
 
 
c1.close()
con.close()