def drop(self): '''Drops the table from the database Arguments: name - name of the table to be dropped Usage: db.dropTable("users") returns an ExecutionCursor object''' query = SQLString.dropTable(self.name) return self.execute(query)
def rename(self, name): '''Renames the current table Arguments: name - new name for the table Usage: db.table("users").rename("old_users") returns an ExecutionCursor object''' query = SQLString.rename(self.name, name) return self.execute(query)
def pragma(self, cmd): '''Executes an SQL PRAGMA function Arguments: cmd - command string Usage: query = db.pragma("INTEGRITY_CHECK") returns an ExecutionCursor object''' query = SQLString.pragma(cmd) return self.execute(query)
def renameColumns(self, **columns): # also need to copy indices and other metadata '''Renames columns in the table Arguments: **columns - dictionary of names and new names Usage: db.table("users").renameColumns(id = "user_id") returns an ExecutionCursor object''' ren_trans = self.db.transaction() temp_name = self.name + str(int(time.time())) column_names = list(columns.items()) old_names, new_names = SQLString.extract(column_names), SQLString.extract(column_names, 1) current_columns = self.columns() keep_columns = list(set(current_columns).difference(old_names)) new_columns = keep_columns + new_names ren_trans.execute("CREATE TABLE {name} ({columns})".format(name = temp_name, columns = ', '.join(new_columns))) ren_trans.execute("INSERT INTO {new_table} SELECT {columns} FROM {old_table}".format(new_table = temp_name, old_table = self.name, columns = ', '.join(current_columns))) self.drop() self.db.table(temp_name).rename(self.name) return ren_trans.commit()
def checkIntegrity(self, max_errors = 100): '''Checks the Database Integrity Arguments: max_errors (default: 100) - number of maximum errors to be displayed Usage: errors = db.checkIntegrity() returns True if there are no errors, or a list of errors''' query = SQLString.checkIntegrity(max_errors) results = self.execute(query).fetch() return True if (len(results) == 1 and results[0]["integrity_check"] == "ok") else results
def insert(self, table = None, **columns): '''Insert rows into the table Arguments: table - table name to insert into **columns - dictionary of column names and values {column_name: value, ...} Usage: db.insert("users", id = 1, username = "******")) returns an ExecutionCursor object''' if not table: table = self.defaultTable query, values = SQLString.insert(table, **columns) return self.execute(query, values)
def addColumns(self, **columns): '''Adds columns to the table Arguments: **columns - dictionary of column names and value types (or a list of [type, default value]) {column_name: value_type, other_column: [second_type, default_value], ...} Usage: db.table("users").addColumns(user_type = "VARCHAR(255)", user_tier = ["INT", 0]) returns an ExecutionCursor object''' add_trans = self.db.transaction() for column, value in columns.items(): query = SQLString.addColumn(self.name, column, value) add_trans.execute(query) return add_trans.commit()
def create(db, name, temporary = False,**columns): '''Creates a new Table in the database Arguments: name - table name temporary (default: False) - whether or not the table should be temporary **columns - dictionary of column names and value types (or a list of [type, default value]) {column_name: value_type, other_column: [second_type, default_value], ...} Usage: db.createTable("users", id = "INT", username = ["VARCHAR(50)", "user"]) returns a Table object''' query = SQLString.createTable(name, temporary, **columns) db.execute(query) return db.table(name, False)
def delete(self, table = None, **options): '''Deletes rows from the table Arguments: table - name of table to delete from equal - dictionary of columns and values to use in WHERE + "=" clauses {column_name: value, ...} like - dictionary of columns and values to use in WHERE + LIKE clauses (column_name: pattern, ...} where - custom WHERE and/or LIKE clause(s) Usage: db.delete("users", equal = {"id": 5}) returns an ExecutionCursor object''' if not table: table = self.defaultTable query, values = SQLString.delete(table, **options) return self.execute(query, values)
def update(self, table = None, equal = None, like = None, where = "1 = 1", **columns): '''Updates rows in the table Arguments: table - name of table to update equal - dictionary of columns and values to use in WHERE + "=" clauses {column_name: value, ...} like - dictionary of columns and values to use in WHERE + LIKE clauses (column_name: pattern, ...} where - custom WHERE and/or LIKE clause(s) **columns - dictionary of column names and values {column_name: value, ...} Usage: db.update("table", equal = {"id": 5}, username = "******") returns an ExecutionCursor object''' if not table: table = self.defaultTable query, values = SQLString.update(table, equal, like, where, **columns) return self.execute(query, tuple(values))
def select(self, table = None, **options): '''Selects rows from the table Arguments: table - table name to select from columns - a list of columns (use ALL for all columns) equal - dictionary of columns and values to use in WHERE + "=" clauses {column_name: value, ...} like - dictionary of columns and values to use in WHERE + LIKE clauses (column_name: pattern, ...} where - custom WHERE and/or LIKE clause(s) Usage: query = db.select("users", columns = ALL, equal = {"id": 1}, like = {"username": "******"}) query = db.select("users", columns = ALL, where = "`ID` = 1 OR `USERNAME` LIKE 'pan%'") returns an ExecutionCursor object''' if not table: table = self.defaultTable query, values = SQLString.select(table, **options) return self.execute(query, values)
def dropTable(self, name): '''Drops a table from the database see Table.drop for further reference''' query = SQLString.dropTable(name) return self.execute(query)