Data Encryption Using AES Algorithm

1. Data Encryption Using AES AlgorithmEnsuring data security is a matter of utmost importance for every company. When performing data tracking on websites, the unique identifier for customers (Customer ID) often needs to be stored in our database. The purpose of this is to correlate and analyze customer behavior data on the website with their order data.

import pandas as pd
import numpy as np
from Crypto.Cipher import AES
import base64
df = pd.read_excel("C:/Users/idoit/Desktop/解密后数据.xlsx")
# Key (key), Initialization Vector (iv) CBC mode encryption
BLOCK_SIZE = 16  # Bytes
pad = lambda s: s + (BLOCK_SIZE - len(s) % BLOCK_SIZE) * 
                chr(BLOCK_SIZE - len(s) % BLOCK_SIZE)
unpad = lambda s: s[:-ord(s[len(s) - 1:])]
key = '_-yu_xuan_3507-_'
vi = 'fs_com_phone2016'
def AES_Encrypt(key, data):
    data = pad(data)  # String padding
    cipher = AES.new(key.encode('utf8'), AES.MODE_CBC, vi.encode('utf8'))
    encryptedbytes = cipher.encrypt(data.encode('utf8'))  # The encrypted data is of bytes type, encoded using Base64, returning byte string
    encodestrs = base64.b64encode(encryptedbytes)  # Decode byte string in utf-8
    enctext = encodestrs.decode('utf8')
    return enctext
def AES_Decrypt(key, data):
    data = data.encode('utf8')
    encodebytes = base64.decodebytes(data)  # Convert encrypted data to bytes type
    cipher = AES.new(key.encode('utf8'), AES.MODE_CBC, vi.encode('utf8'))
    text_decrypted = cipher.decrypt(encodebytes)  # Remove padding
    text_decrypted = unpad(text_decrypted)
    text_decrypted = text_decrypted.decode('utf8')
    print(text_decrypted)
    return text_decrypted
a = []
for x in df["解密"].tolist():
    b = AES_Encrypt(key, x)
    a.append(b)
df["解密再加密"] = np.array(a)
df.to_excel('C:/Users/idoit/Desktop/0313.xlsx', index=None)

2. Reading and Writing Excel Files and Splitting Intervals by Column Data Encryption Using AES Algorithm Split the list [1,2,3,4,5,6,7,8] into intervals of three: [1,3],[3,5],[5,7],[7,8], padding the last number if there are fewer than three.

import pandas as pd
# Read Excel file
data = pd.read_excel(r'\\172.16.11.5\产品中心\（月影）战区战力排行榜\区服列表.xlsx')
# Sort by '区服ID' column
data_sorted = data['区服ID'].sort_values()
# Take one value every 200
interval_points = data_sorted[::200].tolist()
# If the last interval is incomplete, add the last value
if data_sorted.iloc[-1] not in interval_points:
    interval_points.append(data_sorted.iloc[-1])
# Generate interval pairs
intervals = [(interval_points[i], interval_points[i + 1]) for i in range(len(interval_points) - 1)]
# Convert to DataFrame
intervals_df = pd.DataFrame(intervals, columns=['开始区间ID', '结束区间ID'])
# Export to Excel file
output_path = r'\\172.16.11.5\产品中心\（月影）战区战力排行榜\区服ID导出数据.xlsx'
intervals_df.to_excel(output_path, index=False)
print(f"Interval data has been successfully exported to {output_path}")

3. Extracting the Top 50 Combat Power Data Grouped by Region. Data Encryption Using AES Algorithm

import pandas as pd
# Create sample data
data = {
    '赛区': ['A', 'A', 'A', 'A', 'A', 'B'],
    '战斗力': [100, 200, 150, 250, 300, 350]}
server_merge = pd.DataFrame(data)
# Execute code
top_50_per_region = server_merge.groupby('赛区').apply(lambda x: x.nlargest(2, '战斗力')).reset_index(drop=True)
server_merge

