def closeall(self):
"""close the connection to the database"""
if self.konnect.is_connected():
self.kursor.close()
self.konnect.close()
if self.verbose:
echo.info("MariaDB Server Has Disconnected. Session Ended.")
def renumber(self):
"""renumber all rows starting with 1. expects conventional primary key.
fallback tries to renumber by a column named 'id' else it
fails gracefully.
"""
primary_key = self.primary
try:
if primary_key:
self.kursor.execute(
f'ALTER TABLE {self._name} DROP COLUMN {primary_key}')
self.kursor.execute(
f'ALTER TABLE {self._name} ADD COLUMN {primary_key} INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST'
)
if self.verbose:
echo.info(f"{self._name} {primary_key} index reset")
else:
primary_key = 'id'
self.kursor.execute(
f'ALTER TABLE {self._name} ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST'
)
if self.verbose:
echo.info(f"{self._name} {primary_key} index created")
except SQLError as error:
echo.alert(error)
def write(self, **kwargs):
"""insert data into the table
ARGUMENTS:
uses kwargs:
key: n/a: column name
value: str: data written to specified column
USAGE:
db.table.write(**data)
db.table.write(column=data, column=data, column=data)
"""
data = tuple(kwargs.values())
columns = ', '.join(kwargs.keys())
values = ('%s, ' * len(data)).strip(', ')
try:
self.kursor.execute(
f'INSERT IGNORE INTO {self._name} ({columns}) VALUES ({values})',
data)
if self.verbose:
echo.info(
f"{self.kursor.rowcount} record inserted into {self._name}"
)
except SQLError as error:
echo.alert(error)
def commit(self):
"""commit the last transaction(s) and make changes permanent"""
try:
self.konnect.commit()
if self.verbose:
echo.info("Data Commit")
except SQLError as error:
echo.alert(error)
def rollback(self):
"""roll back the current transaction and cancel its changes"""
try:
self.konnect.rollback()
if self.verbose:
echo.info("Rollback Successful")
except SQLError as error:
echo.alert(error)
def rename(self, column, new_name):
"""rename an column in the table"""
try:
self.kursor.execute(
f'ALTER TABLE {self._name} RENAME COLUMN {column} TO {new_name}'
)
if self.verbose:
echo.info(f"Column {column} has been renamed {new_name}")
except SQLError as error:
echo.alert(error)
def execute(self, query):
"""execute any mysql command or statement"""
try:
self.kursor.execute(query)
if self.kursor.with_rows:
return tuple(chain(*self.kursor.fetchall()))
else:
if self.verbose:
echo.info(
f"Number of affected rows: {self.kursor.rowcount}")
except SQLError as error:
echo.alert(error)
def add(self, column, datatype, location='last'):
"""add a column to table
ARGUMENTS:
column: str: name of the new column
datatype: str: data type of new column i.e varchar(255)
location: str: where in the table the column will be inserted
options are:
'first', 'last' or f'after {column}'
USAGE:
db.table.add('lastname', 'varchar(100)', location='after firstname')
"""
self.kursor.execute(
f"ALTER TABLE {self._name} ADD COLUMN {column} {datatype} {location}"
)
echo.info(f"Added Column {column} To {self._name}")
def drop(self, column):
"""drop a column from the table
ARGUMENTS:
column: str: name of the new column
USAGE:
db.table.drop('lastname')
"""
try:
self.kursor.execute(
f'ALTER TABLE {self._name} DROP COLUMN {column}')
if self.verbose:
echo.info(f"Dropped column {column} from {self._name}")
self.renumber()
except SQLError as error:
echo.alert(error)
def delete(self, id, value):
"""delete a record in the table
ARGUMENTS:
id: str: column containing row ids (usually named id)
value: str: int: value that indicates the row to be deleted
USAGE:
db.table.delete('user_id', '12')
"""
try:
self.kursor.execute(
f"DELETE FROM {self._name} WHERE {id}={value}")
if self.verbose:
echo.info(f"Deleted row {value} from {self._name}")
except SQLError as error:
echo.alert(error)
def connect(self, database=None):
if database:
self.config.update({'database': database})
connection = self.config.get('database', self.host)
try:
self.__server_connect__()
if connection != self.host and self.konnect.is_connected():
self.__update_tables__()
if self.verbose:
echo.info(f"MashaDB {self.version} Connected to {connection}")
if connection == self.host:
echo.info(("Use obj.connect(database='database_name'"
" to connect to a specific database."))
except SQLError as error:
echo.alert(error)
def drop(self, table):
"""remove specified table from the database.
ARGUMENTS:
table: str: name of the table to be deleted
USAGE:
db.drop('users')
"""
try:
delattr(self, table)
self.kursor.execute(f"DROP TABLE IF EXISTS {table}")
except AttributeError:
echo.alert(f"The table '{table}' does not exist")
except SQLError as error:
echo.alert(error)
else:
if self.verbose:
echo.info(f"Table {table} has been deleted.")
def create(self, table: str, **kwargs: str) -> None:
"""create a new table in the database.
ARGUMENTS:
uses positional and keyword arguments:
table: str: the new table name
keyword: the column name
value: str: the column dataype; sql statement
USAGE:
db.create('table', **kwargs)
db.create('table', column='datatype', column='datatype')
db.create('users', id='INT AUTO_INCREMENT PRIMARY KEY')
import Columns, Primary
primary = Primary()
name = Column('VARCHAR(100)')
email = Column('VARCHAR(255', unique=True)
db.create(id=primary.key, Name=name.column, Email=email.column)
"""
statement = []
for key, value in kwargs.items():
if value.endswith('PRIMARY KEY'):
statement.append(f"{key} {value}({key})")
else:
statement.append(f"{key} {value}")
try:
self.kursor.execute(
f"CREATE TABLE IF NOT EXISTS {table}({', '.join(statement)})")
if self.verbose:
echo.info(f'Created Table {table}')
except SQLError as error:
echo.alert(f"{error}")
else:
setattr(self, table, self.Table(table))
def update(self, id, **kwargs):
"""update columns in a table row with new data
ARGUMENTS:
id: int: str: the row number or id
kwargs: str: column_name=new_data
USAGE:
db.table.update('10', name='Someone', email='*****@*****.**')
"""
data = tuple(kwargs.values())
columns = f"{'=%s, '.join(kwargs.keys())}=%s"
try:
self.kursor.execute(
f"UPDATE {self._name} SET {columns} WHERE {self.primary}={id}",
data)
if self.verbose:
echo.info(
f"Updated Row: {id} Column(s): {columns.replace('=%s', '')}"
)
except SQLError as error:
echo.alert(error)
def rename(self, table, new_name):
"""rename a table in the database.
ARGUMENTS:
table: str: name of the target table
new_name: str: new name for the target table
USAGE:
db.rename('users', 'superusers')
"""
try:
delattr(self, table)
self.kursor.execute(f'ALTER TABLE {table} RENAME TO {new_name}')
except AttributeError:
echo.alert(f"The table '{table}' does not exist")
except SQLError as error:
echo.alert(error)
else:
setattr(self, new_name, self.Table(new_name))
echo.info(f"Table {table} has been renamed {new_name}")