本文演示创建SQLite数据库及查询、删除与更新
local cjson = require("cjson")
local db = require("sqlite").new()
local response = require("fastweb.response")
local function sqlite_delete()
local ppst = db:setsql("DELETE FROM users")
ppst:update()
end
local function sqlite_insert()
local ppst = db:setsql("INSERT INTO users(username,password)VALUES(?,?)")
ppst:set_str(1,"fastweb")
ppst:set_str(2,"123456")
ppst:update()
end
local function sqlite_update()
local ppst = db:setsql("UPDATE users SET password = ? WHERE username = ?")
ppst:set_str(1,"666666")
ppst:set_str(2,"fastweb")
ppst:update()
end
local function sqlite_select()
local ppst = db:setsql("SELECT * FROM users WHERE id > 0")
ppst:set_i32(1,0)
local result = ppst:query();
local users = {}
while result:next() do
local user = {
username = result:get("username"),
password = result:get("password"),
}
table.insert(users,user)
end
return users;
end
if db pen("E:/data.db") == false then
response.send("sqlite open failed,"..db:last_error())
return
end
local ppst = db:setsql("CREATE TABLE users(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT );")
if ppst ~= nil then
ppst:update()
end
sqlite_insert()
sqlite_update()
sqlite_select()
local users =sqlite_select()
response:send(cjson.encode(users))
|