def index():
form = CadastroForm()
if form.validate_on_submit():
db_insert(
"INSERT INTO [dbo].[Cadastro] (nome, sobrenome, email) VALUES ('" + form.nome.data + "','" + form.sobrenome.data + "','" + form.email.data + "');")
return redirect("/consulta")
return render_template('index.html', form=form)
def insert(request):
# if this is a POST request we need to process the form data
if request.method == 'POST':
# create a form instance and populate it with data from the request:
form = addCompanyData(request.POST)
# check whether it's valid:
if form.is_valid():
# process the data in form.cleaned_data as required
# ...
# redirect to a new URL:
# return HttpResponseRedirect('/index/')
company = form.cleaned_data['company']
date = form.cleaned_data['date']
price = form.cleaned_data['companyprice']
data = []
table = 'invest_sharePrice'
path = '/home/website/website/mysite/db.sqlite3'
db_insert(path, table, company, date, price)
company_list = sharePrice.objects.all()
# time.strftime('%Y-%m-%d', time.strptime("30 Nov 17", "%d %b %y"))
data = serializers.serialize("json", company_list)
companylist = db_query('/home/website/website/mysite/db.sqlite3', 'invest_sharePrice', "*")
context = {
#'company_list': json.dumps(company_list),
'company_list': data,
'companylist': companylist,
}
return render(request, 'invest/index.html', context)
# if a GET (or any other method) we'll create a blank form
else:
form = addCompanyData()
return render(request, 'invest/index.html', {'form': form})
def Excel2array(file_path, inp):
# 打开excel文件
if get_data(file_path) is not None:
book = get_data(file_path)
#以下是输出sheet页并选择对哪个sheet进行处理
# # 抓取所有sheet页的名称
# worksheets = book.sheet_names()
# print "该Excel包含的表单列表为：\n"
# for sheet in worksheets:
# print ('%s,%s' % (worksheets.index(sheet), sheet))
# inp = raw_input(u'请输入表单名对应的编号，对应表单将自动转为array:\n')
sheet = book.sheet_by_index(int(inp))
nrows = sheet.nrows # 行号
ncols = sheet.ncols # 列号
date = []
company = []
for j in range(ncols):
date.append(sheet.row_values(0)[j])
for i in range(nrows):
company.append(sheet.row_values(i)[0])
# 遍历所有行，将excel转化为array对象
for i in range(nrows):
if i == 0:
continue
# 遍历当前行所有列
for j in range(ncols):
if (j==0):
continue
print company[i], date[j], sheet.cell_value(i, j)
datesplit = date[j].split('.')
db.db_insert(dbpath, dbtable, company[i].encode('ascii','ignore'), datetime.date(int(datesplit[0]),int(datesplit[1]),int(datesplit[2])), sheet.cell_value(i, j))
db.datedb_insert(dbpath, dbdatetable, datetime.date(int(datesplit[0]),int(datesplit[1]),int(datesplit[2])))
def store_stocks():
f = open('data/csv/aksjer.csv', 'rU')
lines = f.readlines()[2:]
for line in lines:
cells = line.split(",")
ticker = cells[0].decode("utf-8", "ignore")
name = cells[1].decode("utf-8", "ignore")
exist_ticker = db_search_ticker(ticker)
if not exist_ticker:
db_insert(name, ticker)
f.close()
return True
def insert():
vals = loads(request.form.get("vals", type=str))
app.logger.debug(vals)
ret = "Fill out table properly to submit into database."
if (db_insert(vals)):
ret = "Entries submitted into database."
out = {"ret": ret}
return flask.jsonify(out)
def add():
username = input('请输入用户名：')
age = input('请输入年龄：')
sex = input('请输入性别：')
phone = input('请输入手机号：')
email = input('请输入邮箱地址：')
email_tag = check_email(email)
phone_tag = check_phone(phone)
sex_tag = check_sex(sex)
username_tag = check_user(username)
age_tag = check_age(age)
try:
if email_tag is not True or phone_tag is not True or age_tag is not True or sex_tag is not True or username_tag is not True:
log.info('请检查输入的格式')
else:
data = (username, age, sex, phone, email)
db_insert(data)
except Exception as e:
raise e
def solve_btn(h, properties, choice, task_type):
if st.button('Solve', key='solve_btn'):
st.text('Started weights:')
st.write(pd.DataFrame(h.weight, columns=['w']))
start_time = time.time()
with st.spinner('waiting...'):
loss_history, weights_history, y_pred_history = properties.modification(h, properties.max_num_itter, properties.cost_function,
regularization=properties.regularization, C=properties.reg_coef,
alpha=properties.alpha, eps=properties.eps)
st.success('Calculations have already finished! Look at the results:')
if np.isnan(h.weight).any():
st.error("Result approximates to infinity. Please, select another parameters.")
else:
st.text('Finished weights:')
st.write(pd.DataFrame(h.weight, columns=['w']))
db_insert(h, properties, time.time() - start_time, choice)
if task_type == 'classification':
plot_classification_all(h, properties, weights_history, loss_history, y_pred_history)
elif task_type == 'regression':
plot_regression_all(h, properties, weights_history, loss_history, y_pred_history)
# select文
for title, url in zip(res_titles, res_urls):
title_text = title.get_text() # テキストのみを取得
title_url = url.get('href') # URLを取得
searchlists.append([title_text, title_url])
# table = 'scrapings'
# sql = ('select * from ' + table)
# db = db.db_connect()
# cur = db.cursor()
# cur.execute(sql)
# print(cur.fetchall())
# insert文
sql = ("INSERT INTO scrapings (title, url) VALUES (%s, %s);")
db.db_insert(sql, searchlists)
# csv書き込みをwithを使用して書く closeを書かなくて済む
# with open('scraping.csv', 'w', encoding='utf_8_sig') as csvfile:
# writer = csv.writer(csvfile)
# writer.writerow(['タイトル', 'URL'])
# writer.writerows(searchlists)
'''
# 辞書
title_list = []
dic = []
fieldnames = []
# 取得したリンクの個数分ループでまわす
for i in range(len(res_link)):
title_text = res_link[i].get_text()
title_list.append(res_link[i].get_text())
def get_configs():
global columns_todo, columns_current
count_series = len(series_ids)
count_index = 0
count_series_done = 0
count_specs_done = 0
# 循环抓取所有车系配置
for series_id in series_ids:
count_index = count_index + 1
print('开始处理车系 %s 进度：%d/%d ' % (series_id, count_index, count_series))
if series_id in series_ids_done:
# 该车系已经处理过，跳过
continue
# 每个车系下各车型的配置数据列表，每个元素又是一个 list，代表一个车型
list_specs = []
# 跟上面的列表一一对应，存储对应车型的列名
list_specs_columns = []
# 初始化列表
columns_todo = []
columns_current = [
'series_id', 'series_link', 'series_name', 'official_config',
'spec_id', 'spec_link'
]
# 该车系下的车型数量
count_spec = 0
req = request.Request(url_config % series_id)
req.headers = headers
# 每个车系之间间隔若干秒
time.sleep(random.randint(time_interval_min, time_interval_max))
try:
res = request.urlopen(req)
content = gzip.decompress(res.read()).decode('utf-8')
# 反混淆
content = antiobfuscate.get_complete_text(content)
# 车系
match = pattern_series_name.search(content)
series_name = match.group(1).replace('"', '”')
# 官方配置表
match = pattern_official_config.search(content)
if match:
official_config_link = match.group(1)
else:
official_config_link = ''
# 基本参数
match = pattern_config.search(content)
if not match:
# 该车系下没有车型数据 插入一条空数据用于记录
db.db_insert_nodata(series_id, url_series % series_id,
series_name)
continue
str_config = match.group(1)
# 配置
match = pattern_config_option.search(content)
if not match:
print('车系 %s 中没有配置数据' % series_id)
continue
str_config_option = match.group(1)
# 外观颜色
match = pattern_config_color.search(content)
if not match:
print('车系 %s 中没有外观颜色数据' % series_id)
continue
str_config_color = match.group(1)
# 内饰颜色
match = pattern_config_innerColor.search(content)
if not match:
print('车系 %s 中没有内饰颜色数据' % series_id)
continue
str_config_inner_color = match.group(1)
# 选装包
match = pattern_config_bag.search(content)
if not match:
print('车系 %s 中没有选装包数据' % series_id)
continue
str_config_bag = match.group(1)
# 基本参数
config_params = pattern_config_params.findall(str_config)
for one_config_param in config_params:
item_id = 'item_' + one_config_param[0]
item_name = one_config_param[1]
# 针对 item_id 为 0 的特殊处理
if item_id == 'item_0':
if item_name == '能源类型':
item_id = 'energy_type'
elif item_name == '上市时间':
item_id = 'sell_date'
elif item_name == '纯电续航里程':
# 跟【工信部续航里程(km)】相同
item_id = 'item_1013'
elif item_name == '变速箱':
item_id = 'gear_box'
elif item_name == '后排车门开启方式':
item_id = 'back_door_open'
elif item_name == '货箱尺寸(mm)':
item_id = 'cargo_size'
elif item_name == '最大载重质量(kg)':
item_id = 'max_load'
elif item_name == '排量(L)':
item_id = 'displacement_L'
elif item_name == '电机类型':
item_id = 'e_engine_type'
elif item_name == '系统综合功率(kW)':
item_id = 'system_power_sum'
elif item_name == '系统综合扭矩(N·m)':
item_id = 'system_torque_sum'
elif item_name == '驱动电机数':
item_id = 'e_engine_num'
elif item_name == '电机布局':
item_id = 'e_engine_layout'
elif item_name == '电池类型':
item_id = 'battery_type'
elif item_name == '百公里耗电量(kWh/100km)':
item_id = 'power_use'
elif item_name == '电池组质保':
item_id = 'battery_quality'
elif item_name == '电池充电时间':
item_id = 'battery_charge_time'
elif item_name == '快充电量(%)' or item_name == '快充电量百分比':
item_id = 'quick_charge'
elif item_name == '充电桩价格':
item_id = 'charger_price'
elif item_name == '电动机':
# 重复字段，跳过
continue
else:
print('车系 %s 中出现未知的 item_0，item_name：%s' %
(series_id, item_name))
continue
str_specs = one_config_param[3]
if not add_column(item_id, item_name):
# 该参数已经添加过，跳过
continue
config_specs = pattern_config_specs.findall(str_specs)
if count_spec == 0:
count_spec = len(config_specs)
if count_spec == 0:
print('该参数没有车型数据，车系：%s 基本参数：%s' % (series_id, item_id))
continue
# 第一次执行循环时初始化 list_specs
if len(list_specs) == 0:
# 有多少个车型，则添加多少个 list_specs 元素
for i in range(count_spec):
list_specs.append([])
list_specs_columns.append([])
list_specs_index = 0
for one_config_spec in config_specs:
spec_id = one_config_spec[0]
item_value = one_config_spec[1]
if item_id == 'item_219':
# 价格处理
item_value = get_price(item_value)
else:
item_value = get_simple_word(item_value)
one_spec = list_specs[list_specs_index]
one_column = list_specs_columns[list_specs_index]
if len(one_spec) == 0:
# 第一次添加车型数据时初始化列表
one_spec.append(series_id)
one_spec.append(url_series % series_id)
one_spec.append(series_name)
one_spec.append(official_config_link)
one_spec.append(spec_id)
one_spec.append(url_spec % spec_id)
one_column.extend(
['series_id', 'series_link', 'series_name'])
one_column.extend(
['official_config', 'spec_id', 'spec_link'])
elif one_spec[4] != spec_id:
print('spec_id 不符，车系：%s 基本参数：%s' %
(series_id, item_id))
item_value = '#'
one_spec.append(item_value)
one_column.append(item_id)
list_specs_index = list_specs_index + 1
if len(list_specs) == 0:
print('车系 %s 下没有车型' % series_id)
continue
# 配置
config_option_params = pattern_config_option_params.findall(
str_config_option)
for one_config_option_param in config_option_params:
item_id = 'item_' + one_config_option_param[2]
item_name = one_config_option_param[0]
# 针对 item_id 为 0 的特殊处理
if item_id == 'item_0':
if item_name == '疲劳驾驶提示':
item_id = 'tired_drive'
elif item_name == '自动驾驶技术':
item_id = 'auto_drive'
elif item_name == '上坡辅助':
item_id = 'up_assist'
elif item_name == '电磁感应悬架':
item_id = 'em_feel'
elif item_name == '多天窗':
item_id = 'multi_window'
elif item_name == '感应后备厢':
item_id = 'feel_rear_box'
elif item_name == '车顶行李架':
item_id = 'top_box'
elif item_name == '远程启动':
item_id = 'remote_start'
elif item_name == '皮质方向盘':
item_id = 'steeling_texture'
elif item_name == '方向盘记忆':
item_id = 'steeling_memory'
elif item_name == '全液晶仪表盘':
item_id = 'liquid_view'
elif item_name == '内置行车记录仪':
item_id = 'drive_recorder'
elif item_name == '主动降噪':
item_id = 'noise_down'
elif item_name == '手机无线充电':
item_id = 'remote_charge'
elif item_name == '座椅材质':
item_id = 'seat_texture'
elif item_name == '副驾驶位后排可调节按钮':
item_id = 'sub_driver_button'
elif item_name == '第二排独立座椅':
item_id = 'second_seat'
elif item_name == '可加热/制冷杯架':
item_id = 'heater_cup'
elif item_name == '中控台彩色大屏尺寸':
item_id = 'screen_size'
elif item_name == '手机互联/映射':
item_id = 'mobile_link'
elif item_name == '车联网':
item_id = 'car_net'
elif item_name == '220V/230V电源':
item_id = 'power_220v'
elif item_name == '外接音源接口':
item_id = 'audio_interface'
elif item_name == 'CD/DVD':
item_id = 'cd_dvd'
elif item_name == '近光灯':
item_id = 'low_beam'
elif item_name == '远光灯':
item_id = 'high_beam'
elif item_name == 'LED日间行车灯':
item_id = 'led_daylight'
elif item_name == '自适应远近光':
item_id = 'adapter_beam'
elif item_name == '转向头灯':
item_id = 'turn_beam'
elif item_name == '车窗一键升降':
item_id = 'one_touch_window'
elif item_name == '流媒体车内后视镜':
item_id = 'streaming_rear_mirror'
elif item_name == '车载空气净化器':
item_id = 'air_purifier'
else:
print('车系 %s 中出现未知的 item_0，item_name：%s' %
(series_id, item_name))
continue
str_specs = one_config_option_param[1]
if not add_column(item_id, item_name):
# 该参数已经添加过，跳过
continue
config_option_specs = pattern_config_option_specs.findall(
str_specs)
for one_config_option_spec in config_option_specs:
spec_id = one_config_option_spec[0]
item_price = one_config_option_spec[2]
item_value = get_simple_word(one_config_option_spec[3])
if item_price and item_price != '0':
item_value = item_value + '选装¥' + item_price
list_specs_index = get_list_index_by_specid(
list_specs, spec_id)
if list_specs_index == -1:
print('没找到对应的 spec_id，车系：%s 配置：%s spec：%s' %
(series_id, item_id, spec_id))
continue
one_spec = list_specs[list_specs_index]
one_column = list_specs_columns[list_specs_index]
one_spec.append(item_value)
one_column.append(item_id)
# 外观颜色
config_color_specs = pattern_config_color_specs.findall(
str_config_color)
for one_config_color_spec in config_color_specs:
item_id = 'outer_color'
item_name = '外观颜色'
add_column(item_id, item_name)
spec_id = one_config_color_spec[0]
list_specs_index = get_list_index_by_specid(
list_specs, spec_id)
if list_specs_index == -1:
print('没找到对应的 spec_id，车系：%s 外观颜色 spec：%s' %
(series_id, spec_id))
continue
one_spec = list_specs[list_specs_index]
config_color_colors = pattern_config_color_colors.findall(
one_config_color_spec[1])
item_value = ' '.join(config_color_colors)
item_value = get_simple_word(item_value)
one_spec.append(item_value)
one_column = list_specs_columns[list_specs_index]
one_column.append(item_id)
# 内饰颜色
config_color_specs = pattern_config_color_specs.findall(
str_config_inner_color)
for one_config_color_spec in config_color_specs:
item_id = 'inner_color'
item_name = '内饰颜色'
add_column(item_id, item_name)
spec_id = one_config_color_spec[0]
list_specs_index = get_list_index_by_specid(
list_specs, spec_id)
if list_specs_index == -1:
print('没找到对应的 spec_id，车系：%s 内饰颜色 spec：%s' %
(series_id, spec_id))
continue
one_spec = list_specs[list_specs_index]
config_color_colors = pattern_config_color_colors.findall(
one_config_color_spec[1])
item_value = ' '.join(config_color_colors)
item_value = get_simple_word(item_value)
one_spec.append(item_value)
one_column = list_specs_columns[list_specs_index]
one_column.append(item_id)
# 选装包
config_bag_bags = pattern_config_bag_bags.findall(str_config_bag)
bag_index = 0
for one_config_bag_bag in config_bag_bags:
item_id = 'bag_%d' % bag_index
item_name = '选装包%d' % bag_index
item_price = one_config_bag_bag[0]
bag_name = one_config_bag_bag[1]
bag_desc = one_config_bag_bag[3]
bag_name = '选装[%s]¥%s[%s]' % (bag_name, item_price, bag_desc)
str_specs = one_config_bag_bag[2]
add_column(item_id, item_name)
config_bag_specs = pattern_config_bag_specs.findall(str_specs)
for one_config_bag_spec in config_bag_specs:
spec_id = one_config_bag_spec[0]
item_value = one_config_bag_spec[1]
if item_value != '-':
item_value = item_value + get_simple_word(bag_name)
list_specs_index = get_list_index_by_specid(
list_specs, spec_id)
if list_specs_index == -1:
print('没找到对应的 spec_id，车系：%s 选装包：%d spec：%s' %
(series_id, bag_index, spec_id))
continue
one_spec = list_specs[list_specs_index]
one_spec.append(item_value)
one_column = list_specs_columns[list_specs_index]
one_column.append(item_id)
bag_index = bag_index + 1
count_series_done = count_series_done + 1
count_specs_done = count_specs_done + len(list_specs)
# 配置全部读取完成，进行数据库操作
# 先添加新找到的列
if len(columns_todo) > 0:
db.db_add_columns(columns_todo)
# 将数据存入数据库
db.db_insert(list_specs_columns, list_specs)
print('成功处理车系 %s ---------- %s' % (series_id, series_name))
except error.HTTPError as e:
print('-- %s -- HTTPError:%s' % (series_id, e.reason))
continue
except error.URLError as e:
print('-- %s -- URLError:%s' % (series_id, e.reason))
continue
print('已完成车系：%d，车型：%d' % (count_series_done, count_specs_done))
def test_db_insert():
assert (db_insert(data) == False)