Writing to a database

What it's meant for

The workbook is a simple sheet that allows a user to enter the parameters of a share trade. They can then click on a button, and the details are written to a database.

Naturally, because it is designed to write to a specific database, it won't work immediately once you've downloaded it; you'll have to create a database on your own network and add an appropriate table. The details are below.

How it works

The spreadsheet side of the workbook is simple enough - just a few constant values. The interesting stuff is all in the user code.

The code is structured in three layers: a general database access layer, a "business" layer that knows how to translate from concepts that make sense in the application domain (such as stock symbols and prices) into database commands, and then a UI layer that interfaces between the spreadsheet and the business layer.

The database access layer is a simple bit of generic IronPython in the pre-constants user code. It uses the .NET API to execute a non-query SQL statement against a database, the latter being specified by an ODBC connection string:

# A simple general-purpose function to write to a database
def ExecuteDBNonQuery(connectString, sql):
    from System.Data.Odbc import OdbcConnection, OdbcCommand
    connection = OdbcConnection(connectString)
    command = OdbcCommand(sql, connection)
    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()

Next, the business layer, also in the pre-constants user code, is a little more complex :

# A function to add a row with specific values to a transaction database
def addTransaction(
    symbol, price, currency, broker, numberOfShares, buySell, username):

    try:
        ExecuteDBNonQuery(
            "Driver={SQL Server};Server=NG\SQLEXPRESS;"
            "Database=ResolverDemo;UID=demo;PWD=demo;",
            "INSERT INTO Transactions "
            "(Symbol, Price, Currency, Broker, NumberOfShares, BuySell, Username) "
            "VALUES ('%s', %f, '%s', '%s', '%d', '%s', '%s')" %
            (symbol, price, currency, broker, numberOfShares, buySell, username)
        )
        print "Transaction added"
    except Exception, e:
        print e

This code connects to a database using the OBDC connection string "Driver={SQL Server};Server=NG\SQLEXPRESS;Database=ResolverDemo;UID=demo;PWD=demo;". Changing this is the first thing you will need to do to get this example working on your own system; it needs to be replaced with a connection string that gives access to your database - this page on connection strings gives a great step-by-step guide to putting one together.

Once it has connected to the database, it inserts a new row into the database; to make this work you will need to create in your database a table called Transactions, with columns like this:

The final piece of code, which connects the UI to the business layer, is in the post-formulae user code:

ws = workbook["Sheet1"]
ws.A9 = Button(Text='Add transaction')
ws.A9.Click += lambda : addTransaction(
    ws.B1, ws.B2, ws.B3, ws.B4, ws.B5, ws.B6, ws.B7
)

This simply adds a new button to the grid, and then wires it up to call the addTransaction function defined in the business layer with appropriate values.

The lambda construct may be of interest - it creates a new function that, when called, calls the addTransaction function with the specific parameters.

In action

Once you have a database set up with the appropriate table and have put in the correct connection string, try entering different values into column B and hitting the "Add transaction" button in cell A9. The values will be written to the database.