def showTbl(tbl, db="", sep=("%", 30), err=True, verbose=False):
tools.setStatus("Parsing parameters", verbose=verbose, err=err)
dbs = tools.parseDbs(db)
sep = tools.parseDbs(sep)
tbls = tools.parseTables(tbl, dbs)
for i, db in enumerate(dbs):
tools.setStatus(f"For database: {db}", verbose=verbose, err=err)
for tbl in tbls[i]:
data = None
try:
tools.setStatus(f"Retrieving records for table: {tbl}",
verbose=verbose,
err=err)
data = execute([f"SELECT * FROM {tbl}"], db).get[0][0]
except Exception as e:
if err:
raise e
tools.setStatus("Preparing table", verbose=verbose, err=err)
_tbl = tbl
print(f"{sep[0]*sep[1]} [Table: {_tbl}] {sep[0]*sep[1]}")
tbl = PrettyTable()
tbl.field_names = getCNames(_tbl, db)[0][0]
del _tbl
[tbl.add_row(x) for x in data]
tools.setStatus("Printing table", verbose=verbose, err=err)
print(f"{tbl}\n\n")
def execFile(file, db="", out=True, err=True, verbose=False):
tools.setStatus("Pasing objects", verbose=verbose, err=err)
datab = tools.parseDbs(db)
files = tools.parseTables(file, datab)
for i, db in enumerate(datab):
for file in files[i]:
try:
tools.setStatus("Reading file", verbose=verbose, err=err)
file = open(file)
sql = file.read().replace("\n", "").split(";")
try:
sql.remove("")
except:
pass
file.close()
tools.setStatus(f"Executing file", verbose=verbose, err=err)
result = execute([sql], db, verbose=verbose).get
if out:
tools.setStatus("Printing results",
verbose=verbose,
err=err)
print(result)
except Exception as e:
if err:
raise exception.UnknownError(e)
else:
tools.setStatus(e, verbose=True, err=err)
return False
return True
def delDb(db="", err=True, verbose=False):
db = tools.parseDbs(db)
for x in db:
try:
tools.setStatus(f"Deleting: {x}", verbose=verbose, err=err)
execute(
[f"DROP DATABASE {x};"],
["mysql"],
verbose=verbose,
)
except Exception as e:
if err:
raise e
return True
def createDb(db, err=True, verbose=False):
db = tools.parseDbs(db)
for x in db:
try:
tools.setStatus(f"Creating: {x}", verbose=verbose, err=err)
execute(
[f"CREATE DATABASE {x};"],
["mysql"],
verbose=verbose,
)
except Exception as e:
if err:
raise e
return True
def getDbSize(db="", returnDict=False):
"""
Returns the database size in bytes.
"""
db = tools.parseDbs(db, base="sqlite")
final = []
for i in range(len(db)):
tools.setStatus(f"Getting size of {db[i]}")
final.append(
f"{stat(db[i]).st_size} bytes ({stat(db[i]).st_size * 10**(-6)} MB)"
)
if returnDict:
tools.setStatus("Packing into dictionary")
final = dict(zip(db, final))
return final
def connect(
host,
user,
pword,
db=[],
charset="",
validateDb=True,
err=True,
verbose=False,
):
charset = constants.__charset__ if not charset else charset
tools.setStatus("Checking credentials", verbose=verbose, err=err)
dbs = tools.parseDbs(db)
for db in dbs:
try:
driver.connect(
host=host,
user=user,
password=pword,
db=db if db else "mysql",
charset=charset,
).close()
except Exception:
if err:
raise exception.ConnectionError(
"Cannot connect to MySQL server, it may be server or credential problem"
)
tools.setStatus(
f"Credentials are correct, connecting to database: {db}",
verbose=verbose,
err=err,
)
constants.__credentials__ = (host, user, pword)
constants.__charset__ = charset
tools.setStatus(
"Manipulating database for SQL-Tools execution",
verbose=verbose,
err=err,
)
if tools.checkInstance(dbs, list, tuple):
constants.__dbMysql__.extend(dbs)
else:
if err:
raise exception.DatabaseError(
"Invalid database dtype, it should be str or array")
else:
tools.setStatus(
"Invalid database dtype, it should be str or array",
verbose=True,
err=err,
)
def getTNames(db="", err=True, returnDict=False):
"""
Returns the table names in the provided database.
You can provided multiple database paths..
"""
dbs = tools.parseDbs(db, base="sqlite")
final = []
for _, db in enumerate(dbs):
tables = []
tools.setStatus(f"Getting table names for {db}")
queryResult = execute(
f"SELECT name FROM sqlite_master WHERE type = 'table';", db).get
if "ERROR IN SQL QUERY --->" not in queryResult:
try:
tables.append(queryResult[0].T[0].T[0].tolist())
except IndexError:
tables.append([[]])
else:
if err:
raise ValueError(queryResult)
final.append(tables[0])
if returnDict:
tools.setStatus("Packing into dictionary")
final = dict(zip(dbs, final))
tools.setStatus("Returning results")
return final
def getCNames(tbl, db="", err=True, returnDict=False, __len=False):
"""
Returns the column names of the provided table.
You can provided multiple table names and multiple database paths to get the result in group by providing the arguments in a list.
"""
dbs = tools.parseDbs(db, base="sqlite")
tbls = tools.parseTables(tbl, dbs)
final = []
for i, db in enumerate(dbs):
tables = []
for tbl in tbls[i]:
tools.setStatus(f"Retrieving records for {tbl}")
try:
queryResult = execute(f"PRAGMA table_info({tbl});", db).get
if "ERROR IN SQL QUERY --->" not in queryResult:
try:
data = queryResult[0][0].T[1].tolist()
except IndexError:
data = []
tables.append(data) if not __len else tables.append(
len(data))
except Exception as e:
if err:
raise e
final.append(tables)
if returnDict:
tools.setStatus("Packing into dictionary")
final = dict(zip(dbs, final))
tools.setStatus("Returning results")
return final
def getNRecords(tbl, db="", err=True, returnDict=False):
"""
Returns the no. of records in the provided table.
You can provided multiple table names and multiple database paths to get the result in group by providing the arguments in a list.
"""
dbs = tools.parseDbs(db, base="sqlite")
tbls = tools.parseTables(tbl, dbs)
final = []
for i, db in enumerate(dbs):
tables = []
for tbl in tbls[i]:
tools.setStatus(f"Retrieving records for {tbl}")
try:
queryResult = execute(f"SELECT * FROM {tbl};", db).get
if "ERROR IN SQL QUERY --->" not in queryResult:
tables.append(len(queryResult[0][0].T[0].tolist()))
except Exception as e:
if err:
raise e
final.append(tables)
if returnDict:
tools.setStatus("Packing into dictionary")
final = dict(zip(dbs, final))
tools.setStatus("Returning results")
return final
def getTCommand(tbl, db="", err=True):
"""
Returns the command for creating the provided table in the database accordingly.
You can provided multiple table names and multiple database paths to get the result in group by providing the arguments in a list.
"""
dbs = tools.parseDbs(db, base="sqlite")
tbls = tools.parseTables(tbl, dbs)
final = []
for i, db in enumerate(dbs):
tables = []
for tbl in tbls[i]:
tools.setStatus(f"Retrieving records for {tbl}")
queryResult = execute(
f"SELECT sql FROM sqlite_master WHERE type = 'table' and name='{tbl.strip()}';",
db,
).list
if queryResult == [[[]]]:
queryResult = execute(
f"SELECT sql FROM sqlite_master WHERE type = 'table' and name='{tbl.lower().strip()}';",
db,
).list
if queryResult == [[[]]]:
queryResult = execute(
f"SELECT sql FROM sqlite_master WHERE type = 'table' and name='{tbl.upper().strip()}';",
db,
).list
if queryResult == [[[]]]:
raise exception.TableError(
"Please provide a valid table name.")
if "ERROR IN SQL QUERY --->" not in queryResult:
tables.append(queryResult[0][0][0][0])
else:
if err:
raise exception.TableError(
"Invalid table name, no such table exists")
final.append(tables)
tools.setStatus("Returning results")
return final
def isIdentical(db="", err=True, verbose=False):
db = tools.parseDbs(db)
tables = [execute(["SHOW TABLES"], x).list for x in db]
try:
tables = list(filter(lambda x: x != tables[0], tables))
tools.setStatus("Comparing tables properties",
verbose=verbose,
err=err)
if len(tables) != 0:
return False
else:
tools.setStatus("Comparing tables records",
verbose=verbose,
err=err)
tables = [
execute(["SHOW TABLES"], x, verbose=verbose).list[0][0]
for x in db
]
tables = [[y[0] for y in x] for x in tables]
records = []
for i, x in enumerate(tables):
for table in x:
records.append(
execute([f"SELECT * FROM {table}"],
db[i],
verbose=verbose).list)
records = list(filter(lambda x: x != records[0], records))
if len(records) != 0:
del tables, records
return False
else:
del tables, records
return True
except Exception as e:
if err:
raise e
def disconnect(db=[], err=True, verbose=False):
db = tools.parseDbs(db)
tools.setStatus("Disconnecting database", verbose=verbose, err=err)
constants.__dbMysql__ = [x for x in constants.__dbMysql__ if x not in db]
def csvToTbl(db,
table,
csv,
primaryKey="",
foreignKey="",
err=True,
verbose=False):
tools.setStatus("Parsing parameters", verbose=verbose, err=err)
dbs = tools.parseDbs(db)
tables = tools.parseTables(table, db)
csvs = tools.parseTables(csv, db)
for i, db in enumerate(dbs):
for k, table in enumerate(tables[i]):
tools.setStatus(f"Reading CSV: {csvs[i][k]}",
verbose=verbose,
err=err)
data = pd.read_csv(csvs[i][k])
if primaryKey == foreignKey:
if err:
exception.KeyError("Primary & Foreign keys are same.")
else:
tools.setStatus("Primary & Foreign keys are same.",
verbose=True)
exit()
attributes = ""
for column in data.columns.values:
# &Generating the query
tools.setStatus("Generating temporary query for table",
verbose=verbose,
err=err)
attributes += f"{column} TEXT, "
attributes = attributes[:-2]
try:
tools.setStatus(f"Creating table: {table}",
verbose=verbose,
err=err)
execute(f"CREATE TABLE {table} ({attributes});", db=db)
except Exception as e:
if err:
raise e
else:
tools.setStatus(e, verbose=True, err=err)
del attributes
# Inejecting the records
records = data.values.tolist()
final = []
for record in records:
st = ""
for column in record:
column = str(column).replace('"', '"')
st += f'"{column}", '
final.append(st[:-2])
tools.setStatus("Inserting records", verbose=verbose, err=err)
for record in final:
record = record.replace("nan", "NULL")
execute([f"INSERT INTO {table} VALUES ({record})"],
verbose=verbose,
db=db)
tools.setStatus("Manipulating table for end use",
verbose=verbose,
err=err)
dtypes = constants.dtypeMap
dataDtypes = data.dtypes
for column in data.columns.values:
if column.lower() == primaryKey.lower():
dtypeColumn = dataDtypes[column]
# &Checking whether the type is object, if yes, then setting it to VARCHAR with length of max length of the value in the column
dtypeColumn = (
f"VARCHAR({np.vectorize(len)(data.select_dtypes(include=[object]).values.astype(str)).max(axis=0)[data.columns.get_loc(primaryKey)]})"
if dtypeColumn == "object" else dtypeColumn)
# &Checking whether the dtype is changed to VARCHAR or not
raw = True if dtypeColumn in dtypes.keys() else False
# &Generating the query
execute(
f"ALTER TABLE {table} MODIFY COLUMN {column} {dtypes[f'{dtypeColumn}'] if raw else dtypeColumn} PRIMARY KEY",
db=db,
)
elif column.lower() == foreignKey.lower():
execute(
f"ALTER TABLE {table} MODIFY COLUMN {column} {dtypes[f'{dataDtypes[column]}']} FOREIGN KEY",
db=db,
)
else:
execute(
f"ALTER TABLE {table} MODIFY COLUMN {column} {dtypes[f'{dataDtypes[column]}']}",
db=db,
)