sitecore/prof/export_mysql.py

   1 #!/usr/bin/env python
   2 # Copyright (C) 2010 W. Trevor King <wking@drexel.edu>
   3 #
   4 # This file is part of SiteCorePy.
   5 #
   6 # SiteCorePy is free software; you can redistribute it and/or modify it
   7 # under the terms of the GNU General Public License as published by the
   8 # Free Software Foundation, either version 2 of the License, or (at your
   9 # option) any later version.
  10 #
  11 # SiteCorePy is distributed in the hope that it will be useful, but
  12 # WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14 # General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License
  17 # along with SiteCorePy.  If not, see <http://www.gnu.org/licenses/>.
  18
  19 """Move Professors from MySQL -> YAML.
  20
  21 Not really SiteCore related, but it seves as an example of what to do
  22 before calling prof_import.
  23 """
  24
  25 import getpass
  26 import logging
  27
  28 import MySQLdb
  29 import yaml
  30
  31 from .. import get_logger
  32 from . import Name, Graduation, Contact, Bio, Professor
  33
  34
  35 class SimpleDB (object):
  36     def __init__(self, verbose=0):
  37         self.db = None
  38         self.cursor = None
  39         self.table = None
  40         self.logger = get_logger(verbose)
  41
  42     def connect(self, database, host='localhost',
  43                 username=None, password=None):
  44         if username == None:
  45             username = getpass.getpass('username: ')
  46         if password == None:
  47             password = getpass.getpass('password: ')
  48         self.logger.info('logging in to %s:%s as %s'
  49                          % (host, database, username))
  50         self.db = MySQLdb.connect(host=host, user=username,
  51                                   passwd=password, db=database)
  52         del(username)
  53         del(password)
  54         self.cursor = self.db.cursor()
  55
  56     def disconnect(self):
  57         self.logger.info('disconnecting')
  58         self.cursor = None
  59         self.db.close()
  60
  61     def execute(self, command):
  62         self.logger.info(command)
  63         self.cursor.execute(command)
  64
  65     def set_default_table(self, table):
  66         self.table = table
  67
  68     def add_entry(self, dict_, table=None):
  69         if table == None:
  70             table = self.table
  71         dict_ = self._clean_dict(dict_)
  72         keys = dict_.keys()
  73         values = ["'%s'" % dict_[key] for key in keys]
  74         self.execute("INSERT INTO %s (%s) VALUES (%s)"
  75                      % (table, ', '.join(keys), ', '.join(values)))
  76         # error checking
  77
  78     def list_entries(self, dict_=None, table=None):
  79         if table == None:
  80             table = self.table
  81         where = self._where_string(dict_)
  82         self.execute('SELECT * FROM %s%s' % (table, where))
  83         numrows = int(self.cursor.rowcount)
  84         fields = [dtuple[0] for dtuple in self.cursor.description]
  85         ret = []
  86         for x in range(0,numrows):
  87             row = self.cursor.fetchone()
  88             ret.append(dict(zip(fields, row)))
  89         return ret
  90
  91     def delete_entries(self, dict_, table=None):
  92         if table == None:
  93             table = self.table
  94         where = self._where_string(dict_)
  95         self.execute('DELETE FROM %s%s' % (table, where))
  96
  97     def _clean_dict(self, dict_=None):
  98         if dict_ == None:
  99             return None
 100         for key in dict_.keys():
 101             value = dict_.pop(key)
 102             value = MySQLdb.escape_string(value)
 103             key = MySQLdb.escape_string(key)
 104             assert key not in dict_, '%s in %s!' % (key, dict_)
 105             dict_[key] = value
 106         return dict_
 107
 108     def _where_string(self, dict_=None):
 109         dict_ = self._clean_dict(dict_)
 110         if dict_ == None or len(dict_) == 0:
 111             where = ''
 112         else:
 113             where_args = []
 114             for key,value in dict_.items():
 115                 where_args.append("%s='%s'" % (key,value))
 116             where = ' where %s' % ' and '.join(where_args)
 117         return where
 118
 119
 120 def get_profs(db):
 121     """
 122 mysql> describe people;
 123 +--------------+--------------+------+-----+---------+----------------+
 124 | Field        | Type         | Null | Key | Default | Extra          |
 125 +--------------+--------------+------+-----+---------+----------------+
 126 | ID           | int(11)      | NO   | PRI | NULL    | auto_increment |
 127 | category     | varchar(255) | YES  |     | NULL    |                |
 128 | lastname     | varchar(255) | YES  |     | NULL    |                |
 129 | firstname    | varchar(255) | YES  |     | NULL    |                |
 130 | email        | varchar(255) | YES  |     | NULL    |                |
 131 | office       | varchar(255) | YES  |     | NULL    |                |
 132 | phone        | varchar(255) | YES  |     | NULL    |                |
 133 | lab          | varchar(255) | YES  |     | NULL    |                |
 134 | labphone     | varchar(255) | YES  |     | NULL    |                |
 135 | grouppage    | varchar(255) | YES  |     | NULL    |                |
 136 | personalpage | varchar(255) | YES  |     | NULL    |                |
 137 | position     | varchar(255) | YES  |     | NULL    |                |
 138 | position1    | varchar(255) | YES  |     | NULL    |                |
 139 | degrees      | varchar(255) | YES  |     | NULL    |                |
 140 | research     | mediumtext   | YES  |     | NULL    |                |
 141 | profile      | mediumtext   | YES  |     | NULL    |                |
 142 +--------------+--------------+------+-----+---------+----------------+
 143 16 rows in set (0.00 sec)
 144
 145 Where the relevant categories are
 146 | Faculty            |
 147 | Research Faculty   |
 148 | Teaching Faculty   |
 149 | Professor Emeritus |
 150 | Adjunct Professor  |
 151     """
 152     db.set_default_table('people')
 153     for category in ['Faculty',
 154                      'Research Faculty',
 155                      'Teaching Faculty',
 156                      'Professor Emeritus',
 157                      'Adjunct Professor']:
 158         for prof in db.list_entries({'category':category}):
 159             p = Professor()
 160             p.name = Name(
 161                 first_middle=prof['firstname'],
 162                 last=prof['lastname'],
 163                 )
 164             p.title = prof['position']
 165             if prof['position1'] != None:
 166                 p.title += ', %s' % prof['position1']
 167             p.graduations = []
 168             print prof['degrees']
 169             for degree in prof['degrees']:
 170                 p.greaduations.append(
 171                     Graduation(
 172                         college=None,
 173                         title=None,
 174                         year=None,
 175                         )
 176                     )
 177             p.contact = Contact(
 178                 office=prof['office'],
 179                 email=prof['email'],
 180                 website=prof['personalpage'],
 181                 phone=prof['phone'],
 182                 lab=prof['lab'],
 183                 lab_phone=prof['labphone'],
 184                 )
 185             p.bio = Bio(
 186                 specialization=prof['research'], # prof['grouppage']
 187                 publications=None,
 188                 profile=prof['profile'],
 189                 cv=None,
 190                 )
 191             self.logger.debug(unicode(p))
 192             yield p
 193
 194
 195 def main(argv):
 196     import optparse
 197     usage = """%prog [options] PROF_FILE
 198
 199 Where PROF_FILE is the output YAML file receiving professor data.
 200
 201 Example:
 202   ./sc.py prof-export_mysql profs.yaml
 203 """
 204     p = optparse.OptionParser(usage)
 205     p.add_option('-d', '--database', metavar='NAME', dest='database',
 206                  help='Name of the MySQL database (%default)',
 207                  default='directory')
 208     p.add_option('-v', '--verbose', dest='verbose', action='count',
 209                  help='increment verbosity (%default)',
 210                  default=0)
 211
 212     options,args = p.parse_args(argv)
 213     prof_file = args[0]
 214
 215     db = SimpleDB(verbose=options.verbose)
 216     db.connect(database=options.database)
 217     try:
 218         profs = list(get_profs(db))
 219     finally:
 220         db.disconnect()
 221
 222     yaml.save(db, profs)