mysql写入数据库_将数据写入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'试机号：(.*)', 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)