Dave's page

A quick-start guide to Sqlite3 with Lua

1   Introduction

The documentation is good. All this article aims to do is to get you started quickly with some simple tasks.

2   A few simple operations

2.1   Create a database file and table

You can create a Sqlite3 table with something like the following. Note that if the database file does not exist, it will be created.

local sqllib = require('lsqlite3')
local Dbfilename = 'dbtest03.sqlite'

    function M.create_db()
      db = sqllib.open(Dbfilename)
      db:exec[=[
      CREATE TABLE miscdata(num1,num2,name);
      INSERT INTO miscdata VALUES(1,11,"dave");
      INSERT INTO miscdata VALUES(2,22,"mona");
      INSERT INTO miscdata VALUES(3,33,"taffy");
      INSERT INTO miscdata VALUES(4,44,"sylvia");
      ]=]
      db:close()
    end

Notes:

  • Opening a database file automatically creates that file if it does not already exist.
  • As you can see in this code snippet, other than opening and closing the Sqlite3 database, it's all SQL code.
  • In this case, that SQL code creates a table and inserts several rows into that table.

2.2   A simple demo

This demo (1) opens an Sqlite3 database, (2) prints out its contents, and (3) closes the connection to the database.

function M.showrow(udata, cols, values, names)
  --for i=1,cols do print(names[i],values[i]) end
  print(string.format('row #: "%s"  value: "%s"', values[1], values[2]))
  return 0
end

function M.test()
  db = sqllib.open('dbtest01.sqlite')
  db:exec('select * from numbers', M.showrow)
  db:close()
end

Notes:

  • The db:exec enables us to submit an SQL statement and to provide a callback function that, if the SQL statement is a query and if it returns results, will be called once for each row that is returned.

2.3   db:rows vs. db:nrows vs. db:urows

Here is the documentation on these three methods:

Here is an example that demonstrates some of the difference between db:rows, db:nrows, and db:urows:

function M.test_rows()
  db = M.open()
  print('db:rows:')
  for a in db:rows('SELECT * FROM numbers') do for k, v in pairs(a) do print('', k, v) end end
  print('db:nrows:')
  for a in db:nrows('SELECT * FROM numbers') do for k, v in pairs(a) do print('', k, v) end end
  print('db:urows:')
  for num1,num2 in db:urows('SELECT * FROM numbers') do print('', num1,num2) end
  db:close()
end

Notes:

  • db:nrows returns, for each selected row, a table with named fields; the names correspond to column names in the database.
  • db:rows returns, for each selected row, a table with numerical indices; the numerical indices correspond to the selected columns, from left to right.
  • db:urows returns, for each selected row, an expression list whose values correspond to the selected colmns, from left to right.

More notes:

  • db:rows and db:nrows produces a table for each row returned by the query. In contrast, db:urows delivers a sequence. Of course, you can easily convert a sequence into a table by enclosing it in curly brackets, as illustrated in the last line above.
  • db:urows delivers a sequence containing the number of items selected for each row of the query. In the above example, because we are dealing with a table containing two columns, the number of items delivered for each row is two.

2.4   Database table to Lua table and back

Since db:rows and db:nrows both deliver selected row results in Lua tables, converting from a Sqlite3 table to a Lua table is trivial. Here is an example:

>  t = {}
>  for a in db:nrows('select * from miscdata') do table.insert(t, a) end

Then you can modify the Lua table and its contents. And, it's pretty simple to iterate over the items in the Lua table and use the db:exec method to add or replace rows in the Sqlite3 table (database). Here is a simple example:

local query
for k, v in pairs(dbtbl) do
  query = string.format(
    'INSERT INTO miscdata VALUES(%s,%s,"%s")',
    dbtbl[k][1],
    dbtbl[k][2],
    dbtbl[k][3])
  db:exec(query)
end

Or, you could create all the queries, then submit (execute) them as a batch. For example, here is a function that (1) creates Lua tables from a database; (2) modifies the values in that Lua table; then (3) uses those tables and SQL statements to insert that modified data back into the database:

function M.tables_db()
  db = sqllib.open(Dbfilename)
  local dbtbl = {}
  for tbl in db:rows('SELECT * FROM miscdata') do table.insert(dbtbl, tbl) end
  for k, v in pairs(dbtbl) do dbtbl[k][3] = dbtbl[k][3] .. 'B' end
  local query
  local query_tbl = {}
  for k, v in pairs(dbtbl) do
    query = string.format(
    'INSERT INTO miscdata VALUES(%s,%s,"%s");\n',
    dbtbl[k][1],
    dbtbl[k][2],
    dbtbl[k][3])
    table.insert(query_tbl, query)
  end
  local query_concat = table.concat(query_tbl)
  local code = db:exec(query_concat)
  db:close()
  return code, dbtbl
end

Published

Category

lua

Tags

Contact