Home Integrating SQLite with Python– (Part II of II)

Integrating SQLite with Python– (Part II of II)

Part I can be found here.

Counting Records

To return the total number of records in a table, you can use the COUNT function.

Example:

import sqlite3

def count_records(conn, table_name):
    cursor = conn.cursor()
    cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
    count = cursor.fetchone()[0]
    return count

# Usage
conn = sqlite3.connect('example.db')
print(count_records(conn, 'your_table_name'))

Counting Records with a Condition

To count records that meet a specific condition, you can add a WHERE clause.

Example:

import sqlite3

def count_records(conn, table_name):
    cursor = conn.cursor()
    cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
    count = cursor.fetchone()[0]
    return count

# Usage
conn = sqlite3.connect('example.db')
print(count_records(conn, 'your_table_name'))

Finding the Maximum, Minimum, Average, and Sum

You can use SQL functions like MAX, MIN, AVG, and SUM to get different types of aggregate data.

Example:

def get_aggregate(conn, table_name, column_name, function):
    cursor = conn.cursor()
    query = f"SELECT {function}({column_name}) FROM {table_name}"
    cursor.execute(query)
    result = cursor.fetchone()[0]
    return result

# Usage
print(get_aggregate(conn, 'your_table_name', 'age', 'MAX'))
print(get_aggregate(conn, 'your_table_name', 'age', 'MIN'))
print(get_aggregate(conn, 'your_table_name', 'age', 'AVG'))
print(get_aggregate(conn, 'your_table_name', 'salary', 'SUM'))

Grouping and Counting

To count the number of records for each distinct value in a column, you can use the GROUP BY clause.

Example:

def count_group_by(conn, table_name, column_name):
    cursor = conn.cursor()
    query = f"SELECT {column_name}, COUNT(*) FROM {table_name} GROUP BY {column_name}"
    cursor.execute(query)
    results = cursor.fetchall()
    return results

# Usage
for row in count_group_by(conn, 'your_table_name', 'department'):
    print(f"{row[0]}: {row[1]}")

Combining Aggregate Functions with Conditions

You can combine these functions to get more complex statistics.

Example:

def get_aggregate_with_condition(conn, table_name, column_name, function, condition):
    cursor = conn.cursor()
    query = f"SELECT {function}({column_name}) FROM {table_name} WHERE {condition}"
    cursor.execute(query)
    result = cursor.fetchone()[0]
    return result

# Usage
condition = "department = 'Sales'"
print(get_aggregate_with_condition(conn, 'your_table_name', 'salary', 'AVG', condition))

Tips for Usage:

SQL Injection Protection: Always use parameterized queries or bound parameters to protect against SQL injection.
Error Handling: Add error handling to catch and handle exceptions such as database connection errors or SQL syntax errors.
Database Indexes: Ensure that your database is indexed appropriately to optimize the performance of your queries, especially for large datasets.

By incorporating these features, you can build a more robust and feature-rich SQLite and Python application that provides valuable insights into your data.

Thank you for following along with this tutorial. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!

You can find the same tutorial on Medium.com