How to Use SQLite in Lua Program

How to Use SQLite in Lua Program

This article describes how to use SQLite in The Lua program, including creating connections to basic queries and other operations. For more information, see

SQLite Import

We can use a simple statement to import the SQLite library, assuming that the correct implementation in Lua has been completed. The libsql folder contains database-related files during installation.

The Code is as follows:

Sqlite3 = require "luasql. sqlite3"

The sqlite3 variable provides this function by referring to the master sqlite3 table.

Establish a connection

We initiated the sqlite environment and then created the environment connection. It is as follows.

The Code is as follows:

Local env = sqlite3.sqlite3 ()

Local conn = env: connect ('mydb. sqlite ')

The above connection will connect to an existing SQLite file or create a new source code file, and establish a connection with the new file.

Execute a function

Available, which helps us to execute creation, insertion, deletion, update, and so on. All Database Operations perform simple functions based on connections. The syntax is as follows:

The Code is as follows:

Conn: execute ([['sqlite3statement '])

In the preceding syntax, we need to ensure that the conn is open and the existing sqlite3 connection, instead of "SQLite3STATEMENT" to use the correct statement.

Example of creating a table

An example of creating a simple table is as follows. It creates tables of the int and varchar types and the ID and name parameters.

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 the above program is run, the table named "sample" will have two columns: id and name, which will be created.

The Code is as follows:

SQLite3 environment (003EC918) SQLite3 connection (00421F08)

0 nil

If an error occurs, the nil error statement is returned. The following simple error statement is as follows.

The Code is as follows:

LuaSQL: unrecognized token: "" 'id' INTEGER, 'name' TEXT )"

Example of an Insert statement

An INSERT Statement of SQLite is as follows.

The Code is as follows:

Conn: execute ([[insert into sample values ('11', 'l1')])

Example of a Select statement

For the select statement, we need to traverse each row and extract the required data. The following simple SELECT statement is as follows.

The Code is as follows:

Cursor, errorString = conn: execute ([[select * from sample])

Row = cursor: fetch ({}, "")

While row do

Print (string. format ("Id: % s, Name: % s", row. id, row. name ))

-- Reusing the table of results

Row = cursor: fetch (row, "")

End

In the above Code, conn is an open sqlite3 connection. With the help of executing the statement to return the cursor, you can obtain the required selected data through the response of the table.

A complete example

The following is a complete example of all the above statements.

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', 'l1')])

Print (status, errorString)

Cursor, errorString = conn: execute ([[select * from sample])

Print (cursor, errorString)

Row = cursor: fetch ({}, "")

While row do

Print (string. format ("Id: % s, Name: % s", row. id, row. name ))

Row = cursor: fetch (row, "")

End

-- Close everything

Cursor: close ()

Conn: close ()

Env: close ()

When the above program is run, the following output is obtained.

The Code is as follows:

SQLite3 environment (005EC918) SQLite3 connection (005E77B0)

0 nil

1 nil

SQLite3 cursor (005E9200) nil

Id: 1, Name: AJ

We can use this libsql library to complete all available queries. Therefore, you can test MySQL, SQLite3, and other Lua Support databases to provide various query statements.