def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
# -- TO IMPLEMENT --
# Hint. Google "get primary key columns mysql"
# Hint. THE ORDER OF THE COLUMNS IN THE KEY DEFINITION MATTERS.
sql, args = ('SHOW KEYS FROM ' + self._full_table_name +
' WHERE Key_name = %s', ('PRIMARY'))
res, data = dbutils.run_q(sql, args=args, conn=self._cnx)
if RDBDataTable.is_empty(data):
print('Failed to fetch primary keys')
return None
list_of_key_columns = []
for elem in data:
list_of_key_columns.append(elem['Column_name'])
# NOTE: I'm not sure whether this function should return a value or set the object variable
# In this case, I decide to just return a value and use this value to set the _key_columns variable in __int__
return list_of_key_columns
def get_tables(db_name):
_connect_info = copy.deepcopy(_default_connect_info)
_connect_info['db'] = db_name
_conn = pymysql.connect(**_connect_info)
sql = "show tables"
res, d = dbutils.run_q(sql, conn=_conn)
return d
def find_by_template(self,
template,
field_list=None,
limit=None,
offset=None,
order_by=None,
commit=True):
"""
:param template: A dictionary of the form { "field1" : value1, "field2": value2, ...}
:param field_list: A list of request fields of the form, ['fielda', 'fieldb', ...]
:param limit: Do not worry about this for now.
:param offset: Do not worry about this for now.
:param order_by: Do not worry about this for now.
:return: A list containing dictionaries. A dictionary is in the list representing each record
that matches the template. The dictionary only contains the requested fields.
"""
result = None
try:
sql, args = dbutils.create_select(self._full_table_name,
template=template,
fields=field_list,
limit=limit,
offset=offset)
res, data = dbutils.run_q(sql=sql,
args=args,
conn=self._cnx,
commit=True,
fetch=True)
except Exception as e:
print("Exception e = ", e)
raise e
return list(data)
def get_databases():
"""
:return: A list of databases/schema at this endpoint.
"""
q = "SHOW DATABASES"
schemas = dbutils.run_q(sql=q, conn=_cnx)
return schemas
def get_databases():
"""
:return: A list of databases/schema at this endpoint.
"""
_conn = pymysql.connect(**_default_connect_info)
sql = "show databases"
res, d = dbutils.run_q(sql, conn=_conn)
return d
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
q = "select count(*) from " + self._full_table_name
result, data = dbutils.run_q(q, conn=self._cnx)
return data
def get_databases():
"""
:return: A list of databases/schema at this endpoint.
"""
sql = "show databases"
res, data = dbutils.run_q(sql, conn=_conn)
return data
def get_databases():
"""
:return: A list of databases/schema at this endpoint.
"""
q = "SHOW databases"
res, d = dbutils.run_q(q, conn=_conn)
return d
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
sql = "select count(*) from " + self._table_name
res, data = dbutils.run_q(sql, fetch=True, conn=self._cnx)
return data
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
# -- TO IMPLEMENT --
q = "select COUNT(*) from " + self._table_name
res, data = dbutils.run_q(q, conn=self._cnx)
return data[0].get('COUNT(*)')
def get_tables(db):
q = "show tables from %s;" % db
res, d = dbutils.run_q(q, conn=_conn)
res = []
for x in d:
for _, y in x.items():
res.append(y)
return res
def insert(self, new_record):
"""
:param new_record: A dictionary representing a row to add to the set of records.
:return: None
"""
# Get the list of columns.
sql, args = dbutils.create_insert(self._full_table_name, new_record)
res, d = dbutils.run_q(sql, args=args, conn=self._cnx)
return res
def get_databases():
"""
:return: A list of databases/schema at this endpoint.
"""
# https://stackoverflow.com/questions/44893565/get-list-of-mysql-databases-with-python
databases = "show databases;"
res, d = dbutils.run_q(databases, conn=_conn)
return [x['Database'] for x in d]
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
q = "SELECT COUNT(*) FROM " + self._full_table_name
row_affected, data = dbutils.run_q(q, conn=self._cnx)
self._row_count = data
return data
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
sql = "SELECT {} FROM {};".format("COUNT(1)", self._full_table_name)
res, data = dbutils.run_q(sql=sql, args=None, conn=self._cnx, commit=True, fetch=True)
return list(data[0].values())[0]
def get_tables(dbname):
"""
:param dbname: database name
:return: A list of tables in the database.
"""
q = f"SHOW TABLES FROM {dbname}"
tables = dbutils.run_q(sql=q, conn=_cnx)
return tables
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
# -- TO IMPLEMENT --
sql = f"SELECT COUNT(*) FROM {self._full_table_name}"
_, data = dbutils.run_q(sql, conn=self._cnx)
self._row_count = data[0]['COUNT(*)']
def get_databases():
"""
:return: A list of databases/schema at this endpoint.
"""
# -- TO IMPLEMENT --
query = "show databases"
res, data = dbutils.run_q(query, conn=_conn)
return data
def get_tables(database):
"""
:return: A list of databases/schema at this endpoint.
"""
# -- TO IMPLEMENT --
query = f"show tables in {database}"
res, data = dbutils.run_q(query, conn=_conn)
return data
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
sql = "select count(*) as count from " + self._table_name
res, d = dbutils.run_q(sql=sql,
conn=self._cnx,
commit=True,
fetch=True)
return d[0]['count']
def update_by_template(self, template, new_values):
"""
:param template: A template that defines which matching rows to update.
:param new_values: A dictionary containing fields and the values to set for the corresponding fields
in the records.
:return: The number of rows updates.
"""
sql, args = dbutils.create_update(self._full_table_name, template=template, changed_cols=new_values)
res, d = dbutils.run_q(sql, args=args, conn=self._cnx, commit=True)
return res
def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
key_list = []
sql = "SHOW KEYS FROM "+self._table_name+" WHERE Key_name = 'PRIMARY'"
res, data = dbutils.run_q(sql, fetch=True, conn=self._cnx)
for i in data:
key_list.append(i['Column_name'])
return key_list
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
# -- TO IMPLEMENT --
query = "select count(*) from " + self._full_table_name
res, data = dbutils.run_q(query, conn=_conn)
out = int(list(data[0].values())[0])
self._row_count = out
return out
def get_tables(dbname):
cnx = dbutils.get_connection({
'host': 'localhost',
'user': '******',
'password': '******',
'db': 'lahman2019clean',
'port': 3306
})
q = "show tables in " + dbname
res, tables = dbutils.run_q(q, conn=cnx)
tables = list(i[0] for i in tables)
return tables
def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
# -- TO IMPLEMENT --
q = "SHOW KEYS FROM " + self._full_table_name + " WHERE Key_name = 'PRIMARY'"
row_affected, data = dbutils.run_q(q, conn=self._cnx)
print(data)
self._key_columns = []
for each in data:
self._key_columns.append(each["Column_name"])
return self._key_columns
def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
# -- TO IMPLEMENT --
# Hint. Google "get primary key columns mysql"
# Hint. THE ORDER OF THE COLUMNS IN THE KEY DEFINITION MATTERS.
sql = f"SHOW KEYS FROM {self._full_table_name} WHERE Key_name = 'PRIMARY'"
_, data = dbutils.run_q(sql, conn=self._cnx)
print(data)
primary_key = [d['Column_name'] for d in data]
self._key_columns = primary_key
def delete_by_template(self, template):
"""
Deletes all records that match the template.
:param template: A template.
:return: A count of the rows deleted.
"""
try:
sql, args = dbutils.create_select(self._full_table_name,
template=template,
is_select=False)
res, d = dbutils.run_q(sql, args=args, conn=self._cnx, commit=True)
return res
except Exception as e:
print("Got exception e = ", e)
raise e
def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
# -- TO IMPLEMENT --
# Hint. Google "get primary key columns mysql"
# Hint. THE ORDER OF THE COLUMNS IN THE KEY DEFINITION MATTERS.
q = "show keys in " + self._table_name + " where Key_name = 'PRIMARY'"
res, data = dbutils.run_q(q, conn=self._cnx)
data.sort(key=lambda x: x["Seq_in_index"])
result = [x["Column_name"] for x in data]
return result
def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
# -- TO IMPLEMENT --
# Hint. Google "get primary key columns mysql"
# Hint. THE ORDER OF THE COLUMNS IN THE KEY DEFINITION MATTERS.
sql = "SHOW KEYS FROM " + self._table_name + " WHERE Key_name = 'PRIMARY'"
res, d = dbutils.run_q(sql=sql,
conn=self._cnx,
commit=True,
fetch=True)
keys = []
for row in d:
keys.append(row['Column_name'])
return keys
def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
# -- TO IMPLEMENT --
# Hint. Google "get primary key columns mysql"
# Hint. THE ORDER OF THE COLUMNS IN THE KEY DEFINITION MATTERS.
sql = "SHOW KEYS FROM {} WHERE KEY_NAME = 'PRIMARY'".format(self._full_table_name)
res, data = dbutils.run_q(sql=sql, args=None, conn=self._cnx, commit=True, fetch=True)
res = []
for d in data:
res.append(d['Column_name'])
return res
