将数据写入mysql数据库

import requests
import re
import pymysql
from lxml import etree



def write_to_fuc3d_db(url):
    response = requests.get(url)
    content = response.content.decode('GBK')
    tree = etree.HTML(content)
    # 开奖期号
    result_kjqh = tree.xpath('//span[@class="span_left"]/a//strong/text()')[0]
    # 开奖日期
    result_rq_ = tree.xpath('//span[@class="span_right"]/text()')[0]
    result_rq = re.findall(r'开奖日期：(.*?) ', result_rq_)[0].replace('年', '-').replace('月', '-').replace('日', '')
    close_date_need = re.findall(r'兑奖截止日期：(.*)', result_rq_)[0].replace('年', '-').replace('月', '-').replace('日', '')
    result_kjhm_list = tree.xpath('//div[@class="ball_box01"]/ul/li[@class="ball_orange"]/text()')
    # 中奖号码
    result_kjhm = result_kjhm_list[0] + ',' + result_kjhm_list[1] + ',' + result_kjhm_list[2]
    # 试机号
    result_sjh = re.findall(r'试机号：(.*)</div>', content)[0].replace(' ', ',')
    # # 全国销量
    result_qgxl = int(re.findall(r'(.*)元', tree.xpath('//span[@class="cfont1 "]/text()')[0].replace(',', ''))[0])







    conn = pymysql.connect(host='ip地址', port=端口号, user=’用户名’, password=’密码’, db='数据库的名字',
                           charset='utf8')
    cursor = conn.cursor()
    sql_main_table = "insert into 表名 (fc3d_issue,fc3d_open_date,fc3d_close_date,fc3d_num,fc3d_test_num,fc3d_sales_money) VALUES (%s,%s,%s,%s,%s,%s)"
    data_main_table = (result_kjqh, result_rq, close_date_need, result_kjhm, result_sjh, result_qgxl)
    cursor.execute(sql_main_table, data_main_table)
    # # 得到主表的ID
    id_num = conn.insert_id()




    result_zux_zuxuan = '组选'
    result_zux_zjzs = tree.xpath('//table[@class="kj_tablelist02"]/tr[position()=3]/td[position()=2]/text()')
    if len(result_zux_zjzs) == 0:
        result_zux_zjzs = \
            tree.xpath('//table[@class="kj_tablelist02"][position()=2]/tr[position()=3]/td[position()=2]/text()')[
                0].strip()
    else:
        result_zux_zjzs = result_zux_zjzs[0].strip()
    result_zux_dzjj = 1040
    result_zu = tree.xpath('//table[@class="kj_tablelist02"]/tr[position()=4]/td[@colspan="2"]/text()')
    if len(result_zu) == 0:
        result_zu = \
            tree.xpath('//table[@class="kj_tablelist02"][position()=2]/tr[position()=4]/td[position()=1]/text()')[
                0].strip()
    else:
        result_zu = result_zu[0].strip()
    result_zu_zjzs = tree.xpath('//table[@class="kj_tablelist02"]/tr[position()=4]/td[position()=2]/text()')
    if len(result_zu_zjzs) == 0:
        result_zu_zjzs = \
            tree.xpath('//table[@class="kj_tablelist02"][position()=2]/tr[position()=4]/td[position()=2]/text()')[
                0].strip()
    else:
        result_zu_zjzs = result_zu_zjzs[0].strip()
    result_zu_dzjj_three = 346
    result_zu_dzjj_six = 173
    if result_zu == '组三':
        sql_auxiliary_table_1 = "insert into 表名(fc3d_result_id,fc3d_grade,fc3d_chips,fc3d_money) VALUES (%s,%s,%s,%s)"
        data_auxiliary_table_1 = (id_num, result_zux_zuxuan, result_zux_zjzs, result_zux_dzjj)
        cursor.execute(sql_auxiliary_table_1, data_auxiliary_table_1)
        sql_auxiliary_table_2 = "insert into 表名(fc3d_result_id,fc3d_grade,fc3d_chips,fc3d_money) VALUES (%s,%s,%s,%s)"
        data_auxiliary_table_2 = (id_num, result_zu, result_zu_zjzs, result_zu_dzjj_three)
        cursor.execute(sql_auxiliary_table_2, data_auxiliary_table_2)
        sql_auxiliary_table_3 = "insert into 表名(fc3d_result_id,fc3d_grade,fc3d_chips,fc3d_money) VALUES (%s,%s,%s,%s)"
        data_auxiliary_table_3 = (id_num, '组六', 0, result_zu_dzjj_six)
        cursor.execute(sql_auxiliary_table_3, data_auxiliary_table_3)
    elif result_zu == '组六':
        sql_auxiliary_table_1 = "insert into 表名(fc3d_result_id,fc3d_grade,fc3d_chips,fc3d_money) VALUES (%s,%s,%s,%s)"
        data_auxiliary_table_1 = (id_num, result_zux_zuxuan, result_zux_zjzs, result_zux_dzjj)
        cursor.execute(sql_auxiliary_table_1, data_auxiliary_table_1)
        sql_auxiliary_table_2 = "insert into 表名(fc3d_result_id,fc3d_grade,fc3d_chips,fc3d_money) VALUES (%s,%s,%s,%s)"
        data_auxiliary_table_2 = (id_num, '组三', 0, result_zu_dzjj_three)
        cursor.execute(sql_auxiliary_table_2, data_auxiliary_table_2)
        sql_auxiliary_table_3 = "insert into 表名(fc3d_result_id,fc3d_grade,fc3d_chips,fc3d_money) VALUES (%s,%s,%s,%s)"
        data_auxiliary_table_3 = (id_num, result_zu, result_zu_zjzs, result_zu_dzjj_six)
        cursor.execute(sql_auxiliary_table_3, data_auxiliary_table_3)



    conn.commit()
    cursor.close()
    conn.close()


def get_site(nianfen, qishu):
    for num in range(1, qishu + 1):
        stringnum = str(num)
        num_str = stringnum.zfill(3)
        url = 'http://kaijiang.500.com/shtml/sd/' + str(nianfen) + num_str + '.shtml'
        print(url)
        write_to_fuc3d_db(url)


get_site(2013, 358)