Work with MySQL in Lua Programming

Lua provides different libraries that once can be used to work with MySQL. The most popular framework that enables us to work with MySQL in Lua is LuaSQL.

LuaSQL is a simple interface from Lua to a DBMS. It enables a Lua program to −

• Connect to ODBC, ADO, Oracle, MySQL, SQLite, Firebird and PostgreSQL databases;
• Execute arbitrary SQL statements;
• Retrieve results in a row-by-row cursor fashion.

You can download MySQL with the help of this command −

luarocks install luasql-mysql

MySQL DB Setup

In order to use the following examples to work as expected, we need the initial db setup. The assumptions are listed below.

• You have installed and set up MySQL with the default user as root and password as '123456'.
• You have created a database test.

Importing MySQL

We can use a simple require statement to import the sqlite library assuming that your Lua implementation was done correctly.

mysql = require "luasql.mysql"

The variable mysql will provide access to the functions by referring to the main mysql table.

Setting up Connection

We can set up the connection by initiating a MySQL environment and then creating a connection for the environment. It is shown below.

create environment object
env = assert (mysql.mysql())
connect to data source
con = assert (env:connect("test","root","123456"))

The above connection will connect to an existing MySQL file and establish the connection with the newly created file.

Below code is the complete code that establishes the connection and then traverses over the table present inside the MySQL database.

Example

Consider the code shown below −

-- load driver
local driver = require "luasql.mysql"
create environment object
env = assert (driver.mysql())
connect to data source
con = assert (env:connect("test","root","123456"))
-- reset our table
res = con:execute"DROP TABLE people"
res = assert (con:execute[[
   CREATE TABLE people(
      name varchar(50),
      email varchar(50)
   )
]])
add a few elements list = {
   { name="Mukul Latiyan", email="[email protected]", },
   { name="Manoel Joaquim", email="[email protected]", },
   { name="Rahul", email="[email protected]", },
}
for i, p in pairs (list) do
   res = assert (con:execute(string.format([[
      INSERT INTO people
      VALUES ('%s', '%s')]], p.name, p.email)
   ))
end
-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
   print(string.format("Name: %s, E-mail: %s", row.name, row.email))
   -- reusing the table of results
   row = cur:fetch (row, "a")
end
-- close everything
cur:close() -- already closed because all the result set was consumed
con:close()
env:close()

Output

Name: Mukul Latiyan, E-mail: [email protected]
Name: Manoel Joaquim, E-mail: [email protected]
Name: Rahul, E-mail: [email protected]