retrieving sql server procedure output variables in python

Say, I have a stored procedure like this:

create procedure delete_po @po_number varchar(100) = null output, @delete_message varchar(200) = ‘deleting..’ output as….

To retrieve the the two output parameters, after the connecting with adodbapi, get the cursor and run this:

po_number = ‘ PO1487’

delete_message = ‘ ‘ * 200

result = cursor.callproc(‘delete_po’, (po_number, delete_message))

The output variables will be in result.

If I added print result to the previous code, I get this output:

[u'PO1487', u'cannot delete PO1487 because there are receiving transactions']

Note that the initial values of the variables (po_number, delete_message) must be initialized with sufficient length to contain the output values. Thus my delete_message = ‘ ‘ * 200.

string.Template with default substitution value

I wanted a string.Template class that will substitute placeholders with a default value if the mapping is not provided.

For example, say I create a template

A template with $value1, $value2

When I substitute this template with values, say I provide only $value1, I want other $variables to be substituted with a default value. If my default value is HAHA. and I provide this map {‘value1′: ‘first_value’}, the result is

A template with first_value, HAHA

Creating the class was very simple (in retrospect).

I just subclassed string.Template and provided my own version of safe_substitute.

 

Here’s the class, complete with doctest code.

 

import string
 
class Template(string.Template):
    """
        Template with modified functions
 
    -- doctests ----
    >>> t = 'this is a $test with $result'
    >>> st = Template(t, 'haha')
    >>> d = {'test': 'a big test'}
    >>> result = st.safe_substitute(d)
    >>> expected_result = 'this is a a big test with haha'
    >>> test_result = True if expected_result == result else 'expected %s >>> but got <<< %s' % (expected_result,  result)
    >>> test_result
    True
    >>> st = Template(t)
    >>> d = {'test': 'a big test'}
    >>> result = st.safe_substitute(d)
    >>> expected_result = 'this is a a big test with $result'
    >>> test_result = True if expected_result == result else 'expected %s >>> but got <<< %s' % (expected_result,  result)
    >>> test_result
    True
 
 
    """
 
 
    def __init__(self, template, default_substitution_value = None):
        string.Template(template)
        self.default_substitution_value = default_substitution_value
        self.template = template
 
 
    def safe_substitute(self, *args, **kws):
        '''
            Returns template with placeholders substituted
              if no substitution value is specified, the
                  default_substitution_value
                  is used
        '''
        if len(args) > 1:
            raise TypeError('Too many positional arguments')
        if not args:
            mapping = kws
        elif kws:
            mapping = _multimap(kws, args[0])
        else:
            mapping = args[0]
        # Helper function for .sub()
        def convert(mo):
            named = mo.group('named')
            if named is not None:
                try:
                    # We use this idiom instead of str() because the latter
                    # will fail if val is a Unicode containing non-ASCII
                    return '%s' % (mapping[named],)
                except KeyError:
                    if self.default_substitution_value:
                        return self.default_substitution_value
                    return self.delimiter + named
 
            braced = mo.group('braced')
            if braced is not None:
                try:
                    return '%s' % (mapping[braced],)
                except KeyError:
                    return self.delimiter + '{' + braced + '}'
            if mo.group('escaped') is not None:
                return self.delimiter
            if mo.group('invalid') is not None:
                return self.delimiter
            raise ValueError('Unrecognized named group in pattern',
                             self.pattern)
        return self.pattern.sub(convert, self.template)

 

I built the above because I was building an interface to populate P2 Energy’s Field Operations equipment readings. The application sends data via xml files and there are many attributes in the file that I did not care to populate. Rather than hand code a default value for each of them, I built the above python class.

doctest omits decorator functions / decorated def – a simple workaround

Apparently there is a known issue with doctests, 
in which tests in functions using externally 
defined decorators are ignored by doctest.
Here's a simple workaround.

 

