Мануал Corsair - Market Fill Up / Load

[orohimaru2]

Автор не я взял с Англ сайта
Приветствую. Есть несколько постов о том, как заполнить рынок, и хотя я ценю это, я внёс некоторые изменения и хотел бы поделиться тем, что у меня есть.

1. Для этого требуется Python 3.9.5 (он установлен вместе с Corsair)
. 2. Вам понадобится pip для установки необходимых компонентов (это очень просто освоить).
3. Вам понадобится этот файл Python и MarketItems.xlsx, в котором перечислены товары для добавления на рынок.
4. Эта программа рандомизирует значения, чтобы они попадали в заданный диапазон (например, для товара A в диапазоне от 100 до 200 будет выбрано случайное число между этими двумя значениями). Всё отлично.
5. Обновите строку 13, указав, куда вы поместили таблицу Excel, иначе программа не запустится.

Python

import pandas as pd
import pyodbc
import random
import datetime
import os # For checking if the Excel file exists

# --- Configuration ---
# Database Connection Details
SERVER_NAME = 'localhost' # Your specified server name
DATABASE_NAME = 'SA_BETA_TRADEDB_0002' # Your confirmed database name

# Excel File Details
EXCEL_FILE_PATH = r'D:\Sql\MarketItems.xlsx' # Your specified Excel location and name
EXCEL_SHEET_NAME = 'Sheet1' # Confirm this is the correct sheet name in your Excel file

# --- Fixed Values for Market Insertion (from C# code) ---
_KEY_TYPE = 0
_IS_SEALED = 0
_NEED_MATERIAL_COUNT = 0
_SOLD_COUNT = 0
_ACCUMULATE_MONEY_COUNT = 0
_NATION_CODE = 12
_SERVER_NO = 12
_USER_NO = 1 # Your admin/system account ID for these market listings
_IS_RING_BUFF = 0

