An example on how to install luasql-postgres using luarockt on macOS to query a public database

Lua_postgres_example.adoc

Lua & postgres example (~/devop/lua)

Tags

#lua #postgres #macos

Example on how to use Lua with luasql-postgres to query a public postgres database. This is more or less a followup to https://gist.github.com/zirkuswurstikus/3689d846cdd0d313a4e5448297127093 The steps are for macos but should give you an idea on how to do it on other platforms.

Note	see Tl;dr at the end.

Tip	http://www.troubleshooters.com/codecorn/lua/luapostgres.htm#_Reading_and_Writing_Postgres_from_Lua

Assume we only like to install luasql-postgres.

LUA=~/devop/lua/
cd $LUA

Getting libpq header file libpq-fe.h

brew install libpq
luarocks install luasql-postgres PGSQL_INCDIR="/opt/homebrew/opt/libpq/include"

Example script

Accessing https://rnacentral.org/help/public-database a free public postgres database.

Query RNAcentral using VEGA identifiers. No, I have no idea what this is.

create example dir and cd to it

mkdir -p $LUA/example/ && cd $_

Create the file postgres_rna.lua

-- Import the LuaSQL PostgreSQL module
local luasql = require "luasql.postgres"

-- Check if the module loaded correctly
if not luasql then
    error("Failed to load luasql.postgres module")
end

-- Create an environment object
local env = assert(luasql.postgres())

-- Establish a connection to the PostgreSQL database
local con = assert(env:connect('pfmegrnargs', 'reader', 'NWDMCE5xdipIjRrp', 'hh-pgsql-public.ebi.ac.uk', 5432))

-- Define the SQL query
local query = [[
    SELECT
        upi,     -- RNAcentral URS identifier
        taxid,   -- NCBI taxid
        ac       -- external accession
    FROM xref
    WHERE ac IN ('OTTHUMT00000106564.1', 'OTTHUMT00000416802.1')
]]

-- Execute the query and get a cursor object
local cur = assert(con:execute(query))

-- Fetch and print the results
local row = cur:fetch({}, "a")
while row do
    print(string.format("upi: %s, taxid: %s, ac: %s", row.upi, row.taxid, row.ac))
    row = cur:fetch(row, "a")
end

-- Close the cursor, connection, and environment objects
cur:close()
con:close()
env:close()

test it

 lua postgres_rna.lua
 # OUTPUT:
 #    upi: URS00000B15DA, taxid: 9606, ac: OTTHUMT00000106564.1
 #    upi: URS00000A54A6, taxid: 9606, ac: OTTHUMT00000416802.1

It work’s

Tl;dr

LUA=~/devop/lua/
cd $LUA

brew install libpq

luarocks install luasql-postgres PGSQL_INCDIR="/opt/homebrew/opt/libpq/include"

mkdir -p $LUA/example/ && cd $_

cat <<EOF > postgres_rna.lua
-- Import the LuaSQL PostgreSQL module
local luasql = require "luasql.postgres"

-- Check if the module loaded correctly
if not luasql then
    error("Failed to load luasql.postgres module")
end

-- Create an environment object
local env = assert(luasql.postgres())

-- Establish a connection to the PostgreSQL database
local con = assert(env:connect('pfmegrnargs', 'reader', 'NWDMCE5xdipIjRrp', 'hh-pgsql-public.ebi.ac.uk', 5432))

-- Define the SQL query
local query = [[
    SELECT
        upi,     -- RNAcentral URS identifier
        taxid,   -- NCBI taxid
        ac       -- external accession
    FROM xref
    WHERE ac IN ('OTTHUMT00000106564.1', 'OTTHUMT00000416802.1')
]]

-- Execute the query and get a cursor object
local cur = assert(con:execute(query))

-- Fetch and print the results
local row = cur:fetch({}, "a")
while row do
    print(string.format("upi: %s, taxid: %s, ac: %s", row.upi, row.taxid, row.ac))
    row = cur:fetch(row, "a")
end

-- Close the cursor, connection, and environment objects
cur:close()
con:close()
env:close()
EOF

# test it
lua postgres_rna.lua

# OUTPUT:
#    upi: URS00000B15DA, taxid: 9606, ac: OTTHUMT00000106564.1
#    upi: URS00000A54A6, taxid: 9606, ac: OTTHUMT00000416802.1

# It work's