Archive
Tags
android (3)
ant (2)
beautifulsoup (1)
debian (1)
decorators (1)
django (9)
dovecot (1)
encryption (1)
fix (4)
gotcha (2)
hobo (1)
htmlparser (1)
imaplib (2)
java (1)
json (2)
kerberos (2)
linux (7)
lxml (5)
markdown (4)
mechanize (6)
multiprocessing (1)
mysql (2)
nagios (2)
new_features (3)
open_source (5)
optparse (2)
parsing (1)
perl (2)
postgres (1)
preseed (1)
pxe (4)
pyqt4 (1)
python (41)
raid (1)
rails (1)
red_hat (1)
reportlab (4)
request_tracker (2)
rt (2)
ruby (1)
scala (1)
screen_scraping (7)
shell_scripting (8)
soap (1)
solaris (3)
sql (2)
sqlalchemy (2)
tips_and_tricks (1)
twitter (2)
ubuntu (1)
vmware (2)
windows (1)
zimbra (2)

SQLAlchemy is one of the best pieces of software I have had the pleasure to use. We needed an in-office mirror of a few tables on our Oracle server for Crystal Reports. We did not need full replication, so I decided to write something to make table copying easy. SQLAlchemy made this easy and fun.

This uses the latest version of SQLAlchemy at this time, 5.3. SQLAlchemy only abstracts the database interaction. You will still need to install the database driver, like MySQLdb and cx_Oracle.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
#!/usr/bin/env python

import getopt
import sys
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

def make_session(connection_string):
    engine = create_engine(connection_string, echo=False, convert_unicode=True)
    Session = sessionmaker(bind=engine)
    return Session(), engine

def pull_data(from_db, to_db, tables):
    source, sengine = make_session(from_db)
    smeta = MetaData(bind=sengine)
    destination, dengine = make_session(to_db)

    for table_name in tables:
        print 'Processing', table_name
        print 'Pulling schema from source server'
        table = Table(table_name, smeta, autoload=True)
        print 'Creating table on destination server'
        table.metadata.create_all(dengine)
        NewRecord = quick_mapper(table)
        columns = table.columns.keys()
        print 'Transferring records'
        for record in source.query(table).all():
            data = dict(
                [(str(column), getattr(record, column)) for column in columns]
            )
            destination.merge(NewRecord(**data))
    print 'Committing changes'
    destination.commit()

def print_usage():
    print """
Usage: %s -f source_server -t destination_server table [table ...]
    -f, -t = driver://user[:password]@host[:port]/database

Example: %s -f oracle://someuser:PaSsWd@db1/TSH1 \\
    -t mysql://root@db2:3307/reporting table_one table_two
    """ % (sys.argv[0], sys.argv[0])

def quick_mapper(table):
    Base = declarative_base()
    class GenericMapper(Base):
        __table__ = table
    return GenericMapper

if __name__ == '__main__':
    optlist, tables = getopt.getopt(sys.argv[1:], 'f:t:')

    options = dict(optlist)
    if '-f' not in options or '-t' not in options or not tables:
        print_usage()
        raise SystemExit, 1

    pull_data(
        options['-f'],
        options['-t'],
        tables,
    )

The main event is in pull_data. I create several objects to access both sides. My make_session function creates two of these, a Session, which I use for table transactions, and an Engine, which I use for database transactions. The MetaData is used to pull table schema here.

For every table, I pull the schema of the source table, Table(table_name, smeta, autoload=True), and create that table in destination database, table.metadata.create_all(dengine). The coolest thing about this is that SQLAlchemy does all of the hard work. Oracle fields become MySQL fields, or whatever is appropriate. If the table exists, nothing is done.

The destination system is ready for data now. My quick_mapper spits out ORM for a given table, which makes new records a breeze. I loop through every record from the source, create a dictionary of the column names and data, and make new records on the destination server, using the dictionary as keyword arguments. One point to note is that merge will update records that have the same primary key as the new record.

At the end, I commit the changes. If anything goes wrong, nothing will be written. I have added command-line functionality as well, works like so assuming the script is called puller.py:

Usage: puller.py -f source_server -t destination_server table [table ...]
    -f, -t = driver://user[:password]@host[:port]/database

Example: puller.py -f oracle://someuser:PaSsWd@db1/TSH1 \
    -t mysql://root@db2:3307/reporting table_one table_two
Posted by Tyler Lesmann on April 27, 2009 at 16:28 and commented on 18 times
Tagged as: python sqlalchemy

I have been writing an IMAP archival utility and one of the obstacles was getting PLAIN authentication to work. I know it does not sound complicated. PLAIN authentication allows for multiple identities, which allows a master user to access the mailbox of a regular user using the master user's password. This is vital to my utility as I do not want to keep track of the passwords of each user in the system. This was only a challenge because imaplib has close to no documentation. After sifting through the IMAP specifications and reading the code of imapsync, I have a working example!

