AHK 脚本一旦开始保存账号配置、任务记录、历史数据或者几万行以上的结构化内容,继续靠 ini、txt 和零散 CSV 文件维护,很快就会遇到查询慢、更新麻烦、并发写入难处理的问题。我整理这套 CSQLite,就是为了让 AHK v1 能直接使用 SQLite,把数据放进一个本地数据库文件里管理。
这份资源面向 AutoHotkey v1,核心是对 sqlite3.dll 的一层类封装。常用的打开数据库、执行 SQL、返回结果表、事务写入、错误信息、影响行数和最后插入 ID 都已经整理成比较顺手的接口,新手不用从 SQLite C API 的指针和回调开始啃。
它适合解决什么问题
- 给桌面工具保存配置、用户数据和操作历史;
- 给采集脚本、自动办公脚本建立本地数据表;
- 按条件查询、排序、统计和批量更新数据;
- 使用事务一次写入多条记录,避免写到一半留下不完整数据;
- 用 REGEXP 和 regex_replace 在 SQL 中完成正则筛选与替换。
常用入口怎么理解
OpenDB() 负责打开或创建数据库,Exec() 适合 CREATE、INSERT、UPDATE、DELETE 这类不需要结果表的 SQL,GetTable() 则把 SELECT 结果整理成列名和行数组。执行失败后可以读取 ErrorCode 和 ErrorMsg,排查时不用只看到一个“数据库打不开”。
Changes() 返回最近一次操作影响的行数,TotalChanges() 是当前连接累计变化量,LastInsertRowID() 可取得自增主键。数据库可能被其他程序短暂占用时,可以先用 SetTimeout() 设置等待时间。
依赖和使用边界
下载包需要放入与 AHK 位数匹配的 sqlite3.dll,32 位 AHK 配 32 位 DLL,64 位 AHK 配 64 位 DLL。当前封装要求 SQLite 版本不低于 3.29。SQLite 很适合单机工具和中小型本地数据,但它不是远程多人数据库;需要跨电脑协同或高并发服务时,应该换成专门的数据库服务。
SQL 中拼接外部输入时还要注意引号和注入问题。正式项目更建议使用参数绑定或先严格校验数据,不要把用户输入原样接到 SQL 后面。
我建议的入门顺序
先运行下面的完整示例,确认 DLL 能加载、数据库能建立,再依次看建表、事务插入、GetTable 查询和 UPDATE。示例会在脚本目录创建 test.db,并重建 ahk_demo 测试表,不要直接改成重要数据库路径运行。
本文移植自 thqby 的 ahk2_lib,由本站适配为 AutoHotkey v1 版本,原作者及项目版权归其所有。
源项目地址:https://github.com/thqby/ahk2_lib
demo代码片段展示:
#Requires AutoHotkey v1.1
; 示例内容包括:
;
; 加载 sqlite3.dll
; 打开 test.db
; 创建/重建 ahk_demo 表
; 事务插入多行数据
; 查询全部数据
; 使用自定义 REGEXP
; 使用自定义 regex_replace
; UPDATE
; Changes() / TotalChanges() / LastInsertRowID()
; 最后 MsgBox 显示结果
#SingleInstance Force
#Include <CSQLite_v1>
dllDir := A_ScriptDir
dbPath := A_ScriptDir "\test.db"
out := ""
db := new CSQLite(dllDir)
out .= "SQLite version: " CSQLite.Version "`n"
if !db.OpenDB(dbPath, "W", true)
throw Exception("OpenDB failed: " db.ErrorMsg, -1, db.ErrorCode)
db.SetTimeout(3000)
sql := "DROP TABLE IF EXISTS ahk_demo;"
. "CREATE TABLE ahk_demo ("
. "id INTEGER PRIMARY KEY AUTOINCREMENT,"
. "name TEXT NOT NULL,"
. "age INTEGER,"
. "note TEXT,"
. "created_at TEXT DEFAULT CURRENT_TIMESTAMP"
. ");"
if !db.Exec(sql)
throw Exception("Create table failed: " db.ErrorMsg, -1, db.ErrorCode)
if !db.Exec("BEGIN TRANSACTION;")
throw Exception("BEGIN failed: " db.ErrorMsg, -1, db.ErrorCode)
sql := "INSERT INTO ahk_demo(name, age, note) VALUES"
. "('Alice', 23, 'likes AutoHotkey'),"
. "('Bob', 31, 'uses SQLite'),"
. "('Carol', 28, 'AHK v1 user'),"
. "('Dave123', 35, 'name contains digits');"
if !db.Exec(sql)
throw Exception("Insert failed: " db.ErrorMsg, -1, db.ErrorCode)
if !db.Exec("COMMIT;")
throw Exception("COMMIT failed: " db.ErrorMsg, -1, db.ErrorCode)
out .= "Inserted rows: " db.TotalChanges() "`n"
out .= "Last insert id: " db.LastInsertRowID() "`n`n"
if !db.GetTable("SELECT id, name, age, note, created_at FROM ahk_demo ORDER BY id;", table, -1)
throw Exception("Select failed: " db.ErrorMsg, -1, db.ErrorCode)
out .= "All rows: " table.RowCount "`n"
out .= JoinCols(table.Cols) "`n"
Loop, % table.RowCount
out .= JoinCols(table.Rows[A_Index]) "`n"
if !db.GetTable("SELECT name FROM ahk_demo WHERE name REGEXP '\d+';", regexTable)
throw Exception("REGEXP query failed: " db.ErrorMsg, -1, db.ErrorCode)
out .= "`nNames matching digits: " regexTable.RowCount "`n"
Loop, % regexTable.RowCount
out .= regexTable.Rows[A_Index][1] "`n"
if !db.GetTable("SELECT regex_replace(note, 'AHK', 'AutoHotkey') AS changed_note FROM ahk_demo WHERE name = 'Carol';", replaceTable)
throw Exception("regex_replace query failed: " db.ErrorMsg, -1, db.ErrorCode)
out .= "`nregex_replace result:`n"
out .= replaceTable.Rows[1][1] "`n"
if !db.Exec("UPDATE ahk_demo SET age = age + 1 WHERE name = 'Alice';")
throw Exception("Update failed: " db.ErrorMsg, -1, db.ErrorCode)
out .= "`nChanges after UPDATE: " db.Changes() "`n"
if !db.GetTable("SELECT name, age FROM ahk_demo WHERE name = 'Alice';", oneRow)
throw Exception("Final query failed: " db.ErrorMsg, -1, db.ErrorCode)
out .= "Alice after UPDATE: " oneRow.Rows[1][1] ", age=" oneRow.Rows[1][2] "`n"
db.CloseDB()
MsgBox, 64, CSQLite v1 example, %out%
ExitApp
JoinCols(arr, sep := " | ") {
s := ""
for _, value in arr
s .= (s = "" ? "" : sep) value
return s
}
CSQLite_v1.ahk
; @description SQLite class
; @file CSQLite.ahk
; @author thqby (Original v2) / dbgba (Ported to v1)
; @date 2026/05/06
; @version 0.0.5
; @note Converted for AutoHotkey v1 compatibility by dbgba.
; Original source: https://github.com/thqby/ahk2_lib
CSQLite_RegExp(Context, ArgC, vals) {
try {
regexNeedle := DllCall("SQLite3.dll\sqlite3_value_text16", "Ptr", NumGet(vals + 0, 0, "Ptr"), "Cdecl Str")
search := DllCall("SQLite3.dll\sqlite3_value_text16", "Ptr", NumGet(vals + A_PtrSize, 0, "Ptr"), "Cdecl Str")
DllCall("SQLite3.dll\sqlite3_result_int", "Ptr", Context, "Int", RegExMatch(search, regexNeedle), "Cdecl")
} catch e {
CSQLite_v1_ResultError(Context, e)
}
return 0
}
CSQLite_RegExReplace(Context, ArgC, vals) {
try {
search := DllCall("SQLite3.dll\sqlite3_value_text16", "Ptr", NumGet(vals + 0, 0, "Ptr"), "Cdecl Str")
regexNeedle := DllCall("SQLite3.dll\sqlite3_value_text16", "Ptr", NumGet(vals + A_PtrSize, 0, "Ptr"), "Cdecl Str")
Replacement := DllCall("SQLite3.dll\sqlite3_value_text16", "Ptr", NumGet(vals + A_PtrSize * 2, 0, "Ptr"), "Cdecl Str")
result := RegExReplace(search, regexNeedle, Replacement)
DllCall("SQLite3.dll\sqlite3_result_text16", "Ptr", Context, "Str", result, "Int", -1, "Ptr", -1, "Cdecl")
} catch e {
CSQLite_v1_ResultError(Context, e)
}
return 0
}
CSQLite_GetTableCallback(TB, coln, vals, cols) {
try {
arr := []
TBobj := CSQLite_v1_ObjectFromPtr(TB)
if (!IsObject(TBobj))
return 1
Loop, %coln%
{
p := NumGet(vals + A_PtrSize * (A_Index - 1), 0, "Ptr")
arr.Push(p ? StrGet(p, "UTF-8") : "")
}
TBobj.Rows.Push(arr)
TBobj.RowCount++
return 0
} catch e {
return 1
}
}
CSQLite_v1_ResultError(Context, e) {
msg := CSQLite_v1_ErrorMessage(e)
DllCall("SQLite3.dll\sqlite3_result_error16", "Ptr", Context, "Str", msg, "Int", -1, "Cdecl")
}
CSQLite_v1_ErrorMessage(e) {
local msg
if (!IsObject(e))
return e
try msg := e.Message
catch ex
msg := ""
if (msg = "")
msg := "<no message>"
return msg
}
CSQLite_v1_ObjPtr(obj) {
return &obj
}
CSQLite_v1_PtrKey(ptr) {
ptr += 0
return ptr ? "p:" ptr : ""
}
CSQLite_v1_ObjectTable() {
static table := {}
return table
}
CSQLite_v1_RegisterObject(obj) {
local ptr, table, key
if (!IsObject(obj))
return 0
ptr := CSQLite_v1_ObjPtr(obj)
table := CSQLite_v1_ObjectTable()
key := CSQLite_v1_PtrKey(ptr)
table[key] := obj
return ptr
}
CSQLite_v1_UnregisterObject(ptr) {
local table, key
if (!ptr)
return 0
table := CSQLite_v1_ObjectTable()
key := CSQLite_v1_PtrKey(ptr)
if (table.HasKey(key))
table.Delete(key)
return 1
}
CSQLite_v1_ObjectFromPtr(ptr) {
local table, key
if (!ptr)
return ""
table := CSQLite_v1_ObjectTable()
key := CSQLite_v1_PtrKey(ptr)
if (table.HasKey(key))
return table[key]
return ""
}
class CSQLite {
static Version := ""
static _SQLiteDLL := ""
static _RefCount := 0
static hModule := 0
static _MinVersion := "3.29"
__New(DllPathOrFolder := "") {
if (CSQLite._SQLiteDLL = "") {
SplitPath, A_LineFile,, sqliteLibDir
CSQLite._SQLiteDLL := sqliteLibDir "\sqlite3.dll"
}
this._Path := ""
this.ptr := 0
this.ErrorMsg := ""
this.ErrorCode := 0
this._initialized := 0
if (CSQLite._RefCount = 0) {
SQLiteDLL := CSQLite._SQLiteDLL
if (DllPathOrFolder != "") {
attr := FileExist(DllPathOrFolder)
if (InStr(attr, "D")) {
if FileExist(RTrim(DllPathOrFolder, "\/") "\sqlite3.dll")
SQLiteDLL := CSQLite._SQLiteDLL := RTrim(DllPathOrFolder, "\/") "\sqlite3.dll"
else if FileExist(RTrim(DllPathOrFolder, "\/") "\SQLite3.dll")
SQLiteDLL := CSQLite._SQLiteDLL := RTrim(DllPathOrFolder, "\/") "\SQLite3.dll"
} else if (attr) {
SQLiteDLL := CSQLite._SQLiteDLL := DllPathOrFolder
}
}
if (!DllCall("GetModuleHandle", "Str", SQLiteDLL, "UPtr")
&& !(CSQLite.hModule := DllCall("LoadLibrary", "Str", SQLiteDLL, "UPtr")))
throw Exception("DLL " SQLiteDLL " does not exist!")
CSQLite.Version := StrGet(DllCall("SQLite3.dll\sqlite3_libversion", "Cdecl UPtr"), "UTF-8")
if (VerCompare(CSQLite.Version, CSQLite._MinVersion) < 0) {
DllCall("FreeLibrary", "Ptr", CSQLite.hModule)
CSQLite.hModule := 0
throw Exception("Version " CSQLite.Version " of SQLite3.dll is not supported!`n`nYou can download the current version from www.sqlite.org!")
}
}
CSQLite._RefCount += 1
this._initialized := 1
}
__Delete() {
if (this.ptr)
this.CloseDB()
if (!this._initialized)
return
CSQLite._RefCount -= 1
if (CSQLite._RefCount = 0) {
if (CSQLite.hModule)
DllCall("FreeLibrary", "Ptr", CSQLite.hModule)
CSQLite.hModule := 0
}
}
_StrToUTF8(ByRef buf, Str) {
size := StrPut(Str, "UTF-8")
VarSetCapacity(buf, size, 0)
StrPut(Str, &buf, size, "UTF-8")
return &buf
}
_ErrMsg() {
RC := DllCall("SQLite3.dll\sqlite3_errmsg", "Ptr", this.ptr, "Cdecl Ptr")
return RC ? StrGet(RC, "UTF-8") : ""
}
_ErrCode() {
return DllCall("SQLite3.dll\sqlite3_errcode", "Ptr", this.ptr, "Cdecl Int")
}
_ReturnMsg(RC) {
static Msg := ""
if !IsObject(Msg) {
Msg := {}
Msg[1] := "SQL error or missing database"
Msg[2] := "Internal SQLite logic error"
Msg[3] := "Access permission denied"
Msg[4] := "Callback requested query abort"
Msg[5] := "Database file is locked"
Msg[6] := "A table in the database is locked"
Msg[7] := "malloc failed"
Msg[8] := "Attempt to write a readonly database"
Msg[9] := "Operation interrupted"
Msg[10] := "Disk I/O error"
Msg[11] := "Database disk image is malformed"
Msg[12] := "Unknown opcode in sqlite3_file_control"
Msg[13] := "Insertion failed because database is full"
Msg[14] := "Unable to open database file"
Msg[15] := "Database lock protocol error"
Msg[16] := "Database is empty"
Msg[17] := "Database schema changed"
Msg[18] := "String or BLOB exceeds size limit"
Msg[19] := "Constraint violation"
Msg[20] := "Data type mismatch"
Msg[21] := "Library used incorrectly"
Msg[22] := "Unsupported OS feature"
Msg[23] := "Authorization denied"
Msg[24] := "Auxiliary database format error"
Msg[25] := "Bind parameter index out of range"
Msg[26] := "Opened file is not a database file"
Msg[100] := "sqlite3_step returned a row"
Msg[101] := "sqlite3_step finished"
}
return Msg.HasKey(RC) ? Msg[RC] : ""
}
OpenDB(DBPath, Access := "W", Create := true) {
static SQLITE_OPEN_READONLY := 0x01
static SQLITE_OPEN_READWRITE := 0x02
static SQLITE_OPEN_CREATE := 0x04
static MEMDB := ":memory:"
this.ErrorMsg := "", this.ErrorCode := 0
if (DBPath = "")
DBPath := MEMDB
if ((DBPath = this._Path) && this.ptr)
return true
if (this.ptr)
return this._Fail("You must first close DB " this._Path "!")
Access := SubStr(Access, 1, 1)
if (Access != "W" && Access != "R")
Access := "R"
Flags := SQLITE_OPEN_READONLY
if (Access = "W") {
Flags := SQLITE_OPEN_READWRITE
if (Create)
Flags |= SQLITE_OPEN_CREATE
}
this._Path := DBPath
HDB := 0
sqlbuf := ""
sql := this._StrToUTF8(sqlbuf, DBPath)
RC := DllCall("SQLite3.dll\sqlite3_open_v2", "Ptr", sql, "Ptr*", HDB, "Int", Flags, "Ptr", 0, "Cdecl Int")
if (RC) {
this.ptr := HDB
msg := this._ErrMsg()
if (HDB)
DllCall("SQLite3.dll\sqlite3_close", "Ptr", HDB, "Cdecl Int")
this.ptr := 0, this._Path := ""
return this._Fail(msg, RC)
}
this.ptr := HDB
this._RegisterDefaultFunctions()
return true
}
CloseDB() {
this.ErrorMsg := "", this.ErrorCode := 0, this.SQL := ""
if !this.ptr
return true
RC := DllCall("SQLite3.dll\sqlite3_close", "Ptr", this.ptr, "Cdecl Int")
if (RC)
return this._Fail(this._ErrMsg(), RC)
this._Path := "", this.ptr := 0
return true
}
LoadOrSaveDb(DBPath, isSave := 0) {
static SQLITE_OPEN_READWRITE := 0x02
static SQLITE_OPEN_CREATE := 0x04
this.ErrorMsg := "", this.ErrorCode := 0
HDB := 0
if (DBPath = "")
return false
if (!this.ptr)
return this._Fail("You must first Open Memory DB!")
Flags := SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
if (isSave) {
tmp := DBPath ".tmp"
FileDelete, %tmp%
} else {
tmp := ""
}
openPath := isSave ? tmp : DBPath
sqlbuf := ""
sql := this._StrToUTF8(sqlbuf, openPath)
RC := DllCall("SQLite3.dll\sqlite3_open_v2", "Ptr", sql, "Ptr*", HDB, "Int", Flags, "Ptr", 0, "Cdecl Int")
if (RC) {
this.ErrorCode := RC
this.ErrorMsg := HDB ? StrGet(DllCall("SQLite3.dll\sqlite3_errmsg", "Ptr", HDB, "Cdecl Ptr"), "UTF-8") : this._ReturnMsg(RC)
if (HDB)
DllCall("SQLite3.dll\sqlite3_close", "Ptr", HDB, "Cdecl Int")
return false
}
pFrom := isSave ? this.ptr : HDB
pTo := isSave ? HDB : this.ptr
pBackup := DllCall("SQLite3.dll\sqlite3_backup_init", "Ptr", pTo, "AStr", "main", "Ptr", pFrom, "AStr", "main", "Cdecl Ptr")
if (pBackup) {
DllCall("SQLite3.dll\sqlite3_backup_step", "Ptr", pBackup, "Int", -1, "Cdecl Int")
DllCall("SQLite3.dll\sqlite3_backup_finish", "Ptr", pBackup, "Cdecl Int")
}
RC := DllCall("SQLite3.dll\sqlite3_errcode", "Ptr", pTo, "Cdecl Int")
DllCall("SQLite3.dll\sqlite3_close", "Ptr", HDB, "Cdecl Int")
if (RC) {
this._Path := "", this.ErrorMsg := this._ErrMsg(), this.ErrorCode := RC
if (isSave)
FileDelete, %tmp%
return false
}
if (isSave) {
FileGetSize, tmpSize, %tmp%, K
if (tmpSize < 16) {
FileDelete, %tmp%
return false
}
FileMove, %tmp%, %DBPath%, 1
if (ErrorLevel)
return this._Fail("Failed to move temporary database to " DBPath)
}
return true
}
AttachDB(DBPath, DBAlias) {
return this.Exec("ATTACH DATABASE '" DBPath "' As " DBAlias ";")
}
DetachDB(DBAlias) {
return this.Exec("DETACH DATABASE " DBAlias ";")
}
Exec(SQL, pCallback := 0) {
this.ErrorMsg := "", this.ErrorCode := 0
if !(this.ptr)
return this._Fail("Invalid database handle!")
Err := 0
sqlbuf := ""
sql := this._StrToUTF8(sqlbuf, SQL)
callbackObjPtr := CSQLite_v1_RegisterObject(this)
try {
RC := DllCall("SQLite3.dll\sqlite3_exec", "Ptr", this.ptr, "Ptr", sql, "Ptr", pCallback, "Ptr", callbackObjPtr, "Ptr*", Err, "Cdecl Int")
} catch e {
CSQLite_v1_UnregisterObject(callbackObjPtr)
throw e
}
CSQLite_v1_UnregisterObject(callbackObjPtr)
if (RC) {
this.ErrorMsg := this._ReturnMsg(RC)
if (this.ErrorMsg = "" && Err)
this.ErrorMsg := StrGet(Err, "UTF-8")
this.ErrorCode := RC
if (Err)
DllCall("SQLite3.dll\sqlite3_free", "Ptr", Err, "Cdecl")
return false
}
return true
}
GetTable(SQL, ByRef TB, pcall := 0) {
static callback_gettable_ptr := 0
if (!callback_gettable_ptr)
callback_gettable_ptr := RegisterCallback("CSQLite_GetTableCallback", "F C", 4)
this.ErrorMsg := "", this.ErrorCode := 0
if !(this.ptr)
return this._Fail("Invalid database handle!")
TB := {RowCount: 0, Rows: []}
Err := 0
sqlbuf := ""
sql := this._StrToUTF8(sqlbuf, SQL)
callback := (pcall != -1 && pcall) ? pcall : callback_gettable_ptr
callbackObjPtr := CSQLite_v1_RegisterObject(TB)
try {
RC := DllCall("SQLite3.dll\sqlite3_exec", "Ptr", this.ptr, "Ptr", sql, "Ptr", callback, "Ptr", callbackObjPtr, "Ptr*", Err, "Cdecl Int")
} catch e {
CSQLite_v1_UnregisterObject(callbackObjPtr)
throw e
}
CSQLite_v1_UnregisterObject(callbackObjPtr)
if (RC) {
this.ErrorMsg := this._ReturnMsg(RC)
if (this.ErrorMsg = "" && Err)
this.ErrorMsg := StrGet(Err, "UTF-8")
this.ErrorCode := RC
if (Err)
DllCall("SQLite3.dll\sqlite3_free", "Ptr", Err, "Cdecl")
return false
} else if (pcall = -1) {
Stmt := 0
RC := DllCall("SQLite3.dll\sqlite3_prepare_v2", "Ptr", this.ptr, "Ptr", sql, "Int", -1, "Ptr*", Stmt, "Ptr", 0, "Cdecl Int")
if (RC) {
this.ErrorMsg := this._ErrMsg(), this.ErrorCode := RC
return false
}
TB.Cols := []
TB.ColCount := DllCall("SQLite3.dll\sqlite3_column_count", "Ptr", Stmt, "Cdecl Int")
Loop, % TB.ColCount
TB.Cols.Push(StrGet(DllCall("SQLite3.dll\sqlite3_column_name16", "Ptr", Stmt, "Int", A_Index - 1, "Cdecl UPtr"), "UTF-16"))
DllCall("SQLite3.dll\sqlite3_finalize", "Ptr", Stmt, "Cdecl Int")
}
return true
}
createScalarFunction(name, pfn, params) {
this.ErrorMsg := "", this.ErrorCode := 0
err := DllCall("SQLite3.dll\sqlite3_create_function16", "Ptr", this.ptr, "Str", name, "Int", params, "Int", 0x801, "Ptr", 0, "Ptr", pfn, "Ptr", 0, "Ptr", 0, "Cdecl Int")
if (err)
return this._Fail(this._ErrMsg(), err)
return true
}
Changes() {
return DllCall("SQLite3.dll\sqlite3_changes", "Ptr", this.ptr, "Cdecl Int")
}
LastInsertRowID() {
return DllCall("SQLite3.dll\sqlite3_last_insert_rowid", "Ptr", this.ptr, "Cdecl Int64")
}
TotalChanges() {
return DllCall("SQLite3.dll\sqlite3_total_changes", "Ptr", this.ptr, "Cdecl Int")
}
SetTimeout(Timeout := 1000) {
this.ErrorMsg := "", this.ErrorCode := 0, this.SQL := ""
if !(this.ptr)
return this._Fail("Invalid database handle!")
RC := DllCall("SQLite3.dll\sqlite3_busy_timeout", "Ptr", this.ptr, "Int", Timeout, "Cdecl Int")
if (RC)
return this._Fail(this._ErrMsg(), RC)
return true
}
_RegisterDefaultFunctions() {
static pRegExp := 0, pRegExReplace := 0
if (!pRegExp)
pRegExp := RegisterCallback("CSQLite_RegExp", "F C", 3)
if (!pRegExReplace)
pRegExReplace := RegisterCallback("CSQLite_RegExReplace", "F C", 3)
this.createScalarFunction("regexp", pRegExp, 2)
this.createScalarFunction("regex_replace", pRegExReplace, 3)
}
_Fail(msg, code := 0) {
this.ErrorMsg := msg
this.ErrorCode := code
return false
}
}

评论(0)