Alright, chalo bachay. Welcome to the real deal.

COURSE: Pakistan Ka Pehla Professional Trading Bot Course

MODULE 8: Database & Monitoring — Trade Logging Aur Performance Tracking

LESSON 8.2: CRUD Operations — Positions Aur Trades Manage Karo

Hook: Kyun Zaroori Hai?

Seedhi baat: agar tum har trade ko record nahi kar rahay, tou tum trading nahi, gambling kar rahay ho. Data ke baghair tum andhay ho. Tumhara database tumhara 'munshi' hai jo har cheez ka hisaab rakhta hai. Iske bina, tumhein ghanta nahi pata chalega ke tumhari 'PSL Winner Prediction' strategy ne paise banaye ya tumhari 'KSE-100 Momentum' strategy ne sab dubo diya.

Let's learn how to manage this munshi.

Scene Kya Hai? CRUD Operations

CRUD ka matlab hai Create, Read, Update, Delete. Yeh chaar fundamental operations hain kisi bhi database ke liye. Hamare trading bot ke context mein:

• Create: Jab bot ek nayi trade leta hai, hum database mein ek naya 'position' record create kartay hain.
• Read: Jab humein dekhna hota hai ke portfolio mein kya chal raha hai ya konsi strategy kitna kama rahi hai, hum data read kartay hain.
• Update: Jab hum ek position close kartay hain, hum uss record ko P&L (Profit & Loss) ke saath update kartay hain.
• Delete: Yeh hum almost kabhi nahi kartay. Kyun? Aagay bataunga.

Chalo, code pe aatay hain.

Database Setup: Buniyad

Sab se pehle, humein ek database connection aur ek table chahiye. Hum yahan SQLite use kar rahay hain kyunke yeh simple hai aur koi alag server nahi chahiye. Yeh code tumhare db.py file mein hoga.

# db.py
import sqlite3
from datetime import datetime, timezone

DB_FILE = "trading_bot.db"

def get_conn():
    """DB connection banata hai. Simple."""
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row  # Dictionary-like access ke liye
    return conn

def setup_database():
    """Agar table nahi hai to bana do."""
    conn = get_conn()
    conn.execute('''
        CREATE TABLE IF NOT EXISTS positions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            market_id TEXT NOT NULL,
            question TEXT,
            side TEXT NOT NULL, -- 'YES' or 'NO'
            entry_price REAL NOT NULL,
            shares REAL NOT NULL,
            cost_eur REAL NOT NULL,
            exit_price REAL,
            pnl_eur REAL,
            status TEXT DEFAULT 'open', -- 'open' or 'closed'
            strategy TEXT,
            theme TEXT,
            buy_reasoning TEXT,
            entry_time TEXT NOT NULL,
            exit_time TEXT
        )
    ''')
    conn.commit()
    conn.close()
    print("Database setup complete.")

# Script run honay pe ek baar setup call karlo
# setup_database()

Yeh setup_database function ek positions table banata hai. Ismein har cheez hai jo humein ek trade ke baray mein jannay ki zaroorat hai: kab khareeda, kis price pe, kyun khareeda, aur jab becha to kya bana.

Ab aatay hain asal CRUD operations pe.

Jab bhi tumhara bot trade execute karta hai, for example, execution.py mein, woh trade ki details database mein save karega. Yeh kaam add_position function karega.

Imagine karo, tumhara ai/gemini.py model ne predict kiya ke Karachi Kings jeetay gi. Tumhari strategy (strategies/psl_predictor.py) ne signal diya, aur execution.py ne Polymarket pe 1000 PKR ka 'YES' order laga diya. Order fill hotay hi, execution.py db.py ke is function ko call karega:

# db.py (continued)
from datetime import datetime, timezone

