Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Script to create database entries, folders in iCloud, a folder in Mail, and a set of labels for a new project.
import sqlite3
import os
import sys
from applescript import asrun
from subprocess import Popen, PIPE
import shutil
import requests
import json
# Initialize SQLite database, query, and parent projects directory.
pf = '{}/Library/Mobile Documents/com~apple~CloudDocs/projects'.format(os.environ['HOME'])
pl = '{}/projects.db'.format(pf)
cloudpf = '{}/Library/Mobile Documents/iCloud~com~omz-software~Pythonista3/Documents'.format(os.environ['HOME'])
cloudpl= '{}/projects.db'.format(cloudpf)
db = sqlite3.connect(pl)
query = 'insert into projects values(?, ?, ?, ?, ?, ?, ?, ?)'
pflabels = '{}/Dropbox/bin/pflabels'.format(os.environ['HOME'])
# Initialize Airtable request.
airURL = ''
airHeaders = {
'Authorization': 'Bearer blahblahblah',
'Content-Type': 'application/json'}
# AppleScript template for getting project contact info.
cScript = '''
tell application "Contacts"
set match to first item of (people whose {})
return value of item 1 of (emails of match)
end tell
# AppleScript template for creating new mailbox.
mbScript = '''
tell application "Mail"
tell account "FastMail"
make new mailbox with properties {{name:"{}"}}
end tell
end tell
# Get info from user.
name = raw_input("\033[1mProject name: \033[22m").decode('utf8')
number = int(raw_input("\033[1mProject number: \033[22m"))
client = raw_input("\033[1mClient name: \033[22m").decode('utf8')
# Use client's known email address if in Contacts.
# Otherwise, ask for it.
nameclause = 'name contains "' + \
'" and name contains "'.join(client.split()) + '"'
nameclause = nameclause.encode('utf8')
email = asrun(cScript.format(nameclause)).strip()
except RuntimeError:
email = raw_input("\033[1mClient Email: \033[22m")
ref = raw_input("\033[1mClient reference: \033[22m")
subdir = raw_input("\033[1mSubdirectory: \033[22m")
photos = raw_input("\033[1mPhotos?[y/n]: \033[22m")[0].lower()
small = raw_input("\033[1mSmall project?[y/n]: \033[22m")[0].lower()
rc = raw_input("\033[1mLabel position [r,c]: \033[22m")
# Add record to Airtable database.
payload = {'fields': {
'name': name,
'number': number,
'client': client,
'clientref': ref,
'directory': subdir,
'box': 0,
'email': email}}
r =, headers=airHeaders, json=payload)
print "New record created at {}".format(r.json()['createdTime'])
except KeyError:
print "Airtable error: {}".format(r.json()['error']['message'])
# Add record to SQLite projects database.
project = (name, number, client, ref, subdir, 0, email, '')
test = db.execute(query, project)
# Copy the database file to the iCloud Pythonista folder.
shutil.copyfile(pl, cloudpl)
# Create directories.
os.makedirs('{}/{}/invoices'.format(pf, subdir), 0755)
if photos == 'y':
os.makedirs('{}/{}/Photos'.format(pf, subdir), 0755)
# Make a mailbox folder.
# Parse the label starting position.
row, col = rc.split(',')
# Build the input data for the labels. Always make a label for the
# accounting folder. If a small project, make only one label for all
# file material. If not a small project, make labels for the notes
# folder and the separator tab
labeldata = "#{}|{}\n \n\n".format(name.encode('utf8'), number)
if small =='y':
labeldata += "Notes\n";
labeldata += "Notes\n"
labeldata += "\n#\\0\\0\\0{}|{}\\0\\0\\0\n".format(name.encode('utf8'), number)
# Make the labels.
proc = Popen([pflabels, '-r', row, '-c', col], stdin=PIPE)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment