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