Tina4-Python - Database Class

🔥 Hot Tips

• Instantiate dba in the ./src/__init__.py, include with from . import dba
• dba is the recognized global for database handling, but you can use any naming.
• Always call commit() after insert/update/delete unless in a transaction.
• Most methods return a DatabaseResult object which you can transform.

Connection

from tina4_python.Database import Database

# SQLite (file)
dba = Database("sqlite3:test.db")

# SQLite (in-memory - perfect for tests)
dba = Database("sqlite3::memory:")

# PostgreSQL
dba = Database("psycopg2:localhost/5432:mydb", "postgres", "password")

# MySQL / MariaDB
dba = Database("mysql.connector:localhost/3306:mydb", "root", "secret")

# MSSQL
dba = Database("pymssql:localhost/1433:mydb", "sa", "Password123")

# Firebird
dba = Database("firebird.driver:localhost/3050:/path/db.fdb", "sysdba", "masterkey")

# MongoDB (pip install pymongo)
dba = Database("pymongo:localhost/27017:mydb")
dba = Database("pymongo:localhost/27017:mydb", "user", "password")  # with auth

Core Methods

Method	Description	Returns
execute(sql, params=None)	Run any SQL (CREATE, DROP, INSERT, ...)	Result
execute_many(sql, params_list)	Bulk insert/update	Result
insert(table, data)	Smart insert (auto-increment, returns IDs)	Result
update(table, data, primary_key="id")	Update by PK	bool
delete(table, filter=None)	Delete by filter dict	bool
fetch(sql, params=None, **options)	SELECT with pagination & search	Result
fetch_one(sql, params=None)	Return single row as dict	dict or None
table_exists(table_name)	Check if table exists	bool
commit(), rollback(), start_transaction()	Full transaction control	-
close()	Close connection	-

Result Object

Every query returns a Result with these properties:

result.records          # list[dict] or list[tuple]
result.count            # int
result.error            # None or error message
result.to_json()        # → JSON string
result.to_array()       # → list of records
result.to_paginate()    # → pagination dict with totals
result.to_crud(request) # → CRUD HTML interface
result.to_csv()         # → CSV string

Examples

db = Database("sqlite3::memory:")

# Create table
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

# Insert
db.insert("users", {"name": "Alice", "age": 30})
db.insert("users", [{"name": "Bob", "age": 25}, {"name": "Eve", "age": 35}])

# Update
db.update("users", {"id": 1, "age": 31})

# Delete
db.delete("users", {"id": 2})

# Select
result = db.fetch("SELECT * FROM users")
print(result.records)           # → [{'id': 1, 'name': 'Alice', 'age': 31}, ...]
print(result.count)             # → 2

# With parameters
result = db.fetch("SELECT * FROM users WHERE age > ?", [30])
print(result.records[0]["name"])  # → Alice

# Pagination + search
result = db.fetch(
    "SELECT * FROM users",
    limit=10,
    skip=20,
    search_columns=["name"],
    search="ali"
)

db.close()

Transactions

db.start_transaction()
try:
    db.insert("users", {"name": "Risky"})
    db.commit()
except:
    db.rollback()   # automatically rolls back on exception

One-liner Heaven (Tina4 style)

Database("sqlite3:test.db").execute("INSERT INTO logs (msg) VALUES (?)", ["Hello Tina4"])

That's it. No models. No config files. No nonsense.

MongoDB

MongoDB uses the same SQL API as all other engines. The SQLToMongo module translates SQL to MongoDB queries transparently, with no new API to learn.

db = Database("pymongo:localhost/27017:myapp")

# Works exactly like any other engine
db.execute("CREATE TABLE users (id INTEGER)")  # creates collection
db.insert("users", {"id": 1, "name": "Alice", "email": "alice@test.com"})

result = db.fetch("SELECT * FROM users WHERE name = ?", ["Alice"])
print(result.records)  # → [{'id': 1, 'name': 'Alice', 'email': 'alice@test.com'}]

db.execute("UPDATE users SET name = ? WHERE id = ?", ["Bob", 1])
db.execute("DELETE FROM users WHERE id = ?", [1])

Supported WHERE operators

SQL	MongoDB
=	Direct match
!=, <>	$ne
>, >=, <, <=	$gt, $gte, $lt, $lte
LIKE '%text%'	$regex (case-insensitive)
IN (a, b, c)	$in
NOT IN (a, b)	$nin
IS NULL	None
IS NOT NULL	$ne: None
BETWEEN a AND b	$gte + $lte
AND / OR	$and / $or

Pagination & search

result = db.fetch(
    "SELECT * FROM users",
    limit=10,
    skip=20,
    search="alice",
    search_columns=["name", "email"]
)
print(result.total_count)  # total matching documents

RETURNING emulation

result = db.execute(
    "INSERT INTO users (id, name) VALUES (?, ?) RETURNING *",
    [1, "Alice"]
)
print(result.records)  # → [{'id': 1, 'name': 'Alice'}]

Limitations

• JOINs are not supported: MongoDB is document-based. Use embedded documents or application-level joins.
• CREATE TABLE maps to collection creation, column definitions are ignored (MongoDB is schema-less).
• Migrations will create/drop collections but column-level DDL (ALTER TABLE) is a no-op.