Python & SharePoint - Getting Data, Creating Lists and Uploading Files

Ana NetoTechnical Advisor and Content Writer
CERTIFIED EXPERT
I have been a software engineer since 1997, with a more recent love for writing and public speaking.
Published:
Edited by: Andrew Leniart
For most Python developers, Microsoft business software is either a great unknown or a necessary evil. Microsoft SharePoint is no exception. Yet, knowing how to integrate Microsoft SharePoint from Python can make all the difference. Plus, it is not that complicated!

For many companies, the core of their business is in SharePoint. Being able to quickly develop custom code to get data, manipulate lists, or upload files will make you more valuable as a developer. 

Tech stack

We will see how to access Microsoft SharePoint from within a Python language script. This specific script was tested for SharePoint 2010, 2013 or 2019 / Office 365 with Python 3.7. We will transfer data via the ODBC layer. To do this in Python, I will be using the pyodbc module ver. 4.0.28.

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 less code and less 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. 

Let the scripting start!

The aim here is to create a simple Python script that obtains data from a SharePoint instance. I’ll assume that the SharePoint instance already exists and that you have login credentials for it (keep those at hand!).

Here is the step-by-step sequence you need to follow:

  1. Install Python and pyodbc - I used Python for Windows ver. 3.7 along with the pyodbc module 4.0.28
  2. Request a free trial and then install and activate Connect Bridge
  3. Run Connect Bridge Management Studio and in it:

3.1. Using the credentials I mentioned before, add an account for SharePoint (go to AccountsAdd account). If all is good, you will be able to test the connection successfully.


3.2. Open the option New Query and then find the Connection Browser. Scroll down for the SharePoint Connector and expand it until you see the DefaultConnection. Right-click it and choose the option Get Connection string. You will pass this ODBC connection string to the script further on, so copy it now.

3.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 a single 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. Right after the script, you will find a full description of how this script works.

#!/usr/local/bin/python3.7 
# encoding: utf-8 
''' 
CBQuery -- query data from SharePoint, write data to SharePoint 
  
CBQuery is a script that runs SQL queries using Connect Bridge's ODBC driver 
  
@author: Ana Neto 
  
@copyright: 2020
  
@contact: ana@connecting-soiftware.com 
@deffield updated: 20.08.2020 
''' 
  
 
import os 
import sys 
import pyodbc 
  
from argparse import ArgumentParser 
from argparse import RawDescriptionHelpFormatter 
  
__all__ = [] 
__version__ = 0.3 
__date__ = '2020-08-20' 
__updated__ = '2020-08-20' 
  
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 the argument parser 
     parser = ArgumentParser(description=program_license, formatter_class=RawDescriptionHelpFormatter) 
     parser.add_argument('connstr')     
     parser.add_argument('query') 
     
     # Process the 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())

 

Let's have a detailed look at what this script does:

• Lines 71 to 80 use the pydev’s argparse script template to get the variables from the command line arguments input (connstr and query).

• The variable connstr holds the string with the ODBC connection which I pass into the pyodbc module to build the ODBC connection (which I then store in the conn variable)

• I then open a database cursor using that connection I stored in conn

• I execute the SQL query (this is the query that comes from Connect Bridge as explained in point 4 above and that is sent in via the command line parameter named query)

• I use a while loop to iteratively go through the results in the cursor. When I get None in cursor.fetchone, I break the loop

• If an exception occurs during the ODBC fetch, I also break the loop. In this case, I also let the user know by printing the problem to output.

• If the fetchone method is successful and returns a data row, I print it directly to the output. Naturally, this can be changed to have any kind of format. I could have formatted it as CSV, JSON, XML, or any other type of data exchange format. I could also use the raw data row object to perform custom tasks in further code.

When you run the script, keep in mind that the CBQuery.py script accepts two positional command-line arguments: connstr and query. These are crucial and you should copy them from Connect Bridge Management Studio as explained above (point 4).

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

Let’s now take the next step and explore this integration a bit further. Let’s say we want to create a SharePoint contact list and add a contact to that list. We need to follow the same steps but use a “Stored Procedure” of a "Query". As before, this “Stored Procedure” is not truly a stored procedure. It is an abstraction and, in fact, it will access SharePoint via the API (which means we are safe!). 

What we need to run is:

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

The query starts a new SharePoint list “UploadedContacts” with a descriptive text that will show in the quick launch bar of the SharePoint page.

We need to execute a further query to update the Connect Bridge ODBC schema reflection of the SharePoint schema. This will make our new “table” UploadedContacts visible to the ODBC client.

EXEC SP_UPDATE_SCHEMA;

I can now insert a contact entry for Ana Neto into our contact list.

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

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

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

SELECT FirstName,LastName FROM UploadedContacts

Uploading a shared document

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

• data

• filename

• folder on the server (relative path)

• MIME type

• table name (for the shared documents)

Running the following statement inserts the document 

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

You can check the new document is there by using the SharePoint interface or you can query the “Documents” table by running the following statement:

SELECT Id, Name FROM Documents;

Constraints

If you are using Linux, there is no ODBC Linux client library available (and I am 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. The main reason I like this platform is that it has forward and backward compatibility, meaning that any script I write for SharePoint 2019, also works in any other version of SharePoint from 2010 onwards including future versions.

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.

Now that you know all about it, go and try it out yourself and add integrating SharePoint to your toolbox! If you have any questions on how to get this to work for you, let me know in the comments below.


Note: The code in this article was originally developed by the author together with Michal Hainc (Connecting Software) and included in an article about Connect Bridge.


0
3,933 Views
Ana NetoTechnical Advisor and Content Writer
CERTIFIED EXPERT
I have been a software engineer since 1997, with a more recent love for writing and public speaking.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.