Easy SQL

Reference documentation (auto-generated by mkdocstrings) for the py_simple.easy_sql module.

`py_simple.easy_sql`

Beginner friendly helpers for handling databases.

`EasySqlError`

Bases: Exception

Custom exception for py_simple SQL helpers.

Raised when a database operation fails - for example, when the connection to the database file cannot be established.

Parameters:

Name	Type	Description	Default
`message`	`str`	Description of what went wrong.	required

`ExperimentalWarning`

Bases: UserWarning

Raised when calling a py_simple function that isn't yet covered by tests.

`conditional_run_select(connection, cursor, table_name, to_select, condition, params=(), close_conn_after=False)`

Runs a SELECT query with a WHERE condition and returns matching rows.

Validates table_name and to_select first - only letters, numbers, and underscores are allowed - to guard against SQL injection before building the query string.

Parameters:

Name	Type	Description	Default
`connection`	`Connection`	Open connection to the database.	required
`cursor`	`Cursor`	Cursor for executing SQL statements.	required
`table_name`	`str`	Name of the table to select from.	required
`to_select`	`str`	Column name(s) to select, comma-separated, or "*" for all columns.	required
`condition`	`str`	SQL condition with `?` placeholders for values used in the WHERE clause.	required
`params`	`tuple`	Values to substitute into the `?` placeholders in `condition`, in order. Defaults to an empty tuple.	`()`
`close_conn_after`	`bool`	If True, closes `connection` after the query runs. Defaults to False.	`False`

Returns:

Type	Description
	list[tuple]: All rows matching the condition.

Raises:

Type	Description
`EasySqlError`	If `table_name` or `to_select` contain anything other than letters, numbers, underscores, or "*", or if the query itself fails.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db, conditional_run_select

connection, cursor = open_db('mydb.db')
rows = conditional_run_select(connection, cursor, 'users',
                               'name, email', "age > ?", (18,))

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
rows = cursor.execute(
    "SELECT name, email FROM users WHERE age > ?",
    (18,)).fetchall()

`delete_all_from_table(connection, cursor, table_name, close_conn_after=False)`

Deletes all rows from a table, leaving the table itself intact.

Validates table_name first - only letters, numbers, and underscores are allowed - to guard against SQL injection before building the query string.

Parameters:

Name	Type	Description	Default
`connection`	`Connection`	Open connection to the database.	required
`cursor`	`Cursor`	Cursor for executing SQL statements.	required
`table_name`	`str`	Name of the table to empty.	required
`close_conn_after`	`bool`	If True, closes `connection` after the delete runs. Defaults to False.	`False`

Raises:

Type	Description
`EasySqlError`	If `table_name` contains anything other than letters, numbers, or underscores, or if the delete itself fails.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db, delete_all_from_table

connection, cursor = open_db('mydb.db')
delete_all_from_table(connection, cursor, 'users')

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute("DELETE FROM users")

`open_db(db_filepath)`

Opens a connection to a SQLite database file.

Parameters:

Name	Type	Description	Default
`db_filepath`	`str`	Path to the database file.	required

Returns:

Type	Description
`tuple[Connection, Cursor]`	tuple[sqlite3.Connection, sqlite3.Cursor]: The open connection and a cursor for executing SQL statements.

Raises:

Type	Description
`EasySqlError`	If the connection cannot be established.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db

connection, cursor = open_db('mydb.db')

import sqlite3

conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()

`run_delete(connection, cursor, table_name, condition, params=(), close_conn_after=False)`

Deletes rows from a table matching a condition.

Validates table_name first - only letters, numbers, and underscores are allowed -to guard against SQL injection before building the query string.

Parameters:

Name	Type	Description	Default
`connection`	`Connection`	Open connection to the database.	required
`cursor`	`Cursor`	Cursor for executing SQL statements.	required
`table_name`	`str`	Name of the table to delete from.	required
`condition`	`str`	SQL condition with `?` placeholders for values used in the WHERE clause.	required
`params`	`tuple`	Values to substitute into the `?` placeholders in `condition`, in order. Defaults to an empty tuple.	`()`
`close_conn_after`	`bool`	If True, closes `connection` after the delete runs. Defaults to False.	`False`

Raises:

Type	Description
`EasySqlError`	If `table_name` contains anything other than letters, numbers, or underscores, or if the delete itself fails.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db, run_delete

connection, cursor = open_db('mydb.db')
run_delete(connection, cursor, 'users', "name = ?", ('Ada',))

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute("DELETE FROM users WHERE name = ?", ('Ada',))

`run_insert(connection, cursor, to_insert, table_name, columns, close_conn_after=False)`

Inserts a single row into a table.

