1#!/usr/local/bin/python 

2# encoding: utf-8 

3""" 

4*Render a python list of dictionaries in various list and markup formats* 

5 

6:Author: 

7 David Young 

8""" 

9from builtins import str 

10from builtins import range 

11from builtins import object 

12import sys 

13import os 

14import io 

15import re 

16import codecs 

17import copy 

18import json 

19import yaml 

20from decimal import Decimal 

21from datetime import datetime 

22os.environ['TERM'] = 'vt100' 

23from fundamentals import tools 

24from fundamentals.mysql import convert_dictionary_to_mysql_table 

25 

26 

27class list_of_dictionaries(object): 

28 """ 

29 *The dataset object is a list of python dictionaries. Using this class, the data can be rendered as various list and markup formats* 

30 

31 **Key Arguments** 

32 

33 - ``log`` -- logger 

34 - ``listOfDictionaries`` -- the list of dictionaries to render 

35 - ``reDatetime`` -- a pre-compiled datetime regex. Default *False*fss  

36 

37 

38 **Usage** 

39 

40 To initialise the dataset object: 

41 

42 ```python 

43 dataList = [ 

44 { 

45 "owner": "daisy", 

46 "pet": "dog", 

47 "address": "belfast, uk" 

48 }, 

49 { 

50 "owner": "john", 

51 "pet": "snake", 

52 "address": "the moon" 

53 }, 

54 { 

55 "owner": "susan", 

56 "pet": "crocodile", 

57 "address": "larne" 

58 } 

59 

60 ] 

61 

62 from fundamentals.renderer import list_of_dictionaries 

63 dataSet = list_of_dictionaries( 

64 log=log, 

65 listOfDictionaries=dataList 

66 ) 

67 ``` 

68 

69 """ 

70 

71 def __init__( 

72 self, 

73 log, 

74 listOfDictionaries, 

75 reDatetime=False 

76 ): 

77 self.log = log 

78 self.log.debug("instansiating a new 'list_of_dictionaries' object") 

79 self.listOfDictionaries = listOfDictionaries 

80 self.reDatetime = reDatetime 

81 

82 return None 

83 

84 @property 

85 def list( 

86 self): 

87 """*Returns the original list of dictionaries* 

88 

89 **Usage** 

90 

91 dataSet.list 

92 

93 """ 

94 return self.listOfDictionaries 

95 

96 def csv( 

97 self, 

98 filepath=None 

99 ): 

100 """*Render the data in CSV format* 

101 

102 **Key Arguments** 

103 

104 - ``filepath`` -- path to the file to write the csv content to. Default *None* 

105 

106 

107 **Return** 

108 

109 - ``renderedData`` -- the data rendered in csv format 

110 

111 

112 **Usage** 

113 

114 To render the data set as csv: 

115 

116 ```python 

117 print(dataSet.csv()) 

118 ``` 

119 

120 ```text 

121 owner,pet,address 

122 daisy,dog,"belfast, uk" 

123 john,snake,the moon 

124 susan,crocodile,larne 

125 ``` 

126 

127 and to save the csv rendering to file: 

128 

129 ```python 

130 dataSet.csv("/path/to/myfile.csv") 

131 ``` 

132 

133 """ 

134 self.log.debug('starting the ``csv`` method') 

135 

136 renderedData = self._list_of_dictionaries_to_csv("machine") 

137 

138 if filepath and renderedData != "NO MATCH": 

139 

140 # RECURSIVELY CREATE MISSING DIRECTORIES 

141 if not os.path.exists(os.path.dirname(filepath)): 

142 os.makedirs(os.path.dirname(filepath)) 

143 

144 writeFile = codecs.open(filepath, encoding='utf-8', mode='w') 

145 writeFile.write(renderedData) 

146 writeFile.close() 

147 

148 self.log.debug('completed the ``csv`` method') 

149 return renderedData 

150 

151 def table( 

152 self, 

153 filepath=None 

154 ): 

155 """*Render the data as a plain text table* 

156 

157 **Key Arguments** 

158 

159 - ``filepath`` -- path to the file to write the table to. Default *None* 

160 

161 

162 **Return** 

163 

164 - ``renderedData`` -- the data rendered as a plain text table 

165 

166 

167 **Usage** 

168 

169 To render the data set as a plain text table: 

170 

171 ```python 

172 print(dataSet.table()) 

173 ``` 

174 

175 ```text 

176 +--------+------------+--------------+ 

177 | owner | pet | address | 

178 +========+============+==============+ 

179 | daisy | dog | belfast, uk | 

180 | john | snake | the moon | 

181 | susan | crocodile | larne | 

182 +--------+------------+--------------+ 

183 ``` 

184 

185 and to save the table rendering to file: 

186 

187 ```python 

188 dataSet.table("/path/to/myfile.ascii") 

189 ``` 

190 

191 """ 

