Access Trello API using Python scripts with Connect Bridge

There you simply fill your user name and password and allow access to your Trello.

Keen to access Trello API with Python scripts quick and without the need to learn Trello API? Read this tutorial and learn how easy it is with Connect Bridge.

Trello API Python - Introduction

No one likes to learn long and complicated documentation for APIs each time there is a need to access it. This article describes how to access Trello API via Python (have tried in version 3.3) language scripts without any expertise in the API of Trello. For this tutorial you´ll need the integration platform Connect Bridge to allow the data communication via Trello API using mostly simple SQL statements. The data transfers are made via ODBC layer. I have achieved this in Python using the pyodbc module ver. 3.0.7 x64

What is “Connect Bridge”?

Connect Bridge is an integration platform that allows to connect any software through ODBC, JDBC drivers and Web Services with more than 31 connectors (including Trello). Developers can access these connectors in any programming language without the need to have an expertise in target system, basically within minutes. The thing is you can use the tool to access not only Trello API via Python, you can also grab/put data from/to MS Exchange, MS Dynamics CRM, MS SharePoint and many more systems.

Prerequisites

Now let us go through the development process of a simple Python script that accesses Trello API using Connect Bridge via an ODBC driver in several simple steps:

1. Install Connect Bridge (you can get free trial here)
2. Install Python for Windows ver. 3.3+
3. Install pyodbc module 3.0.7+ (I have used the pyodbc-3.0.7.win-amd64-py3.3.exe)
4. In order to have writing & running & debugging my scripts easier, I have used Eclipse Luna with pydev plugin
5. Setup account for Trello in Connect Bridge (see paragraph below)

Connect Bridge setup

After installation of Connect Bridge, you can see three applications:

• CB Service Controller – serves for starting and stopping of Connect Bridge
• CB Administration Tool – here you can setup your accounts for connection to target system
• CB Query Analyzer – this tool serves for visualization of target system database Schema and you can try here your queries easily

So let’s create your account for Trello. Open Administration Tool and in Accounts section create new account, in connectors combobox select CBTrelloConnector and click Authentication Wizard.

Then just save it and we have our account.
Now we can use the CB Query Analyzer tool to visualize schema. We need to add new connection as described in the picture below.

For connection from python script we will need a connection string to this account.

To know our connection string we can just right-click the Trello connection and then click “Edit connection”, in the connection editor choose the “Advanced” tab and copy the value from the text box “Connection String”. (See pictures below)

The core and at the same time the only file in my solution is CBQuery.py. Please find the source code below.

 #!/usr/local/bin/python2.7
# encoding: utf-8
'''
CBQuery2 -- query data from, write data to SharePoint, Dynamics CRM, Exchange
CBQuery2 is a script that allows to run SQL queries via Connect Bridge ODBC driver

@author:     Michal Hainc
@copyright:  2014
@license:    Apache 2.0

@contact:    michalhainc@gmail.com
@deffield    updated: 18.8.2014
'''

import sys
import os
import pyodbc

from argparse import ArgumentParser
from argparse import RawDescriptionHelpFormatter

__all__ = []
__version__ = 0.1
__date__ = '2014-08-15'
__updated__ = '2014-08-15'

DEBUG = 1
TESTRUN = 0
PROFILE = 0

class CLIError(Exception):
    '''Generic exception to raise and log different fatal errors.'''
    def __init__(self, msg):
        super(CLIError).__init__(type(self))
        self.msg = "E: %s" % msg
    def __str__(self):
        return self.msg
    def __unicode__(self):
        return self.msg

