Writing back from Spotfire to SQLite using Python

Introduction

This article describes how to write back to an SQLite database using Spotfire.

Prerequisites

Create an SQLite database file already created and stored in the location "C:\Temptestdb.db" with a table that has the following columns:

• date
• oil
• water
• wellno

Spotfire and Python Scripting

We will design an interface to DELETE, INITIALIZE and ADD data to database as the following:

• Add a text area with a dropdown list of three values (Add, Delete, Initialize) these three values are tied to document property name what)

Preparing the ADD section

We need to add 4 input fields, feel free to style them the way you want. I use this website to do so. These four fields are document properties as:

• date
• oil
• water
• wellno

Preparing the DELETE section

Add another text area with one input field, which is tied to document property id

Adding a preview section

You can also add a preview to your database to make sure everything is working correctly.

Python Scripting

Create a Python Script Function and paste the following code:

import pandas as pd 
import sqlite3 

con = sqlite3.connect(r'C:\Temptestdb.db') 
cur = con.cursor() 
df=pd.DataFrame() 

reading = "SELECT rowid,* FROM daily_production" 
if flag == 'INITIALIZE': 	
    cur.execute(reading) 	
    df = pd.DataFrame(cur.fetchall(), columns = ['ID', 'Date','Oil', 'Water', 'Well No']) 	
    con.close()     
    
if flag == 'DELETE': 	
    delete_query = "DELETE FROM daily_production WHERE rowid ="+str(id) 	
    cur.execute(delete_query) 	
    con.commit() 	
    df=pd.DataFrame() 	
    reading = "SELECT rowid,* FROM daily_production" 	
    cur.execute(reading) 	
    df = pd.DataFrame(cur.fetchall(), columns = ['ID', 'Date','Oil', 'Water', 'Well No']) 	
    con.close() 	   
    
if flag == 'ADD': 	
    date = "'"+date+"'" 	
    oil= "'"+str(oil)+"'" 	
    water= "'"+str(water)+"'" 	
    wellno= "'"+wellno+"'" 	
    insert_query = "INSERT INTO daily_production VALUES ("+date+","+oil+","+water+","+wellno+")" 	
    cur.execute(insert_query) 	
    con.commit() 	
    df=pd.DataFrame() 	
    reading = "SELECT rowid,* FROM daily_production" 	
    cur.execute(reading) 	
    df = pd.DataFrame(cur.fetchall(), columns = ['ID', 'Date','Oil', 'Water', 'Well No']) 	

2. Set up your Input parameters as below:

3. Set up your output parameters as below:

4. Map your input parameters to your actual inputs as below:

5. Map your output values as below:

6. Now let's test!

See also

 Writing to files and databases from Spotfire