sqlite utils Python (How It Works For Developers)

Introduction

The SQLite-utils Python package is a versatile tool that contains Python utility functions for working with SQLite databases. It provides a command-line interface (CLI) and a Python library, making it easy to create, manipulate, and query SQLite databases. Let's dive into its features and see some code examples. Later in this article, we will explore IronPDF, a PDF generation library developed by Iron Software.

Overview of SQLite-utils

SQLite-utils is designed to simplify various tasks related to manipulating SQLite databases. Some of its key features include:

• Creating and managing databases: Easily create new databases and tables.
• Inserting and querying data: Insert JSON data, CSV, or TSV files and run SQL queries.
• Full-text search: Configure and run full-text search queries.
• Schema transformations: Perform schema changes that SQLite's ALTER TABLE does not support directly.
• Data normalization: Extract columns into separate tables to normalize data.
• Custom SQL functions: Install plugins to add custom SQL functions.

Installation

You can install SQLite-utils using pip:

pip install sqlite-utils

Or, if you use Homebrew on macOS:

brew install sqlite-utils

brew install sqlite-utils

Using SQLite-utils as a CLI Tool

The CLI tool allows you to perform various operations directly from the command line. Here are some examples:

Creating a Database and Inserting Data

Let's create a new SQLite database and insert some data from a CSV file:

# Create a new database and insert data from a CSV file
sqlite-utils insert dogs.db dogs dogs.csv --csv

# Create a new database and insert data from a CSV file
sqlite-utils insert dogs.db dogs dogs.csv --csv

Querying Data

The command below is how you would do an SQL query from the database:

# Query the database and display results in JSON format
sqlite-utils dogs.db "select * from dogs" --json

# Query the database and display results in JSON format
sqlite-utils dogs.db "select * from dogs" --json

Listing Tables

List all tables in the database along with their row counts:

sqlite-utils tables dogs.db --counts

sqlite-utils tables dogs.db --counts

Using SQLite-utils as a Python Library

You can also use SQLite-utils as a Python library to interact with SQLite databases programmatically.

Creating a Database and Inserting Data

Here's how to create a new database and insert data using Python:

import sqlite_utils
# Create a new database
db = sqlite_utils.Database("demo_database.db")
# Insert data into a table
db["dogs"].insert_all([
    {"id": 1, "age": 4, "name": "Cleo"},
    {"id": 2, "age": 2, "name": "Pancakes"}
], pk="id")

import sqlite_utils
# Create a new database
db = sqlite_utils.Database("demo_database.db")
# Insert data into a table
db["dogs"].insert_all([
    {"id": 1, "age": 4, "name": "Cleo"},
    {"id": 2, "age": 2, "name": "Pancakes"}
], pk="id")

Querying Data

You can run SQL queries and fetch results:

# Run a query and fetch results
rows = db.query("SELECT * FROM dogs")
for row in rows:
    print(row)

# Run a query and fetch results
rows = db.query("SELECT * FROM dogs")
for row in rows:
    print(row)

Full-Text Search

Enable full-text search on a table and search queries:

# Enable full-text search
db["dogs"].enable_fts(["name"])
# Run a search query
results = db["dogs"].search("Cleo")
for result in results:
    print(result)

# Enable full-text search
db["dogs"].enable_fts(["name"])
# Run a search query
results = db["dogs"].search("Cleo")
for result in results:
    print(result)

Introducing IronPDF

IronPDF is a powerful Python library designed to create, edit, and sign PDFs using HTML, CSS, images, and JavaScript. It offers commercial-grade performance with a low memory footprint. Key features include:

HTML to PDF Conversion:

Convert HTML files, HTML strings, and URLs to PDFs. For example, render a webpage as a PDF using the Chrome PDF renderer.

Cross-Platform Support:

Compatible with various .NET platforms, including .NET Core, .NET Standard, and .NET Framework. It supports Windows, Linux, and macOS.

Editing and Signing:

Set properties, add security with passwords and permissions, and apply digital signatures to your PDFs.

Page Templates and Settings:

You can customize PDFs with headers, footers, page numbers, and adjustable margins. It additionally supports custom paper sizes and responsive layouts.

Standards Compliance:

