1 #!/usr/bin/pythonw
   2 
   3 from pprint import pprint
   4 from appscript import *
   5 from Carbon.File import FSSpec
   6 import codecs
   7 from sets import Set
   8 
   9 DBPATH = '/Users/SOMEUSER/Desktop/Library.fp7'
  10 DBNAME = 'Library'
  11 TBLNAME = 'Library'
  12 IMPORTFILE = '/Users/SOMEUSER/Desktop/library.tab'
  13 
  14 
  15 """ 
  16         Connecting to /FileMaker and opening database
  17 """
  18 
  19 fm = app(id='com.filemaker.pro7') # [1]
  20 
  21 if not fm.databases[DBNAME].exists():
  22         fm.open(FSSpec(DBPATH)) # [2]
  23 
  24 
  25 """
  26     Querying database schema
  27 """
  28 
  29 print "FileMaker has %d database(s) opened: " % fm.count(class_=k.database)
  30 print ", ".join(fm.databases.name.get()) # [3]
  31 
  32 db = fm.databases[DBNAME] # [3]
  33 
  34 print "\nDatabase '%s' has %d table(s): " % (DBNAME, db.count(class_='cTBL')) # [4]
  35 print ", ".join(db.tables.name.get())
  36 
  37 t1 = db.tables[TBLNAME]
  38 
  39 print "\nTable '%s' has %d field(s): " % (TBLNAME, t1.count(class_=k.field))
  40 print ", ".join(t1.fields.name.get())
  41 
  42 print "\nTable '%s' schema:\n(name, type, nulls OK, unique value, global value)" % TBLNAME
  43 pprint(zip(
  44         t1.fields.name.get(), 
  45         t1.fields.default_type.get(),
  46         t1.fields.nulls_OK.get(),
  47         t1.fields.unique_value.get(),
  48         t1.fields.globalValue.get()))
  49 
  50 
  51 """
  52         Creating new records
  53 """
  54 
  55 if t1.count(class_=k.record) > 0:
  56         t1.records.delete()
  57 
  58 f = codecs.open(IMPORTFILE, 'r', 'mac_roman')
  59 
  60 for line in f:
  61         data = line.split('\t')
  62         rec = fm.create(new=k.record, with_data=data, at=t1) # [5]
  63 
  64 f.close()
  65 
  66 
  67 """
  68         Looking at records
  69 """
  70 
  71 print "\nTable '%s' has %d record(s)" % (TBLNAME, t1.count(class_=k.record))
  72 
  73 qry1 = t1.records.filter(its.fields['title'].cellValue.contains(u'Calvin'))
  74 
  75 if qry1.exists():
  76         print "\nselect title from library where title contains \'Calvin\':"
  77         pprint(qry1.fields['title'].get())
  78 
  79 qry2 = t1.records.filter(its.fields['author'].cellValue.contains(u'McCaffrey'))
  80 
  81 if qry2.exists():
  82         print "\nselect title from library where author contains \'McCaffrey\':"
  83         pprint(qry2.fields['title'].get())
  84 
  85 qry3 = t1.records.filter(its.fields['genre'].cellValue.contains(u'Thrillers'))
  86 
  87 if qry3.exists():
  88         print "\nselect title, authors from library where genre contains \'Thrillers\':"
  89         pprint(zip(qry3.fields['title'].get(), qry3.fields['author'].get()))
  90 
  91 print "\nselect distinct author from library:"
  92 pprint(Set(t1.fields['author'].get())) # [XXX]
  93 
  94 # show "List" layout with the records from qry3, sorted by authors
  95 
  96 qry3.show()
  97 fm.layouts['List'].show()
  98 fm.layouts['List'].sort(by=fm.fields['author']) # [XXX]