从PostgreSQL表生成指定格式XML文件的技术需求

Alright, let's walk through building this exact XML feed step by step. We'll use Python's built-in XML library to construct the structure and psycopg2 to pull data from your PostgreSQL table.

Step 1: Install Dependencies #

First, install the PostgreSQL adapter for Python (use the binary version since Python 3.5 is an older release):

pip install psycopg2-binary==2.8.6

(Version 2.8.6 is compatible with Python 3.5 and PostgreSQL 11)

Step 2: Complete Code Implementation #

Here's a full, commented script that handles database connection, data fetching, XML construction, and file output:

import xml.etree.ElementTree as ET
import psycopg2
import time
from datetime import datetime

# ----------------------
# Configuration Settings
# ----------------------
DB_PARAMS = {
    'dbname': 'your_database',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',
    'port': '5432'
}
FEED_CONFIG = {
    'system': 'Magento',
    'extension': 'Magmodules',
    'extension_version': '1.6.8',
    'store': 'store_1',
    'url': 'https://www.company.com/',
    'shipping_country': 'USA',
    'shipping_service': 'DHL',
    'shipping_price': '2.90'
}
OUTPUT_FILE = 'google_merchant_feed.xml'

# Start timing processing time
start_time = time.time()

try:
    # ----------------------
    # Connect to PostgreSQL
    # ----------------------
    conn = psycopg2.connect(**DB_PARAMS)
    cursor = conn.cursor()

    # Fetch all products from your table (replace 'product_info' with your actual table name)
    cursor.execute("SELECT * FROM product_info")
    products = cursor.fetchall()
    product_count = len(products)

    # Get column names to map directly to XML elements
    column_names = [desc[0] for desc in cursor.description]

    # ----------------------
    # Build XML Structure
    # ----------------------
    # Register Google namespace for proper XML prefixing
    ET.register_namespace('g', 'http://base.google.com/ns/1.0')

    # Root RSS element
    rss = ET.Element('rss', version='2.0', encoding='utf-8')
    rss.set('xmlns:g', 'http://base.google.com/ns/1.0')

    # Config section
    config = ET.SubElement(rss, 'config')
    ET.SubElement(config, 'g:system').text = FEED_CONFIG['system']
    ET.SubElement(config, 'g:extension').text = FEED_CONFIG['extension']
    ET.SubElement(config, 'g:extension_version').text = FEED_CONFIG['extension_version']
    ET.SubElement(config, 'g:store').text = FEED_CONFIG['store']
    ET.SubElement(config, 'g:url').text = FEED_CONFIG['url']
    ET.SubElement(config, 'g:products').text = str(product_count)
    ET.SubElement(config, 'g:generated').text = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    # Channel section
    channel = ET.SubElement(rss, 'channel')

    # Add each product as an <item> element
    for product in products:
        item = ET.SubElement(channel, 'item')
        for col_name, value in zip(column_names, product):
            # Handle shipping separately (since it's a nested element)
            if col_name == 'shipping':
                shipping = ET.SubElement(item, 'g:shipping')
                ET.SubElement(shipping, 'g:country').text = FEED_CONFIG['shipping_country']
                ET.SubElement(shipping, 'g:service').text = FEED_CONFIG['shipping_service']
                ET.SubElement(shipping, 'g:price').text = FEED_CONFIG['shipping_price']
            else:
                # Create the g-prefixed element, handle NULL values as empty strings
                elem = ET.SubElement(item, f'g:{col_name}')
                elem.text = str(value) if value is not None else ''

    # Calculate and add processing time
    processing_time = time.time() - start_time
    ET.SubElement(config, 'g:processing_time').text = f"{processing_time:.4f}"

    # ----------------------
    # Save XML to File
    # ----------------------
    tree = ET.ElementTree(rss)
    with open(OUTPUT_FILE, 'wb') as f:
        # Write XML declaration manually to ensure proper encoding
        f.write(b'<?xml version="1.0" encoding="utf-8"?>\n')
        tree.write(f, encoding='utf-8')

    print(f"Feed generated successfully! Saved to {OUTPUT_FILE}")

except Exception as e:
    print(f"Error occurred: {str(e)}")
finally:
    # Close database connection
    if conn:
        cursor.close()
        conn.close()

Key Notes: #

• Database Mapping: The script assumes your PostgreSQL table columns exactly match the g: element names in the XML (e.g., column id maps to <g:id>). If your column names differ, adjust the col_name mapping logic.
• Shipping Handling: In the example, shipping details are hardcoded. If you store shipping data in your table, modify the shipping section to pull values from the product row instead of using FEED_CONFIG.
• NULL Values: The script converts PostgreSQL NULL values to empty strings, which will generate empty elements like <g:google_product_category/> as in your example.
• Encoding: The output is saved as UTF-8, matching your specified XML encoding.

内容的提问来源于stack exchange，提问作者Linu