How I access Microsoft SharePoint in my Python scripts

Following from the positive feedback I got for my previous article "How I built a Python script to read e-mails from an Exchange Server", I decided to write a little more about accessing Microsoft business software from Python.
I think most Python developers think of Microsoft software as a necessary evil. This is why I am sticking to the Microsoft universe and going into how to make Python connect to Microsoft SharePoint.

Tech stack

We will see how to access data of Microsoft SharePoint (versions 2010, 2013 or 2019) from within a Python language script (I'll be using version 3.7). The data transfers will be made via the ODBC layer. To do this in Python I will be using the pyodbc module ver. 4.0.26.
It is important to note that my script uses a commercial product called Connect Bridge, which makes the Python / SharePoint integration possible. With Connect Bridge, I did this integration with little cod and little effort. And I did it in a way that did not mess up the SharePoint side (trust me, this is VERY important). You can try all this yourself by getting a free trial for Connect Bridge.
Connect Bridge is an integration platform developed by Connecting Software that allows you to connect any software through ODBC drivers, JDBC drivers, or Web Services. The architecture of the platform is on this client-server scenario diagram.

As you can see in the diagram, you can use this tool to access not only Microsoft SharePoint data but also data from many other programs. These include Microsoft Dynamics and Microsoft Exchange, and the access can be bidirectional. Access to Microsoft Exchange from Python was precisely what I covered in my previous article.

Let the scripting start

Now let's start the scripting part! My goal is to create a Python script that accesses a SharePoint instance in the simplest possible way. I'll assume the SharePoint instance already exists. Please have your SharePoint login credentials at hand. Here is the sequence of steps you need to follow:

1. Request a free trial and then install Connect Bridge
2. Install Python for Windows ver. 3.7+ along with pyodbc module 4.0.26+
3. Run Connect Bridge Management Studio and in it:
   4.1. Using the credentials I mentioned earlier, add an account for SharePoint (Accounts – Add account).
   4.2. Open the option New Query and then the Connection Browser. Look for the SharePoint Connector and expand it until you see the DefaultConnection. Right-click it and choose Get Connection string. Copy the ODBC connection string. You will pass it to the script further on.
   4.3. Use the New Query option to test out a query that will obtain the data you need in SharePoint. I will present an example query here, but this is something you should change. Once you have clicked the New Query option, open the Connection Browser. Find the SharePoint connector and open it until you see the Tables option. You can see that the schema contains a "table" called Site_Pages. We can create our query as

SELECT UniqueId, ContentType, Created, Modified, ContentVersion FROM Site_Pages LIMIT 20;

This query will select the first twenty entries from the SharePoint's Site Pages list. Please note that it looks like we are using a database directly, but that is not what is happening. Connect Bridge is accessing the API and then presenting the data as if it was a database. Once you have finished writing your query, copy it, as you will also need it to pass it on to the script.

Hands on scripting!

The whole solution is in one script file CBQuery.py. You can go through the complete source code below. If you focus on lines 70-92 you will see the core of the solution. A full description of how this script works is below.

#!/usr/local/bin/python3.7 
# encoding: utf-8 
''' 
CBQuery -- query data from, write data to SharePoint 
  
CBQuery is a script that allows to run SQL queries via Connect Bridge ODBC driver 
  
@author:    Ana Neto 
  
@copyright:  2019 
  
@contact:    ana@connecting-soiftware.com 
@deffield    updated: 07.10.2019 
''' 
  
import sys 
import os 
import pyodbc 
  
from argparse import ArgumentParser 
from argparse import RawDescriptionHelpFormatter 
  
__all__ = [] 
__version__ = 0.2 
__date__ = '2019-10-07' 
__updated__ = '2019-10-07' 
  
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 Ana Neto 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 = 'CBQuery_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())

Here is what the script does:
• Lines 71-80 use the pydev’s argparse script template to get the variables connstr and query from the command line arguments input.
• The variable connstr holds the ODBC connection string which I pass into the pyodbc module to build the ODBC connection (stored in the conn variable)
• I then open a database cursor using the connection stored in conn
• I execute the query (this is the SQL query that I got via the command line parameter named query)
• I cycle using a while loop to read results from the cursor row by row. When cursor.fetchone returns None, I break the loop
• I also break the loop if an exception occurs during the ODBC fetch. In thsi case I also print the problem to output.
• If the fetchone method succeeds and returns a data row, I print the raw data row directly to the output. Please note this could be any type of output. I could have formatted it as XML, JSON, CSV, or any other kind of data-interchange format. I could also simply use the raw data row object to be in further code to perform custom tasks.
To run the script, don't forget the CBQuery.py script accepts two positional command-line arguments: connstr and query. You'll need to copy these from Connect Bridge Management Studio as explained above (point 4).

Can we create a SharePoint contact list and write an entry?

Let’s now take this example a bit further. Ler's say we want to create a contact list in SharePoint and add a contact to that list. We need to follow the same process but change the query to use a “Stored Procedure”. Again this “Stored Procedure” is not a true stored procedure. It will, in fact, be accessing SharePoint via the API. For this the query that we should run is:

EXEC SP_CREATE_TABLE 'MyContacts', 'Created using the Connect Bridge platform', true, 'Contacts';

The query starts a new SharePoint list “MyContacts” with a description that will show up in the quick launch bar of the SharePoint page.

To complete the procedure, we need to execute the following query to update the Connect Bridge ODBC schema reflection of the SharePoint schema. So, our new “table” gets visible to the ODBC client.

EXEC SP_UPDATE_SCHEMA;

I can now insert a contact entry into our contact list.

INSERT INTO MyContacts (FirstName, LastName) VALUES ('Ana', 'Neto');

If you go into SharePoint, you should now be able to see this entry and our new SharePoint list.

I can select the entry I have just created by running the following query

SELECT FirstName,LastName FROM MyContacts

Uploading a shared document

For uploading a shared document, I will use the existing SharePoint list “Documents” and the “Stored Procedure” SP_INSERT_SHAREDDOCUMENT. The parameters it takes are:

• data

• filename

• folder on the server (relative path)

• MIME type

• table name (for the shared documents)

We can insert the document by running the following statement:

EXEC SP_INSERT_SHAREDDOCUMENT 'Documents', 'myfile.txt', '/TestFolder/Documents', 'text/plain', 'YWJjZGVm';

You can check the presence of the document in the “Documents” table by running the statement:

SELECT Id,Name FROM Documents;

Constraints

There is no ODBC Linux client library available (and we are not sure If that is even possible). For this reason, the use of the Connect Bridge tool is limited to Windows machines.

Conclusion

We have seen how accessing SharePoint data in Python can be quickly done using the Connect Bridge integration platform. You can also use the same Connect Bridge tool to access other Microsoft software, such as Dynamics or Exchange, or other kinds of software like Salesforce or SAP. And if you want to use it in a project in a different programming language, that is also a possibility. For example, to get SharePoint data in Java code with Connect Bridge you can check my article "Using Java to get appointments and business documents into SharePoint".