def main(argv=None): # IGNORE:C0111
    '''Command line options.'''

    if argv is None:
        argv = sys.argv
    else:
        sys.argv.extend(argv)

    program_name = os.path.basename(sys.argv[0])
    program_version = "v%s" % __version__
    program_build_date = str(__updated__)
    program_version_message = '%%(prog)s %s (%s)' % (program_version, program_build_date)
    program_shortdesc = __import__('__main__').__doc__.split("n")[1]
    program_license = '''%s

  Created by Michal Hainc on %s.

  Licensed under the Apache License 2.0
  http://www.apache.org/licenses/LICENSE-2.0

  Distributed on an "AS IS" basis without warranties
  or conditions of any kind, either express or implied.

USAGE
''' % (program_shortdesc, str(__date__))

    try:
        # Setup argument parser
        parser = ArgumentParser(description=program_license, formatter_class=RawDescriptionHelpFormatter)
        parser.add_argument('connstr')        
        parser.add_argument('query')
        
        # Process arguments
        args = parser.parse_args()

        query = args.query
        connstr = args.connstr

        conn = pyodbc.connect(connstr)
        cursor = conn.cursor()
        cursor.execute(query)
        while 1:
            row = None
            try:
                row = cursor.fetchone()
            except: 
                print(sys.exc_info()[1])
                break
            if not row:
                break                    
            print(row)
                        
            
    except KeyboardInterrupt:
        ### handle keyboard interrupt ###
        return 0
    except:
        print(sys.exc_info()[1])
        #indent = len(program_name) * " "        
        #sys.stderr.write(program_name + ": " + repr(e) + "n")
        #sys.stderr.write(indent + "  for help use --help")
        return 2

if __name__ == "__main__":
         
    if TESTRUN:
        import doctest
        doctest.testmod()
    if PROFILE:
        import cProfile
        import pstats
        profile_filename = 'CBQuery2_profile.txt'
        cProfile.run('main()', profile_filename)
        statsfile = open("profile_stats.txt", "wb")
        p = pstats.Stats(profile_filename, stream=statsfile)
        stats = p.strip_dirs().sort_stats('cumulative')
        stats.print_stats()
        statsfile.close()
        sys.exit(0)
    sys.exit(main())

• connstr (Connection string) holds the ODBC connection string which we pass into the pyodbc module to create an ODBC connection stored in conn variable
• as next step we open a database cursor using the connection stored in conn
• then we execute the SQL query passed via the command line parameter named query
• as last we use a while loop to read results from the cursor row by row, when the cursor.fetchone returns None, we break the loop
• if an exception occurs during the ODBC fetch, we also break the loop and print the problem to output
• if fetchone method suceeds and returns a data row, we will print the raw data row to the output for demonstration purposes (… could be any kind of output…we could format as xml or json, csv… or any kind of data-interchange format) or simply use the raw data row object to be used in further code logic to perform custom tasks

Running the CBQuery.py script

I have tested my script using the Windows PowerShell, but you can run any other shell you like to use…

We know that our CBQuery.py accepts two positional command line arguments the “connstr” and “query”.

We can see that the schema contains a “table” called “list” so we can construct our query as  SELECT * FROM list LIMIT 10 to select first 10 entries from Trello list, and by running the query as we can see on our picture below that the query returned 10 rows  in the output.

Now we can try to create new Trello Card into first ‘Blank’ list. We will use for it query:

INSERT INTO Card (name, idlist) VALUES (‘New card’, ‘565c0d32c75039b07e86b0b3’)

You can see in Trello that the new card has been successfully created

Constraints

So far I have been able to use the Connect Bridge tool only from Windows operating systems, due to the fact that Connecting Software did not provide any ODBC Linux client library (If that is even possible). So be sure to play with your Python scripts on a Windows machine.

Conclusion

We have proved that accessing Trello API in Python can be really easy using Connect Bridge integration tool. And Trello is only one system out of many others which Connect Bridge makes possible to integrate. Imagine you can securely access APIs of major software and services like Dynamics CRM, NAV, AX, Salesforce, Exchange and many more. If you´re interested to stop wasting your time learning hundreds of API documentation pages each time you run into new target system, ask for free trial, pricing and any additional questions.

Cheers,
Ondrej

Download CB_Query_Python