#/usr/bin/env python import sys import MySQLdb from time import * from datetime import * class DataBase: def connect(self): self.db = MySQLdb.connect(db="szene",\ host="localhost", \ user="szeneevents", \ passwd="szene", \ use_unicode=1) print "Connection to databse established" def close (self): self.db.close() print "Connection to database detached" def get_latest_uid_category(self, type): c = self.db.cursor() sqlqueue="SELECT uid FROM tx_skcalendar_" + type c.execute(sqlqueue) j=1 for i in c: if i[0] > j: j = i[0] print "latest uid for %s is: %i" % (type, j) return j def get_latest_monthid(self, type): c = self.db.cursor() sqlqueue="SELECT uid FROM tx_skcalendar_" + type c.execute(sqlqueue) j=1 for i in c: if i[0] > j: j = i[0] print "latest uid for %s is: %i" % (type, j) return j def create_rubrik(self, rubrik, parentid, new_month): latest_month_id = ("SELECT `uid` FROM `pages` WHERE `title` = '" + new_month + "' AND `pid` = '""" + parentid + "'") self.db.query(latest_month_id) monthid = self.db.store_result().fetch_row()[0][0] #print "%s %s %s" % (rubrik, parentid, new_month) #print monthid #monthid = monthid[0][0] get_rubrik_id = ("""SELECT `uid` FROM `pages` WHERE `title` = '"""+ rubrik + """' AND `pid` = '""" + str(monthid) + """' LIMIT 1""") self.db.query(get_rubrik_id) rubrik_id = self.db.store_result().fetch_row() ## create dir for the rubrik if it doesn't exist yet if not rubrik_id: sqlqueue = " \ INSERT INTO `pages` (`pid`,`title`,`doktype`,`urltype`,`tx_rgmenu_special`,`sorting`,`cruser_id`) \ VALUES ("\ + "'" + str(monthid) + "', " \ + "'" + str(rubrik) + \ "', '254','1','5','8','1');" self.db.query(sqlqueue) print "Created rubrik %s under monthid %s pid %s" % (rubrik, parentid, monthid) #print sqlqueue get_rubrik_id = ("""SELECT `uid` FROM `pages` WHERE `title` = '"""+ rubrik + """' AND `pid` = '""" + str(monthid) + """' LIMIT 1""") self.db.query(get_rubrik_id) rubrik_id = self.db.store_result().fetch_row()[0][0] if not rubrik_id: print "Couldn't create rubrik %s" % rubrik sys.exit() return rubrik_id def prepare_new_month(self, parentid, month): '''create a new folder in typo3 under the given parentid ''' new_month = str(month) #parentid = str(34) doktype = str(254) perms_user = str(31) perms_group = str(27) hidden = str(1) tx_rgmenu_special = str(5) tstamp = str(int(mktime(datetime.now().timetuple()))) ## delete entries with same parent id and month to avoid duplicates #latest_month_id = ("DELETE FROM `pages` WHERE `title` = '" + new_month + "' AND `pid` = '" + parentid + "'") ## better don't delete, just inform latest_month_id = ("""SELECT `uid` FROM `pages` WHERE `title` = '"""+ new_month + """' AND `pid` = '""" + parentid + """' LIMIT 1""") self.db.query(latest_month_id) monthid = self.db.store_result().fetch_row() self.monthid = monthid if monthid: print "Caution, the month >%s< alread exists in this dir >%s< !" % (new_month, parentid) print "It's ID is >%s<. I'm cowardly stopping now." % monthid[0][0] print "Rename or delete it manually to go on with this action." return ## insert new month (as typo3 folder) sqlqueue="INSERT INTO `pages` (`pid`, `title`, `doktype`, `tstamp`, `perms_user`, `perms_group`, `hidden`, `tx_rgmenu_special`) \ VALUES (" \ + "'" + parentid + "', " \ + "'" + new_month + "', " \ + "'" + doktype + "', " \ + "'" + tstamp + "', " \ + "'" + perms_user + "', " \ + "'" + perms_group + "', " \ + "'" + hidden + "', " \ + "'" + tx_rgmenu_special \ + "');" #print sqlqueue c = self.db.cursor() c.execute(sqlqueue) ## get uid of newly created month, there should be exactly one latest_month_id = ("SELECT `uid` FROM `pages` WHERE `title` = '" + new_month + "' AND `pid` = '""" + parentid + "'") self.db.query(latest_month_id) monthid = self.db.store_result().fetch_row()[0][0] print "The new month has the ID >%s<" % monthid ''' ## create categories for title in categories: sqlqueue = " \ INSERT INTO `pages` (`pid`,`title`,`doktype`,`urltype`,`tx_rgmenu_special`,`sorting`,`cruser_id`) \ VALUES ("\ + "'" + str(monthid) + "', " \ + "'" + title + \ "', '254','1','5','8','1');" c.execute(sqlqueue) print sqlqueue ''' def delete_month_entries(self, monthid): '''create a new folder in typo3 under the given parentid ''' c = self.db.cursor() rubrik_query = ("""SELECT `uid` FROM `pages` WHERE `pid` = """ + monthid ) c.execute(rubrik_query) rubrik_ids = c.fetchall() print "deleting rubrik IDs: " + rubrik_ids for r_id in rubrik_ids: self.delete_event_in_rubrik(r_id) def delete_event_in_rubrik(self, rubrikid): print rubrikid[0] rubrikid = str(rubrikid[0]) #delete_query = ("""SELECT `uid` FROM `tx_skcalendar_event` WHERE `pid` = """ + rubrikid ) delete_query = ("""DELETE FROM `tx_skcalendar_event` WHERE `pid` = """ + rubrikid ) c = self.db.cursor() c.execute(delete_query) #rubrik_ids = c.fetchall() #print rubrik_ids def prepare_event(self, event_dic, month, day, parentid, rubrikid): '''hiermit wird eine neue Veranstaltung in passender Form fuer die VCE Extension vorbereitet''' ## event_pid is the uid of the current rubrik event_pid = rubrikid if int(event_pid) <= int(parentid): print "Event ID is smaller than parent ID." print "%s <= %s" % (event_pid, parentid) print "This should not happen, so I stop processing here." sys.exit() event_title = "

