mysql 命令 读取文件内容_lua 实现从ftp获取文件，并把文件部分内容记mysql

其中借助了shell 脚本，实现从ftp中获取以明天命名的文件类似(20161019.txt)，并下载到指定的路径上，

请注意一下，shell脚本是比较严格的检测，赋值"="之间不能有空格

filename=`date -d next-day +'%Y%m%d.txt'`

path=`/data/oct/rate/`

ftp -n <

open 175.10.1.155

user zhongxin zhongxin.12356

cd file

lcd  ${path}

bin

get  ${filename}

bye

EOF

lua实现解析入库，其中mysql库和日志都是自己实现的调用库，其具体实现，会在其他篇章实现

package.path = "../nserver/?.lua;"..package.path

local mysql = require("common.mysql")

local log  = require("common.log").log

local cjson = require("cjson")

local mccode = "000493"

local buyratetable = {}

local function getNextDay()

return os.date("%Y%m%d", os.time() + 24 * 3600)

end

local function getCurrentDay()

returnos.date("%Y%m%d")

end

local function selectMysql(msql)    local selsql = "select * from tb_hkd_exc_rate_diff where mccode = '".. mccode .. "' and begin_date <=" .. "'" ..  getNextDay() .. "' and end_date >" .. "'" ..  getNextDay() .. "'"  log(tostring(selsql))  local res , _err = mysql.query(msql,selsql)  if res then    log(cjson.encode(res))       for k, v in pairs(res) do     if type(v) == "string" then     log(k .. " = " .. v)     end    end    return res[1].exc_rate_diff  else    log("fail to get the rate diff, err msg is :" .. tostring(_err) )   return   end   end local function inserMysql(msql,buyratetable)     local inssql  = "insert into tb_exc_rate(mccode, time_date, buy_rate, exc_rate_diff) values('".. mccode .. "',"  .. getNextDay() ..  ","  .. buyratetable.RMB  .. "," .. buyratetable.diff ..");"   log(tostring(inssql))   local res, _err =mysql.insert(msql, inssql)   if res then      log("sucess to insert buy rate and rate diff, return msg is :" ..cjson.encode(res))      return   else     log("fail to insert buy rate and rate diff, err msg is :" .. tostring(_err) )  return   end end local function split( str,reps )     local resultStrList = {}     string.gsub(str,'[^'..reps..']+',function ( w )         table.insert(resultStrList,w)     end)     return resultStrList end function non()    file:close()    log(tostring(err))   local cmd = ([[sh %s %s %s]]):format(getshell, filename, path)   log(tostring(cmd))     local ok =  os.execute(cmd)    if ok == 0 then       log("success to get the rate file")    else        log("fail to get the rate file")    end     file, err = io.open(ratefile, "r")         if not file then      log("ftp not exist the file " .. filename)       return      end       end local function main()   local path = "/data/oct/rate/"   local filename = getNextDay()..".txt"   local ratefile = path .. filename   local getshell = path .. "getratefile.sh"   log(tostring(getshell))   local file, err    file, err = io.open(ratefile, "r")      if not file then             return   end      for line in file:lines() do      print(line)      local ratetable =   split( line,"|" )      if ratetable[3] == "RMB" then      log(" get the rate is " .. tostring(ratetable[4]))        buyratetable["RMB"] = ratetable[4]      end    end   file:close()    local msql  = mysql.newml()   buyratetable["diff"] = selectMysql(msql)   if  buyratetable["diff"] then      inserMysql(msql,buyratetable)       end   msql:close()  return end main()