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)
Raw data
{
"_id": null,
"home_page": "https://github.com/technicalheist/sqlite3utils.git",
"name": "sqlite3utils",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "SQLite,database,CRUD,utility",
"author": "Technical Heist",
"author_email": "contact@technicalheist.com",
"download_url": "https://files.pythonhosted.org/packages/f9/8f/1159a1096a25b7b994876510d58b9af634d1645d0e8fba73a38806100cad/sqlite3utils-0.5.tar.gz",
"platform": null,
"description": "SQLite3 Database Utility Functions\n=====================================\n\nA set of Python functions to interact with SQLite3 databases. These\nfunctions cover various database operations, including table\nmanipulation, data insertion, selection, update, and deletion.\n\nFunction Definitions\n--------------------\nfrom sqlite3utils import list_tables \n\ndatabase_url = \"mydatabase.db\"\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nReturns a database_url which needs to pass for all the further functions\n\n``list_tables(database_url)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nReturns a list of tables in the specified SQLite3 database.\n\n``create_table(database_url, table_name, columns)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nCreates a new table in the database with the given name and columns.\n\n``insert(database_url, table_name, data)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nInserts a single row of data into the specified table.\n\n``insert_many(database_url, table_name, data_list)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nInserts multiple rows of data into the specified table.\n\n``select_all(database_url, table_name)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nSelects all rows from the specified table.\n\n``select_with_pagination(database_url, table_name, from_index, to_index)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nSelects a range of rows from the specified table with pagination.\n\n``select_by_column(database_url, table_name, column_name, column_value)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nSelects rows from the specified table where a specific column matches a\ngiven value.\n\n``select(database_url, table_name, where_dict)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nSelects rows from the specified table based on the provided conditions.\n\n``update(database_url, table_name, update_dict, where_dict)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nUpdates rows in the specified table based on the provided conditions\nwith new values.\n\n``delete(database_url, table_name, where_dict)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nDeletes rows from the specified table based on the provided conditions.\n\n``truncate(database_url, table_name)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nTruncates (removes all rows from) the specified table.\n\n``delete_table(database_url, table_name)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nDeletes the specified table from the database.\n\n``sql_query(database_url, raw_sql_query)``\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nExecutes a raw SQL query on the specified database and returns the\nresult.\n\nExample Usage\n-------------\n\nBelow are examples illustrating the usage of some functions. Note that\nyou need to replace the placeholder values with your actual database\ndetails.\n\n\\```python # Example usage of get_database_url database_url =\nget_database_url(\u201clocalhost\u201d, \u201c5432\u201d, \u201ctest\u201d, \u201croot\u201d, \u201cpassword\u201d)\n\nExample Usage of list_tables\n============================\n\nlist_of_tables = list_tables(database_url) print(\u201cList of Tables:\u201d,\nlist_of_tables)\n\nExample Usage of create_table\n=============================\n\ncreate_table(database_url, \u201cnew_table\u201d, [\u201ccolumn1 INT\u201d, \u201ccolumn2\nVARCHAR(50)\u201d])\n\nExample Usage of insert\n=======================\n\ninsert(database_url, \u201cexisting_table\u201d, {\u201ccolumn1\u201d: 123, \u201ccolumn2\u201d:\n\u201cexample_data\u201d})\n\nExample Usage of insert_many\n============================\n\ndata_to_insert_many = [ {\u201ccolumn1\u201d: 456, \u201ccolumn2\u201d: \u201cmore_data\u201d},\n{\u201ccolumn1\u201d: 789, \u201ccolumn2\u201d: \u201cadditional_data\u201d}]\ninsert_many(database_url, \u201cexisting_table\u201d, data_to_insert_many)\n\nExample Usage of select_all\n===========================\n\nall_rows = select_all(database_url, \u201cexisting_table\u201d) print(\u201cAll Rows:\u201d,\nall_rows)\n\nExample Usage of select_with_pagination\n=======================================\n\nselected_rows = select_with_pagination(database_url, \u201cexisting_table\u201d,\n1, 5) print(\u201cSelected Rows:\u201d, selected_rows)\n\nExample Usage of select_by_column\n=================================\n\nselected_rows_by_column = select_by_column(database_url,\n\u201cexisting_table\u201d, \u201ccolumn1\u201d, 456) print(\u201cSelected Rows by Column:\u201d,\nselected_rows_by_column)\n\nExample Usage of select\n=======================\n\nconditions = {\u201ccolumn1\u201d: 123} selected_rows_with_conditions =\nselect(database_url, \u201cexisting_table\u201d, conditions) print(\u201cSelected Rows\nwith Conditions:\u201d, selected_rows_with_conditions)\n\nExample Usage of update\n=======================\n\nupdate_conditions = {\u201ccolumn1\u201d: 123} update_values = {\u201ccolumn2\u201d:\n\u201cupdated_data\u201d} update(database_url, \u201cexisting_table\u201d, update_values,\nupdate_conditions)\n\nExample Usage of delete\n=======================\n\ndelete_conditions = {\u201ccolumn1\u201d: 123} delete(database_url,\n\u201cexisting_table\u201d, delete_conditions)\n\nExample Usage of truncate\n=========================\n\ntruncate(database_url, \u201cexisting_table\u201d)\n\nExample Usage of delete_table\n=============================\n\ndelete_table(database_url, \u201cexisting_table\u201d)\n\nExample Usage of sql_query\n==========================\n\nraw_sql_query = \u201cSELECT \\* FROM example_table WHERE age > 25\u201d result =\nsql_query(database_url, raw_sql_query) print(result)\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "",
"version": "0.5",
"project_urls": {
"Homepage": "https://github.com/technicalheist/sqlite3utils.git"
},
"split_keywords": [
"sqlite",
"database",
"crud",
"utility"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "56ff33fda4f0a74e51f5d5e2a6ab1d93726c02540ef78f6b020461462d6212cf",
"md5": "e5457c0c421146beba3ca580ce5255f3",
"sha256": "476c04f90999c8a74f379c436750c038b145f31dd3657a2a5d748e2e76d41a0e"
},
"downloads": -1,
"filename": "sqlite3utils-0.5-py3-none-any.whl",
"has_sig": false,
"md5_digest": "e5457c0c421146beba3ca580ce5255f3",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 3695,
"upload_time": "2023-12-01T15:52:09",
"upload_time_iso_8601": "2023-12-01T15:52:09.444396Z",
"url": "https://files.pythonhosted.org/packages/56/ff/33fda4f0a74e51f5d5e2a6ab1d93726c02540ef78f6b020461462d6212cf/sqlite3utils-0.5-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "f98f1159a1096a25b7b994876510d58b9af634d1645d0e8fba73a38806100cad",
"md5": "cec4205d00724f8681baf610fb9f4ef0",
"sha256": "62564dde12d8f4b1b12befe35a32d941d23602e8310490d9c70de69f6b551398"
},
"downloads": -1,
"filename": "sqlite3utils-0.5.tar.gz",
"has_sig": false,
"md5_digest": "cec4205d00724f8681baf610fb9f4ef0",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 3303,
"upload_time": "2023-12-01T15:52:11",
"upload_time_iso_8601": "2023-12-01T15:52:11.464770Z",
"url": "https://files.pythonhosted.org/packages/f9/8f/1159a1096a25b7b994876510d58b9af634d1645d0e8fba73a38806100cad/sqlite3utils-0.5.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-12-01 15:52:11",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "technicalheist",
"github_project": "sqlite3utils",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "sqlite3utils"
}
JSON
