sql5 2.0.0

SQLite-compatible database with CJK FTS5 support

sql5

SQLite-compatible database with native CJK FTS5 support.

Installation

pip install sql5

Python API

import sql5

# Create in-memory database
db = sql5.connect()

# Or open a file
# db = sql5.connect("mydb.db")

# Execute SQL
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
db.execute("INSERT INTO users VALUES (1, 'Alice', 30)")
db.execute("INSERT INTO users VALUES (2, 'Bob', 25)")
db.execute("INSERT INTO users VALUES (3, 'Charlie', 35)")

# Query with parameters
db.execute("INSERT INTO users VALUES (?, ?, ?)", (4, "David", 28))

# Fetch results
cursor = db.execute("SELECT * FROM users WHERE age > ?", (25,))
for row in cursor:
    print(row)
# (1, 'Alice', 30)
# (2, 'Bob', 25)
# (3, 'Charlie', 35)
# (4, 'David', 28)

# Fetch as list
cursor = db.execute("SELECT name, age FROM users ORDER BY age")
rows = cursor.fetchall()
print(rows)
# [('Bob', 25), ('David', 28), ('Alice', 30), ('Charlie', 35)]

# Fetch one
cursor = db.execute("SELECT * FROM users WHERE id = ?", (1,))
row = cursor.fetchone()
print(row)
# (1, 'Alice', 30)

# Transactions
db.execute("BEGIN")
db.execute("INSERT INTO users VALUES (5, 'Eve', 40)")
db.execute("COMMIT")

# Or rollback
db.execute("BEGIN")
db.execute("INSERT INTO users VALUES (6, 'Frank', 45)")
db.execute("ROLLBACK")

# Full-text search (FTS5)
db.execute("CREATE VIRTUAL TABLE articles USING fts5(title, body)")
db.execute("INSERT INTO articles VALUES ('Hello World', 'The quick brown fox')")
db.execute("INSERT INTO articles VALUES ('Rust Guide', 'Memory safety without GC')")
db.execute("INSERT INTO articles VALUES ('中文測試', '繁體中文全文檢索')")

cursor = db.execute("SELECT * FROM articles WHERE articles MATCH ?", ("rust",))
print(cursor.fetchall())
# [('Rust Guide', 'Memory safety without GC')]

cursor = db.execute("SELECT * FROM articles WHERE articles MATCH ?", ("中文",))
print(cursor.fetchall())
# [('中文測試', '繁體中文全文檢索')]

# Close database
db.close()

CLI Usage

# Run the REPL
sql5

# Open a database file
sql5 /path/to/database.db

# Execute single query
echo "SELECT 1 + 1;" | sql5

Features

• Full SQL support (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP)
• ACID transactions (BEGIN, COMMIT, ROLLBACK)
• WAL mode
• Foreign keys
• Views
• Triggers
• Full-text search (FTS5) with CJK bigram tokenization
• Multiple database attachment (ATTACH DATABASE)
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.)
• String functions (UPPER, LOWER, SUBSTR, REPLACE, etc.)
• Date/time functions (DATE, TIME, DATETIME, STRFTIME)
• JSON functions (JSON, JSON_EXTRACT, JSON_SET, etc.)

Requirements

• Python 3.8+

License

MIT