192 self.log.debug('starting the ``table`` method') 

193 

194 self.filepath = filepath 

195 renderedData = self._list_of_dictionaries_to_csv("human") 

196 

197 if filepath and len(self.listOfDictionaries): 

198 

199 # RECURSIVELY CREATE MISSING DIRECTORIES 

200 if not os.path.exists(os.path.dirname(filepath)): 

201 os.makedirs(os.path.dirname(filepath)) 

202 

203 writeFile = codecs.open(filepath, encoding='utf-8', mode='w') 

204 writeFile.write(renderedData) 

205 writeFile.close() 

206 

207 self.log.debug('completed the ``table`` method') 

208 return renderedData 

209 

210 def reST( 

211 self, 

212 filepath=None 

213 ): 

214 """*Render the data as a resturcturedText table* 

215 

216 **Key Arguments** 

217 

218 - ``filepath`` -- path to the file to write the table to. Default *None* 

219 

220 

221 **Return** 

222 

223 - ``renderedData`` -- the data rendered as a resturcturedText table 

224 

225 

226 **Usage** 

227 

228 To render the data set as a resturcturedText table: 

229 

230 ```python 

231 print(dataSet.reST()) 

232 ``` 

233 

234 ```text 

235 +--------+------------+--------------+ 

236 | owner | pet | address | 

237 +========+============+==============+ 

238 | daisy | dog | belfast, uk | 

239 +--------+------------+--------------+ 

240 | john | snake | the moon | 

241 +--------+------------+--------------+ 

242 | susan | crocodile | larne | 

243 +--------+------------+--------------+ 

244 ``` 

245 

246 and to save the table rendering to file: 

247 

248 ```python 

249 dataSet.reST("/path/to/myfile.rst") 

250 ``` 

251 

252 """ 

253 self.log.debug('starting the ``table`` method') 

254 

255 self.filepath = filepath 

256 renderedData = self._list_of_dictionaries_to_csv("reST") 

257 

258 if filepath and len(self.listOfDictionaries): 

259 

260 # RECURSIVELY CREATE MISSING DIRECTORIES 

261 if not os.path.exists(os.path.dirname(filepath)): 

262 os.makedirs(os.path.dirname(filepath)) 

263 

264 writeFile = codecs.open(filepath, encoding='utf-8', mode='w') 

265 writeFile.write(renderedData) 

266 writeFile.close() 

267 

268 self.log.debug('completed the ``table`` method') 

269 return renderedData 

270 

271 def markdown( 

272 self, 

273 filepath=None 

274 ): 

275 """*Render the data as a markdown table* 

276 

277 **Key Arguments** 

278 

279 - ``filepath`` -- path to the file to write the markdown to. Default *None* 

280 

281 

282 **Return** 

283 

284 - ``renderedData`` -- the data rendered as a markdown table 

285 

286 

287 **Usage** 

288 

289 To render the data set as a markdown table: 

290 

291 ```python 

292 print(dataSet.markdown()) 

293 ``` 

294 

295 ```markdown 

296 | owner | pet | address | 

297 |:-------|:-----------|:-------------| 

298 | daisy | dog | belfast, uk | 

299 | john | snake | the moon | 

300 | susan | crocodile | larne | 

301 ``` 

302 

303 and to save the markdown table rendering to file: 

304 

305 ```python 

306 dataSet.table("/path/to/myfile.md") 

307 ``` 

308 

309 """ 

310 self.log.debug('starting the ``markdown`` method') 

311 

312 self.filepath = filepath 

313 renderedData = self._list_of_dictionaries_to_csv("markdown") 

314 

315 if filepath and len(self.listOfDictionaries): 

316 

317 # RECURSIVELY CREATE MISSING DIRECTORIES 

318 if not os.path.exists(os.path.dirname(filepath)): 

319 os.makedirs(os.path.dirname(filepath)) 

320 

321 writeFile = codecs.open(filepath, encoding='utf-8', mode='w') 

322 writeFile.write(renderedData) 

323 writeFile.close() 

324 

325 self.log.debug('completed the ``markdown`` method') 

326 return renderedData 

327 

328 def json( 

329 self, 

330 filepath=None 

331 ): 