4. Importing Excel Files into Database and Adding Table Comments in Database

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import text
import re
config = {
    'host': 'your_host',
    'port': your_port,
    'user': 'your_username',
    'password': 'your_password',
    'db': 'your_database',}
engine = create_engine(f'mysql+pymysql://{config["user"]}:{config["password"]}@{config["host"]}:{config["port"]}/{config["db"]}?charset=utf8mb4')
# Read Excel file
df = pd.read_excel(r'\\172.16.11.5\产品中心\档案管理\合并玩家战力榜.xlsx')
df = df.drop_duplicates(keep=False)
def extract_numbers(row):
    match = re.search(r'-(\d+)', row['server'])
    if match:
        return match.group(1)
    return None
df['server_id'] = df['服务器'].apply(lambda x: re.search(r'-(\d+)', x).group(1) if re.search(r'-(\d+)', x) else None)
df.columns = [
    'ranking',
    'account',
    'account_id',
    'server_area_name',
    'server',
    'character_id',
    'character_name',
    'profession',
    'level',
    'recharge_amount',
    'recharge_count',
    'combat_power',
    'guild',
    'last_recharge_time',
    'last_login_time',
    'registration_time',
    'registration_ip',
    'server_id']
# Convert time columns to timestamps
time_columns = ['last_recharge_time', 'last_login_time', 'registration_time']
for col in time_columns:
    df[col] = pd.to_datetime(df[col], errors='coerce').apply(lambda x: x.timestamp() if pd.notnull(x) else None)
# Write data to database
table_name = 'data_player_rank'
df.to_sql(table_name, engine, index=False, if_exists='replace', schema=None)
# Add table comment
add_table_comment_query = text(f"ALTER TABLE {table_name} COMMENT 'Merged player combat power data, updated every 15 minutes'")
# Modify field comments
alter_query_modify_columns = text(f"""ALTER TABLE {table_name}
MODIFY COLUMN ranking INT COMMENT 'Ranking',
MODIFY COLUMN account VARCHAR(255) COMMENT 'Account',
MODIFY COLUMN account_id VARCHAR(255) COMMENT 'Account ID',
MODIFY COLUMN server_area_name VARCHAR(255) COMMENT 'Server Name',
MODIFY COLUMN server VARCHAR(255) COMMENT 'Server',
MODIFY COLUMN character_id VARCHAR(255) COMMENT 'Character ID',
MODIFY COLUMN character_name VARCHAR(255) COMMENT 'Character Name',
MODIFY COLUMN profession VARCHAR(255) COMMENT 'Profession',
MODIFY COLUMN level INT COMMENT 'Level',
MODIFY COLUMN recharge_amount DECIMAL(10, 2) COMMENT 'Recharge Amount',
MODIFY COLUMN recharge_count INT COMMENT 'Recharge Count',
MODIFY COLUMN combat_power BIGINT COMMENT 'Combat Power',
MODIFY COLUMN guild VARCHAR(255) COMMENT 'Guild',
MODIFY COLUMN last_recharge_time INT COMMENT 'Last Recharge Time',
MODIFY COLUMN last_login_time INT COMMENT 'Last Login Time',
MODIFY COLUMN registration_time INT COMMENT 'Registration Time',
MODIFY COLUMN registration_ip VARCHAR(255) COMMENT 'Registration IP',
MODIFY COLUMN server_id INT COMMENT 'Server ID';"""
# Add primary key
add_primary_key_query = text(f"""ALTER TABLE {table_name}
ADD PRIMARY KEY (character_id);"""
try:
    with engine.connect() as conn:
        conn.execute(add_table_comment_query)
        conn.execute(alter_query_modify_columns)
        conn.execute(add_primary_key_query)
        print("Successfully modified table comments, field comments, and primary key")
except Exception as e:
    print(f"Operation failed: {e}")

Related posts

Leave a Comment Cancel reply