def add(name,
question=None,
answer=None,
tag_num=None,
title=None,
content=None,
file_name=None):
conn = None
cur = None
if name == 'other':
conn = get_connection()
cur = conn.cursor()
sql = 'insert into article(title,url_address,content,tag_id)VALUES (%s,%s,%s,%s)'
try:
cur.execute(sql, (title, file_name, content, tag_num))
conn.commit()
finally:
conn.close()
cur.close()
elif name == 'QA':
conn = get_connection()
cur = conn.cursor()
sql = 'insert into qa(question,answer,tag_id)VALUES (%s,%s,%s)'
try:
cur.execute(sql, (question, answer, tag_num))
conn.commit()
except Exception as e:
conn.rollback()
finally:
conn.close()
cur.close()
def __create_transformed_sales_table(table_name: str):
postgres.create_table(get_connection(), table_name, [
"id serial PRIMARY KEY", "app_id varchar(36) NOT NULL UNIQUE",
"date date", "address varchar(255)", "postcode varchar(20)",
"county varchar(20)", "price decimal", "full_price boolean",
"vat_exclusive boolean", "new boolean", "size varchar(20)"
])
def persist_raw_sales(property_sales: List[RawPropertySale]):
table_name = config['storage']['table']['raw']
postgres.drop_table(get_connection(), table_name)
logging.debug("Dropped table '{}'".format(table_name))
__create_raw_sale_table(table_name)
logging.debug("Table '{}' is available".format(table_name))
__insert_raw_property_sales(table_name, property_sales)
def __create_raw_sale_table(table_name: str):
postgres.create_table(get_connection(), table_name, [
"id serial PRIMARY KEY", "app_id varchar(36) NOT NULL UNIQUE",
"date varchar(20)", "address varchar(255)", "postcode varchar(20)",
"county varchar(20)", "price varchar(20)",
"not_full_price varchar (20)", "vat_exclusive varchar (20)",
"property_description varchar(255)", "size_description varchar(255)"
])
def persist_transformed_sales(
transformed_sales: List[TransformedPropertySale]):
table_name = config['storage']['table']['transformed']
postgres.drop_table(get_connection(), table_name)
logging.debug("Dropped table '{}'".format(table_name))
__create_transformed_sales_table(table_name)
logging.debug("Table '{}' is available".format(table_name))
__insert_transformed_sales(table_name, transformed_sales)
def __insert_transformed_sales(table_name: str,
property_sales: List[TransformedPropertySale]):
postgres.bulk_insert(get_connection(), table_name, [
"app_id", "date", "address", "postcode", "county", "price",
"full_price", "vat_exclusive", "new", "size"
], ((sale.app_id, sale.date, sale.address, sale.postcode, sale.county,
sale.price, sale.full_price, sale.vat_exclusive, sale.new, sale.size)
for sale in property_sales))
def get_done(risk_num):
conn = get_connection()
cur = conn.cursor()
sql = 'update user set risk_tendency={} where name="dp"'.format(risk_num)
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
return
def __insert_raw_property_sales(table_name: str,
property_sales: List[RawPropertySale]):
postgres.bulk_insert(get_connection(), table_name, [
"app_id", "date", "address", "postcode", "county", "price",
"not_full_price", "vat_exclusive", "property_description",
"size_description"
], ((sale.app_id, sale.date, sale.address, sale.postcode, sale.county,
sale.price, sale.not_full_price, sale.vat_exclusive,
sale.property_description, sale.size_description)
for sale in property_sales))
def get_top10_funplay():
conn = get_connection()
cur = conn.cursor()
sql = 'select id,rank,name,pic_url from amusement_project order by rank DESC '
cur.execute(sql)
res = cur.fetchall()
data = []
for i in res:
content = {"id": i[0], "rank": i[1], "name": i[2], "pic_url": i[3]}
data.append(content)
return data
def create_post_record(post):
if check_post_exists_by_slug(post["slug"]):
return
connection = get_connection()
with connection.cursor() as cursor:
sql = """INSERT INTO kodilan_posts(slug, position, location, created_at, company, tags, description)VALUES(%s,%s,%s,now(),%s,%s,%s)"""
cursor.execute(
sql,
(post.get('slug'), post.get('position'), post.get('location'),
post.get('company'), post.get('tags'), post.get('description')))
connection.commit()
def run_query(
query,
binds=None,
cursor_type=pymysql.cursors.DictCursor) -> pymysql.cursors.Cursor:
connection = get_connection()
cursor = connection.cursor(cursor_type)
if (binds is None):
cursor.execute(query)
else:
cursor.execute(query, binds)
connection.commit()
return cursor
def open(self, message_type, destination):
from app import get_connection
""" example: open(message_type='room', destination='room1') or
open(message_type='user' destination='fred')"""
if not destination or not message_type:
self.write_message({'error': 1, 'text': 'Error: No room or user specified'})
self.close()
self.connection = get_connection()
self.destination = self._format_destination(message_type, destination)
self.message_type = message_type
yield tornado.gen.Task(self.connection.subscribe, self.destination)
self.connection.listen(self.on_messages_published)
def get_front_end_tech_stats(startDate, endDate, order="desc"):
connection = get_connection()
langs = [
"react", "vue", "jquery", "bootstrap", "angular", "redux", "vuex",
"figma", "photoshop"
]
with connection.cursor() as cursor:
res = []
for lang in langs:
where = f'where (tags like %s or tags like %s) && `created_at` >= %s && `created_at` <= %s'
sql = f'select %s as tech, count(*) as total from kodilan_posts {where}'
cursor.execute(
sql, (lang, f'%{lang}', f'%{lang},%', startDate, endDate))
res.append(cursor.fetchone())
res.sort(key=operator.itemgetter('total'), reverse=True)
return res
def get_tech_stats(startDate, endDate, order="desc"):
connection = get_connection()
langs = [
"spring", "django", "nodejs", "react", "vue", "react-native",
"flutter", "rails", "dotnet", "laravel"
]
with connection.cursor() as cursor:
res = []
for lang in langs:
where = f'where (tags like %s or tags like %s) && `created_at` >= %s && `created_at` <= %s'
sql = f'select %s as tech, count(*) as total from kodilan_posts {where}'
cursor.execute(
sql, (lang, f'%{lang}', f'%{lang},%', startDate, endDate))
res.append(cursor.fetchone())
res.sort(key=operator.itemgetter('total'), reverse=True)
return res
def get_lang_stats(startDate, endDate, order="desc"):
connection = get_connection()
langs = [
"java", "c#", "python", "javascript", "go", "dart", "php", "ruby", "c",
"c++", "typescript"
]
with connection.cursor() as cursor:
res = []
for lang in langs:
where = f'where (tags like %s or tags like %s) && `created_at` >= %s && `created_at` <= %s'
sql = f'select %s as lang,count(*) as total from kodilan_posts {where}'
cursor.execute(
sql, (lang, f'%{lang}', f'%{lang},%', startDate, endDate))
res.append(cursor.fetchone())
res.sort(key=operator.itemgetter('total'), reverse=True)
return res
def get_new_by_type(type):
conn = get_connection()
cur = conn.cursor()
sql = 'select * from article where tag_id={}'.format(type)
cur.execute(sql)
res = cur.fetchall()
data = []
for i in res:
content = {
"id": i[0],
"title": i[1],
"url_address": i[2],
"content": i[3],
"collection": i[5]
}
data.append(content)
return data
def get_order(name):
conn = get_connection()
cur = conn.cursor()
sql = 'select * from amusement_project where name=%s'
time_list = []
cur.execute(sql,name)
res = cur.fetchall()
time=res[2]
max_man=res[6]
for i in range(1,5):
wait_people=random.randint(1,max_man)
content={"back_time":back_time(time,i)}
time_list.append(content)
conn.close()
cur.close()
this_data={"id":res[0],"name":res[1],"back_time":time_list}
return this_data
def login2():
APPID = 'wxe9f8ea2b5c9b1f26'
SECRET = 'bd9151ef9d8d4baeed692e67cec9b16b'
# 获取appid和secret
data = request.get_json()
# 拿到微信小程序发送code信息
JSCODE = data["code"]
url = 'https://api.weixin.qq.com/sns/jscode2session?appid=' + APPID + '&secret=' + SECRET + '&js_code=' + JSCODE + '&grant_type=authorization_code'
# 腾讯的ap
print(url)
res = requests.get(url).json()
print(type(res))
if 'openid' in res.keys():
openid = res["openid"]
con = None
cur = None
try:
con = get_connection()
cur = con.cursor()
sql = 'INSERT INTO `user` (id) VALUES (%s)'
cur.execute(sql, openid)
con.commit()
session_key = res["session_key"]
# 访问腾讯给的接口用code换取openid和session——
sessionid = uuid.uuid1()
# 随机生产sessionid作为key
value = '"openid":"{}","session_key":"{}"'.format(
openid, session_key)
# openid和session_key作为value
conn = redis.StrictRedis(host='120.79.154.232', port=6379)
# 连接redis数据库ps这里我是试用我自己的来测试
conn.hset("3rd_sessionid", sessionid, value)
# 数据传入数据库
return jsonify({'code': 1, 'msg': '获取成功', 'data': sessionid})
except Exception as e:
con.rollback()
raise e
finally:
if con is not None:
cur.close()
con.close()
else:
return jsonify({'code': 0, 'msg': '获取失败', 'data': None})
def get(tag):
conn = get_connection()
cur = conn.cursor()
sql = 'select url,content,title,collection_num from for_something where tag_id=%s'
cur.execute(sql, tag)
res = cur.fetchall()
print(res)
data = []
for i in res:
list = {
"url": i[0],
"content": i[1],
"title": i[2],
"collection_num": i[3]
}
data.append(list)
cur.close()
conn.close()
return data
def get_all_news():
conn = None
cur = None
conn = get_connection()
cur = conn.cursor()
sql = 'select * from article'
data=[]
content={}
try:
cur.execute(sql)
res=cur.fetchall()
for i in res:
print(i)
content={"id":i[0],"title":i[1],"url_address":i[2],"content":i[3]}
data.append(content)
except Exception as e:
conn.rollback()
finally:
conn.close()
cur.close()
return data
import app, time
while True:
try:
app.get_connection()
app.query_db("SELECT 1")
print("successfully connected to DB")
break
except:
print("couldn't connect to DB, waiting...")
time.sleep(1)
time.sleep(1)
def read_transformed_sales() -> List[TransformedPropertySale]:
table_name = config['storage']['table']['transformed']
logging.debug(
"Reading all transformed property sales from '{}'".format(table_name))
sales = postgres.read_all(get_connection(), table_name)
return [__create_transformed_sale_from_db(db_tuple) for db_tuple in sales]
