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)
