How to connect & query MySQL from within Lua?

Question

How can I connect to a MySQL database from using Lua programming language?

If a good/popular library exists, what is it?

Solution 1

Minimal woking example for LuaSQL - simple interface from Lua to a DBMS.

package.cpath = package.cpath .. ";/usr/lib/i386-linux-gnu/lua/5.1/?.so"

luasql = require "luasql.mysql"

env = assert (luasql.mysql())
con = assert (env:connect("dbname","user","password"))
cur = assert (con:execute("SHOW TABLES"))

row = cur:fetch ({}, "a")
while row do
  print(string.format("Name: %s", row.Tables_in_dbname))
  row = cur:fetch (row, "a")
end

Line 1 used if module luasql.mysql not found. Also environment variable LUA_CPATH may be used.

Solution 2

From LuaSQL -- Database connectivity for the Lua programming language:

require "luasql.mysql"
env = assert (luasql.mysql())
con = assert (env:connect"my_db")
for id, name, address in rows (con, "select * from contacts") do
  print (string.format ("%s: %s", name, address))
end

Solution 3

In case your mysql database is remote, you can add host as another optional parameter to connect. Port can follow host as well:

con = assert (env:connect("dbname","user","password","host",port))

Comments

[TeddyH]

Found it: keplerproject.org/luasql

[James Black]

Have you seen this: gammon.com.au/forum/?id=5983

[71GA]

What about if your database has a password?

[71GA]

What about password protected databases?

[Stanislav Ivanov]

@71GA At line 6. The third argument of env:connect.

[Ken]

@71GA that is where it says 'password' - replace that with your password.