Annotation of ZSQLExtend/importCDLIimglist.py, revision 1.3
1.1 casties 1: #!/usr/local/bin/python
2: #
3:
4: import string
5: import logging
6: import sys
7: import types
8: import time
9:
10: from importASCII import ASCII_handler
11: from importASCII import importASCII
12: from importASCII import SimpleSearch
13:
1.3 ! casties 14: version_string = "V0.2.1 ROC 21.1.2009"
1.1 casties 15:
16: # mapping img_type to SQL field names
17: imgTypeMap = {
18: 'p':'img_p',
19: 'd':'img_d',
20: 'e':'img_e',
21: 'ed':'img_ed',
22: 'l':'img_l',
23: 'ld':'img_ld',
24: 'ls':'img_ls'}
1.2 casties 25: # list of fields in constant order (for SQL queries)
26: imgTypes = imgTypeMap.keys()
1.1 casties 27:
28: upd_fields = "fn,,img_type,id_text"
29: id_field = "id_text"
30: img_type_field = "img_type"
31:
32: def setup(self):
33: """specialized setup version"""
34: ASCII_handler._setup(self)
1.2 casties 35: # create special updQuery for img_type fields
36: setStr=string.join(["%s = %%s"%imgTypeMap[f] for f in imgTypes], ', ')
37: self.updQuery = "UPDATE %s SET %s WHERE id_text = %%s"%(self.table,setStr)
38: # create special delQuery for img_type fields
39: delStr=string.join(["%s = null"%imgTypeMap[f] for f in imgTypes], ', ')
40: self.delQuery = "UPDATE %s SET %s WHERE id_text = %%s"%(self.table,delStr)
1.1 casties 41: # text file field for img_type
42: self.xml_img_type = self.sql_field_map[img_type_field]
1.2 casties 43: # dict of all img fields
44: self.img_data = {}
1.1 casties 45:
46:
47: def handle_line(self, line):
48: """process single line of text data"""
49: self.logger.debug("START ROW")
50:
51: content = line.split()
52: self.xml_data = content
53: self.rowcnt += 1
54: # process collected row data
55: update=False
1.3 ! casties 56:
1.1 casties 57: # synchronize by id_field
58: id_val = self.xml_data[self.xml_id]
1.3 ! casties 59:
1.1 casties 60: # get img_type
61: img_type_val = self.xml_data[self.xml_img_type]
62:
63: # collect all values
64: # filename is first value
65: fn = self.xml_data[self.sql_field_map['fn']]
66: if fn.startswith('tn_'):
67: # ignore thumbnails
68: self.logger.debug("END ROW")
69: return
70:
1.3 ! casties 71: # is the entry new?
! 72: if id_val in self.dbIDs:
! 73: self.dbIDs[id_val] += 1
! 74: update=True
! 75:
1.1 casties 76: if update:
77: # update existing row (by id_field)
1.2 casties 78: if id_val in self.img_data:
79: self.img_data[id_val][img_type_val] = fn
80: else:
81: self.img_data[id_val] = {img_type_val:fn}
1.1 casties 82:
83: self.logger.debug("update: %s = %s"%(id_val, args))
84:
85: elif not self.update_mode:
1.2 casties 86: # create new row (doesn't work)
1.1 casties 87: self.logger.debug("insert: %s"%args)
88: #SimpleSearch(self.db, self.addQuery, args, ascii=self.ascii_db)
89:
90: #self.logger.info(" row:"+"%d (%s)"%(self.rowcnt,id_val))
91: if (self.rowcnt % 100) == 0:
92: self.logger.info(" row:"+"%d (id:%s)"%(self.rowcnt,id_val))
1.2 casties 93:
94: self.logger.debug("END ROW")
95: return
96:
97:
98: def parse(self, filename):
99: """open file and read data"""
100: self.logger.info("reading data...")
101: self.rowcnt = 0
102:
103: fh = open(filename,"r")
104: self.logger.debug("BEGIN RESULTSET")
105: # parse line-wise
106: for line in fh:
107: self.handle_line(line)
108:
109: # done. Wrap up
110: self.logger.debug("END RESULTSET")
111:
112: self.logger.info("importing rows in db...")
113: i = 0
114: for id in self.dbIDs.keys():
115: # find all fields
116: if self.dbIDs[id] == 0:
117: # unmatched entry
118: #self.logger.debug("CLEAN: %s with %s"%(self.delQuery,id))
119: SimpleSearch(self.db, self.delQuery, [id], ascii=self.ascii_db, result=False)
120:
121: elif self.dbIDs[id] > 0:
122: # assemble query
1.3 ! casties 123: imgd = self.img_data.get(id, None)
! 124: if imgd is None:
! 125: self.logger.error("No data for id %s while marked for update!"%id)
! 126: continue
! 127:
! 128: args = [ imgd.get(f,None) for f in imgTypes ]
1.2 casties 129: args.append(id)
130: # update
131: #self.logger.debug("UPDATE: %s with %s"%(self.updQuery,args))
132: SimpleSearch(self.db, self.updQuery, args, ascii=self.ascii_db, result=False)
133:
134: i += 1
135: if i % 100 == 0:
136: self.logger.info(" import: %d (%s)"%(i,id))
137: self.dbCon.commit()
138:
139: self.dbCon.commit()
140: # reinstate backup tables
141: if self.backup_table:
142: backup_name = "%s_%s"%(self.orig_table,time.strftime('%Y_%m_%d_%H_%M_%S'))
143: self.logger.info("rename backup table %s to %s"%(self.orig_table,backup_name))
144: qstr = "ALTER TABLE %s RENAME TO %s"%(self.orig_table,backup_name)
145: self.db.execute(qstr)
146: self.logger.info("rename working table %s to %s"%(self.table,self.orig_table))
147: qstr = "ALTER TABLE %s RENAME TO %s"%(self.table,self.orig_table)
148: self.db.execute(qstr)
1.1 casties 149: self.dbCon.commit()
150:
151: return
152:
153: # monkey patch ASCII_handler
154: ASCII_handler._handle_line = ASCII_handler.handle_line
155: ASCII_handler.handle_line = handle_line
1.2 casties 156: ASCII_handler._parse = ASCII_handler.parse
157: ASCII_handler.parse = parse
1.1 casties 158: ASCII_handler._setup = ASCII_handler.setup
159: ASCII_handler.setup = setup
160:
161:
162: if __name__ == "__main__":
163: from optparse import OptionParser
164:
165: opars = OptionParser()
166: opars.add_option("-f", "--file",
167: dest="filename",
168: help="text file name", metavar="FILE")
169: opars.add_option("-c", "--dsn",
170: dest="dsn",
171: help="database connection string")
172: opars.add_option("-t", "--table",
173: dest="table",
174: help="database table name")
175: opars.add_option("--ascii-db", default=False, action="store_true",
176: dest="ascii_db",
177: help="the SQL database stores ASCII instead of unicode")
178: opars.add_option("--replace", default=False, action="store_true",
179: dest="replace_table",
180: help="replace table i.e. delete and re-insert data")
181: opars.add_option("--backup", default=False, action="store_true",
182: dest="backup_table",
183: help="create backup of old table (breaks indices)")
184: opars.add_option("-d", "--debug", default=False, action="store_true",
185: dest="debug",
186: help="debug mode (more output)")
187:
188: (options, args) = opars.parse_args()
189:
190: if (options.filename is None
191: or options.dsn is None
192: or options.table is None):
193: # not enough parameters
194: print "importCDLIimglist "+version_string
195: opars.print_help()
196: sys.exit(1)
197:
198: if options.debug:
199: loglevel = logging.DEBUG
200: else:
201: loglevel = logging.INFO
202:
203: logging.basicConfig(level=loglevel,
204: format='%(asctime)s %(levelname)s %(message)s',
205: datefmt='%H:%M:%S')
206:
207: # fixed settings for CDLI imglist
208: options.update_fields = upd_fields
209: options.id_field = id_field
210: options.update_mode = True
211:
212: importASCII(options)
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvs/cvsweb/icons/back.gif){kind=icon}
Return to importCDLIimglist.py CVS log ![[TXT]](/cvs/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvs/cvsweb/icons/dir.gif){kind=icon}
Up to [Repository] / ZSQLExtend