def populate_market_from_excel(server, database, excel_path, sheet_name):
    """
    Reads market item data from an Excel file, filters for unique primary keys,
    and inserts it into the SQL Server database.
    """
    if not os.path.exists(excel_path):
        print(f"Error: Excel file not found at '{excel_path}'")
        return

    cnxn = None # Initialize connection to None for finally block

    try:
        # Read data from Excel using pandas
        # header=0 means the first row is the header
        # dtype=str to read all as strings initially to prevent conversion issues, then convert later
        df = pd.read_excel(excel_path, sheet_name=sheet_name, header=0, dtype=str)
        print(f"Successfully loaded {len(df)} rows from Excel.")

        # Filter out rows where essential columns might be missing or invalid
        df = df.dropna(subset=['_mainKey', '_subKey', '_pricePerOne'])
        df['_mainKey'] = pd.to_numeric(df['_mainKey'], errors='coerce').astype('Int64')
        df['_subKey'] = pd.to_numeric(df['_subKey'], errors='coerce').astype('Int64')
        df['_pricePerOne'] = pd.to_numeric(df['_pricePerOne'], errors='coerce').astype('Int64')
       
        # Remove rows where conversion failed (NaNs introduced by coerce)
        df = df.dropna(subset=['_mainKey', '_subKey', '_pricePerOne'])

        # --- CRITICAL: Remove duplicates based on the market's composite primary key ---
        # Temporarily add the hardcoded PK components to the DataFrame for the drop_duplicates check.
        # These columns (_keyType, _isSealed, _userNo) are hardcoded in the SQL INSERT statement.
        df['_keyType_temp'] = _KEY_TYPE     # Value: 0
        df['_isSealed_temp'] = _IS_SEALED   # Value: 0
        df['_userNo_temp'] = _USER_NO       # Value: 1 (your admin account)

        # Based on the error message (9275, 0, 1, 0, 0) and your script's values,
        # the Primary Key columns in the database are likely in this order:
        # (_mainKey, _subKey, _userNo, _isSealed, _keyType)
        pk_columns_for_drop = ['_mainKey', '_subKey', '_userNo_temp', '_isSealed_temp', '_keyType_temp']

        initial_rows_after_cleaning = len(df)
        df.drop_duplicates(subset=pk_columns_for_drop, inplace=True)
        if len(df) < initial_rows_after_cleaning:
            print(f"Removed {initial_rows_after_cleaning - len(df)} duplicate rows from Excel data based on the market's primary key columns before insertion.")
        # --- END CRITICAL CORRECTION ---

        if df.empty:
            print("No valid data rows found in Excel after cleaning and duplicate removal. Exiting.")
            return

        # Establish SQL Server connection using Windows Authentication
        # Make sure you have the 'ODBC Driver 17 for SQL Server' installed.
        # If using SQL Server Authentication, use:
        # conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID=your_username;PWD=your_password'
        conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};Trusted_Connection=yes'
       
        cnxn = pyodbc.connect(conn_str)
        cursor = cnxn.cursor()
        print("Successfully connected to SQL Server.")

        # --- Clear existing "system" market entries before re-populating ---
        # This deletes ALL entries listed by your designated admin/system account (_USER_NO).
        # It ensures a clean refresh for items populated by this script.
        delete_query = f"DELETE FROM [PaGamePrivate].[TblWorldMarketBiddingSell] WHERE _userNo = {_USER_NO};"
        cursor.execute(delete_query)
        cnxn.commit() # Commit the delete operation immediately
        print(f"Cleared existing market entries for _userNo={_USER_NO}.")

        # Prepare the INSERT statement using placeholders for parameters
        insert_query = """
        INSERT INTO [PaGamePrivate].[TblWorldMarketBiddingSell]
               ([_keyType]
               ,[_mainKey]
               ,[_subKey]
               ,[_isSealed]
               ,[_chooseSubKey]
               ,[_needMaterialCount]
               ,[_leftCount]
               ,[_soldCount]
               ,[_pricePerOne]
               ,[_accumulateMoneyCount]
               ,[_nationCode]
               ,[_serverNo]
               ,[_userNo]
               ,[_registerDate]
               ,[_isRingBuff])
         VALUES
               (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
        """
       
        inserted_count = 0
        # Iterate over DataFrame rows and insert into database
        for index, row in df.iterrows():
            main_key = row['_mainKey']
            sub_key = row['_subKey']
            price_per_one = row['_pricePerOne']

            # Generate random quantity (leftCount) between 5000 and 10000
            left_count = random.randint(5000, 10000)
           
            # Current timestamp for registerDate
            register_date = datetime.datetime.now()

            # Execute the insert query with parameters.
            # The order of parameters here MUST match the order of '?' placeholders in insert_query
            cursor.execute(insert_query,
                           _KEY_TYPE,               # Mapped to _keyType
                           main_key,                # Mapped to _mainKey
                           sub_key,                 # Mapped to _subKey
                           _IS_SEALED,              # Mapped to _isSealed
                           sub_key,                 # Mapped to _chooseSubKey (uses _subKey value)
                           _NEED_MATERIAL_COUNT,
                           left_count,
                           _SOLD_COUNT,
                           price_per_one,
                           _ACCUMULATE_MONEY_COUNT,
                           _NATION_CODE,
                           _SERVER_NO,
                           _USER_NO,                # Mapped to _userNo
                           register_date,
                           _IS_RING_BUFF)
            inserted_count += 1

        cnxn.commit() # Commit all insertions in one go
        print(f"Successfully inserted {inserted_count} market items.")

    except pyodbc.Error as ex:
        sqlstate = ex.args[0]
        print(f"Database error: {sqlstate} - {ex}")
        if cnxn:
            cnxn.rollback() # Rollback all changes if an error occurred during insertion
    except Exception as ex:
        print(f"An unexpected error occurred: {ex}")
    finally:
        if cnxn:
            cnxn.close()
            print("Database connection closed.")

if __name__ == "__main__":
    populate_market_from_excel(SERVER_NAME, DATABASE_NAME, EXCEL_FILE_PATH, EXCEL_SHEET_NAME)

=== Market Items ===
Я прикрепил MarketItems.txt.
Загрузите его и измените тип файла (.txt на .xlsx), и у вас будет нужный вам файл.