Validates table_name and columns first - only letters, numbers, and underscores are allowed - to guard against SQL injection before building the query string. Values in to_insert are passed as parameters, not interpolated into the query.

Parameters:

Name	Type	Description	Default
`connection`	`Connection`	Open connection to the database.	required
`cursor`	`Cursor`	Cursor for executing SQL statements.	required
`to_insert`	`list`	Values to insert, in the same order as `columns`.	required
`table_name`	`str`	Name of the table to insert into.	required
`columns`	`list`	Column names the values in `to_insert` map to.	required
`close_conn_after`	`bool`	If True, closes `connection` after the insert runs. Defaults to False.	`False`

Raises:

Type	Description
`EasySqlError`	If `table_name` or `columns` contain anything other than letters, numbers, or underscores, or if the insert itself fails.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db, run_insert

connection, cursor = open_db('mydb.db')
run_insert(connection, cursor, ['Ada', 'ada@example.com'],
           'users', ['name', 'email'])

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)",
               ['Ada', 'ada@example.com'])
conn.commit()

`run_select(connection, cursor, table_name, to_select, close_conn_after=False)`

Runs a SELECT query against a table and returns all matching rows.

Validates table_name and to_select first - only letters, numbers, and underscores are allowed (or * for to_select) - to guard against SQL injection before building the query string.

Parameters:

Name	Type	Description	Default
`connection`	`Connection`	Open connection to the database.	required
`cursor`	`Cursor`	Cursor for executing SQL statements.	required
`table_name`	`str`	Name of the table to select from.	required
`to_select`	`str`	Column name(s) to select, comma-separated, or "*" for all columns.	required
`close_conn_after`	`bool`	If True, closes `connection` after the query runs. Defaults to False.	`False`

Returns:

Type	Description
	list[tuple]: All rows returned by the query.

Raises:

Type	Description
`EasySqlError`	If `table_name` or `to_select` contain anything other than letters, numbers, underscores, or "*", or if the query itself fails.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db, run_select

connection, cursor = open_db('mydb.db')
rows = run_select(connection, cursor, 'users', 'name, email')

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
rows = cursor.execute("SELECT name, email FROM users").fetchall()

`run_update(connection, cursor, table_name, updates, condition, params=(), close_conn_after=False)`

Updates rows in a table matching a condition.

Validates table_name and the keys of updates first - only letters, numbers, and underscores are allowed - to guard against SQL injection before building the query string. Values in updates and params are passed as parameters, not interpolated into the query.

Parameters:

Name	Type	Description	Default
`connection`	`Connection`	Open connection to the database.	required
`cursor`	`Cursor`	Cursor for executing SQL statements.	required
`table_name`	`str`	Name of the table to update.	required
`updates`	`dict`	Column names mapped to their new values, e.g. {"age": 30, "city": "Boston"}.	required
`condition`	`str`	SQL condition with `?` placeholders for values used in the WHERE clause.	required
`params`	`tuple`	Values to substitute into the `?` placeholders in `condition`, in order. Defaults to an empty tuple.	`()`
`close_conn_after`	`bool`	If True, closes `connection` after the update runs. Defaults to False.	`False`

Raises:

Type	Description
`EasySqlError`	If `table_name` or any key in `updates` contains anything other than letters, numbers, or underscores, or if the update itself fails.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db, run_update

connection, cursor = open_db('mydb.db')
run_update(connection, cursor, 'users',
           {"age": 30, "city": "Boston"}, "name = ?", ('Ada',))

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute(
    "UPDATE users SET age = ?, city = ? WHERE name = ?",
    (30, "Boston", 'Ada'))
conn.commit()

`table_exists(connection, cursor, table_name)`

Checks whether a table exists in the SQLite database.

The table_name is validated before being used in the query because SQLite does not support parameterized table names. The validation ensures that only letters, numbers, and underscores are allowed, preventing invalid input from being interpolated into the query.

Parameters:

Name	Type	Description	Default
`connection`	`Connection`	Open connection to the database.	required
`cursor`	`Cursor`	Cursor for executing SQL statements.	required
`table_name`	`str`	Name of the table to check.	required

Returns:

Name	Type	Description
`bool`	`bool`	True if the table exists, otherwise False.

Raises:

Type	Description
`EasySqlError`	If `table_name` contains anything other than letters, numbers, or underscores, or if checking the table fails.

Example

The Py_simple WayThe Traditional Way

from py_simple import open_db, table_exists

connection, cursor = open_db('mydb.db')

if table_exists(connection, cursor, 'users'):
    print("Table exists")

import sqlite3

conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()

result = cursor.execute(
    "SELECT name FROM sqlite_master "
    "WHERE type = 'table' AND name = ?",
    ('users',)
).fetchone()

if result:
    print("Table exists")