def get_request(department_id):
"""
Handle GET request to - /<department_id>/
Return a list of tuples - (<employee id>, <employee name>)
"""
data = [(x[0], x[1]) for x in query_db("employees")
if x[2] == department_id]
response = make_response(json.dumps(data))
response.content_type = 'application/json'
return response
def get_request(department_id):
"""
Handle GET request to - /<department_id>/
Return a list of tuples - (<employee id>, <employee name>)
"""
data = [
(x[0], x[1]) for x in query_db("employees")
if x[2] == department_id]
response = make_response(json.dumps(data))
response.content_type = 'application/json'
return response
def index():
"""
Render form and handle form submission
"""
form = TestForm(request.form)
form.department.choices = [('', 'Select a department')] + [
(x[0], x[1]) for x in query_db("departments")]
chosen_department = None
chosen_employee = None
return render_template('index.html', form=form)
def index():
"""
Render form and handle form submission
"""
form = TestForm(request.form)
form.department.choices = [('', 'Select a department')] + [
(x[0], x[1]) for x in query_db("departments")
]
chosen_department = None
chosen_employee = None
return render_template('index.html', form=form)
def return_district():
mydic = {}
data = request.data
root = ET.fromstring(data)
mydict = {child.tag:child.text for child in root}
try:
if mydict['Content'] == 'Hi':
mydic['Content'] = '你好，我是你的买房私人专家。你想买哪里的房子？'
else:
mydic['Content'] = query_db(mydict['Content'])
except:
mydic['Content'] = '您的输入有误，请重新输入。'
myxml = '''\
<xml>
<ToUserName><![CDATA[{}]]></ToUserName>
<FromUserName><![CDATA[{}]]></FromUserName>
<CreateTime>12345678</CreateTime>
<MsgType><![CDATA[text]]></MsgType>
<Content><![CDATA[{}]]></Content></xml>
'''.format(mydict['FromUserName'],mydict['ToUserName'],mydic['Content'])
return myxml
from query import query_db connected = False drop_item = "DROP TABLE `Item`" drop_user = "******" drop_link = "DROP TABLE `Link`" query_db(drop_link, connected) query_db(drop_user, connected) query_db(drop_item, connected)
from query import query_db
connected = False
create_item = "CREATE TABLE `Item` (" \
"`id` int NOT NULL AUTO_INCREMENT," \
"`item_name` varchar(50) NOT NULL," \
"`price` int NOT NULL," \
"`quantity` int NOT NULL," \
"`status` varchar(20) NOT NULL," \
"`instock` boolean NOT NULL," \
"`src` varchar(250) NOT NULL," \
"`sell` boolean NOT NULL," \
"`rent` boolean NOT NULL," \
"PRIMARY KEY(`id`) )"
create_user = "******" \
"`id` int NOT NULL AUTO_INCREMENT," \
"`user_name` varchar(50) NOT NULL," \
"`password` varchar(64) NOT NULL," \
"PRIMARY KEY(`id`) )"
create_link = "CREATE TABLE `Link` (" \
"`item_id` int NOT NULL," \
"`current_user_id` int NOT NULL," \
"`new_user_id` int," \
"PRIMARY KEY(`item_id`)," \
"FOREIGN KEY(`item_id`) REFERENCES Item(`id`)," \
"FOREIGN KEY(`current_user_id`) REFERENCES User(`id`) )"
query_db(create_item, connected)
query_db(create_user, connected)
query_db(create_link, connected)
def return_district():
mydic = {}
data = request.data
root = ET.fromstring(data)
mydict = {child.tag:child.text for child in root}
try:
if mydict['Content'].lower() == 'hi':
mydic['Content'] = '''你好，我是你的买房私人专家。
本微信号可以提供房源查询以及房价查询功能：
输入a+面积上下限根据面积筛选，例a50:100
输入p+价格上下限根据面积筛选，例p100:200
输入‘合并查询’将前几个条件合并查询
输入cp+地区名查询某地区房价均价，例cp中关村
输入dp+区县名查询某区县房价均价，例dp海淀
不加前缀，默认为区县名，例 海淀'''
elif re.match(r'^a', mydict['Content']):
pattern_a = re.compile(r'\d+')
global area
area = pattern_a.findall(mydict['Content'])
area1 = int(area[0])
area2 = int(area[1])
print "area1:" + str(area1) + "area2:" + str(area2)
try:
mydic['Content'] = query_db_area(area1, area2)
print mydic['Content']
except:
print "query db erorr"
mydic['Content'] = '数据库查询错误，请重新输入。'
elif re.match(r'^p', mydict['Content']):
pattern_p = re.compile(r'\d+')
global price
price = pattern_p.findall(mydict['Content'])
price1 = int(price[0])
price2 = int(price[1])
print "price1:" + price[0] + "price2:" + price[1]
try:
mydic['Content'] = query_db_price(price1, price2)
print mydic['Content']
except:
print "query db erorr"
mydic['Content'] = '数据库查询错误，请重新输入。'
elif re.match(r'^cp', mydict['Content']):
xx=u"([\u4e00-\u9fa5]+)"
pattern = re.compile(xx)
temp = mydict['Content'].decode('utf8')
comarea = pattern.findall(temp)
try:
mydic['Content'] = query_db_cp(comarea)
print mydic['Content']
except:
print "query db erorr"
mydic['Content'] = '数据库查询错误，请重新输入。'
elif re.match(r'^dp', mydict['Content']):
xx=u"([\u4e00-\u9fa5]+)"
pattern = re.compile(xx)
temp = mydict['Content'].decode('utf8')
district = pattern.findall(temp)
try:
mydic['Content'] = query_db_dp(district)
print mydic['Content']
except:
print "query db erorr"
mydic['Content'] = '数据库查询错误，请重新输入。'
else:
print 'here else'
if mydict['Content'] != '合并查询':
global addr
addr = mydict['Content']
try:
mydic['Content'] = query_db(addr)
print mydic['Content']
except:
print "query db erorr"
mydic['Content'] = '数据库查询错误，请重新输入。'
elif mydict['Content'] == '合并查询':
area1 = int(area[0])
area2 = int(area[1])
price1 = int(price[0])
price2 = int(price[1])
print area[0] + area[1] + price[0] + price[1] + addr
try:
mydic['Content'] = query_db_all(price1, price2, area1, area2, addr)
print mydic['Content']
except:
print "query db erorr"
mydic['Content'] = '数据库查询错误，请重新输入。'
#print 'here he'
#print price, area, addr
else:
mydic['Content'] = '您的输入有误，请重新输入。'
except:
mydic['Content'] = '您的输入有误，请重新输入。'
f = open('content.txt', 'a+')
f.write(mydict['Content']+'\n')
f.close()
myxml = '''\
<xml>
<ToUserName><![CDATA[{}]]></ToUserName>
<FromUserName><![CDATA[{}]]></FromUserName>
<CreateTime>12345678</CreateTime>
<MsgType><![CDATA[text]]></MsgType>
<Content><![CDATA[{}]]></Content></xml>
'''.format(mydict['FromUserName'],mydict['ToUserName'],mydic['Content'])
return myxml
conn.send(err.encode())
else:
conn.send(err.encode())
# Check remove arguments
elif arg[1] == "remove":
if len(arg) == 3:
conn.send("Removing item " + str(arg[:]) + " from database".encode())
myquery = arg
else:
conn.send(err.encode())
else:
conn.send(err.encode())
else:
conn.send(err.encode())
if __name__ == "__main__":
print("myquery is type " + str(type(myquery)))
sql = query.query_db(myquery)
if arg[1] == "get":
get = database.get(sql)
rows = ""
for row in get:
rows += str(row) + " "
print(rows)
conn.send(rows.encode())
else:
database.create_query(sql)
conn.close()
from query import query_db
import json
connected = False
insert_users = "INSERT INTO `User` (`user_name`,`password`) VALUES ('{}', '{}')"
insert_items = "INSERT INTO `Item` (`item_name`,`price`,`quantity`,`status`,`instock`,`src`,`sell`,`rent`) VALUES ('{}', {}, {}, '{}', {}, '{}', {}, {})"
insert_links = "INSERT INTO `Link` (`item_id`,`current_user_id`,`new_user_id`) VALUES ({}, {}, {})"
with open("users.json", 'r') as json_file:
all_users = json.load(json_file)
for user in all_users["Users"]:
name = user["user_name"]
user_pass = user["password"]
insert_statement = insert_users.format(name, user_pass)
query_db(insert_statement,connected)
with open("items.json", 'r') as json_file:
all_items = json.load(json_file)
for item in all_items["for_sell"]:
name = item["item_name"]
price = item["price"]
quantity = item["quantity"]
status = item["status"]
instock = item["instock"]
src = item["src"]
sell = item["sell"]
rent = item["rent"]
insert_statement = insert_items.format(name,price,quantity,status,instock,src,sell,rent)
query_db(insert_statement,connected)
