def table_drop(conn: Connection, table_name: str) -> None: with conn: table_name = _safe_table_name(table_name) conn.execute(f"DROP TABLE IF EXISTS {table_name}") conn.execute( f"DELETE FROM {_SCHEMA_TABLE_NAME} WHERE table_name = '{table_name}'" )
def table_merge( conn: Connection, table_names: List[str], on: List[str], how: str = "inner", into_table: str = None, ) -> Optional[Iterable[Dict[str, Any]]]: table_names = [_safe_table_name(name) for name in table_names] assert len(table_names) == len( set(table_names)), f"Table names must all be unique" on = [_safe_column_name(col) for col in on] left = table_names[0] statement_join = f"SELECT * FROM {left}" for right in table_names[1:]: clause_on = " AND ".join(f"{left}.{col} = {right}.{col}" for col in on) statement_join += f" {how.upper()} JOIN {right} ON ({clause_on})" with conn: if into_table: combined_schema = {} for table_name in table_names: combined_schema.update(_fetch_table_schema(conn, table_name)) # Make sure the receiving table exists and is empty into_table = _safe_table_name(into_table) table_drop(conn, into_table) table_create(conn, into_table, combined_schema) # Coalesce all shared columns if len(table_names) > 1: coalesce_helper = lambda x: ",".join(f"{table}.{x}" for table in table_names) select_map = { col: _safe_column_name(col) for col in combined_schema.keys() } select_map.update({ col: f"COALESCE({coalesce_helper(col)}) AS {col}" for col in on }) clause_select = ",".join(select_map[col] for col in combined_schema.keys()) statement_join = statement_join.replace( "SELECT *", f"SELECT {clause_select}") # Pre-pend the insert statement so the output goes into the table conn.execute(f"INSERT INTO {into_table} " + statement_join) # Otherwise perform the merge and yield records from the cursor else: cursor = conn.execute(statement_join) return _output_named_records(cursor)
def __ensure_table(self, connection: Connection) -> None: info = connection.execute(*compile( Q(T.sqlite_master).fields( '*').where((T.sqlite_master.name == 'version') & (T.sqlite_master.type == 'table')))).fetchone() if not info: connection.execute( 'CREATE TABLE version (number INTEGER NOT NULL)') connection.execute( *Q(T(self.table), result=Result( compile=compile)).insert({T(self.table).number: 0}))
def _statement_insert_record_tuple( conn: Connection, table_name: str, columns: Tuple[str], record: Tuple[str], replace: bool = False, ) -> None: table_name = _safe_table_name(table_name) verb_insert = "INSERT " + ("OR REPLACE " if replace else "") placeholders = ", ".join("?" for _ in columns) column_names = ", ".join(_safe_column_name(name) for name in columns) conn.execute( f"{verb_insert} INTO {table_name} ({column_names}) VALUES ({placeholders})", record)
def table_select_all(conn: Connection, table_name: str, sort_by: List[str] = None, sort_ascending: bool = True) -> Iterable[Dict[str, Any]]: table_name = _safe_table_name(table_name) statement_select = f"SELECT * FROM {table_name}" if sort_by: sort_by = ",".join(_safe_column_name(col) for col in sort_by) statement_select += f" ORDER BY {sort_by} {'ASC' if sort_ascending else 'DESC'}" cursor = conn.execute(statement_select) return _output_named_records(cursor)
def table_create(conn: Connection, table_name: str, schema: Dict[str, str]) -> None: table_name = _safe_table_name(table_name) sql_schema = ", ".join(f"{_safe_column_name(name)} {dtype}" for name, dtype in schema.items()) with conn: # Create new table (ignore if exists) conn.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({sql_schema})") # Every time a table is created, store its schema in our helper table _statement_insert_record_dict( conn, _SCHEMA_TABLE_NAME, { "table_name": table_name, "schema_json": json.dumps(schema) }, replace=True, )
def _get_recipe(db: Connection, uuid: UUID, version: int) -> Optional[GetRecipe]: recipe = db.execute(GET_RECIPE, (str(uuid), version)).fetchone() if recipe is None: return None ingredients = db.execute(GET_INGREDIENTS, (str(recipe[0]))) steps = db.execute(GET_STEPS, (str(recipe[0]))) return GetRecipe( uuid=UUID(recipe[1]), version=recipe[2], name=recipe[3], duration_minute=recipe[4], ingredients=[ Ingredient( name=i[0], numerator=i[1], denominator=i[2], measure=Measure[i[3]], ) for i in ingredients ], steps=[Step(name=s[0], duration_minute=s[1]) for s in steps], )
def _fetch_table_schema(conn: Connection, table_name: str) -> Dict[str, str]: with conn: schema_json = conn.execute( f'SELECT schema_json FROM {_SCHEMA_TABLE_NAME} WHERE table_name = "{table_name}"' ).fetchone()[0] return json.loads(schema_json)
def create_table(connection: Connection, query: str) -> None: connection.execute(query) connection.commit()