1
2
3
4
5
6
7
def login_plain(imap, user, password, authuser=None):
    def plain_callback(response):
        if authuser is None:
            return "%s\x00%s\x00%s" % (user, user, password)
        else:
            return "%s\x00%s\x00%s" % (user, authuser, password)
    return imap.authenticate('PLAIN', plain_callback)

The imaplib.IMAP4 authenticate method has a weird way of working. It take two arguments. The first is a string declaring the method to use, PLAIN in this case. The second is a callable that returns a string or None. What should that string contain though? It is different for everything and you have to read specifications to know what is needed. PLAIN takes a null, \x00, delimited string of three fields. The first field is the authorization identity whose mailbox will be accessed. The second is the authentication identity whose credentials will be used. The third is the password. Pretty easy after that!!! Here is an example of how to use this:

import imaplib
imapconn = imaplib.IMAP4_SSL(host1, 993) # Non-SSL works too
# Login as tlesmann using adminuser's credentials    
login_plain(imapconn, 'tlesmann', 'adminuser', 'adminuserspassword')

This is how imaplib should work and now it will for you.

Posted by Tyler Lesmann on April 15, 2009 at 15:33
Tagged as: imaplib python

Zimbra has a way of developing unusable share permissions and the only way to fix this is to strip all of the permissions are start fresh. In earlier releases, you could use this script, but the parsing is broken in version 5.0.x. I have written a script to handle this. I could have written it in bash, but shell scripts have a hard time with many characters, like backslashes and quotes. This python script requires simplejson and should be ran as the zimbra user, just like zmmailbox. Usage is as such:

Usage: fixgrants.py -u user [-t] folder ...

-u user = account to which the folders belong
-f = strip flags, this will fix inheritance problems.
-t = test_mode, just show commands, nothing will be executed

And here is the script:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
#!/usr/bin/env python

"""
fixgrants is a utility script for maintaining Zimbra's mailbox sharing
permissions.  Zimbra has a tendency to develop unusable permission sets and this
script will strip all permissions from specified folders of a specified account.
It also will strip the folder flags, which will fix permission inheritance
issues.
"""

import getopt
import os
import simplejson
import sys

def get_flagged_list(folder):
    """ Parse getFolder JSON to get a dictionary of folders with flags """
    if 'flags' in folder:
        # Double escape backslashes
        folders = {folder['path'].replace('\\', '\\\\'): folder['flags'],}
    else:
        folders = {}
    if folder['children']:
        for child in folder['children']:
            folders.update(get_flagged_list(child))
    return folders

def get_grants_list(folder):
    """ Parse getFolder JSON to get a dictionary of folders with grants """
    if folder['grants']:
        # Double escape backslashes
        folders = {folder['path'].replace('\\', '\\\\'): folder['grants'],}
    else:
        folders = {}
    if folder['children']:
        for child in folder['children']:
            folders.update(get_grants_list(child))
    return folders

def rm_folder_grants(user, folder, test_mode=False):
    """
    Remove grants recursively

    rm_folder_grants(user, folder, test_mode=False)

    user is a zimbra account.
    folder is a getFolder JSON parsed by simplejson.
    test_mode determine if any commands will actually be executed.
    """
    print "Removing Grants"
    for folder, grants in get_grants_list(folder).iteritems():
        print "Processing", folder
        for grant in grants:
            grant['account'] = user
            grant['folder'] = folder
            cmd = "zmmailbox -z -m %(account)s mfg \"%(folder)s\" account %(name)s none" % grant
            print cmd
            if not test_mode is True:
                os.popen(cmd)

def rm_folder_flags(user, folder, test_mode=False):
    """
    Remove folder flags recursively

    rm_folder_flags(user, folder, test_mode=False)

    user is a zimbra account.
    folder is a getFolder JSON parsed by simplejson.
    test_mode determine if any commands will actually be executed.
    """
    print "Removing Flags"
    for folder in get_flagged_list(folder):
        print "Processing", folder
        args = {'account': user, 'folder': folder}
        cmd = "zmmailbox -z -m %(account)s mff \"%(folder)s\" ''" % args
        print cmd
        if not test_mode is True:
            os.popen(cmd)

def print_usage():
    sys.stderr.write("""
    Usage: %s -u user [-t] folder ...

    -u user = account to which the folders belong
    -f = strip flags, this will fix inheritance problems.
    -t = test_mode, just show commands, nothing will be executed
""" % sys.argv[0])