%s

%s

%s

" \ % (event_dic["WANN_START_XML"], event_dic["WAS_XML"], event_dic["WO_XML"]) event_description = event_dic["INFORMATION"] event_startdate = self.calc_correct_day(month, day) event_targetgroup = "5" event_category = "1" event_organizer = "1" event_location = "1" event_tstamp = str(int(mktime(datetime.now().timetuple()))) event_crdate = str(int(mktime(datetime.now().timetuple()))) ## felder ohne default wert mit leerem string besetzen event_image = "" event_link = "" event_exeptions = "" event_l18n_diffsource = "" event = (event_pid, event_title, event_description, event_startdate, event_targetgroup, event_category, event_organizer, event_location, event_tstamp, event_crdate, event_image, event_link, event_exeptions, event_l18n_diffsource) #print event return event def insert_event(self, dataset): '''den MySQL Befehl absetzen''' #sqlqueue="INSERT INTO `tx_skcalendar_event` (`pid`) VALUES ('29');" #c.execute(sqlqueue) ''' sqlqueue="INSERT INTO `tx_skcalendar_event` (`pid` , `title`, `description`, `targetgroup`, `category`, `organizer`, `location`, `tstamp` , `crdate` ) VALUES (" \ + "'" + self.event_pid + "', "\ + "'" + self.event_title + "', "\ + "'" + self.event_description + "', "\ + "'" + self.event_targetgroup + "', "\ + "'" + self.event_category + "', "\ + "'" + self.event_organizer + "', "\ + "'" + self.event_location + "', "\ + "'" + self.event_tstamp + "', "\ + "'" + self.event_crdate \ + "');" print "folgende Datensaetze einfuegen:" for i in dataset: print i #c.executemany("""INSERT INTO `tx_skcalendar_event` (`pid` , `title`, `description`, `startdate`, `targetgroup`, `category`, `organizer`, `location`, `tstamp` , `crdate`, `image`, `link`, `exeptions`, `l18n_diffsource`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", data) ''' #data = self.prepare_event() c = self.db.cursor() c.executemany("""INSERT INTO `tx_skcalendar_event` (`pid` , `title`, `description`, `startdate`, `targetgroup`, `category`, `organizer`, `location`, `tstamp` , `crdate`, `image`, `link`, `exeptions`, `l18n_diffsource`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", dataset) def insert_organizer(self): #get_organizers_from_config() return def calc_correct_day(self, month, day): return str(int(mktime((datetime(2008,int(month),int(day),11,11,00)).timetuple()))) if __name__ == '__main__': test = DataBase() test.connect() #test.get_latest_uid_category("organizer") datasource = XMLSource() #test.insert_event() test.close() sys.exit(0) ''' ## zur Umwandlungs von unix timestamps in mysql un umgekehrt from time import * from datetime import * # von mysql (unix) timestamp in python datetime umwandeln datetime.fromtimestamp(1199142000) # und zurueck mktime((datetime(2008,01,31,21,8,00)).timetuple()) ## Python & MySQL # http://www.kitebird.com/articles/pydbapi.html # http://dustman.net/andy/python/python-and-mysql '''