332 """*Render the data in json format* 

333 

334 **Key Arguments** 

335 

336 - ``filepath`` -- path to the file to write the json content to. Default *None* 

337 

338 

339 **Return** 

340 

341 - ``renderedData`` -- the data rendered as json 

342 

343 

344 **Usage** 

345 

346 To render the data set as json: 

347 

348 ```python 

349 print(dataSet.json()) 

350 ``` 

351 

352 ```json 

353 [ 

354 { 

355 "address": "belfast, uk", 

356 "owner": "daisy", 

357 "pet": "dog" 

358 }, 

359 { 

360 "address": "the moon", 

361 "owner": "john", 

362 "pet": "snake" 

363 }, 

364 { 

365 "address": "larne", 

366 "owner": "susan", 

367 "pet": "crocodile" 

368 } 

369 ] 

370 ``` 

371 

372 and to save the json rendering to file: 

373 

374 ```python 

375 dataSet.json("/path/to/myfile.json") 

376 ``` 

377 

378 """ 

379 self.log.debug('starting the ``json`` method') 

380 

381 dataCopy = copy.deepcopy(self.listOfDictionaries) 

382 for d in dataCopy: 

383 for k, v in list(d.items()): 

384 if isinstance(v, datetime): 

385 d[k] = v.strftime("%Y%m%dt%H%M%S") 

386 

387 renderedData = json.dumps( 

388 dataCopy, 

389 separators=(',', ': '), 

390 sort_keys=True, 

391 indent=4 

392 ) 

393 

394 if filepath and len(self.listOfDictionaries): 

395 

396 # RECURSIVELY CREATE MISSING DIRECTORIES 

397 if not os.path.exists(os.path.dirname(filepath)): 

398 os.makedirs(os.path.dirname(filepath)) 

399 

400 writeFile = codecs.open(filepath, encoding='utf-8', mode='w') 

401 writeFile.write(renderedData) 

402 writeFile.close() 

403 

404 self.log.debug('completed the ``json`` method') 

405 return renderedData 

406 

407 def yaml( 

408 self, 

409 filepath=None 

410 ): 

411 """*Render the data in yaml format* 

412 

413 **Key Arguments** 

414 

415 - ``filepath`` -- path to the file to write the yaml content to. Default *None* 

416 

417 

418 **Return** 

419 

420 - ``renderedData`` -- the data rendered as yaml 

421 

422 

423 **Usage** 

424 

425 To render the data set as yaml: 

426 

427 ```python 

428 print(dataSet.yaml()) 

429 ``` 

430 

431 ```yaml 

432 - address: belfast, uk 

433 owner: daisy 

434 pet: dog 

435 - address: the moon 

436 owner: john 

437 pet: snake 

438 - address: larne 

439 owner: susan 

440 pet: crocodile 

441 ``` 

442 

443 and to save the yaml rendering to file: 

444 

445 ```python 

446 dataSet.json("/path/to/myfile.yaml") 

447 ``` 

448 

449 """ 

450 self.log.debug('starting the ``yaml`` method') 

451 

452 dataCopy = [] 

453 dataCopy[:] = [dict(l) for l in self.listOfDictionaries] 

454 renderedData = yaml.dump(dataCopy, default_flow_style=False) 

455 

456 if filepath and len(self.listOfDictionaries): 

457 

458 # RECURSIVELY CREATE MISSING DIRECTORIES 

459 if not os.path.exists(os.path.dirname(filepath)): 

460 os.makedirs(os.path.dirname(filepath)) 

461 

462 stream = open(filepath, 'w') 

463 yaml.dump(dataCopy, stream, default_flow_style=False) 

464 stream.close() 

465 

466 self.log.debug('completed the ``yaml`` method') 

467 return renderedData 

468 

469 def mysql( 

470 self, 

471 tableName, 

472 filepath=None, 

473 createStatement=None 

474 ): 

