2 # Copyright (C) 2010 W. Trevor King <wking@drexel.edu>
4 # This file is part of SiteCorePy.
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.
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.
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/>.
19 """Move Professors from MySQL -> YAML.
21 Not really SiteCore related, but it seves as an example of what to do
22 before calling prof_import.
31 from .. import get_logger
32 from . import Name, Graduation, Contact, Bio, Professor
35 class SimpleDB (object):
36 def __init__(self, verbose=0):
40 self.logger = get_logger(verbose)
42 def connect(database, host='localhost',
43 username=None, password=None):
45 username = getpass.getpass('username: ')
47 password = getpass.getpass('password: ')
48 self.db = MySQLdb.connect(host=host, user=username,
49 passwd=password, db=database)
52 self.cursor = self.db.cursor()
58 def set_default_table(self, table):
61 def add_entry(self, dict, table=None):
64 dict = self._clean_dict(dict)
66 values = ["'%s'" % dict[key] for key in keys]
67 self.cursor.execute("INSERT INTO %s (%s) VALUES (%s)"
68 % (table, ', '.join(keys), ', '.join(values)))
71 def list_entries(self, dict=None, table=None):
74 where = self._where_string(dict)
75 self.cursor.execute('SELECT * FROM %s%s' % (table, where))
76 numrows = int(self.cursor.rowcount)
77 print '\t'.join([dtuple[0] for dtuple in self.cursor.description])
78 for x in range(0,numrows):
79 row = self.cursor.fetchone()
80 print '\t'.join(['%s' % x for x in row])
82 def delete_entries(self, dict, table=None):
85 where = self._where_string(dict)
86 self.cursor.execute('DELETE FROM %s%s' % (table, where))
88 def _clean_dict(self, dict=None):
91 for key in dict.keys():
93 value = MySQLdb.escape_string(value)
94 key = MySQLdb.escape_string(key)
95 assert key not in dict, '%s in %s!' % (key, dict)
99 def _where_string(self, dict=None):
100 dict = self._clean_dict(dict)
101 if dict == None or len(dict) == 0:
105 for key,value in dict.items():
106 where_args.append("%s='%s'" % (key,value))
107 where = 'where %s' % ' and '.join(where_args)
113 mysql> describe people;
114 +--------------+--------------+------+-----+---------+----------------+
115 | Field | Type | Null | Key | Default | Extra |
116 +--------------+--------------+------+-----+---------+----------------+
117 | ID | int(11) | NO | PRI | NULL | auto_increment |
118 | category | varchar(255) | YES | | NULL | |
119 | lastname | varchar(255) | YES | | NULL | |
120 | firstname | varchar(255) | YES | | NULL | |
121 | email | varchar(255) | YES | | NULL | |
122 | office | varchar(255) | YES | | NULL | |
123 | phone | varchar(255) | YES | | NULL | |
124 | lab | varchar(255) | YES | | NULL | |
125 | labphone | varchar(255) | YES | | NULL | |
126 | grouppage | varchar(255) | YES | | NULL | |
127 | personalpage | varchar(255) | YES | | NULL | |
128 | position | varchar(255) | YES | | NULL | |
129 | position1 | varchar(255) | YES | | NULL | |
130 | degrees | varchar(255) | YES | | NULL | |
131 | research | mediumtext | YES | | NULL | |
132 | profile | mediumtext | YES | | NULL | |
133 +--------------+--------------+------+-----+---------+----------------+
134 16 rows in set (0.00 sec)
136 Where the relevant categories are
140 | Professor Emeritus |
141 | Adjunct Professor |
143 db.set_default_table('people')
144 for category in ['Faculty',
147 'Professor Emeritus',
148 'Adjunct Professor']:
149 for prof in db.list_entries({'category':category}):
157 usage = """%prog [options] PROF_FILE
159 Where PROF_FILE is the output YAML file receiving professor data.
162 ./sc prof-export_mysql profs.yaml
164 p = optparse.OptionParser(usage)
165 p.add_option('-d', '--database', metavar='NAME', dest='database',
166 help='Name of the MySQL database (%default)',
168 p.add_option('-v', '--verbose', dest='verbose', action='count',
169 help='increment verbosity (%default)',
172 options,args = p.parse_args(argv)
175 db = SimpleDB(verbose=options.verbose)
176 db.connect(database=options.database)
178 profs = list(get_profs(db))