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.
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.
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:
3.1. Using the credentials I mentioned before, add an account for SharePoint (go to Accounts – Add 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.
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).
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
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;
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.
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.
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.
Comments (0)