sqlite3utils

SQLite3 Database Utility Functions

A set of Python functions to interact with SQLite3 databases. These functions cover various database operations, including table manipulation, data insertion, selection, update, and deletion.

Function Definitions

from sqlite3utils import list_tables

database_url = "mydatabase.db"

Returns a database_url which needs to pass for all the further functions

``list_tables(database_url)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Returns a list of tables in the specified SQLite3 database.

``create_table(database_url, table_name, columns)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Creates a new table in the database with the given name and columns.

``insert(database_url, table_name, data)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Inserts a single row of data into the specified table.

``insert_many(database_url, table_name, data_list)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Inserts multiple rows of data into the specified table.

``select_all(database_url, table_name)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Selects all rows from the specified table.

``select_with_pagination(database_url, table_name, from_index, to_index)``

Selects a range of rows from the specified table with pagination.

select_by_column(database_url, table_name, column_name, column_value)

Selects rows from the specified table where a specific column matches a
given value.

``select(database_url, table_name, where_dict)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Selects rows from the specified table based on the provided conditions.

``update(database_url, table_name, update_dict, where_dict)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Updates rows in the specified table based on the provided conditions
with new values.

``delete(database_url, table_name, where_dict)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Deletes rows from the specified table based on the provided conditions.

``truncate(database_url, table_name)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Truncates (removes all rows from) the specified table.

``delete_table(database_url, table_name)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Deletes the specified table from the database.

``sql_query(database_url, raw_sql_query)``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Executes a raw SQL query on the specified database and returns the
result.

Example Usage
-------------

Below are examples illustrating the usage of some functions. Note that
you need to replace the placeholder values with your actual database
details.

\```python # Example usage of get_database_url database_url =
get_database_url(“localhost”, “5432”, “test”, “root”, “password”)

Example Usage of list_tables
============================

list_of_tables = list_tables(database_url) print(“List of Tables:”,
list_of_tables)

Example Usage of create_table
=============================

create_table(database_url, “new_table”, [“column1 INT”, “column2
VARCHAR(50)”])

Example Usage of insert
=======================

insert(database_url, “existing_table”, {“column1”: 123, “column2”:
“example_data”})

Example Usage of insert_many
============================

data_to_insert_many = [ {“column1”: 456, “column2”: “more_data”},
{“column1”: 789, “column2”: “additional_data”}]
insert_many(database_url, “existing_table”, data_to_insert_many)

Example Usage of select_all
===========================

all_rows = select_all(database_url, “existing_table”) print(“All Rows:”,
all_rows)

Example Usage of select_with_pagination
=======================================

selected_rows = select_with_pagination(database_url, “existing_table”,
1, 5) print(“Selected Rows:”, selected_rows)

Example Usage of select_by_column
=================================

selected_rows_by_column = select_by_column(database_url,
“existing_table”, “column1”, 456) print(“Selected Rows by Column:”,
selected_rows_by_column)

Example Usage of select
=======================

conditions = {“column1”: 123} selected_rows_with_conditions =
select(database_url, “existing_table”, conditions) print(“Selected Rows
with Conditions:”, selected_rows_with_conditions)

Example Usage of update
=======================

update_conditions = {“column1”: 123} update_values = {“column2”:
“updated_data”} update(database_url, “existing_table”, update_values,
update_conditions)

Example Usage of delete
=======================

delete_conditions = {“column1”: 123} delete(database_url,
“existing_table”, delete_conditions)

Example Usage of truncate
=========================

truncate(database_url, “existing_table”)

Example Usage of delete_table
=============================

delete_table(database_url, “existing_table”)

Example Usage of sql_query
==========================

raw_sql_query = “SELECT \* FROM example_table WHERE age > 25” result =
sql_query(database_url, raw_sql_query) print(result)