Dynamically Inserting into a Database with SQLite

In the previous SQLite tutorial, we learned how to create a database, a table, and how to insert data. In this tutorial, we're going to build on that, covering how to insert dynamically into a database's table, using variables.

Code leading up to this point:

import sqlite3

conn = sqlite3.connect('tutorial.db')
c = conn.cursor()

def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)")


def data_entry():
    c.execute("INSERT INTO stuffToPlot VALUES(1452549219,'2016-01-11 13:53:39','Python',6)")

    conn.commit()
    c.close()
    conn.close()

create_table()
data_entry()

First, let's import a couple new things:

import time
import datetime
import random

We get the first two modules so that we can create the timestamps to use, and then we use random to create some random values to use.

Next, we'll make a new function, dynamic_data_entry:

def dynamic_data_entry():

    unix = int(time.time())
    date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
    keyword = 'Python'
    value = random.randrange(0,10)

    c.execute("INSERT INTO stuffToPlot (unix, datestamp, keyword, value) VALUES (?, ?, ?, ?)",
          (unix, date, keyword, value))

    conn.commit()

In this function, we set some variables, then we execute a slightly different SQL query. Note that we are using ? for the variable input. With MySQL, you'd be using %s instead. At the end, we commit. Now let's change the end of the script:

#create_table()
#data_entry()

for i in range(10):
    dynamic_data_entry()
    time.sleep(1)

c.close
conn.close()

We comment out the create_table, which we could leave if we wanted, but there is no need for it anymore. We comment out data_entry as well. Then, we use a one-liner for-loop to run dynamic_data_entry() ten times. This gives us the current time, converted to datestamp, and then a random value for the value column.

Full code to this point:

import sqlite3
import time
import datetime
import random

conn = sqlite3.connect('tutorial.db')
c = conn.cursor()

def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)")


def data_entry():
    c.execute("INSERT INTO stuffToPlot VALUES(1452549219,'2016-01-11 13:53:39','Python',6)")
    
    conn.commit()
    c.close()
    conn.close()

def dynamic_data_entry():

    unix = int(time.time())
    date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
    keyword = 'Python'
    value = random.randrange(0,10)

    c.execute("INSERT INTO stuffToPlot (unix, datestamp, keyword, value) VALUES (?, ?, ?, ?)",
          (unix, date, keyword, value))

    conn.commit()

    
for i in range(10):
    dynamic_data_entry()
    time.sleep(1)

c.close
conn.close()

Re-open the database and table if you like to see how it looks now.

Now that you know how to insert properly, we're going to be covering how to read data from SQLite with the select statement.

The next tutorial: Read from Database with SQLite