Tutorial on using SQLite in LUA programs

This article mainly introduces the use of SQLite in LUA programs, including the establishment of a connection to the basic query and other operations, the need for friends can refer to the

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.

The code is 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.

The code is 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

The code is 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.

The code is 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.

The code is 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.

The code is as follows:

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

Examples of INSERT statements

An INSERT statement for SQLite is shown below.

The code is 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.

The code is 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.

The code is 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.

The code is 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.