def db_user_create(conn, cursor, user_info): sql = "CREATE USER %s IDENTIFIED BY %s" args = [user_info["user"], user_info["password"]] # build sql arguments ret = sql_execute(conn, cursor, sql, args) logger.debug("User: %s is created." % user_info["user"]) return ret
def drop_tables(cursor, tables_list): if len(tables_list) == 0: # none table to drop logger.error("None table to drop.") return -1 try: for table in tables_list: # fetch one table a time cursor.execute("DROP TABLE %s" % table) logger.debug("SQL Result: %s", cursor.fetchone()) except pymysql.Error as e: logger.error("Error %d: %s" % (e.args[0], e.args[1])) return 1
def insert_row_nocommit(cursor, insert_data): if len(insert_data) == 0: # none data to insert logger.debug("None data to insert.") return [] pmkey = [] # store inserted row id for table, data in insert_data.items(): # fetch table name and data list for one_script in data: # fetch a row, keys coresponding to column name sql = build_insertsql(table, one_script) # execute INSERT SQL, values of dict as the arguments pmkey.append( sql_execute_keyreturn(None, cursor, sql, one_script.values())) return pmkey # return rows' primary keys list
def db_restore(restore_info): db_user = restore_info["user"] db_passwd = restore_info["password"] backup_file = restore_info["file"] db_list = restore_info["dblist"] if len(db_list) == 0: # None database to restore logger.debug("None database to resotre.") return elif len(db_list) == 1: # Only 1 database to restore cmd = "mysql -u %s --password=%s %s < %s" % (db_user, db_passwd, db_list[0], backup_file) else: # multi databases to restore cmd = "mysql -u %s --password=%s < %s" % (db_user, db_passwd, backup_file) os.system(cmd)
def db_backup(backup_info): db_user = backup_info["user"] db_passwd = backup_info["password"] backup_file = backup_info["file"] db_list = backup_info["dblist"] if len(db_list) == 0: logger.debug("None db to backup.") return -1 db_join = ' '.join(db_list) # build database list as "db1 db2 ..." logger.debug(db_join) # use mysqldump to backup databases. use "--password" to assign password without inputting while mysqldump cmd = "mysqldump -u %s --password=%s --databases %s > %s" % ( db_user, db_passwd, db_join, backup_file) os.system(cmd) return 1
def insert_many(cursor, insert_data): if len(insert_data) == 0: # none data to insert logger.debug("None data to insert.") return [] args = [] pmkey = [] # sql= "" for table, data in insert_data.items(): # fetch table name and data list if len(data) == 0: return [] else: sql = build_insertsql(table, data[0]) for one_script in data: # fetch a row, keys coresponding to column name args.append(one_script.values()) # build arguments list pmkey.append(sql_executemany(None, cursor, sql, args)) return pmkey # return rows' primary keys list
def insert_row(conn, cursor, insert_data): if len(insert_data) == 0: # none data to insert logger.debug("None data to insert.") return [] pmkey = [] # store inserted row id for table, data in insert_data.items(): # fetch table name and data list for one_script in data: # fetch a row, keys coresponding to column name # qmarks = ','.join(["%s"] * len(one_script)) # build VALUES sub string: "%s, %s, ..." # cols = ','.join(one_script.keys()) # build COLUMNS sub string: "col1, col2, ..." # sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, cols, qmarks) # build orginal INSERT SQL with table, columns and %s sql = build_insertsql(table, one_script) # execute INSERT SQL, values of dict as the arguments pmkey.append( sql_execute_keyreturn(None, cursor, sql, one_script.values())) conn.commit() return pmkey # return rows' primary keys list
def insert_row_foreignkey(conn, cursor, insert_data): if len(insert_data) == 0: # none data to insert logger.debug("None data to insert.") return -1 row_id = 0 # row primary key id # fetch each table's data: {"table_name":{"data":{"col1":val1, "col2":val2}, "foreignkey":"col"}}]} for one_script in insert_data: for table, one_row in one_script.items( ): # fetch table name and row data data_dict = one_row # copy data for adding foreignkey key-val pair if "foreignkey" in one_row: # check if table has a foreign key data_dict = one_row["data"] data_dict[one_row[ "foreignkey"]] = row_id # last table's insert-row id as foreignkey value sql = build_insertsql(table, data_dict) logger.debug(data_dict) pmkey = sql_execute_keyreturn(conn, cursor, sql, data_dict.values()) row_id = pmkey # store insert-row id logger.debug(pmkey) return row_id # last inserted primary key
def set_passwd(conn, cursor, user_info): sql = "SET PASSWORD FOR %s = PASSWORD(%s)" args = [user_info["user"], user_info["password"]] # build sql arguments sql_execute(conn, cursor, sql, args) logger.debug("Password of user: %s has changed." % user_info["user"])
def db_user_delete(conn, cursor, user): sql = "DROP USER %s" args = [user] sql_execute(conn, cursor, sql, args) logger.debug("User: %s is deleted." % user)