def connect_to_server(connection_params):
"""
Establish a server connection and return a connection object.
inputs:
connection_params: dict of connection parameters
host: server address - defaults to localhost if not supplied
user: username - defaults to root if not supplied
password: required
returns:
connection object on success
raises
ConnectionError on failure
"""
try:
host = connection_params.pop('host', 'localhost')
user = connection_params.pop('user', 'root')
password = connection_params.pop('password')
if connection_params:
raise ConnectionError('Unknown connection parameter(s):', connection_params)
connection = connector.connect(
host=host,
user=user,
password=password
)
return connection
except KeyError as e:
raise ConnectionError('Missing connection parameter') from e
except Exception as e:
raise ConnectionError('Error connecting to server instance') from e
def table_maintenance(connection_params, maintenance_type='check'):
"""
https://dev.mysql.com/doc/refman/8.0/en/check-table.html
https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html
https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html
inputs:
connection_params: dict of connection parameters
database: database name
host: server address - defaults to localhost if not supplied
user: username - defaults to root if not supplied
*will prompt for password
type: type of maintenance [check, optimize, analyze] - see docs
returns:
result record
"""
host = connection_params.pop('host', 'localhost')
user = connection_params.pop('user', 'root')
database = connection_params.pop('database')
if connection_params:
raise ConnectionError('Unknown connection parameter(s):', connection_params)
cmd = "mysqlcheck -h {} -u {} -p --{} --databases {}".format(host, user, maintenance_type, database)
results = os.system(cmd)
return results
def connect(database,
server='localhost',
driver='SQL Server Native Client 11.0',
trusted_connection=True):
"""
Establish a database connection and return a connection object.
inputs:
database: database name
server: server address - defaults to localhost
driver: database driver - defaults to SQL Server Native Client 11.0
trusted_connection: defaults to True
returns:
connection object
raises:
ConnectionError on failure
"""
try:
connection_config = "Driver={};Server={};Database={};Trusted_Connection={};".format(
driver,
server,
database,
trusted_connection,
)
connection = pyodbc.connect(connection_config)
return connection
except Exception as e:
raise ConnectionError('Error connecting to database:') from e
def connect(database, host, user, password):
"""
Establish a database connection and return a connection object.
inputs:
database: database name
host: server address - defaults to localhost
username: defaults to postgres (root)
password:
returns:
connection object
raises:
ConnectionError on failure
"""
try:
connection = psycopg2.connect(database=database,
host=host,
user=user,
password=password)
return connection
except Exception as e:
raise ConnectionError('Error connecting to database:') from e
def backup_database(connection_params, database, backup_path):
"""
Backup database to sql file
inputs:
connection_params: dict of connection parameters
host: server address - defaults to localhost if not supplied
user: username - defaults to root if not supplied
database: database name
backup_path: full file path and name of backup (no extension)
returns:
True on success
raises:
DatabaseBackupError on failure
NOTE: Requires MySQL\MySQL Server <VER>\bin directory on PATH
NOTE: Will prompt for password
"""
try:
host = connection_params.pop('host', 'localhost')
user = connection_params.pop('user', 'root')
if connection_params:
raise ConnectionError('Unknown connection parameter(s):', connection_params)
if not backup_path.endswith('.sql'):
backup_path += '.sql'
cmd = "mysqldump -h {} -u {} -p {} > {}".format(host, user, database, backup_path)
os.system(cmd)
zip_cmd = ''
# os.system(zip_cmd)
return True
except KeyError as e:
raise ConnectionError('Missing connection parameter') from e
except Exception as e:
raise DatabaseBackupError('Error connecting to database') from e
def connect(database):
"""
Establish a database connection and return a connection object.
This really just wraps sqlite3.connect() for polymorphism
inputs:
database: database full file path
returns:
connection object
raises:
ConnectionError on failure
"""
try:
connection = sqlite3.connect(database)
return connection
except Exception as e:
raise ConnectionError('Error connecting to database:') from e