@aspect.processedby(aspect.tracing_processor)
def aa():
    ''' Returns None
 
    -- doctests ----
 
    >>> aa()
    True
 
    '''
    return False
 
def all_tests():
    ''' Returns None
 
    -- doctests ----
 
    >>> aa()
    True
    >>> aa()
    False
 
    '''
 
    return False

 

In the above snippet, my aa function is decorated and as expected, doctest ignores the tests within the docstring.

However, the all_tests function is not decorated and doctest picks it up and runs the tests.

**********************************************************************
File "C:\1\python\test_doctest.py", line 31, in __main__.all_tests
Failed example:
    aa()
Expected:
    True
Got:
    False
**********************************************************************
1 items had failures:
   1 of   2 in __main__.all_tests
***Test Failed*** 1 failures.
completed doctest

 

This is a simple workaround (and solution) and the obvious disadvantage is that the tests are further away from the tested function.

That is not altogether too bad, considering that if you switch to other unit testing methods like nose, py.test, you would have to place your tests separately anyway.

Upgrading from Python 2.5 to Python 2.5.4

I was hesitant to upgrade from Python 2.5 to 2.5.4 on because upgrades usually break something.

I tested it first on a desktop. The default upgrade installed to a C:\Python25 directory and a lot of things broke. Python scripts that worked before stopped working.

easy_install would not work because it could not find Python.exe.

This was due to my previous Python installation was on c:\1Programs\Python.

Fortunately I created a system restore point before the upgrade and I restored to the point.

At this time, Python was still not working properly – the restore did not repair everything. So much for Window’s System Restore.

Then I installed Python 2.5.4 again, this time on c:\1Programs\Python.

Now, everything works!

Lessons learnt:

1. Test the upgrade on a test machine first

2. Install upgrades on the same path of the original installation

3. Don’t count on Window’s System Restore Points

Vim compiled with Python 2.5

You might want Vim compiled with +python if you’re trying to get Vim’s wonderful omnicomplete to work.

The gvim.exe at vim.org does not have +python for 2.5.

You can get a python 2.5 build at Yong Wei’s site.

image

Now omnicomplete works for me !!

Scheduling Python scripts as Scheduled Tasks

When scheduling python scripts as Scheduled Tasks in Windows, take note that although you specify the script with arguments eg :

kl_ofm.py -g -v

When the scheduled task runs, it runs as kl_ofm.py without the arguments.

The above is something to take note of when scheduling Python scripts.

 

One option around this is to put kl_ofm.py -g -v into a batch file and schedule the batch file as the job to run.

Python programming with Excel, how to overcome COM_error from the makepy generated python file

import win32com.client
 
xl = win32com.client.Dispatch('Excel.Application')
wb = xl.Workbooks('Book1')
ws = wb.Worksheets('Sheet1')
cell = ws.Range('A1')
cell.SetValue(arg1 = 'test entry in Excel')

 

Above is a very simple python script to write something in Excel.

To run the above, you need Mark Hammond’s pywin32 to generate the PythonCOM package. Read O’Reilly’s Python Programming on Win32 for more information on what PythonCOM packages are.

Once you have installed pythonwin, start it and generate the python code for Excel like this:

image

1. Start PythonWin

image 

2. Select MakePy

image

3. Select the Excel library

image

4. The python file is generated.

image

However, the generated file will not yet work. If you now type in the above code snippet, you will get an COM error:

com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -214735
2565), None)

The cause of the com_error is various, on one machine, it was due to some bug in the file.

Use your editor and open the generated python file, find all the ret = Dispatch lines …

ret = Dispatch(ret, 'Item', '{00020857-0000-0000-C000-000000000046}', UnicodeToString=0)

and replace with

ret = Dispatch(ret, 'Item', '{00020857-0000-0000-C000-000000000046}')

On another, it was because xl was linked to an Excel instance which was visible. I did this to make it show itself

xl.Visible = 1

 

Technorati tags: ,