Lua simple sqlite3 operation

Document directory

• Structure
• Member
• Structure
• Member
• Structure
• Member

The luasql module supports sqlite3 and can complete the most basic database functions. However, the official documentation is not very detailed. I scanned the source code, added the experiment, and gained some experience.

Environment

Windows XP, luaforwindows

Code

Require "luasql. sqlite3 "function enumsimpletable (t) print" ----------------- "fork, V in pairs (t) Do print (k," = ", V) end print "------------------- \ n" endfunction rows (cur) returnfunction (cur) localt = {} if (nil ~ = Cur: Fetch (T, 'A') then return t elsereturn nil end, curendenv = assert (luasql. sqlite3 () dB = assert (ENV: connect ("test. DB ") DB: setautocommit (false) RES = assert (DB: Execute [[[Create tablepeople (Name text, sex text)]) RES = assert (DB: execute [[insert into people values ('applice', 'male')]) RES = assert (DB: Execute [[insert into people values ('applice ', 'femal')]) assert (DB: Commit () RES = assert (DB: Execute [[select * frompeople]) colnames = Res: getcolnames () coltypes = Res: getcoltypes () enumsimpletable (colnames) enumsimpletable (coltypes) for R in rows (RES) Do enumsimpletable (r) Endres: Close () DB: Close () ENV: close ()

Conclusion environment object (database-driven) Construction

ENV = luasql. sqlite3 ()

Member

Close ()

Disable the environment. Close all connection objects first.

Connection object (database connection) Construction

Con = env: connect (sqlite3_database_file_path)

• Env: Environment
• Sqlite3_database_file_path: Database path string
• Con: database connection object

Member

Res = execute (SQL _statement)

Execute SQL statements

• SQL _statement: the SQL statement to be executed. It is generally in the [[SQL] string format, because the SQL statement usually contains special characters such as quotation marks.
• Res:
  • For SQL statements (such as create and delete) that do not return record sets, RES is a number indicating the number of records affected by the operation.
  • For SQL statements (such as select) that return record sets, RES is a cursor object (record set)

Setautocommit (bauto)

• Bauto
  • When set to true, rollback the current transaction and ignore the error
  • When set to false, start a new transaction.

Commit ()

Commit transactions

Rollback ()

Roll back a transaction

Close ()

Close the database connection. Close all cursor objects first.

Rowed = getlastautoid ()

Obtain the rowid field value of the last auto-generated SQLite (corresponding to sqlite3_last_insert_rowid ())

Cursor object construction

Con: Execute (select_ SQL _statement)

Member

Colnametable = getcolnames ()

Returns the name of each column in the record set as a table.

Coltypetable = getcoltypes ()

Returns the type of each column in the record set as a table.

Res = fetch ([Table [, modestring])

Obtain the next record set

• Table: If a table is specified, data is copied to the table.
• Modestring: Optional values: "N" or "A". The default value is "N ",
  • "N": the key in the table is a number.
  • "A": the key in the table is a string (column name)
• Res: If no table is specified, a record is returned. If a table is specified, the modified table is returned. If no next record exists, Nil is returned.

Close ()

Close cursor object