From ccb05be91cf041923e6c730e250ab719e6f23416 Mon Sep 17 00:00:00 2001 From: "W. Trevor King" Date: Fri, 25 Jun 2010 22:08:58 -0400 Subject: [PATCH] Added sitecore.prof_export (unfinished) for MySQL -> YAML --- sitecore/prof_export.py | 183 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 183 insertions(+) create mode 100644 sitecore/prof_export.py diff --git a/sitecore/prof_export.py b/sitecore/prof_export.py new file mode 100644 index 0000000..0de8037 --- /dev/null +++ b/sitecore/prof_export.py @@ -0,0 +1,183 @@ +#!/usr/bin/env python +# Copyright (C) 2010 W. Trevor King +# +# This file is part of SiteCorePy. +# +# SiteCorePy is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by the +# Free Software Foundation, either version 2 of the License, or (at your +# option) any later version. +# +# SiteCorePy is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with SiteCorePy. If not, see . + +"""Move Professors from MySQL -> YAML. + +Not really SiteCore related, but it seves as an example of what to do +before calling prof_import. +""" + +import getpass +import logging + +import MySQLdb + +from . import get_logger +from .prof_import import Name, Graduation, Contact, Bio, Professor + + +class SimpleDB (object): + def __init__(self, verbose=0): + self.db = None + self.cursor = None + self.table = None + self.logger = get_logger(verbose) + + def connect(database, host='localhost', + username=None, password=None): + if username == None: + username = getpass.getpass('username: ') + if password == None: + password = getpass.getpass('password: ') + self.db = MySQLdb.connect(host=host, user=username, + passwd=password, db=database) + del(username) + del(password) + self.cursor = self.db.cursor() + + def disconnect(self): + self.cursor = None + self.db.close() + + def set_default_table(self, table): + self.table = table + + def add_entry(self, dict, table=None): + if table = None: + table = self.table + dict = self._clean_dict(dict) + keys = dict.keys() + values = ["'%s'" % dict[key] for key in keys] + self.cursor.execute("INSERT INTO %s (%s) VALUES (%s)" + % (table, ', '.join(keys), ', '.join(values))) + # error checking + + def list_entries(self, dict=None, table=None): + if table = None: + table = self.table + where = self._where_string(dict) + self.cursor.execute('SELECT * FROM %s%s' % (table, where)) + numrows = int(self.cursor.rowcount) + print '\t'.join([dtuple[0] for dtuple in self.cursor.description]) + for x in range(0,numrows): + row = self.cursor.fetchone() + print '\t'.join(['%s' % x for x in row]) + + def delete_entries(self, dict, table=None): + if table = None: + table = self.table + where = self._where_string(dict) + self.cursor.execute('DELETE FROM %s%s' % (table, where)) + + def _clean_dict(self, dict=None): + if dict == None: + return None + for key in dict.keys(): + value = dict.pop(key) + value = MySQLdb.escape_string(value) + key = MySQLdb.escape_string(key) + assert key not in dict, '%s in %s!' % (key, dict) + dict[key] = value + return dict + + def _where_string(self, dict=None): + dict = self._clean_dict(dict) + if dict == None or len(dict) == 0: + where = '' + else: + where_args = [] + for key,value in dict.items(): + where_args.append("%s='%s'" % (key,value)) + where = 'where %s' % ' and '.join(where_args) + return where + + +def get_profs(db): + """ +mysql> describe people; ++--------------+--------------+------+-----+---------+----------------+ +| Field | Type | Null | Key | Default | Extra | ++--------------+--------------+------+-----+---------+----------------+ +| ID | int(11) | NO | PRI | NULL | auto_increment | +| category | varchar(255) | YES | | NULL | | +| lastname | varchar(255) | YES | | NULL | | +| firstname | varchar(255) | YES | | NULL | | +| email | varchar(255) | YES | | NULL | | +| office | varchar(255) | YES | | NULL | | +| phone | varchar(255) | YES | | NULL | | +| lab | varchar(255) | YES | | NULL | | +| labphone | varchar(255) | YES | | NULL | | +| grouppage | varchar(255) | YES | | NULL | | +| personalpage | varchar(255) | YES | | NULL | | +| position | varchar(255) | YES | | NULL | | +| position1 | varchar(255) | YES | | NULL | | +| degrees | varchar(255) | YES | | NULL | | +| research | mediumtext | YES | | NULL | | +| profile | mediumtext | YES | | NULL | | ++--------------+--------------+------+-----+---------+----------------+ +16 rows in set (0.00 sec) + +Where the relevant categories are +| Faculty | +| Research Faculty | +| Teaching Faculty | +| Professor Emeritus | +| Adjunct Professor | + """ + db.set_default_table('people') + for category in ['Faculty', + 'Research Faculty', + 'Teaching Faculty', + 'Professor Emeritus', + 'Adjunct Professor']: + for prof in db.list_entries({'category':category}): + p = Professor() + TODO + yield p + + +if __name__ == '__main__': + import optparse + usage = """%prog [options] PROF_FILE + +Where PROF_FILE is the output YAML file receiving professor data. + +Example: + ./sitecore/prof_export.py profs.yaml +""" + p = optparse.OptionParser(usage) + p.add_option('-d', '--database', metavar='NAME', dest='database', + help='Name of the MySQL database (%default)', + default='directory') + p.add_option( + p.add_option('-v', '--verbose', dest='verbose', action='count', + help='increment verbosity (%default)', + default=0) + + options,args = p.parse_args() + ] + prof_file = args[0] + + db = SimpleDB(verbose=options.verbose) + db.connect(database=options.database) + try: + profs = list(get_profs(db)) + finally: + db.disconnect() + + yaml.save(db, profs) -- 2.26.2