Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to 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="immukul@protonmail.com", },
{ name="Manoel Joaquim", email="manoel@cafundo.com", },
{ name="Rahul", email="rahul@protonmail.com", },
}
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: immukul@protonmail.com Name: Manoel Joaquim, E-mail: manoel@cafundo.com Name: Rahul, E-mail: rahul@protonmail.com
1K+ Views
