How to write to MsAccess database in Spotfire® Using IronPython Scripting

Introduction

With python scripting one can write back to an Ms access database easily. This can be useful when a user say wants to write back user comments to an access database.

Pre-requisite

"Microsoft Access Database Engine 2010 Redistributable" needs to be installed in order to use "Microsoft.ACE.OLEDB.12" provider.

Code sample

# Copyright © 2017. TIBCO Software Inc.  Licensed under TIBCO BSD-style license.

import clr
import System
clr.AddReference("System.Data")

from System.Data import DataSet
from System.Data.OleDb import OleDbConnection, OleDbDataAdapter, OleDbCommand
from System.Data import CommandType

conStr = r'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:tempmyaccessdb.accdb;'
con = OleDbConnection(conStr)
con.Open()

query = "insert into tblDoctors values ('Dr Spotfire',1234)"
adapter = OleDbDataAdapter(query, con)
ds = DataSet()
adapter.Fill(ds)


query = "SELECT * FROM tblDoctors"
adapter = OleDbDataAdapter(query, con)
ds = DataSet()
adapter.Fill(ds)

print 'ncolumn names:', ', '.join(str(x) for x in ds.Tables[0].Columns)
print 'n', ds.Tables[0].Rows.Count, 'rows of data:' 
for row in ds.Tables[0].Rows:
 print ', '.join(str(x) for x in row)

con.Close()

See also

• Writing to files and databases from Spotfire

References

• Microsoft OleDbDataAdapter API

License:  TIBCO BSD-Style License