Tutorial on using SQLite in Lua programs _lua

SQLite Import

We can import the SQLite library with a simple statement, assuming that LUA is properly implemented and completed. During the installation process, the folder Libsql contains database-related files.

 code as follows:

Sqlite3 = require "Luasql.sqlite3"

The variable sqlite3 provides access to the feature by referencing the main sqlite3 table.
Establish a connection

We set up a connection that initiates the SQLite environment and then creates the environment. It looks like the following.

 code as follows:

Local env = Sqlite3.sqlite3 ()
Local conn = Env:connect (' Mydb.sqlite ')

The connection above will connect to an existing SQLite file or create a new source file and establish a connection to the newly created file.
execution Function

There are available, which will help us perform creation, INSERT, delete, UPDATE, etc., and all database operations perform simple functions based on the connection. The syntax is shown below

code as follows:

Conn:execute ([[' Sqlite3statement ']])

In the above syntax, we need to make sure that Conn is open and existing sqlite3 joins, instead of "sqlite3statement" using the correct statements.
example of creating a table

A simple example of creating a table is shown below. It creates a table of type int and varchar type, two parameter IDs and name.

code as follows:

Sqlite3 = require "Luasql.sqlite3"

Local env = Sqlite3.sqlite3 ()
Local conn = Env:connect (' Mydb.sqlite ')
Print (Env,conn)

status,errorstring = Conn:execute ([[CREATE TABLE sample (' id ' INTEGER, ' name ' TEXT]]]
Print (status,errorstring)

When running the above program, the table named sample will have two columns, ID and name will be created.

 code as follows:

SQLite3 Environment (003ec918) SQLite3 connection (00421F08)
0 Nil

If there is an error, the nil Error statement is returned. A simple error statement below is shown below.

 code as follows:

Luasql:unrecognized token: "" ' ID ' INTEGER, ' name ' TEXT "

Examples of INSERT statements

An INSERT statement for SQLite is shown below.

 code as follows:

Conn:execute ([INSERT into sample values (' One ', ' Raj ')]]

Example of a SELECT statement

In the case of a SELECT statement, we need to iterate through each row and extract the required data. The following simple SELECT statement is shown below.

 code as follows:

cursor,errorstring = Conn:execute ([[SELECT * from sample]])
Row = Cursor:fetch ({}, "a")
While row do
Print (String.Format ("Id:%s, Name:%s", Row.id, Row.name))
--reusing the table of results
row = Cursor:fetch (row, "a")
End

In the above code, Conn is an open sqlite3 connection. With the help of the execution statement return cursor, you can return the desired selection data through the reaction of the table.
a complete example

A complete example of all of the above statements is given below.

 code as follows:

Sqlite3 = require "Luasql.sqlite3"

Local env = Sqlite3.sqlite3 ()
Local conn = Env:connect (' Mydb.sqlite ')
Print (Env,conn)

status,errorstring = Conn:execute ([[CREATE TABLE sample (' id ' INTEGER, ' name ' TEXT]]]
Print (status,errorstring)

status,errorstring = Conn:execute ([INSERT into sample values (' 1 ', ' Raj ')]]
Print (status,errorstring)

cursor,errorstring = Conn:execute ([[SELECT * from sample]])
Print (cursor,errorstring)

Row = Cursor:fetch ({}, "a")
While row do
Print (String.Format ("Id:%s, Name:%s", Row.id, Row.name))
row = Cursor:fetch (row, "a")
End
--Close everything
Cursor:close ()
Conn:close ()
Env:close ()

When you run the above program, you will get the following output.

 code as follows:

SQLite3 Environment (005ec918) SQLite3 connection (005E77B0)
0 Nil
1 Nil
SQLite3 cursor (005E9200) nil
Id:1, Name:raj

We can use this Libsql library to complete all the available queries. So you can experiment with mysql,sqlite3 and other LUA support DB to provide a variety of query statements.