Sqlite

This is a demonstration of using Sqlite from within Calico Python.

First, you must add a reference to the Mono.Data.Sqlite library:

In [38]:
import clr
clr.AddReference("Mono.Data.Sqlite")
import Mono.Data.Sqlite

Next, you must create a connection to the file:

In [23]:
connection = Mono.Data.Sqlite.SqliteConnection("Data Source=file:database.db")

And then open the connection:

In [24]:
connection.Open()

Once you have the connection opened, then you can execute commands. First, we need to create a Command:

In [25]:
cmd = connection.CreateCommand()

After you have a Command object, then you can:

  1. set the CommandText
  2. ExecuteReader
  3. Read the results

We use the set, execute, read steps to create a table:

In [26]:
cmd.CommandText = """CREATE TABLE employee (
                     firstname varchar(32),
                     lastname varchar(32));"""
In [27]:
reader = cmd.ExecuteReader()
In [31]:
while reader.Read():
    FirstName = reader.GetString(0)
    LastName = reader.GetString (1)
    print(FirstName, LastName)
reader.Close()

We use the set, execute, read steps to add data to the table:

In [32]:
cmd.CommandText = """INSERT into employee (firstname, lastname)
                     VALUES ("Firstname", "Lastname");"""
In [33]:
reader = cmd.ExecuteReader()
In [34]:
while reader.Read():
    FirstName = reader.GetString(0)
    LastName = reader.GetString (1)
    print(FirstName, LastName)
reader.Close()

We use the set, execute, read steps to read data from a table:

In [35]:
cmd.CommandText = """select * from employee;"""
In [36]:
reader = cmd.ExecuteReader()
In [37]:
while reader.Read():
    FirstName = reader.GetString(0)
    LastName = reader.GetString (1)
    print(FirstName, LastName)
reader.Close()
Firstname Lastname
In [ ]: