QuickBooks SDK from Python 2.5

So, for a particular project, I found myself needing to get into a client’s QuickBooks file from a python script. I had previously done this sort of thing using a COM from another language using the QuickBooks SDK.

Being somewhat new to python, I had never even tried to use it to interface with Windows COM Objects before, but, I decided that I knew enough about COM that I could fill in the gaps on whatever documentation I found and so I decided to give it a shot. I downloaded and installed the QBSDK, from the Intuit site, and got started putting together a script that “dumps” some of the data into some export files.

Of course you are going to have to download and install the Python Win32 Extensions. (Yes, that means you can’t do this on Linux, but honestly, you can’t really run QB on Linux either, so I don’t feel too bad.)

Here is what I came up with:

import win32com.client

query_rqs = (
  'Host',
  'Company',
  'Account',
  'Entity',
  'Terms',
  'Class',
  'CustomerType',
  'VendorType',
  'JobType',
  'PaymentMethod',
  'ShipMethod',
  'SalesTaxCode',
  'Item',
  )

qbxml_header = """



"""

qbxml_footer = """

;
"""

qb = win32com.client.Dispatch("QBXMLRP.RequestProcessor")
qb.OpenConnection("Data Sucker", "Data Sucker")
ticket = qb.BeginSession("",0)

for rq in query_rqs:
    print "Exporting %s(s)" % rq
    output_file = open("c:\\qb-export-data\\%s.xml" % rq, 'w')
    output_file.write(qb.ProcessRequest(ticket, "%s<%sQueryRq>%s" % (qbxml_header, rq, rq, qbxml_footer)))
    output_file.close()
print "Data Export Completed"

qb.EndSession(ticket)
qb.CloseConnection()
qb = None #drop our QB object

The First part where we define ‘query_rqs’ just makes a convenient list of the object types we are going to query from the QB company file. Requesting data is a uniform enough process that I decided to just loop through these names below.

To understand the part about the ‘qbxml_header’ and ‘qbxml_footer’ you simply have to understand that every exchange with the QB SDK is done in XML. this header/footer combination will be used to wrap every request we make, so I put it in a variable for easy use a little lower.

Then, I actually call use the COM API to create a COM object of type “QBXMLRP.RequestProcessor” the request processor is the primary object in the QBSDK. You use it to connect to QuickBooks, and then to send XML requests, and receive XML responses. The QBSDK does not have COM Objects for every QB object, instead it uses XML to pass properties back and forth about the internal QB objects. In this way, they have an API that doesn’t need to change much in order to support different versions and/or editions of their product.

Anyway, the next line opens up a connection and tells QuickBooks that the application called “Data Sucker” wants permission to connect.

If this is the first time that “Data Sucker” has requested access to the company file, QB needs to be open. It will prompt the user for permission.. .. .. and eventually you will connect (or be denied, depending on your user).

Then, we begin a session. the empty string parameter is the name of the company file we want access to. Empty string implies “whatever company is opened by the user right now.” Which was good enough for my purposes. I suppose I could have supplied a path to a QB file, but it wasn’t necessary in this case. I also elected to use Single User Mode (that is what the 0 in the second parameter means). If I am sucking data out of the company file, I don’t want it to be changing between requests — I want a snapshot that agrees with itself.

Then, I loop though all the requests (rq) that I listed up at the beginning of the file. I create an output file with the XML returned by each request that I made (one file per request).

After the loop, I tidy up a bit and look at the generated export files, and can see that life is good.

I have no idea whether or not this would work on a QB install without the QB SDK on the same PC. The Request Processor object might be installed as part of QuickBooks, and the QB SDK might only contain extra resources. If anyone tries this at home, it would be interesting to know if you can run the script without installing the QBSDK.

Also, I know that this only works on the Pro and Premier Editions of QuickBooks. The Basic Edition is intentionally not capable of interfacing with QBSDK — its an upgrade incentive.

As this was also an exercise in COM, I want to point out the last line of the script where I set qb = None. This is the only way I could think of to be sure I had decremented the object reference count and cause the COM engine to destroy the object. I don’t know if Python has issues with COM object destruction, but I do know that early releases of VB6 had memory leaks if you didnt explicitly destroy objects (in some scenarios) so I figured, better safe than sorry.

-Brent

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

Comments

Cool! I have used the QBSDK and PHP to integrate my company’s website (inventory / customers / sales) with QB and then retrieve and upload an invoice to a demand fulfillment house. It worked, but there are allot of issues that arise from using PHP…I can’t wait to try out python in a similar situation. Thanks for this code example!

The script DID work without QBSDK installed with UK Quickbooks Pro running Python 2.5.
However, there was a problem with the parsing the XML. QB didn’t like the space before the ‘?’ on the first line of qbxml_header. Changing it to that below worked (also replacing all the <, > to angled brackets.
qbxml_header = “”"

Richard, Thanks for the feedback. I went in and tried to “fix” the space and the gr/lt stuff, but it looks like wordpress really doesn’t want me to allow it to look right. I think it has to do with the “pre” tag used for displaying code.

Anyway, I’m glad you got it working. I’m also glad it works for the UK version of QB pro. They only had US and Canada back in 2002 when I first got started integrating things with QB via the QB-SDK.

Again, thanks for the feedback.

Brent – I worked with your script, and it needs a couple of minor changes.

1) the header code should look like this:
qbxml_header = “”"

“”"
There are two changes here: a) I added “utf-8″ (not strictly necessary, but why not)
b) the line about DOCTYPE was only used for very old versions of qbxml; it has been replaced by the qbxml version line

2) the call to client dispatch should read:
qb = win32com.client.Dispatch(“QBXMLRP2.RequestProcessor”)

The change here is the ’2′ at the end of QBXMLRP. This is very important, as the fields that each version of the SDK know how to fetch and replace vary depending on the qbxml version number AND QBXMLRP (without the ’2′) only knows how to deal with the fields up to version 2.1. For most uses, that will not be enough.

Once those two changes are made, the script works fine.

Uncle Bruce

Somehow the middle lines of the header disappeared when I hit ‘submit’.

Here they are again:

(4 lines)

Hmmm – they’ve disappeared again!

I suspect that XML is somehow treated specially by this page.

In the lines below, the ‘less than’ sign and the ‘greater than’ sign have been replaced by open and close curly brace.

{?xml version=”1.0″ encoding=”utf-8″?}
{?qbxml version=”6.0″?}
{QBXML}
{QBXMLMsgsRq onError=”continueOnError”}

To get code that will actually execute, change them back to less than or greater than.

UncleBruce

Leave a comment

(required)

(required)