475 """*Render the dataset as a series of mysql insert statements* 

476 

477 **Key Arguments** 

478 

479 - ``tableName`` -- the name of the mysql db table to assign the insert statements to. 

480 - ``filepath`` -- path to the file to write the mysql inserts content to. Default *None* 

481 createStatement 

482 

483 

484 **Return** 

485 

486 - ``renderedData`` -- the data rendered mysql insert statements (string format) 

487 

488 

489 **Usage** 

490 

491 ```python 

492 print(dataSet.mysql("testing_table")) 

493 ``` 

494 

495 this output the following: 

496 

497 ```plain 

498 INSERT INTO `testing_table` (address,dateCreated,owner,pet) VALUES ("belfast, uk" ,"2016-09-14T16:21:36" ,"daisy" ,"dog") ON DUPLICATE KEY UPDATE address="belfast, uk", dateCreated="2016-09-14T16:21:36", owner="daisy", pet="dog" ; 

499 INSERT INTO `testing_table` (address,dateCreated,owner,pet) VALUES ("the moon" ,"2016-09-14T16:21:36" ,"john" ,"snake") ON DUPLICATE KEY UPDATE address="the moon", dateCreated="2016-09-14T16:21:36", owner="john", pet="snake" ; 

500 INSERT INTO `testing_table` (address,dateCreated,owner,pet) VALUES ("larne" ,"2016-09-14T16:21:36" ,"susan" ,"crocodile") ON DUPLICATE KEY UPDATE address="larne", dateCreated="2016-09-14T16:21:36", owner="susan", pet="crocodile" ; 

501 ``` 

502 

503 To save this rendering to file use: 

504 

505 ```python 

506 dataSet.mysql("testing_table", "/path/to/myfile.sql") 

507 ``` 

508 

509 """ 

510 self.log.debug('starting the ``mysql`` method') 

511 

512 import re 

513 if createStatement and "create table if not exists" not in createStatement.lower(): 

514 regex = re.compile(r'^\s*CREATE TABLE ', re.I | re.S) 

515 createStatement = regex.sub( 

516 "CREATE TABLE IF NOT EXISTS ", createStatement) 

517 

518 renderedData = self._list_of_dictionaries_to_mysql_inserts( 

519 tableName=tableName, 

520 createStatement=createStatement 

521 ) 

522 

523 if filepath and len(self.listOfDictionaries): 

524 

525 # RECURSIVELY CREATE MISSING DIRECTORIES 

526 if not os.path.exists(os.path.dirname(filepath)): 

527 os.makedirs(os.path.dirname(filepath)) 

528 

529 writeFile = open(filepath, mode='w') 

530 writeFile.write(renderedData) 

531 writeFile.close() 

532 

533 self.log.debug('completed the ``mysql`` method') 

534 return renderedData 

535 

536 def _list_of_dictionaries_to_csv( 

537 self, 

538 csvType="human"): 

539 """Convert a python list of dictionaries to pretty csv output 

540 

541 **Key Arguments** 

542 

543 - ``csvType`` -- human, machine or reST 

544 

545 

546 **Return** 

547 

548 - ``output`` -- the contents of a CSV file 

549 

550 """ 

551 self.log.debug( 

552 'starting the ``_list_of_dictionaries_to_csv`` function') 

553 import unicodecsv as csv 

554 

555 if not len(self.listOfDictionaries): 

556 return "NO MATCH" 

557 

558 dataCopy = copy.deepcopy(self.listOfDictionaries) 

559 

560 tableColumnNames = list(dataCopy[0].keys()) 

561 columnWidths = [] 

562 columnWidths[:] = [len(tableColumnNames[i]) 

563 for i in range(len(tableColumnNames))] 

564 

565 output = io.BytesIO() 

566 # setup csv styles 

567 if csvType == "machine": 

568 delimiter = "," 

569 elif csvType in ["human", "markdown"]: 

570 delimiter = "|" 

571 elif csvType in ["reST"]: 

572 delimiter = "|" 

573 if csvType in ["markdown"]: 

574 writer = csv.writer(output, delimiter=delimiter, 

575 quoting=csv.QUOTE_NONE, doublequote=False, quotechar='"', escapechar="\\", lineterminator="\n") 

576 else: 

577 writer = csv.writer(output, dialect='excel', delimiter=delimiter, 

578 quotechar='"', quoting=csv.QUOTE_MINIMAL, lineterminator="\n") 

579 

580 if csvType in ["markdown"]: 

581 dividerWriter = csv.writer( 

582 output, delimiter="|", quoting=csv.QUOTE_NONE, doublequote=False, quotechar='"', escapechar="\\", lineterminator="\n") 

583 else: 

584 dividerWriter = csv.writer(output, dialect='excel', delimiter="+", 

585 quotechar='"', quoting=csv.QUOTE_MINIMAL, lineterminator="\n") 

586 # add column names to csv 

587 header = [] 

588 divider = [] 

589 rstDivider = [] 

590 allRows = [] 

591 

592 # clean up data 

593 for row in dataCopy: 

594 for c in tableColumnNames: 

595 if isinstance(row[c], float) or isinstance(row[c], Decimal): 

596 row[c] = "%0.9g" % row[c] 

597 elif isinstance(row[c], datetime): 

598 thisDate = str(row[c])[:10] 

599 row[c] = "%(thisDate)s" % locals() 

