#TelegramBot #Python #SQLite #DataExport #CSV #API

Lesson: Creating a Telegram Bot to Export Channel Members to a CSV File

This tutorial guides you through building a Telegram bot from scratch. When added as an administrator to a channel, the bot will respond to an /export command from the channel owner, exporting the list of members (Username, User ID, etc.) to a CSV file and storing a log of the action in a SQLite database.

IMPORTANT NOTE: Due to Telegram's privacy policy, bots CANNOT access users' phone numbers. This field will be marked as "N/A".

---

#Step 1: Bot Creation and Project Setup

First, create a bot via the @BotFather on Telegram. Send it the /newbot command, follow the instructions, and save the HTTP API token it gives you.

Next, set up your Python environment. Install the necessary library: python-telegram-bot.

pip install python-telegram-bot

Create a new Python file named bot.py and add the basic structure. Replace 'YOUR_TELEGRAM_API_TOKEN' with the token you got from BotFather.

import logging
from telegram import Update
from telegram.ext import Application, CommandHandler, ContextTypes

# Enable logging
logging.basicConfig(format="%(asctime)s - %(name)s - %(levelname)s - %(message)s", level=logging.INFO)

TOKEN = 'YOUR_TELEGRAM_API_TOKEN'

def main() -> None:
    """Start the bot."""
    application = Application.builder().token(TOKEN).build()

    # We will add command handlers here in the next steps
    # application.add_handler(CommandHandler("export", export_members))

    print("Bot is running...")
    application.run_polling()

if __name__ == "__main__":
    main()

# Hashtags: #Setup #TelegramAPI #PythonBot #BotFather

---

#Step 2: Database Setup for Logging (database.py)

To fulfill the requirement of using a database, we will log every export request. Create a new file named database.py. This separates our data logic from the bot logic.

import sqlite3
from datetime import datetime

DB_NAME = 'bot_logs.db'

def setup_database():
    """Creates the database table if it doesn't exist."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS export_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            chat_id INTEGER NOT NULL,
            chat_title TEXT,
            requested_by_id INTEGER NOT NULL,
            requested_by_username TEXT,
            timestamp TEXT NOT NULL
        )
    ''')
    conn.commit()
    conn.close()

def log_export_action(chat_id, chat_title, user_id, username):
    """Logs a successful export action to the database."""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute(
        "INSERT INTO export_logs (chat_id, chat_title, requested_by_id, requested_by_username, timestamp) VALUES (?, ?, ?, ?, ?)",
        (chat_id, chat_title, user_id, username, timestamp)
    )
    conn.commit()
    conn.close()

# Hashtags: #SQLite #DatabaseDesign #Logging #DataPersistence

---

#Step 3: Implementing the Export Command and Permission Check

Now, we'll write the core function in bot.py. This function will handle the /export command. The most crucial part is to check if the user who sent the command is the creator of the channel. This prevents any admin from exporting the data.

# converter_bot.py (continued)

def run_conversion(input_path: str, output_path: str) -> bool:
    """Runs the ebook-convert command and returns True on success."""
    try:
        command = ['ebook-convert', input_path, output_path]
        result = subprocess.run(command, check=True, capture_output=True, text=True)
        logging.info(f"Calibre output: {result.stdout}")
        return True
    except FileNotFoundError:
        logging.error("CRITICAL: 'ebook-convert' command not found. Is Calibre installed and in the system's PATH?")
        return False
    except subprocess.CalledProcessError as e:
        logging.error(f"Conversion failed for {input_path}. Error: {e.stderr}")
        return False

#Conversion #Calibre #Subprocess

---

Part 5: Database Logging Function

This helper function will connect to our SQLite database and insert a new record for each successful conversion.

# converter_bot.py (continued)

def log_to_db(user_id: int, original_file: str, converted_file: str, conv_type: str):
    """Logs a successful conversion to the SQLite database."""
    try:
        conn = sqlite3.connect('conversions.db')
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO conversions (user_id, original_filename, converted_filename, conversion_type) VALUES (?, ?, ?, ?)",
            (user_id, original_file, converted_file, conv_type)
        )
        conn.commit()
        conn.close()
    except sqlite3.Error as e:
        logging.error(f"Database error: {e}")

#Database #Logging #SQLite

---

Part 6: Handling Incoming Files

This is the main handler that will be triggered when a user sends a document. It downloads the file, determines the target format, calls the conversion function, sends the result back, logs it, and cleans up.

# converter_bot.py (continued)

async def handle_document(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    doc = update.message.document
    file_id = doc.file_id
    file_name = doc.file_name
    
    input_path = os.path.join("downloads", file_name)
    os.makedirs("downloads", exist_ok=True) # Ensure download directory exists
    
    new_file = await context.bot.get_file(file_id)
    await new_file.download_to_drive(input_path)
    
    await update.message.reply_text(f"Received '{file_name}'. Starting conversion...")

    output_path = ""
    conversion_type = ""

    if file_name.lower().endswith('.pdf'):
        output_path = input_path.rsplit('.', 1)[0] + '.epub'
        conversion_type = "PDF -> EPUB"
    elif file_name.lower().endswith('.epub'):
        output_path = input_path.rsplit('.', 1)[0] + '.pdf'
        conversion_type = "EPUB -> PDF"
    else:
        await update.message.reply_text("Sorry, I only support PDF and EPUB files.")
        os.remove(input_path)
        return

    # Run the conversion
    success = run_conversion(input_path, output_path)
    
    if success and os.path.exists(output_path):
        await update.message.reply_text("Conversion successful! Uploading your file...")
        await context.bot.send_document(chat_id=update.effective_chat.id, document=open(output_path, 'rb'))
        
        # Log to database
        log_to_db(update.effective_user.id, file_name, os.path.basename(output_path), conversion_type)
    else:
        await update.message.reply_text("An error occurred during conversion. Please check the file and try again. The file might be corrupted or protected.")

    # Cleanup
    if os.path.exists(input_path):
        os.remove(input_path)
    if os.path.exists(output_path):
        os.remove(output_path)

#FileHandler #BotLogic

---