Geek asks and answers

Sqlite - how do I get a single row result? (LuaSQLite3)

How can I get the result of one row (e.g. as a table / array) from sql statement. Using Lua Sqlite (LuaSQLite3). For example, this one:

SELECT * FROM sqlite_master WHERE name ='myTable';

So far I have noted:

  • using "nrows" / "rows", it returns the iterator back
  • using "exec" it doesn't seem to return a result (?)

Concrete questions:

Q1 - How to return the result of one row (for example, the first row)?

Q2 - How to get the number of rows? (e.g. num_rows_returned = db: XXXX (sql))

+3
source share
4 answers

The following is a demo of the returned account:

> require "lsqlite3"
> db = sqlite3.open":memory:"
> db:exec "create table foo (x,y,z);"
> for x in db:urows "select count(*) from foo" do print(x) end
0
> db:exec "insert into foo values (10,11,12);"
> for x in db:urows "select count(*) from foo" do print(x) end
1
>
+2
source

, db:first_row. .

row = db:first_row("SELECT `id` FROM `table`")
print(row.id)

, SQL COUNT. .

row = db:first_row("SELECT COUNT(`id`) AS count FROM `table`")
print(row.count)

EDIT: , . , .

db:nrows. .

rows = db:nrows("SELECT `id` FROM `table`")
row = rows[1]
print(row.id)

, .

rows = db:nrows("SELECT COUNT(`id`) AS count FROM `table`")
row = rows[1]
print(row.count)
+3

, rows , . , break , .

- SQL. SELECT:

SELECT count(*) FROM ...

, : .

+2

, , .

local query = "SELECT content FROM playerData WHERE name = 'myTable' LIMIT 1"

local queryResultTable = {}

local queryFunction = function(userData, numberOfColumns, columnValues, columnTitles)

    for i = 1, numberOfColumns do

        queryResultTable[columnTitles[i]] = columnValues[i]

    end

end

db:exec(query, queryFunction)

for k,v in pairs(queryResultTable) do

    print(k,v)

end

You can even combine values ​​into a query to be placed inside a common method / function.

local query = "SELECT * FROM ZQuestionTable WHERE ConceptNumber = "..conceptNumber.." AND QuestionNumber = "..questionNumber.." LIMIT 1"
+1
source

More articles:


All Articles