Complies with PDF standards, including PDF/A and PDF/UA, supports UTF-8 character encoding, and manages assets such as images, CSS, and fonts.

Generate PDF Documents using IronPDF and Sqlite Utils.

import sqlite_utils
from ironpdf import * 
# Apply your license key
License.LicenseKey = "key"
db = sqlite_utils.Database("mydatabase.db")
# Define a table schema
schema = {
    "id": int,
    "name": str,
    "age": int
}
# Create a table
db["users"].create(schema)
data = [
    {"id": 1, "name": "Alice", "age": 30},
    {"id": 2, "name": "Bob", "age": 28},
    {"id": 3, "name": "Charlie", "age": 32}
]
# Insert data into the table
db["users"].insert_all(data)
# Query all records
results = list(db["users"].rows)
# Filter records
filtered_results = list(db["users"].rows_where("age > ?", [30]))
# Display all records
rows = db.query("SELECT * FROM users")
renderer = ChromePdfRenderer()
# Create a PDF from a HTML string using Python
content = "<h1>Awesome IronPDF with Sqlite-Utils</h1>"
content += "<p>table data</p>"
for row in rows:
    print(row)
    content += "<p>"+str(row)+"</p>"
pdf = renderer.RenderHtmlAsPdf(content)    
    # Export to a file or Stream
pdf.SaveAs("DemoSqliteUtils.pdf")

import sqlite_utils
from ironpdf import * 
# Apply your license key
License.LicenseKey = "key"
db = sqlite_utils.Database("mydatabase.db")
# Define a table schema
schema = {
    "id": int,
    "name": str,
    "age": int
}
# Create a table
db["users"].create(schema)
data = [
    {"id": 1, "name": "Alice", "age": 30},
    {"id": 2, "name": "Bob", "age": 28},
    {"id": 3, "name": "Charlie", "age": 32}
]
# Insert data into the table
db["users"].insert_all(data)
# Query all records
results = list(db["users"].rows)
# Filter records
filtered_results = list(db["users"].rows_where("age > ?", [30]))
# Display all records
rows = db.query("SELECT * FROM users")
renderer = ChromePdfRenderer()
# Create a PDF from a HTML string using Python
content = "<h1>Awesome IronPDF with Sqlite-Utils</h1>"
content += "<p>table data</p>"
for row in rows:
    print(row)
    content += "<p>"+str(row)+"</p>"
pdf = renderer.RenderHtmlAsPdf(content)    
    # Export to a file or Stream
pdf.SaveAs("DemoSqliteUtils.pdf")

Code Explanation

This script combines the functionalities of the SQLite-utils Python package and IronPDF library to manage an SQLite database and generate a PDF document. The following is a step-by-step deconstruction of what the code does:

1. Database Initialization:

• Initializes an SQLite database named "mydatabase4.db" using SQLite-utils.2. Table Creation:
• Defines a table schema with columns `id,` `name,` and `age.`
• Creates a table named "users" in the SQLite database using the defined schema.3. Data Insertion:
• Inserts multiple records into the "users" table using SQLite-utils.4. Data Querying:
• Retrieves all records from the "users" table and stores them in `results.`
• Filters records where the `age` column exceeds 30 and stores them in `filtered_results.`5. PDF Generation:
• Utilizes IronPDF to create a PDF document.
• Constructs HTML content for the PDF document, including headers and table data retrieved from the SQLite database.
• Saves the generated PDF document as "DemoSqliteUtils.pdf."

Overall, this script demonstrates how to leverage SQLite-utils for database management tasks such as table creation, data insertion, and querying, combined with IronPDF for generating PDF documents from dynamic content sourced from an SQLite database in Python applications.

Output

PDF

IronPDF License

IronPDF runs on the Python license key. IronPDF for Python offers a free trial license key to allow users to test its extensive features before purchasing.

Place the License Key at the start of the script before using IronPDF package:

from ironpdf import * 
# Apply your license key
License.LicenseKey = "key"

from ironpdf import * 
# Apply your license key
License.LicenseKey = "key"

Conclusion

Sqlite-utils is a powerful tool for working with SQLite databases. It offers both a CLI and a Python library. Whether you need to quickly manipulate data from the command line or integrate SQLite operations into your Python applications, SQLite provides a flexible and easy-to-use solution.