SQLite
Upload a SQLite database to aiXplain, create a tool that queries it, and wire it into an agent that answers natural language questions against your data.
Prerequisites:
- aiXplain account and API key (get one ↗)
pip install aixplain
Quick start
import sqlite3
import time
from aixplain import Aixplain
aix = Aixplain(api_key="YOUR_API_KEY")
# 1. Create a local database
conn = sqlite3.connect("quickstart.db")
conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
conn.execute("INSERT INTO products VALUES (1, 'Laptop', 999.99)")
conn.commit()
conn.close()
# 2. Upload as a Resource
resource = aix.Resource(name=f"Quick DB {int(time.time())}", file_path="quickstart.db")
resource.save()
# 3. Create the SQLite tool
sqlite_tool = aix.Tool(
name=f"SQLite Tool {int(time.time())}",
description="Query product database.",
integration="689e06ed3ce71f58d73cc999",
config={"url": resource.url},
)
sqlite_tool.save()
# 4. Run a query
result = sqlite_tool.run(action="query", data="SELECT * FROM products")
print(result.data)
Step 1: Create a local SQLite database
import sqlite3
import os
import time
db_filename = "business.db"
if os.path.exists(db_filename):
os.remove(db_filename)
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
signup_date DATE
)
""")
cursor.execute("""
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
product TEXT,
amount REAL,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(id)
)
""")
cursor.executemany("INSERT INTO customers VALUES (?, ?, ?, ?)", [
(1, "TechCorp", "contact@techcorp.com", "2024-01-10"),
(2, "StartupXYZ", "hello@startupxyz.com", "2024-01-15"),
])
cursor.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", [
(1, 1, "Enterprise License", 5000.00, "2024-01-20"),
(2, 1, "Support Package", 1200.00, "2024-02-01"),
(3, 2, "Starter Plan", 500.00, "2024-01-18"),
])
conn.commit()
conn.close()
Always use ? placeholders when inserting data — never interpolate user input directly into SQL strings.
Changes made through the tool are applied to an in-memory copy of the database. To persist writes, download and re-upload the modified file. For write-heavy workflows, consider separate read and write databases.
Step 2: Upload as a Resource
resource = aix.Resource(
name=f"Business DB {int(time.time())}",
file_path=db_filename,
)
resource.save()
print(resource.url) # S3 URL used in the tool config
save() uploads the file to cloud storage and returns a URL.
Step 3: Create the SQLite tool
sqlite_tool = aix.Tool(
name=f"Business Database {int(time.time())}",
description="Customer and order database.",
integration="689e06ed3ce71f58d73cc999", # SQLite integration ID
config={"url": resource.url},
)
sqlite_tool.save()
Inspect available actions after saving:
sqlite_tool.list_actions()
The primary action is query, which accepts any valid SQL statement.
Step 4: Query the tool directly
Test queries before attaching the tool to an agent:
# List tables
result = sqlite_tool.run(
action="query",
data={"query": "SELECT name FROM sqlite_master WHERE type='table'"},
)
print(result.data)
# SELECT with filter
result = sqlite_tool.run(
action="query",
data="SELECT * FROM orders WHERE amount > 1000",
)
print(result.data)
# Aggregate
result = sqlite_tool.run(
action="query",
data="SELECT customer_id, SUM(amount) as total FROM orders GROUP BY customer_id",
)
print(result.data)
Results are returned in Markdown table format.
The data parameter accepts either a SQL string or a {"query": "..."} dict — both are equivalent.
Step 5: Build and run the agent
bi_agent = aix.Agent(
name="Business Intelligence Agent",
description="Analyses customer and order data.",
instructions="""
You have access to a database with two tables:
- customers (id, name, email, signup_date)
- orders (id, customer_id, product, amount, order_date)
Answer business questions by querying the database.
Always format results as clear markdown tables.
""",
output_format="markdown",
tools=[sqlite_tool],
)
bi_agent.save()
response = bi_agent.run("Which customer has spent the most?")
print(response.data.output)
Include the table schema in instructions so the agent constructs accurate SQL without guessing column names.
Natural language queries
questions = [
"What is our total revenue?",
"Show me all orders from January 2024.",
"How many customers signed up each month?",
]
for question in questions:
response = bi_agent.run(question)
print(f"\n{question}\n{response.data.output}")
Full example
import sqlite3, os, time
from aixplain import Aixplain
aix = Aixplain(api_key="YOUR_API_KEY")
# 1. Database
db_filename = "business.db"
if os.path.exists(db_filename):
os.remove(db_filename)
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
cursor.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, signup_date DATE)")
cursor.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, product TEXT, amount REAL, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id))")
cursor.executemany("INSERT INTO customers VALUES (?, ?, ?, ?)", [(1, "TechCorp", "contact@techcorp.com", "2024-01-10"), (2, "StartupXYZ", "hello@startupxyz.com", "2024-01-15")])
cursor.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", [(1, 1, "Enterprise License", 5000.00, "2024-01-20"), (2, 1, "Support Package", 1200.00, "2024-02-01"), (3, 2, "Starter Plan", 500.00, "2024-01-18")])
conn.commit()
conn.close()
# 2. Resource
resource = aix.Resource(name=f"Business DB {int(time.time())}", file_path=db_filename)
resource.save()
# 3. Tool
sqlite_tool = aix.Tool(
name=f"Business Database {int(time.time())}",
description="Customer and order database.",
integration="689e06ed3ce71f58d73cc999",
config={"url": resource.url},
)
sqlite_tool.save()
# 4. Agent
bi_agent = aix.Agent(
name="Business Intelligence Agent",
description="Analyses customer and order data.",
instructions="""
Tables: customers (id, name, email, signup_date), orders (id, customer_id, product, amount, order_date).
Answer questions by querying the database. Format results as markdown tables.
""",
output_format="markdown",
tools=[sqlite_tool],
)
bi_agent.save()
# 5. Run
for question in ["What is our total revenue?", "Which customer has spent the most?"]:
print(f"\n{question}")
print(bi_agent.run(question).data.output)
# 6. Cleanup
os.remove(db_filename)
Troubleshooting
Database file not found
Verify the file path and confirm the file has a .db extension.
Query returns no results
Check table names — they are case-sensitive. Use SELECT name FROM sqlite_master WHERE type='table' to list all tables.
"Table not found" errors
Confirm the table was created and conn.commit() was called before closing the connection.
Resource upload fails Check file size (keep under 100 MB) and confirm your API key has upload permissions.
Agent generates incorrect SQL
Include the full table schema and example queries in instructions. Test queries manually with sqlite_tool.run() first.
Writes not persisting The tool operates on an in-memory copy. Re-upload the database file to persist changes.