Table: sitemap_urls

Columns (8)

Calculate Optimal Datatypes	Find the optimal datatypes for this table by reading existing data.  Read more

FieldTypeComment
idbigint(20) NOT NULL
website_idint(11) NOT NULL
urlvarchar(1000) NOT NULL页面URL
url_hashvarchar(64) NOT NULLURL的SHA256哈希值
lastmoddatetime NULL最后修改时间
first_found_datedate NOT NULL首次发现日期
is_new_todaytinyint(1) NULL今日新增
created_attimestamp NOT NULL


Indexes (4)

Find Redundant Indexes	Find the redundant indexes of this table.  Read more

IndexesColumnsIndex Type
PRIMARYidUnique
unique_website_url_hashwebsite_id, url_hashUnique
idx_website_datewebsite_id, first_found_date
idx_new_todayis_new_today, first_found_date

2025-05-30 11:05:39 INFO 正在处理: 阿联酋-AE
2025-05-30 11:05:39 INFO 开始爬取网站 1: https://www.oppo.com/ae/sitemap.xml
2025-05-30 11:05:41 ERROR 保存数据失败: 1364 (HY000): Field 'url_hash' doesn't have a default value
2025-05-30 11:05:43 INFO 正在处理: 印度
2025-05-30 11:05:43 INFO 开始爬取网站 2: https://www.oppo.com/in/sitemap.xml
2025-05-30 11:05:44 ERROR 保存数据失败: 1364 (HY000): Field 'url_hash' doesn't have a default value
2025-05-30 11:05:46 INFO 爬取完成: 0/2 个网站成功 
为什么报错啊，你根据表和代码看下
import requests
import xml.etree.ElementTree as ET
from datetime import datetime, date
import mysql.connector
from urllib.parse import urljoin, urlparse
import logging
import time
from config import DB_CONFIG, CRAWLER_CONFIG  # 直接从配置文件导入

# 配置日志（只配置一次）
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s %(levelname)s %(message)s',
    datefmt='%Y-%m-%d %H:%M:%S',
    filename='sitemap_crawler.log',
    filemode='a'
)
logger = logging.getLogger(__name__)