def add_position(market_id, question, side, entry_price, cost, strategy, theme='', reasoning=''):
    """
    Jab bhi koi trade execute ho, is function se DB mein entry daalo.
    """
    conn = get_conn()
    # Shares calculate karo: kitna paisa lagaya / kis price pe khareeda
    shares = cost / entry_price
    
    conn.execute(
        '''INSERT INTO positions (market_id, question, side, entry_price, 
           shares, cost_eur, strategy, entry_time, theme, buy_reasoning)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
        (market_id, question, side, entry_price, shares, cost,
         strategy, datetime.now(timezone.utc).isoformat(), theme, reasoning)
    )
    conn.commit() # Changes save karo
    conn.close()  # Connection band karo
    print(f"✅ NEW POSITION ADDED: {strategy} - {shares:.2f} shares of '{question}' on {side}")

# --- Example Usage ---
# Yeh call execution.py se aayegi
# add_position(
#     market_id='0x123abc',
#     question='Will Karachi Kings win PSL 10?',
#     side='YES',
#     entry_price=0.65, # 65% probability
#     cost=50.0, # 50 EUR (approx 15,000 PKR)
#     strategy='PSL_Gemini_Predictor',
#     theme='cricket',
#     reasoning='Gemini analysis shows 75% confidence based on player form.'
# )

Code Breakdown:

1. get_conn(): Har function mein pehle connection establish kartay hain.
2. shares = cost / entry_price: Simple math. Agar tumne 50 EUR lagaye aur har share 0.65 EUR ka tha, to tumhare paas 76.92 shares hain.
3. conn.execute(...): Yeh asal SQL command hai.
4. INSERT INTO positions (...) VALUES (...): Standard SQL. Hum bata rahay hain ke positions table ke in columns mein yeh values daal do.
5. ? (Question Marks): Yeh placeholders hain. Yeh SQL Injection se bachanay ka sab se best tareeqa hai. Kabhi bhi string formatting (f"INSERT ... VALUES ('{variable}')") mat use karna database queries mein. Tumhara bot hack hojayega.
6. conn.commit(): Jab tak tum commit nahi kartay, changes database mein permanently save nahi hotay. Yeh "final save" button hai.
7. conn.close(): Achi practice hai ke kaam khatam hotay hi connection close kar do.

Samajh lagi? Jab trade hui, record ban gaya. Simple.

Ab, match khatam ho gaya aur Karachi Kings haar gayi. Sad. Tumhari 'YES' position ki value ab 0 hai. Ya shayad market resolve honay se pehle hi tumne 0.20 pe bech di. In any case, position ab close ho chuki hai.

Humein puranay record ko delete nahi karna. Humein usko update karna hai with the final details: exit_price aur pnl_eur (Profit and Loss).

Yeh hai close_position function:

# db.py (continued)

def close_position(position_id, exit_price):
    """
    Ek open position ko close karta hai aur P&L calculate karke update karta hai.
    """
    conn = get_conn()
    
    # Pehle position ki details get karo P&L calculation ke liye
    pos = conn.execute('SELECT * FROM positions WHERE id = ?', (position_id,)).fetchone()
    if not pos:
        print(f"❌ ERROR: Position ID {position_id} not found.")
        conn.close()
        return

    # P&L Calculation
    # Polymarket pe, YES shares 1 pe resolve hotay hain, NO shares 0 pe.
    # P&L = (Exit Value) - (Entry Cost)
    # Exit Value = shares * exit_price
    pnl = (pos['shares'] * exit_price) - pos['cost_eur']

    conn.execute(
        '''UPDATE positions SET status='closed', exit_price=?, 
           exit_time=?, pnl_eur=? WHERE id=?''',
        (exit_price, datetime.now(timezone.utc).isoformat(), pnl, position_id)
    )
    conn.commit()
    conn.close()
    print(f"🔒 POSITION CLOSED: ID {position_id}. PnL: {pnl:.2f} EUR")

# --- Example Usage ---
# Assume karo ke upar wali position ka ID 1 tha.
# Karachi Kings haar gayi, so exit_price is 0.
# close_position(position_id=1, exit_price=0.0) 
# Output: 🔒 POSITION CLOSED: ID 1. PnL: -50.00 EUR

# Agar woh jeet jaati, to exit_price 1.0 hota
# close_position(position_id=1, exit_price=1.0)
# Output: 🔒 POSITION CLOSED: ID 1. PnL: 26.92 EUR

Code Breakdown:

1. fetchone(): Hum pehle position_id use karke uss position ka saara data nikaaltay hain. P&L calculate karnay ke liye shares aur cost_eur chahiye.
2. P&L Calculation: pnl = (pos['shares'] * exit_price) - pos['cost_eur']. Yehi sab se important hissa hai. Tumne kitnay ka becha minus tumne kitnay ka khareeda.
3. UPDATE positions SET ... WHERE id=?: Yeh SQL command record ko update karti hai. Hum status ko 'closed' kar rahay hain, exit_price, exit_time, aur calculated pnl_eur add kar rahe hain.

CRUD operations form the backbone of state management in trading bots.

📺 Recommended Videos & Resources

• SQL CRUD Operations — Data manipulation
  • Type: YouTube
  • Link description: Search "SQL CRUD operations tutorial"
• SQL Injection & Prepared Statements — Security best practices
  • Type: Wikipedia
  • Link description: Learn parameterized queries to prevent SQL injection
• Transaction Management — Data consistency
  • Type: Official Documentation
  • Link description: Learn SQLite transactions for atomic operations
• Foreign Key Constraints — Data integrity
  • Type: Official Documentation
  • Link description: Learn foreign keys for referential integrity
• Query Performance Analysis — Optimization
  • Type: YouTube
  • Link description: Search "SQL EXPLAIN ANALYZE performance"

🎯 Mini-Challenge

5-Minute Practical Task: Write Python functions for: (1) CREATE a new position, (2) READ a position by ID, (3) UPDATE a position's status/exit_price, (4) DELETE a position. Test all 4 operations with sample data and verify database changes.

🖼️ Visual Reference

📊 CRUD Operations Lifecycle
CREATE NEW POSITION
─────────────────────
INSERT INTO positions (market_id, entry_price, size, status)
VALUES (101, 75.00, 100, 'OPEN')
→ Position #4567 created

READ POSITION
─────────────────────
SELECT * FROM positions WHERE id = 4567
→ Returns: {entry_price: 75.00, size: 100, status: OPEN}

UPDATE POSITION
─────────────────────
UPDATE positions
SET exit_price = 80.00, status = 'CLOSED',
    pnl = 500.00
WHERE id = 4567
→ Position updated

DELETE POSITION (Archive)
─────────────────────
DELETE FROM positions WHERE id = 4567
→ Position removed (careful!)

Better: Mark as ARCHIVED instead
of DELETE to keep history.