SQLite operations helper classes

SQLite operations are a bit tricky. In order to ease the SQLite related operations, there are two helper classes to assist.

Mainly, only Sqlite class in sqlite module is used. The module and class separation is made due to better code managament.

All methods can be directly called from core.

Example:

from ozcore import core

core.sql.read(...)

Sqlite Class

sqlite helper methods

class ozcore.core.data.sqlite.sqlite.Sqlite[source]

Sqlite helper methods using ORM class

Set the engine before using or if .db is in the current folder, creates the engine automatically

Warning

set_engine() before proceeding any other method!

usage:

from ozcore import core

core.sql.set_engine(engine=core.sql.engines.engine_name)

core.sql.tables.table_name
core.sql.path_to_database

core.sql.read(table_name, engine, limit=100)
create_engine(path: Union[str, pathlib.PosixPath])[source]

Creates an engine.

Parameters

path – str | posixpath, path to the sqlite db

Returns

Engine object

Note

Allowed extensions: “db”,”sqlite”,”sqlite3”

set_engine(engine: Union[sqlalchemy.engine.base.Engine, str, pathlib.PosixPath], return_engine: bool = False)[source]

Set the engine to work with.

Parameters
  • engine – sqlalchemy engine, Posixpath, or str

  • return_engine – bool, default False, returns the engine set

Returns

  • fills self engine

  • if returns=True, returns the engine set

Warning

To work with core.sql methods, you should first set the engine!

usage:

# with an engine name
core.sql.set_engine(engine=core.sql.engines._ENGINE_NAME)

# with an relative path
core.sql.set_engine(engine="./sample.db")
property metadata

returns sqlalchemy metadata of the current engine

property tables

enum tables in a database

Returns

a table name from Enum also, assigns table names as a list in self.tables_list

usage:

core.sql.tables.table_name
columns(table_name)[source]

enum columns in a table

Parameters

table_name – str|enum

Returns

enum columns (.name as str name, .value as sa col object) also assigns this query to self.columns_list as a dict

column_exists(table_name, col_name)[source]

checks if a column name exists in a table

Parameters
  • table_name – str|enum

  • col_name – str|enum

Returns

boolean

table_exists(table_name)[source]

checks if a table name exists in a database

Parameters

table_name – str|enum

Returns

boolean

property path_to_database

path to database from current engine

Parameters

engine

read(table_name, limit=None, index_column=None)[source]

read a table

Parameters
  • table_name – str

  • limit – int, default None

Returns

a dataframe with table results


ORM Class

sqlite alter operations

class ozcore.core.data.sqlite.orm.ORM[source]

Sqlite alter operations with ORM and Alembic

Warning

The sa_ prefixed methods are for advanced usage

sa_add_a_column(table_name, column_name, type_=<class 'sqlalchemy.sql.sqltypes.TEXT'>)[source]

alter table: add a new column in the given table

Parameters
  • table_name – str

  • column_name – str, name for the new column

Returns

  • creates the column in the table as Text type

  • returns Error if exception

Warning

new column is created as sqlalchemy.sql.sqltypes.Text

sa_drop_a_column(table_name, column_name)[source]

alter table: drop a column from a table

Parameters
  • table_name – str

  • column_name – str, as column name or a sqlalchemy Column object

Returns

drops the column in the given table

sa_change_column_type(table_name, column_name, type_)[source]

alter table: change a column’s type

Parameters
  • table_name – str

  • column_name – str, as column name or a sqlalchemy Column object

  • type – Sqlalchemy Type

Returns

  • True if type is changed successfully

usage:

import core

core.sql.("table_name", "column_name", type_=sa.TEXT)
# type changed to SQLalchemy Text type
sa_table(table_name: str)[source]

Sqalchemy Table object of a given table

Parameters

table_name – str or Sqlalchemy Table object

Returns

Sqlalchemy Table object of the set engine

sa_column(table_name: str, column_name: str)[source]

Sqalchemy Coulumn object of a given table

Parameters
  • table_name – str

  • column_name – str

sa_update_a_record(table_name, column_name, compare_column, compare_val, val)[source]

update a single record in a given column of a table

Parameters
  • table_name – str or Sqlalchemy Table object

  • column_name – str or Sqlalchemy Column object

  • compare_column – str, the common unique columnn to compare record

  • compare_val – mixed, a value to compare in compare_column

  • val – mixed, the new value of the record

sa_update_a_column(table_name, column_name, compare_column, source_df)[source]

update a column’s records based on a given df_slice

Parameters
  • table_name – str or Sqlalchemy Table object

  • column_name – str or Sqlalchemy Column object

  • compare_column – str, the common unique columnn to compare record

  • compare_val – mixed, a value to compare in compare_column

  • source_df – source records as a DataFrame or Series, to update the Table

Warning

index of source_df is ignored