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)
