将python文件与db2连接并上传文件

今天刚完成一个assessment
里面涉及了连接IBM数据库，上传本地文件，使用AI里的pi里的personality insight分析，还有筛选输出的json格式的表格，修改IBM数据库里的表内容。
用得是python语言。
哈，发现大三的学长学姐好厉害，不过也都是自己学的吧，上课还是教不了那么多东西，自己探索总结才是真的有用嗷。

其中连接数据库用的是数据库里的服务凭证，如果没有服务凭证，选择添加凭证即可。

连接pi用的是API和url

上传本地文件的时候，在命令行依次输入

ibmcloud api https://cloud.ibm.com --skip-ssl-validation
ibmcloud login -u xx -o xx -s （xx为需要替换内容，在数据库查看，两个xx一般来说是一样的）
（操作时要确保在上传文件的目录下 命令行输入 cd xxxxx 进入对应目录）
ibmcloud app push <文件名字>

如果上传失败，输入以下命令行可查看历史操作（logs为日志）
ibmcloud cf logs ty-python --recent

从数据字典筛选出需要的数据

jsondict = json.loads(json.dumps(profile))

# TODO: Filter the information listed under "values" property in the json output. You can get this from jsondict dictionary.

key1= jsondict['values']
values = []
p_name = []
for i in key1:
    values.append(str(i["raw_score"]))
    p_name.append(i["name"])

sql语句对数据库的表进行修改

create_query = “INSERT INTO DASH100495.PPI VALUES (‘obama’,” + “,”.join(values) + “)”
statement = ibm_db.exec_immediate(c, create_query)
ibm_db.free_stmt(statement)

输出到html页面

(部分th tr 有多余的转义字符)
htmltext = ‘Personality Values</br>’
htmltext += ‘<\table border=“1”> <\tr> <\th> Personality Property</th> <\th>Value</th></tr>’
# TODO: Concatenate the filtered scores into HTML
for i in range(len(values)):
htmltext += ‘<\tr> <\th>’ + p_name[i] + ’ </th><\th>’ + values[i] + ‘</th></tr>’

源代码 未修改格式

import os
from ibm_watson import PersonalityInsightsV3
from flask import Flask, redirect, url_for, request, render_template
import json
from os.path import join, dirname
from werkzeug import secure_filename
from json2html import *
import ibm_db

app = Flask(name)

Initiating personality insights service

You need to update iam_apikey and url according to the credentials of your personality insights service

personality_insights = PersonalityInsightsV3(
version=‘2019-06-30’,
iam_apikey=‘0eYfllrHl3RbcB18tTUEmHzevpAcynMVOjnvZVlzlAj6’,
url=‘https://gateway.watsonplatform.net/personality-insights/api’
)
#***********************************************

TODO: establish a connection to the database

c = ibm_db.connect(‘DATABASE=BLUDB;UID=hdb72684;PWD=dgdjfnv706hpkk^t; HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50000;’,’’,’’)

when user lands on the homepage, we redirect to the pi.html page

@app.route(’/’)
def home():
return redirect(url_for(‘static’, filename=‘pi.html’))

handling the form data (upload) form pi.html

@app.route(’/uploader’, methods=[‘GET’, ‘POST’])
def upload_file():
if request.method == ‘POST’:
f = request.files[‘file’]
f.save(secure_filename(f.filename))
# once the file is uploaded, it is sent to the personality insights service by calling the API
with open(join(dirname(file), f.filename)) as profile_f:
profile = personality_insights.profile(
profile_f.read(),
‘application/json’,
content_type=‘text/plain’,
consumption_preferences=True,
raw_scores=True
).get_result()

# getting the json results into a dictionary
jsondict = json.loads(json.dumps(profile))

# TODO: Filter the information listed under "values" property in the json output. You can get this from jsondict dictionary.

key1= jsondict['values']
values = []
p_name = []
for i in key1:
    values.append(str(i["raw_score"]))
    p_name.append(i["name"])

    # TODO: Write an SQL query to include a new entry with the filtered scores.
create_query = "INSERT INTO DASH100495.PPI VALUES ('obama'," + ",".join(values) + ")"
statement = ibm_db.exec_immediate(c, create_query)
ibm_db.free_stmt(statement)
# concatenating HTML code into a variable so that it could be rendered on the web browser.
htmltext = '<h2>Personality Values</h2></br>'
htmltext += '<table border="1"> <tr> <th> Personality Property</th> <th>Value</th></tr>'


# TODO: Concatenate the filtered scores into HTML
for i in range(len(values)):
    htmltext += '<table border="1"> <tr> <th>' + p_name[i] + ' </th><th>' + values[i] + '</th></tr>'

***********************************************

htmltext += '</table>'
return htmltext


# return json2html.convert(json = profile)

if name == ‘main’:
# server port is 8080
port = int(os.getenv(‘PORT’, 8080))
app.run(host=‘0.0.0.0’, port=port, debug=False)
#(0.0.0.0用于联网，127.0.0.1用于本地测试)