Cacher is the code snippet organizer for pro developers

local mysql = require "resty.mysql"
local M = {}
local function open()
    local db,err = mysql:new()
    assert(db,err)
    local ok,err = db:connect {
        host = "127.0.0.1",
        user = "root",
        password = "123456",
        port = 3306,
        charset = "utf8",
        database = "zscq",
        max_packet_size = 1024*1024*20
    }
    assert(ok,err)
    return db
end

local function bind_param(sql,p)
    local rpl = function(name)  
        return ngx.quote_sql_str(p[string.sub(name,2)] or name)
    end
    return string.gsub(sql,"@[%w_]+",rpl)
end

function M.query_list(sql,param)
    local db = open()
    if param then
        sql = bind_param(sql,param)
    end
    local r,err = db:query(sql) 
    assert(r,err)
    return (#r > 0) and r or {}
end

function M.query_one(sql,param)
    local r = M.query_list(sql,param)
    return (#r > 0) and r[1] or nil
end

function M.query_nothing(sql,param)
     local db = open()
    if param then
        sql = bind_param(sql,param)
    end
    local r,err = db:query(sql) 
    assert(r,err)
    return r.affected_rows
end

return M