def get_table_live():
v2t = {
'org.apache.cassandra.db.marshal.UTF8Type': 'text',
'org.apache.cassandra.db.marshal.LongType': 'bigint',
'org.apache.cassandra.db.marshal.Int32Type': 'int',
'org.apache.cassandra.db.marshal.FloatType': 'float',
'org.apache.cassandra.db.marshal.TimestampType': 'timestamp',
'org.apache.cassandra.db.marshal.BooleanType': 'boolean',
}
tables = {}
cfs = db.execute('''select columnfamily_name, column_aliases, key_aliases
from system.schema_columnfamilies where keyspace_name=:keyspace''',
dict(keyspace=keyspace),
result=True).results
for table, cols, keys in cfs:
pk = eval(keys)
pk.extend(eval(cols))
cols = {}
for cname, validator in db.execute(
'''select column_name, validator from system.schema_columns
where keyspace_name=:keyspace and columnfamily_name=:cfname allow filtering''',
dict(keyspace=keyspace, cfname=table),
result=True).results:
cols[cname] = v2t.get(validator, validator)
tables[table] = {'cols': cols, 'pk': pk}
return tables
def insertIntoTempUser(id, type, name, pwd, email):
"""将新申请的用户加入数据库"""
sql = "insert into tempuser (id,type,name,pwd,email) values ('%s','%s','%s','%s','%s');" % (
clean(id), clean(type), clean(name), clean(pwd), email)
# email到底怎么防止注入呢orz 并不会啊，需要改进
db.execute(sql)
def insertIntoTempUser(id, type, name, pwd, email):
"""将新申请的用户加入数据库"""
sql = "insert into tempuser (id,type,name,pwd,email) values ('%s','%s','%s','%s','%s');" % (
clean(id), clean(type), clean(name), clean(pwd), email)
# email到底怎么防止注入呢orz 并不会啊，需要改进
db.execute(sql)
def update_zhishu():
from flask import request
ktype = request.args.get('ktype')
key = request.args.get('key')
stype = request.args.get('stype')
val = request.args.get('val')
print ktype, key, stype, val, "##############"
ret = None
if ktype == "brand":
if stype == "query":
ret = db.execute('UPDATE ataobao2.brand SET search_index=:search_index WHERE name =:name',
dict(search_index=int(val), name=key)
)
elif stype == "trade":
ret = db.execute('UPDATE ataobao2.brand SET sales_index=:sales_index WHERE name =:name',
dict(sales_index=int(val), name=key)
)
elif ktype == "cate":
if stype == "query":
ret = db.execute('UPDATE ataobao2.cate SET search_index=:search_index WHERE id =:id',
dict(search_index=int(val), id=int(key))
)
elif stype == "trade":
pass
return str(ret)
def update_zhishu():
from flask import request
ktype = request.args.get('ktype')
key = request.args.get('key')
stype = request.args.get('stype')
val = request.args.get('val')
print ktype, key, stype, val, "##############"
ret = None
if ktype == "brand":
if stype == "query":
ret = db.execute(
'UPDATE ataobao2.brand SET search_index=:search_index WHERE name =:name',
dict(search_index=int(val), name=key))
elif stype == "trade":
ret = db.execute(
'UPDATE ataobao2.brand SET sales_index=:sales_index WHERE name =:name',
dict(sales_index=int(val), name=key))
elif ktype == "cate":
if stype == "query":
ret = db.execute(
'UPDATE ataobao2.cate SET search_index=:search_index WHERE id =:id',
dict(search_index=int(val), id=int(key)))
elif stype == "trade":
pass
return str(ret)
def new_course(tid, idc, year, st, en):
"""教师新增加课程"""
sql = "insert into Course (idCourse,tid,year,start_week,end_week) " \
"values ('%s','%s','%s','%s','%s');" \
% (clean(idc), clean(tid), year, st, en)
db.execute(sql)
def delete_server(name="", fuel_versions=None):
if fuel_versions is None:
db.execute("DELETE FROM Server " "WHERE name='{0}';".format(name))
return "Request for deleting server {} " \
"has been accepted".format(name)
else:
return delete_server_from_certification(name, fuel_versions)
def backup(self):
exportPath = datetime.today().strftime("%m-%d-%Y_%H-%M") + "_DB_EXPORT.csv"
# the export file will be stored at '/usr/local/mysql-someedition/data/' on *nix system
if db.execute("SHOW TABLES LIKE \'complaint\'").arraysize > 0:
db.execute("SELECT * FROM complaint INTO OUTFILE $exportPath \
FIELDS TERMINATED BY \',\'\
ENCLOSED BY \'\"\'\
LINES TERMINATED BY \'\n\'")
def cqlui_table(table):
page = int(request.args.get('page', 1))
limit = int(request.args.get('limit', 20))
order_by = request.args.get('order_by')
column_clause = ''
limit_clause = 'limit {}'.format(limit)
where_clause = ''
order_by_clause = ''
try:
if request.method == 'POST':
for k, v in request.form.iteritems():
if not k or not v:
continue
if k == 'select_columns':
for column in request.form.getlist(k):
column_clause += ', {}'.format(column) \
if column_clause else '{}'.format(column)
continue
if not where_clause:
where_clause = 'where {} = {}'.format(k, v)
else:
where_clause += 'and {} = {}'.format(k, v)
if not column_clause:
column_clause = '*'
if order_by:
order_by_clause = 'order by {} {}'.format(order_by, request.args.get('scend')).strip()
count = 1000000000000
cql = 'select {} from {}.{} {} {} {}'.format(\
column_clause, KEYSPACE, table, where_clause, order_by_clause, limit_clause)
columns, rows = db.execute(cql, result=True)
except:
traceback.print_exc()
count = 0
try:
columns, rows = db.execute('select {} from {}.{} {}'.format(\
column_clause, KEYSPACE, table, limit_clause), result=True)
rows = []
except:
traceback.print_exc()
full_columns = db.execute("select column_name from system.schema_columns \
where keyspace_name='{}' and columnfamily_name='{}';".format(KEYSPACE, table), result=True)[1]
return render_template(
'cqlui/table.html',
columns = columns,
rows = rows,
table = table,
full_columns = full_columns,
**paginate(count=count, page=page, limit=limit)
)
def delete_server_from_certification(name, fuel_versions):
certifications = select(certification for server in Server
for certification in Certification
if certification.fuel_version.name in fuel_versions
and server.name == name)[:]
with db_session:
for c in certifications:
db.execute("DELETE FROM Certification "
"WHERE id='{0}';".format(c.id))
def authNewUser(id):
"""管理员通过信用户申请"""
sql = "select * from tempuser where id='%s';" % (clean(id))
user = db.get(sql)
name = user['name']
pwd = user['pwd']
type = user['type']
email = user['email']
if type == 't':
sql = "insert into Teacher (idTeacher,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
elif type == 's':
sql = "insert into Student (idStudent,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
else:
return 'fail'
def authNewUser(id):
"""管理员通过信用户申请"""
sql = "select * from tempuser where id='%s';" % (clean(id))
user = db.get(sql)
name = user['name']
pwd = user['pwd']
type = user['type']
email = user['email']
if type == 't':
sql = "insert into Teacher (idTeacher,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
elif type == 's':
sql = "insert into Student (idStudent,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
else:
return 'fail'
def publish_res(tid, idInfo, detail, title):
"""教师发布课程 资源"""
if not getTeacher(tid):
return 't nt exist'
# 需要加入判断内容长度的部分，防止内容转换后过长
detail = text2Html(detail)
title = clean(title)
sql = '''insert into Info (tid,idInfo,detail,title,type) values ('%s','%s','%s','%s','%s');''' \
% (clean(tid), clean(idInfo), detail, title, 'res');
db.execute(sql)
def imp_controls_from_json(json_filepath=CONTROLS_FILE):
try:
with open(json_filepath, 'r') as f:
contrs = json.load(f)
query = Control.select()
cursor = db.execute(query)
control_fnames = [x[0] for x in cursor.fetchall()]
for uid in contrs:
if contrs.get("filename") not in control_fnames:
Control.create(
uid=uid,
filename=contrs.get(uid).get("filename"),
title=contrs.get(uid).get("title"),
requirements=contrs.get(uid).get("requirements"),
description=contrs.get(uid).get("description"))
except (ValueError, FileNotFoundError):
print(
"[{FAIL}ERR{END}] {CONTROLS_FILE} file error! Test the project (doc tests/README.md)"
.format(FAIL=C_FAIL, END=C_END, CONTROLS_FILE=CONTROLS_FILE))
close_program()
else:
print("[{OK}OK{END}] Imported data from {CONTROLS_FILE}".format(
OK=C_OKGREEN, END=C_END, CONTROLS_FILE=CONTROLS_FILE))
def refineBlock(id):
answer = db.execute(
'SELECT gid, block_num FROM forest.block WHERE forestry_id = {} ORDER BY block_num ASC'
.format(int(id)))
return json.dumps({'result': [dict(row) for row in answer]},
ensure_ascii=False,
default=str)
def submit_homework_vedio(idHomework, sid, content, tag):
"""学生提交作业 视频"""
sql = "insert into Homework (idHomework,cid,sid,content,tag,type) values " \
"('%s',(select cid from Student where idStudent='%s'),'%s','%s','%s','video')" \
% (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def blacklist_del():
global blacklist, whitelist
if whitelist is None:
whitelist = []
if blacklist is None:
blacklist = []
type = 'shop' + request.args.get('type', 'blacklist')[:-4]
shopid = request.args.get('shopid', 0)
print 'deleting {} {}'.format(type, shopid)
blacklist = [r for r in blacklist if str(r[0]) != str(shopid)]
whitelist = [r for r in whitelist if str(r[0]) != str(shopid)]
db.execute(
'delete from ataobao2.blacklist where type=:type and args=:args',
dict(type=type, args=str(shopid)))
return redirect('/blacklist/#' + request.args.get('type', 'blacklist'))
def submit_homework_vedio(idHomework, sid, content, tag):
"""学生提交作业 视频"""
sql = "insert into Homework (idHomework,cid,sid,content,tag,type) values " \
"('%s',(select cid from Student where idStudent='%s'),'%s','%s','%s','video')" \
% (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def blacklist_add():
global blacklist, whitelist
if whitelist is None:
whitelist = []
if blacklist is None:
blacklist = []
type = request.args.get('type')
if type == 'blacklist':
for itemid in request.args.get('itemid', '0').split(','):
try:
itemid = int(itemid)
except:
itemid = 0
if itemid == 0:
# illegal input
pass
elif itemid not in [v[1] for v in blacklist]:
# should get shopid and save to blacklist
i = get_item(itemid)
if not i or 'error' in i:
pass
else:
if i['shopid'] != 0:
shopid = i['shopid']
print 'inserting shopblack, {}, {}'.format(
shopid, itemid)
blacklist.insert(0, [shopid, itemid])
db.execute(
'insert into ataobao2.blacklist (type, args, value) values (:type, :args, :value)',
dict(type='shopblack',
args=str(shopid),
value=str(itemid)))
elif type == 'whitelist':
for shopid in request.args.get('shopid', '0').split(','):
try:
shopid = int(shopid)
except:
shopid = 0
if shopid == 0:
pass
else:
print 'inserting shopwhite, {}'.format(shopid)
whitelist.insert(0, [shopid, None])
db.execute(
'insert into ataobao2.blacklist (type, args) values (:type, :args)',
dict(type='shopwhite', args=str(shopid)))
return redirect('/blacklist/#' + type)
def publish_notif(tid, idInfo, detail, title):
"""教师发布课程 通知 """
if not getTeacher(tid):
return "t nt exist"
# 需要加入判断内容长度的部分，防止内容转换后过长
# detail = text2Html(detail)
title = clean(title)
sql = "insert into Info (tid,idInfo,detail,title) values ('%s','%s','%s','%s');" % (
clean(tid),
clean(idInfo),
detail,
title,
)
db.execute(sql)
def get_lists():
global last_update, blacklist, whitelist
if last_update is None or last_update < time.time() - 86400:
r = db.execute('select type, args, value from ataobao2.blacklist',
result=True)
last_update = time.time()
blacklist = list(
reversed([row[1:] for row in r.results if row[0] == 'shopblack']))
whitelist = list(
reversed([row[1:] for row in r.results if row[0] == 'shopwhite']))
return blacklist, whitelist
def set_alias(username, new_alias):
global ALIAS
if new_alias in ALIAS:
return (
False,
f"Maaf, sudah ada user '{ALIAS[new_alias]}' dengan alias '{new_alias}'. Mohon ubah dahulu alias yang lama agar tidak terjadi bentrok alias, kemudian lakukan reload_data"
)
query_find_user = """
SELECT username
FROM users
WHERE username = :username"""
res_find_user = db.execute(query_find_user, {
'username': username
},
once=True).fetchall()
if len(res_find_user) < 1:
query_update = """
INSERT INTO users
(username, alias, password)
VALUES(:username, :alias, :password)"""
res = db.execute(query_update, {
'username': username,
'alias': new_alias,
'password': username,
},
once=True)
else:
query_update = """
UPDATE users
SET alias = :alias
WHERE username = :username"""
res = db.execute(query_update, {
'alias': new_alias,
'username': username
},
once=True)
load_user_data()
return (True, 'success')
def submit_homework(idHomework, sid, content, tag):
"""学生提交作业 三视图"""
sql = "insert into Homework " \
"(idHomework,cid,sid,content,tag) " \
"values " \
"('%s'," \
"(select cid from Student where idStudent='%s')," \
"'%s'," \
"'%s'," \
"'%s')" % (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def delete_component(name=None, hw_id=None):
if name is not None:
cmp = select(c for c in Component if c.name == name)[:]
else:
raise Exception('Specify component name')
if hw_id is not None:
components = filter(lambda c: c.hw_id == hw_id, cmp)
else:
components = cmp
if len(components) == 0:
return "Nothing to delete"
if len(components[0].servers) > 0:
return "Cannot delete component that is used in servers"
else:
db.execute("DELETE FROM Component "
"WHERE name='{0}';".format(components[0].name))
return "Request for deleting component {0} " \
"has been accepted".format(name)
def submit_homework(idHomework, sid, content, tag):
"""学生提交作业 三视图"""
sql = "insert into Homework " \
"(idHomework,cid,sid,content,tag) " \
"values " \
"('%s'," \
"(select cid from Student where idStudent='%s')," \
"'%s'," \
"'%s'," \
"'%s')" % (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def set_alias(username, new_alias):
global ALIAS
print('set alias for : username', username, 'new_alias', new_alias)
if new_alias in ALIAS:
return (False, 'Alias already exists')
query_find_user = """
SELECT username
FROM users
WHERE username = :username"""
res_find_user = db.execute(
query_find_user,
{'username':username},
once=True).fetchall()
print('res_find_user', res_find_user)
if len(res_find_user) < 1:
query_update = """
INSERT INTO users
(username, alias, password)
VALUES(:username, :alias, :password)"""
res = db.execute(
query_update, {
'username':username,
'alias':new_alias,
'password':username,
}, once=True)
else:
query_update = """
UPDATE users
SET alias = :alias
WHERE username = :username"""
res = db.execute(
query_update, {
'alias':new_alias,
'username':username
}, once=True)
load_user_data()
return (True, 'success')
def getForestComposition(blockId, cycle):
answer = db.execute(
'SELECT stand_code FROM forest.stand WHERE block_id = {} ORDER BY block_num ASC'
.format(int(blockId)))
d, standCodes = {}, []
preStandEstimations, standEstimations, standEstimationIds = {}, [], []
for part in answer:
for column, value in part.items():
d = {**d, **{column: value}}
standCodes.append(d)
for stand in standCodes:
estimation = db.execute(
'SELECT * FROM forest.tax WHERE stand_code = {} AND cycle = {} ORDER BY stand_num'
.format(stand['stand_code'], cycle))
for part in estimation:
for column, value in part.items():
preStandEstimations = {
**preStandEstimations,
**{
column: value
}
}
standEstimations.append(preStandEstimations)
standEstimationIds.append(
preStandEstimations.get('standestimation_id'))
standForestComposeResult = []
for id in standEstimationIds:
c, preStandForestComposeResult = {}, []
standForestCompose = db.execute(
'SELECT woodspecies_id, standestimation_id, species_percent, plan_fact from forest.forestcomposition WHERE standestimation_id = {}'
.format(id))
for part in standForestCompose:
for column, value in part.items():
c = {**c, **{column: value}}
preStandForestComposeResult.append(c)
standForestComposeResult.append(preStandForestComposeResult)
return (json.dumps(standForestComposeResult,
ensure_ascii=False,
default=str))
def getData(blockId, cycle):
answer = db.execute(
'SELECT stand_code FROM forest.stand WHERE block_id = {} ORDER BY block_num ASC'
.format(int(blockId)))
d, standCodes = {}, []
preStandEstimations, standEstimations, standEstimationIds = {}, [], []
for part in answer:
for column, value in part.items():
d = {**d, **{column: value}}
standCodes.append(d)
# for stand in standCodes:
# print(stand['stand_code'])
for stand in standCodes:
# print(stand)
estimation = db.execute(
'SELECT * FROM forest.tax WHERE stand_code = {} AND cycle = {} ORDER BY stand_num'
.format(stand['stand_code'], cycle))
for part in estimation:
for column, value in part.items():
preStandEstimations = {
**preStandEstimations,
**{
column: value
}
}
# print(preStandEstimations.get('standestimation_id'))
# print(preStandEstimations.items())
standEstimations.append(preStandEstimations)
standEstimationIds.append(
preStandEstimations.get('standestimation_id'))
# print(standEstimations[1])
# print(standEstimationIds)
# print(json.dumps({ 'result': [dict(row) for row in standEstimations] }, ensure_ascii=False, default=str))
# return json.dumps({'result': [dict(row) for row in answer]}, ensure_ascii=False, default=str)
return json.dumps({'result': [dict(row) for row in standEstimations]},
ensure_ascii=False,
default=str)
def getActions(blockId, cycle):
answer = db.execute(
'SELECT stand_code FROM forest.stand WHERE block_id = {} ORDER BY block_num ASC'
.format(int(blockId)))
d, standCodes = {}, []
preStandEstimations, standEstimations, standEstimationIds = {}, [], []
for part in answer:
for column, value in part.items():
d = {**d, **{column: value}}
standCodes.append(d)
for stand in standCodes:
estimation = db.execute(
'SELECT * FROM forest.tax WHERE stand_code = {} AND cycle = {} ORDER BY stand_num'
.format(stand['stand_code'], cycle))
for part in estimation:
for column, value in part.items():
preStandEstimations = {
**preStandEstimations,
**{
column: value
}
}
standEstimations.append(preStandEstimations)
standEstimationIds.append(
preStandEstimations.get('standestimation_id'))
actionsResult = []
for id in standEstimationIds:
c, preActionsResult = {}, []
action = db.execute(
'SELECT action_id, standestimation_id, actiontype_id, actionurgency_id from forest.action WHERE standestimation_id = {}'
.format(id))
for part in action:
for column, value in part.items():
c = {**c, **{column: value}}
preActionsResult.append(c)
actionsResult.append(preActionsResult)
print(standEstimationIds)
return (json.dumps(actionsResult, ensure_ascii=False, default=str))
def get_table_live():
v2t = {
'org.apache.cassandra.db.marshal.UTF8Type': 'text',
'org.apache.cassandra.db.marshal.LongType': 'bigint',
'org.apache.cassandra.db.marshal.Int32Type': 'int',
'org.apache.cassandra.db.marshal.FloatType': 'float',
'org.apache.cassandra.db.marshal.TimestampType': 'timestamp',
'org.apache.cassandra.db.marshal.BooleanType': 'boolean',
}
tables = {}
cfs = db.execute('''select columnfamily_name, column_aliases, key_aliases
from system.schema_columnfamilies where keyspace_name=:keyspace''',
dict(keyspace=keyspace), result=True).results
for table, cols, keys in cfs:
pk = eval(keys)
pk.extend(eval(cols))
cols = {}
for cname, validator in db.execute('''select column_name, validator from system.schema_columns
where keyspace_name=:keyspace and columnfamily_name=:cfname allow filtering''',
dict(keyspace=keyspace, cfname=table), result=True).results:
cols[cname] = v2t.get(validator, validator)
tables[table] = {'cols':cols, 'pk':pk}
return tables
def insert(chat_id, message_id, content):
insert_content = content \
if isinstance(content,str) else \
json.dumps(content)
query_insert = """
INSERT INTO chat_history
(chat_id, message_id, content)
VALUES (:chat_id, :message_id, :content)"""
res = db.execute(query_insert, {
'chat_id': chat_id,
'message_id': message_id,
'content': insert_content
},
once=True)
return res
def populateTable(type):
# fields: name, address, zipcode, lat/long, opening_hours, business_status, rating, website, google_maps_url
# phone_number, city_id, drugstores_nearby
for city in cities_list:
city_info = get_city_opendata(city)
result = getJSON(type, city, city_info['state'])
name = result['name']
address = result['address']
zipcode = result['zipcode']
latitude = result['latitude']
longitude = result['longitude']
opening_hours = result['opening_hours']
business_status = result['business_status']
# rating = result['rating']
# website = result['website']
google_maps_url = result['url']
# phone_number = result['phone_number']
id = result['place_id']
city_id = db.execute("SELECT * FROM city WHERE name = :city", {
":city": city
}).first()
if (type == 'hospital'):
entry = Hospital(id=id,
name=name,
address=address,
zipcode=zipcode,
latitude=latitude,
longitude=longitude,
opening_hours=opening_hours,
business_status=business_status,
google_maps_url=google_maps_url,
city_id=city_id)
else:
entry = Drugstore(id=id,
name=name,
address=address,
zipcode=zipcode,
latitude=latitude,
longitude=longitude,
opening_hours=opening_hours,
business_status=business_status,
google_maps_url=google_maps_url,
city_id=city_id)
db.session.add(entry)
db.session.commit()
def get(chat_id=None, message_id=None):
query_select = """
SELECT * FROM chat_history
WHERE chat_id = :chat_id AND
message_id = :message_id"""
res = db.execute(query_select, {
'chat_id': chat_id,
'message_id': message_id,
},
once=True).fetchone()
if res is None:
return None
res = dict(res)
if isinstance(res['content'], str):
res['content'] = json.loads(res['content'])
return res
def execute(self):
if self.status is Query.Inserted:
self.scan()
self.generate()
self.before_execution()
if not self.has_error():
try:
result = db.execute(self.sql).fetchall()
if self.select:
self.result = {item[0]: item[1] for item in result}
else:
self.result = [item[0] for item in result]
logger.debug(self.result)
self.status = Query.Executed
except Exception as e:
self.error_string = str(e)
self.error = Query.ExecutionError
self.after_execution()
logger.debug(self)
def update_comment(hid, comment):
"""添加/修改作业的评语"""
sql = 'update Homework set comment="%s" where idHomework="%s";' % (text2Html(comment), clean(hid))
return db.execute(sql)
def check_schema(fix=False):
t1 = get_table_defs()
t2 = get_table_live()
problems = 0
plans = []
for table in t1:
print 'checking table {}'.format(table)
t1t = t1[table]
if table not in t2:
print '... table {} does not exists'.format(table)
problems += 1
stmt = ['create table {}.{} ('.format(keyspace, table)]
for cname, ctype in t1t['cols'].items():
stmt.append(' {} {},'.format(cname, ctype))
stmt.append(' primary key ({})'.format(', '.join(t1t['pk'])))
stmt.append(');')
plans.append('\n'.join(stmt))
continue
t2t = t2[table]
if t1t['pk'] != t2t['pk']:
print '... primary key differs'
problems += 1
plans.append('drop table {}.{}'.format(keyspace, table))
stmt = ['create table {}.{} ('.format(keyspace, table)]
for cname, ctype in t1t['cols'].items():
stmt.append(' {} {},'.format(cname, ctype))
stmt.append(' primary key ({})'.format(', '.join(t1t['pk'])))
stmt.append(');')
plans.append('\n'.join(stmt))
continue
t2tcnames = set(t2t['cols'].keys())
t1tcnames = set(t1t['cols'].keys())
missing = list(t1tcnames - t2tcnames)
unused = list(t2tcnames - t1tcnames)
if missing:
print '... missing columns {}'.format(missing)
for cname in missing:
ctype = t1t['cols'][cname]
problems += 1
plans.append('alter table {}.{} add {} {}'.format(keyspace, table, cname, ctype))
if unused:
print '... unused columns {}'.format(unused)
for cname in unused:
problems += 1
if cname in t2t['pk']:
print('... oops you are going to delete a primary key, the only way of doing it is to recreate the table')
plans.append('drop table {}.{}'.format(keyspace, table))
stmt = ['create table {}.{} ('.format(keyspace, table)]
for cname, ctype in t1t['cols'].items():
stmt.append(' {} {},'.format(cname, ctype))
stmt.append(' primary key ({})'.format(', '.join(t1t['pk'])))
stmt.append(');')
plans.append('\n'.join(stmt))
else:
plans.append('alter table {}.{} drop {}'.format(keyspace, table, cname))
for cname, ctype in t1t['cols'].items():
if cname in t2t['cols'] and t2t['cols'][cname] != ctype:
wtype = t2t['cols'][cname]
print '... {} has wrong type {}, != {}'.format(cname, wtype, ctype)
problems += 1
if cname in t2t['pk']:
print '... unfortunately this field is in primary key, we need to rebuild the whole table!!!'
plans.append('drop table {}.{}'.format(keyspace, table))
stmt = ['create table {}.{} ('.format(keyspace, table)]
for cname, ctype in t1t['cols'].items():
stmt.append(' {} {},'.format(cname, ctype))
stmt.append(' primary key ({})'.format(', '.join(t1t['pk'])))
stmt.append(');')
plans.append('\n'.join(stmt))
else:
plans.append('alter table {}.{} drop {} '.format(keyspace, table, cname))
plans.append('alter table {}.{} add {} {}'.format(keyspace, table, cname, ctype))
print 'check finished, {} problems found'.format(problems)
if plans:
print 'fix plans:'
for plan in plans:
print ' ', plan.replace('\n', '\n ')
if fix:
db.execute(plan)
if fix:
print 'fixed'
def update_notif(idInfo, detail, title):
"""更新消息"""
# detail = text2Html(detail)
sql = "update Info set detail='%s',title='%s' where idInfo='%s';" % (detail, title, idInfo)
db.execute(sql)
def delete_notif(iid, tid):
"""教师删除发布的消息"""
sql = 'delete from Info where tid="%s" and idInfo="%s"' % (clean(tid), clean(iid))
db.execute(sql)
def create_table(cls):
db.execute("CREATE TABLE IF NOT EXISTS cells ("
"id INTEGER PRIMARY KEY NOT NULL,"
"row INTEGER NOT NULL,"
"column INTEGER NOT NULL,"
"data REAL)")
def set_course(uid, cid):
"""学生录入课序号"""
sql = "update Student set cid='%s' where idStudent='%s';" % (clean(cid), clean(uid))
db.execute(sql)
def update_comment(hid, comment):
"""添加/修改作业的评语"""
sql = "update Homework set comment='%s' where idHomework='%s';" % (comment, clean(hid))
return db.execute(sql)
def deleteAllResolve(self):
db.execute("DELETE FROM complaint")
def refineLeshoz(id):
answer = db.execute(
"SELECT l.leshoz_id, CONCAT(lt.leshoztype_ru, ' ', l.leshoz_ru, ' (', l.leshoz_id, ')') AS leshoz FROM forest.leshoz l LEFT JOIN forest.leshoztype lt ON lt.leshoztype_id = l.leshoztype_id WHERE oblast_id = {} ORDER BY leshoz"
.format(int(id)))
return json.dumps({'result': [dict(row) for row in answer]},
ensure_ascii=False)
def delete_homework(hid, sid):
"""删除已上传的作业"""
sql = "delete from Homework where sid='%s' and idHomework='%s'" % (
clean(sid), clean(hid))
db.execute(sql)
def update_comment(hid, comment):
"""添加/修改作业的评语"""
sql = 'update Homework set comment="%s" where idHomework="%s";' % (
text2Html(comment), clean(hid))
return db.execute(sql)
def refineForestry(id):
answer = db.execute(
"SELECT f.gid, CONCAT (f.forestry_num, '. ', ft.forestrytype_ru, ' ', f.forestry_ru) AS forestry FROM forest.forestry f LEFT JOIN forest.forestrytype ft ON ft.forestrytype_id = f.forestrytype_id WHERE leshoz_id = {} ORDER BY forestry "
.format(int(id)))
return json.dumps({'result': [dict(row) for row in answer]},
ensure_ascii=False)
def delete_homework(hid,sid):
"""删除已上传的作业"""
sql= "delete from Homework where sid='%s' and idHomework='%s'" % (clean(sid),clean(hid))
db.execute(sql)
def insert(cls):
db.execute("DROP TABLE IF EXISTS cells")
Cell.create_table()
cells = [(cell.id, cell.row_id, cell.column_id, cell._data)
for key, cell in Cell.array.items()]
db.executemany("INSERT INTO cells VALUES (?, ?, ?, ?)", cells)