def delete_class_name():
un = session.get('username', None)
if un == None:
print("用户session不存在!")
return '{"status": "failed"}'
try:
cls_name = request.json['class_name']
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'delete from Share where tbl_id in (select id from Data where belongTo = %s and class = %s)',
(un, cls_name))
conn.commit()
res = cursor.execute(
'delete from Data where belongTo = %s and class = %s',
(un, cls_name))
conn.commit()
res = cursor.execute('delete from Class where className = %s',
(cls_name, ))
conn.commit()
return '{"status": "success"}'
except Exception as e:
print(e)
return '{"status": "failed"}'
def get_person_info():
un = session.get('username', None)
if un == None:
print("用户session不存在!")
return '{"status": "failed"}'
try:
db = DBTools('root', 'liubixue', 'GetMyTable')
db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'select username,ava_url,email,person_net,intro from User where username = %s',
(un, ))
res = cursor.fetchall()
info = {}
info['status'] = 'success'
info['username'] = res[0][0]
info['ava_url'] = res[0][1]
info['email'] = res[0][2]
info['person_net'] = res[0][3]
info['intro'] = res[0][4]
print(info)
return json.dumps(info)
except Exception as e:
print(e)
return '{"status": "failed"}'
def add_new_class():
print('Session:')
print(session)
un = session.get('username', None)
if un == None:
print("没有用户名session")
return '{"status": "failed"}'
print(request.json)
new_class_name = request.json['new_class_name']
print("新分类" + new_class_name)
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'select count(*) from Class where className = %s and belongTo = %s ',
(new_class_name, un))
res = cursor.fetchall()
if (res[0][0] > 0):
return '{"status": "existed"}'
try:
cursor.execute('insert into Class(className,belongTo) values(%s,%s)',
(new_class_name, un))
conn.commit()
return '{"status": "success"}'
except Exception as e:
print(e)
return '{"status": "failed"}'
def valid_email(em):
db = DBTools('root', 'liubixue', 'GetMyTable')
db.connect()
cursor = db.get_cursor()
res = cursor.execute('select count(*) from User where email = %s', (em, ))
res = cursor.fetchall()
print("数据库查询结果:" + str(res[0][0]))
print(type(res[0][0]))
if res[0][0] > 0:
return False
return True
def add_new_user(un, pw, em):
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
try:
cursor.execute(
'insert into User(username,password,email) values(%s,%s,%s)',
(un, pw, em))
conn.commit()
return True
except Exception:
return False
def exist_user(un, pw):
db = DBTools('root', 'liubixue', 'GetMyTable')
db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'select count(*) from User where username = %s and password = %s',
(un, pw))
res = cursor.fetchall()
print("数据库查询结果:" + str(res[0][0]))
print(type(res[0][0]))
if res[0][0] > 0:
return True
return False
def save_table_to_DB(un, tbl, tbl_n, tbl_c):
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
try:
cursor.execute(
'insert into Data(belongTo,content,name,class) values(%s,%s,%s,%s)',
(un, tbl, tbl_n, tbl_c))
conn.commit()
return True
except Exception as e:
print(e)
return False
def test_b01setandget_timeref(self):
dbt: DBTools = None
dbname = 'python1test'
try:
dbt = DBTools(dbid=dbname)
dbt.open()
aa: Tuple[int, float, Dtc, str] = dbt.set_timeref(cmt='testing')
bb = dbt.get_timeref()
self.assertEqual(aa, bb)
idx: int = aa[0] # 1
self.assertEqual(1, idx)
timestamp: float = aa[1]
time: Dtc = aa[2]
utc1: Dtc = Dtc.fromtimestamp(timestamp, timezone.utc)
local: Dtc = utc1.astimezone()
self.assertEqual(time, local)
cmta: str = aa[3] # 'testing'
self.assertEqual('testing', cmta)
except:
self.fail('unexpected exception')
finally:
dbt.close_and_disconnect()
def get_all_table():
un = session.get('username', None)
cn = request.args.get('class_name')
print(cn)
if un == None:
return '[]'
db = DBTools('root', 'liubixue', 'GetMyTable')
db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'select name,id from Data where belongTo = %s and class = %s',
(un, cn))
res = cursor.fetchall()
return json.dumps(res)
def get_all_class():
un = session.get('username', None)
if un == None:
return '[]'
db = DBTools('root', 'liubixue', 'GetMyTable')
db.connect()
cursor = db.get_cursor()
res = cursor.execute('select className from Class where belongTo = %s',
(un, ))
res = cursor.fetchall()
classes = []
for t in res:
classes.append(t[0])
print(classes)
return json.dumps(classes)
def get_all_share():
un = session.get('username', None)
if un == None:
return '{"status": "failed"}'
try:
res = {}
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
cursor.execute('select * from Share order by time desc')
res['content'] = cursor.fetchall()
res['status'] = 'success'
return json.dumps(res)
except Exception as e:
print(e)
return '{"status": "failed"}'
def delete_table_by_id():
un = session.get('username', None)
if un == None:
print("用户session不存在!")
return '{"status": "failed"}'
try:
tbl_id = request.json['table_id']
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'delete from Data where belongTo = %s and id = %s', (un, tbl_id))
conn.commit()
cursor.execute('delete from Share where tbl_id = %s', (tbl_id, ))
conn.commit()
return '{"status": "success"}'
except Exception as e:
return '{"status": "failed"}'
def get_table_name_by_id():
un = session.get('username', None)
if un == None:
print("用户session不存在!")
return '{"status": "failed"}'
try:
tbl_id = request.json['table_id']
db = DBTools('root', 'liubixue', 'GetMyTable')
db.connect()
cursor = db.get_cursor()
res = cursor.execute('select name from Data where id = %s', (tbl_id, ))
res = cursor.fetchall()
tbl = {}
tbl['status'] = 'success'
tbl['name'] = res[0][0]
return json.dumps(tbl)
except Exception as e:
print(e)
return '{"status": "failed"}'
def save_person_info():
ava_url = request.json['ava_url']
person_net = request.json['person_net']
intro = request.json['intro']
un = session.get('username', None)
if un == None:
return '{"status": "failed"}'
try:
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'update User set ava_url = %s,person_net = %s,intro = %s where username = %s',
(ava_url, person_net, intro, un))
conn.commit()
return '{"status": "success"}'
except Exception as e:
print(e)
return '{"status": "failed"}'
def add_new_share():
tbl_id = request.json['tbl_id']
time = request.json['time']
comm = request.json['comment']
un = session.get('username', None)
if un == None:
return '{"status": "failed"}'
try:
db = DBTools('root', 'liubixue', 'GetMyTable')
conn = db.connect()
cursor = db.get_cursor()
res = cursor.execute(
'insert into Share(username,time,tbl_id,comment) values(%s,%s,%s,%s)',
(un, time, tbl_id, comm))
conn.commit()
return '{"status": "success"}'
except Exception as e:
print(e)
return '{"status": "failed"}'
def setUpClass(cls):
dbt: DBTools = None
dbname = 'python1test'
try:
dbt = DBTools(dbid=dbname)
dbt.open()
dbt.resetdb()
except Exception as ex:
print('unexpected exception')
a = 0
finally:
dbt.close_and_disconnect()
pass
def get_avatar_url_by_name():
un = session.get('username', None)
if un == None:
print("用户session不存在!")
return '{"status": "failed"}'
try:
db = DBTools('root', 'liubixue', 'GetMyTable')
db.connect()
cursor = db.get_cursor()
un = request.args.get('username')
res = cursor.execute('select ava_url from User where username = %s',
(un, ))
res = cursor.fetchall()
tbl = {}
tbl['status'] = 'success'
tbl['url'] = res[0][0]
return json.dumps(tbl)
except Exception as e:
print(e)
return '{"status": "failed"}'
def test_a01open(self):
dbname = 'python1test'
dm = DBTools(dbid=dbname)
dm.open()
self.assertEqual(
f'Schema is {dbname}, opened = True, connected = True', str(dm))
self.assertEqual(
f'DBTools: Schema is {dbname}, opened = True, connected = True',
repr(dm))
try:
dm.open()
self.fail('did not cause exception on second open')
except Exception as ex:
self.assertEqual('database already opened', str(ex))
a = 0
dm.close_and_disconnect()
self.assertEqual(
f'Schema is {dbname}, opened = False, connected = False', str(dm))
self.assertEqual(
f'DBTools: Schema is {dbname}, opened = False, connected = False',
repr(dm))
try:
dm.close_and_disconnect()
except:
self.fail(
'should not have caused an exception on 2nd close_and_disconnect'
)
dm.open()
self.assertEqual(
f'Schema is {dbname}, opened = False, connected = False', str(dm))
def test_a00instat(self):
dbname = 'python1test'
dm = DBTools(dbid=dbname)
self.assertEqual(
f'Schema is {dbname}, opened = False, connected = True', str(dm))
self.assertEqual(
f'DBTools: Schema is {dbname}, opened = False, connected = True',
repr(dm))
dm.close_and_disconnect()
self.assertEqual(
f'DBTools: Schema is {dbname}, opened = False, connected = False',
repr(dm))
dm.close_and_disconnect()
dm.close_and_disconnect()
dm = DBTools(dbid=dbname)
try:
self.assertTrue(dm.open())
dm.close_and_disconnect()
except:
self.fail('unexpected exception')
a = 0
finally:
try:
dm.close_and_disconnect()
except:
self.fail('unexpected exception')