to use this script, you'll need the Library.fp7 and Library.tab sample files (I will post them asap) and to edit the DBPATH and IMPORTFILE variables accordingly.
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]
Footnotes:
use app(id='com.filemaker.pro7') instead of app('FileMaker Pro') to be sure only /FileMakerPro 7 is launched
- Carbon.File.FSSpec is not the best way to handle files. I have to investigate that point...
fm.databases.name returns the db name with the '.fp7' suffix, but it can be omitted when specifying fm.databases[...]
specifying the table class as class_=k.table doesn't work, use class_='cTBL' instead
according to FileMakerPro's Apple Event Reference, the fastest way to insert data is to:
use fm.create(new=k.record, with_data=data, at=table1), with data as a list of values in the same order as table1.fields.name.get()
- insert data at the table level, outside the currently found set.