if __name__ == '__main__':
    optlist, args = getopt.getopt(sys.argv[1:], 'ftu:')
    if not args:
        sys.stderr.write("You must specify folders to alter\n")
        print_usage()
        raise SystemExit, 1
    optd = dict(optlist)
    if '-u' not in optd:
        sys.stderr.write("You must specify a user\n")
        print_usage()
        raise SystemExit, 1
    test_mode = False
    if '-t' in optd:
        test_mode = True
    strip_flags = False
    if '-f' in optd:
        strip_flags = True

    user = optd['-u']
    for folder in args:
        output = os.popen("zmmailbox -z -m \"%s\" gf \"%s\"" % (user, folder))
        folders = simplejson.load(output)
        rm_folder_grants(user, folders, test_mode=test_mode)
        if strip_flags is True:
            rm_folder_flags(user, folders, test_mode=test_mode)
Posted by Tyler Lesmann on April 3, 2009 at 14:21 and commented on 1 time
Tagged as: fix python zimbra

Waylan Limberg tells me python-markdown will be releasing version 2.0 at the end of this week. This release breaks my extension and all others written for 1.7. My extension obsoleted within a less than a week of release. That would not do, so I have updated the code to work on 2.0! It's still under LGPL.

You can get it here or clone the Mercurial repository like so:

hg clone http://code.tylerlesmann.com/mdx_video2

Use and install are exactly the same as with the earlier version. Adding new services has changed and I will get to documenting that soon.

Posted by Tyler Lesmann on April 2, 2009 at 15:20 and commented on 6 times
Tagged as: django markdown open_source python

I'm a big fan of markdown, especially the fact that it can be extended so easily. I wanted to give the users of DeathCat Inc. the ability to embed video from popular services in their posts with only an URL, so I wrote up a new extension. To see it in action, go here. This code is licensed under LGPL.

You can get it here.

It is installable as many other python modules...

python setup.py install

..., but mdx_video.py only has to be in your PYTHON_PATH. With Django, for instance, you can place it in the same directory as settings.py.

Using my extension is like using any other extension for markdown.

>>> import markdown
>>> s = "http://www.youtube.com/watch?v=F8qwxzQar2g"
>>> markdown.markdown(s, ['video'], safe_mode='escape')

Remember to set safe_mode to escape if you are passing untrusted users' input through markdown, extension or not. This extension supports arguments for setting the dimension of the video. By default, the dimensions are what the specific service gives for their example embed. If you don't like this size, then you can change it like so:

>>> markdown.markdown(s, ['video(youtube_width=720, youtube_height=400)'], safe_mode='escape')

If you want to integrate markdown and this extension with Django, then I recommend looking at this post.

This extension supports the following services:

  • Blip.tv
  • Dailymotion
  • Metacafe
  • Veoh
  • Vimeo
  • Yahoo! video
  • Youtube

NOTE: Blip.tv works a little differently than the others because there is no way to construct a working object with the player URL. Instead of the URL to the Blip.tv page, you will use the URL to the flv file, like http://blip.tv/file/get/Pycon-DjangoOnJython531.flv for example. This is located in Files and Links section of Blip.tv.

Adding extra services is easy. Note: This portion is relevant to the extension for python-markdown 1.7. The first part is defining what URL for the video service should look like. You do this in the extendMarkdown method of VideoExtension.

# This regular expression looks for a youtube URL that do not start with parenthesis.
# It does this to avoid eating regular markdown links.
YOUTUBE_RE = r'([^(]|^)http://www\.youtube\.com/watch\?\S*v=(?P<youtubeid>[A-Za-z0-9_-]+)\S*'
# Here we plug our expression into the bit that builds the video embed html.
# We define this shortly.
YOUTUBE_PATTERN = Youtube(YOUTUBE_RE)
# The next two lines allow control of markdown through instances of this class.
YOUTUBE_PATTERN.md = md
YOUTUBE_PATTERN.ext = self
# This registers everything with markdown, so the code will be executed.
md.inlinePatterns.append(YOUTUBE_PATTERN)

Next, we get to build the HTML. We need to add a new subclass of markdown.BasePattern to the module.

class Youtube(markdown.BasePattern):
    def handleMatch(self, m, doc):
        url = 'http://www.youtube.com/v/%s' % m.group('youtubeid')
        width = self.ext.getConfig('youtube_width')
        height = self.ext.getConfig('youtube_height')
        return FlashObject(doc, url, width, height)

For the most part, building the HTML is easy. I have defined a flash_object function that builds an object element that work in most cases. You only need to feed it your minidom instance, doc, an url, and width/height, both as strings. You will notice that I am using self.ext.getConfig to assign my width and height. These are the extension arguments. You will want to use these too. To do so, add a new key to the self.config dictionary of __init__ in VideoExtension.

'youtube_width': ['640', 'Width for Youtube videos'],
'youtube_height': ['385', 'Height for Youtube videos'],

The first part is the default value and the second bit is a description of the argument. If you need to define more HTML, then I suggest taking look at the Yahoo class.

Posted by Tyler Lesmann on April 2, 2009 at 8:16 and commented on 10 times
Tagged as: django markdown open_source python