Examples
Here is an example of the basic use of the library. After that, another example shows how to create an iterator over the result of a SELECT query.
Basic use
-- load driver
local driver = require "luasql.postgres"
-- create environment object
env = assert (driver.postgres())
-- connect to data source
con = assert (env:connect("luasql-test"))
-- 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="Jose das Couves", email="jose@couves.com", },
{ name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
{ name="Maria das Dores", email="maria@dores.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()
And the output of this script should be:
Name: Jose das Couves, E-mail: jose@couves.com Name: Manoel Joaquim, E-mail: manoel.joaquim@cafundo.com Name: Maria das Dores, E-mail: maria@dores.com
Iterator
It may be useful to offer an iterator for the resulting rows:
function rows (connection, sql_statement)
local cursor = assert (connection:execute (sql_statement))
return function ()
return cursor:fetch()
end
end
Here is how the iterator is used:
env = assert (require"luasql.mysql".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
Obviously, the code above only works if there is a table called contacts with the columns id, name and address in this order. At the end of the loop the cursor will be automatically closed by the driver.