600 

601 # set the column widths 

602 for row in dataCopy: 

603 for i, c in enumerate(tableColumnNames): 

604 if len(str(row[c])) > columnWidths[i]: 

605 columnWidths[i] = len(str(row[c])) 

606 

607 # table borders for human readable 

608 if csvType in ["human", "markdown", "reST"]: 

609 header.append("") 

610 divider.append("") 

611 rstDivider.append("") 

612 

613 for i, c in enumerate(tableColumnNames): 

614 if csvType == "machine": 

615 header.append(c) 

616 elif csvType in ["human", "markdown", "reST"]: 

617 header.append( 

618 c.ljust(columnWidths[i] + 2).rjust(columnWidths[i] + 3)) 

619 divider.append('-' * (columnWidths[i] + 3)) 

620 rstDivider.append('=' * (columnWidths[i] + 3)) 

621 

622 # table border for human readable 

623 if csvType in ["human", "markdown", "reST"]: 

624 header.append("") 

625 divider.append("") 

626 rstDivider.append("") 

627 

628 # fill in the data 

629 for row in dataCopy: 

630 thisRow = [] 

631 # table border for human readable 

632 if csvType in ["human", "markdown", "reST"]: 

633 thisRow.append("") 

634 

635 for i, c in enumerate(tableColumnNames): 

636 if csvType in ["human", "markdown", "reST"]: 

637 if row[c] == None: 

638 row[c] = "" 

639 row[c] = str(str(row[c]).ljust(columnWidths[i] + 2) 

640 .rjust(columnWidths[i] + 3)) 

641 thisRow.append(row[c]) 

642 # table border for human readable 

643 if csvType in ["human", "markdown", "reST"]: 

644 thisRow.append("") 

645 allRows.append(thisRow) 

646 if csvType in ["reST"]: 

647 allRows.append(divider) 

648 

649 if csvType == "machine": 

650 writer.writerow(header) 

651 if csvType in ["reST"]: 

652 dividerWriter.writerow(divider) 

653 writer.writerow(header) 

654 dividerWriter.writerow(rstDivider) 

655 if csvType in ["human"]: 

656 dividerWriter.writerow(divider) 

657 writer.writerow(header) 

658 dividerWriter.writerow(divider) 

659 elif csvType in ["markdown"]: 

660 writer.writerow(header) 

661 dividerWriter.writerow(divider) 

662 

663 # write out the data 

664 writer.writerows(allRows) 

665 # table border for human readable 

666 if csvType in ["human"]: 

667 dividerWriter.writerow(divider) 

668 

669 output = output.getvalue() 

670 output = output.strip() 

671 try: 

672 output = output.decode("UTF-8") 

673 except: 

674 output = str(output) 

675 

676 if csvType in ["markdown"]: 

677 output = output.replace("|--", "|:-") 

678 if csvType in ["reST"]: 

679 output = output.replace("|--", "+--").replace("--|", "--+") 

680 

681 self.log.debug( 

682 'completed the ``_list_of_dictionaries_to_csv`` function') 

683 

684 return output 

685 

686 def _list_of_dictionaries_to_mysql_inserts( 

687 self, 

688 tableName, 

689 createStatement=None): 

690 """Convert a python list of dictionaries to pretty csv output 

691 

692 **Key Arguments** 

693 

694 - ``tableName`` -- the name of the table to create the insert statements for 

695 - ``createStatement`` -- add this create statement to the top of the file. Will only be executed if no table of that name exists in database. Default *None* 

696 

697 

698 **Return** 

699 

700 - ``output`` -- the mysql insert statements (as a string) 

701 

702 """ 

703 self.log.debug( 

704 'completed the ````_list_of_dictionaries_to_mysql_inserts`` function') 

705 

706 if not len(self.listOfDictionaries): 

707 return "NO MATCH" 

708 

709 dataCopy = copy.deepcopy(self.listOfDictionaries) 

710 

711 if createStatement: 

712 output = createStatement + "\n" 

713 else: 

714 output = "" 

715 

716 inserts = [] 

717 

718 inserts = [] 

719 inserts[:] = [convert_dictionary_to_mysql_table(log=self.log, dictionary=d, dbTableName=tableName, uniqueKeyList=[ 

720 ], dateModified=False, returnInsertOnly=True, replace=True, batchInserts=False, reDatetime=self.reDatetime) for d in dataCopy] 

721 output += ";\n".join(inserts) + ";" 

722 

723 self.log.debug( 

724 'completed the ``_list_of_dictionaries_to_mysql_inserts`` function') 

725 return output