class SitemapCrawler:
    def __init__(self, db_config=None):
        """
        初始化爬虫
        db_config: 数据库连接配置，如果不提供则使用默认配置
        """
        self.db_config = db_config or DB_CONFIG
        self.crawler_config = CRAWLER_CONFIG
        self.session = requests.Session()
        self.session.headers.update({
            'User-Agent': self.crawler_config['user_agent']
        })
        self.logger = logger  # 使用全局logger
        self.logger.info(f"初始化爬虫，数据库: {self.db_config['database']}")
    
    def get_db_connection(self):
        """获取数据库连接"""
        return mysql.connector.connect(**self.db_config)
    
    def fetch_sitemap(self, sitemap_url):
        """获取sitemap内容"""
        try:
            response = self.session.get(
                sitemap_url, 
                timeout=self.crawler_config['request_timeout']
            )
            response.raise_for_status()
            return response.content
        except Exception as e:
            self.logger.error(f"获取sitemap失败 {sitemap_url}: {str(e)}")
            return None
    
    def parse_sitemap(self, xml_content):
        """解析sitemap XML内容"""
        urls_data = []
        try:
            root = ET.fromstring(xml_content)
            
            # 处理命名空间
            namespaces = {
                'sitemap': 'http://www.sitemaps.org/schemas/sitemap/0.9'
            }
            
            # 查找所有URL条目
            for url_elem in root.findall('.//sitemap:url', namespaces):
                url_data = {}
                
                # 获取URL
                loc_elem = url_elem.find('sitemap:loc', namespaces)
                if loc_elem is not None:
                    url_data['url'] = loc_elem.text.strip()
                
                # 获取最后修改时间
                lastmod_elem = url_elem.find('sitemap:lastmod', namespaces)
                if lastmod_elem is not None:
                    try:
                        lastmod_str = lastmod_elem.text.strip()
                        if 'T' in lastmod_str:
                            url_data['lastmod'] = datetime.fromisoformat(lastmod_str.replace('Z', '+00:00'))
                        else:
                            url_data['lastmod'] = datetime.strptime(lastmod_str, '%Y-%m-%d')
                    except:
                        url_data['lastmod'] = None
                
                if 'url' in url_data:
                    urls_data.append(url_data)
            
            return urls_data
            
        except Exception as e:
            self.logger.error(f"解析sitemap失败: {str(e)}")
            return []
    
    def save_urls_to_db(self, website_id, urls_data):
        """保存URL数据到数据库"""
        if not urls_data:
            return 0, 0
        
        conn = self.get_db_connection()
        cursor = conn.cursor()
        
        new_urls_count = 0
        total_urls = len(urls_data)
        today = date.today()
        
        try:
            for url_data in urls_data:
                url = url_data['url']
                lastmod = url_data.get('lastmod')
                
                # 检查URL是否已存在
                cursor.execute(
                    "SELECT id FROM sitemap_urls WHERE website_id = %s AND url = %s",
                    (website_id, url)
                )
                
                if cursor.fetchone() is None:
                    # 新URL，插入数据库
                    cursor.execute("""
                        INSERT INTO sitemap_urls (website_id, url, lastmod, first_found_date, is_new_today)
                        VALUES (%s, %s, %s, %s, TRUE)
                    """, (website_id, url, lastmod, today))
                    new_urls_count += 1
                else:
                    # 已存在的URL，更新最后修改时间
                    if lastmod:
                        cursor.execute("""
                            UPDATE sitemap_urls 
                            SET lastmod = %s, is_new_today = FALSE
                            WHERE website_id = %s AND url = %s
                        """, (lastmod, website_id, url))
            
            conn.commit()
            return total_urls, new_urls_count
            
        except Exception as e:
            conn.rollback()
            self.logger.error(f"保存数据失败: {str(e)}")
            raise
        finally:
            cursor.close()
            conn.close()
    
    def crawl_website(self, website_id, sitemap_url):
        """爬取单个网站的sitemap"""
        start_time = datetime.now()
        self.logger.info(f"开始爬取网站 {website_id}: {sitemap_url}")
        
        # 获取sitemap内容
        xml_content = self.fetch_sitemap(sitemap_url)
        if xml_content is None:
            self.log_crawl_result(website_id, 'failed', 0, 0, "无法获取sitemap内容")
            return False
        
        # 解析sitemap
        urls_data = self.parse_sitemap(xml_content)
        if not urls_data:
            self.log_crawl_result(website_id, 'failed', 0, 0, "sitemap解析失败或无内容")
            return False
        
        # 保存到数据库
        try:
            total_urls, new_urls = self.save_urls_to_db(website_id, urls_data)
            self.log_crawl_result(website_id, 'success', total_urls, new_urls)
            
            self.logger.info(f"网站 {website_id} 爬取完成: 总计{total_urls}个URL，新增{new_urls}个")
            return True
            
        except Exception as e:
            self.log_crawl_result(website_id, 'failed', 0, 0, str(e))
            return False
    
    def log_crawl_result(self, website_id, status, urls_found, new_urls, error_message=None):
        """记录爬取结果"""
        conn = self.get_db_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute("""
                INSERT INTO crawl_logs (website_id, status, urls_found, new_urls, error_message)
                VALUES (%s, %s, %s, %s, %s)
            """, (website_id, status, urls_found, new_urls, error_message))
            conn.commit()
        except Exception as e:
            self.logger.error(f"记录日志失败: {str(e)}")
        finally:
            cursor.close()
            conn.close()
    
    def crawl_all_active_websites(self):
        """爬取所有活跃网站"""
        conn = self.get_db_connection()
        cursor = conn.cursor()
        
        try:
            # 重置今日新增标记
            cursor.execute("UPDATE sitemap_urls SET is_new_today = FALSE")
            conn.commit()
            
            # 获取所有活跃网站
            cursor.execute("""
                SELECT id, name, sitemap_url 
                FROM websites 
                WHERE status = 'active'
                ORDER BY region, name
            """)
            
            websites = cursor.fetchall()
            self.logger.info(f"开始爬取 {len(websites)} 个网站")
            
            success_count = 0
            for website_id, name, sitemap_url in websites:
                self.logger.info(f"正在处理: {name}")
                if self.crawl_website(website_id, sitemap_url):
                    success_count += 1
                
                # 使用配置中的延迟时间
                time.sleep(self.crawler_config['request_delay'])
            
            self.logger.info(f"爬取完成: {success_count}/{len(websites)} 个网站成功")
            
        except Exception as e:
            self.logger.error(f"批量爬取失败: {str(e)}")
        finally:
            cursor.close()
            conn.close()

# 使用示例
if __name__ == "__main__":
    try:
        logger.info("=" * 50)
        logger.info("启动 Sitemap 爬虫")
        
        # 直接使用配置文件中的配置
        crawler = SitemapCrawler()
        crawler.crawl_all_active_websites()
        
        logger.info("Sitemap 爬虫运行完成")
        logger.info("=" * 50)
        
    except Exception as e:
        logger.exception("主程序发生异常: %s", str(e))
        print(f"发生错误: {str(e)}")