class Seebug(Base):
"""
定义数据库映射
"""
__tablename__ = "vul_seebug"
id = Column(Integer, primary_key=True)
title = Column(String(256), nullable=False)
ssvid = Column(String(32), nullable=False)
discover_time = Column(Date, nullable=True)
commit_time = Column(Date, nullable=True)
#PubTime = Column(Date,nullable=True)
danger_level = Column(String(8), nullable=False)
bug_type = Column(String(32), nullable=True)
cveid = Column(String(16), nullable=True)
cnnydid = Column(String(16), nullable=True)
cnvdid = Column(String(16), nullable=True)
author = Column(String(16), nullable=True)
commitor = Column(String(16), nullable=True)
zoomeye_dork = Column(String(16), nullable=True)
influence_component = Column(String(16), nullable=True)
bug_abstract = Column(String(512), nullable=True)
url = Column(String(256), nullable=False)
url_md5 = Column(String(32),
nullable=False,
unique=True,
default=func.md5(url))
save_time = Column(DateTime, default=func.now())
last_modified = Column(DateTime, onupdate=func.utc_timestamp())
class Student(db.Model):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
contextual_id = column_property(
func.md5(
bindparam('context', value='', type_=String) +
func.cast(id, String)))
name = Column(String(64), index=True, nullable=False)
address = Column(String(128), index=False, nullable=True)
phone = Column(String(35), nullable=True)
home_phone = Column(String(35), nullable=True)
email = Column(CIText(64, True), nullable=True)
created = Column(DateTime, server_default='now()')
guardian_number = MapColumn('home_phone')
phone_numbers = MapColumn(['phone', 'home_phone'])
contact_info = MapColumn({
'phone': 'phone',
'home_phone': 'home_phone',
'email': 'email'
})
teachers = relationship(
"Teacher",
secondary='teacher_to_student',
primaryjoin="Student.id == teacher_to_student.c.student_id",
secondaryjoin="teacher_to_student.c.teacher_id == Teacher.id")
first_name = column_property(func.split_part(func.trim(name), " ", 1))
def fill_db():
session = db.Session()
user1 = User(name="tr", password=func.md5("123"), email="bbb")
session.add(user1)
user2 = User(name="ft", password=func.md5("ysance"), email="*****@*****.**")
session.add(user2)
user3 = User(name="test1", password=func.md5("ysance"), email="*****@*****.**")
session.add(user3)
session.commit()
board1 = Board(name="Board1")
session.add(board1)
session.commit()
board_user1 = BoardUser(user_id=user1.id, board_id=board1.id, user_group=UserGroup.OWNER)
board_user2 = BoardUser(user_id=user2.id, board_id=board1.id, user_group=UserGroup.OWNER)
session.add(board_user1)
session.add(board_user2)
session.commit()
def add(self, name, email, password):
user = User(
name=name,
password=func.md5(password),
email=email)
self.session.add(user)
self.session.commit()
return user
class RefreshToken(Base):
__tablename__ = 'users_refreshtoken'
user_id = Column(Integer,
ForeignKey(User.id, ondelete='CASCADE'),
primary_key=True)
token = Column(String(50),
server_default=func.md5(cast(func.random(), String)),
nullable=False)
def __repr__(self):
return '<RefreshToken(user_id="{}")>'.format(self.user_id)
def process_request(self, request):
if is_static( request ) or is_github( request ) :
return
id_cookie = request.COOKIES['gany_user_identity'] if 'gany_user_identity' in request.COOKIES else '-'
user = db.user_session.query( User ).filter( func.md5( User.salt ) == id_cookie ).first()
if not request.path.startswith("/user/login") and \
user is None :
return redirect( u"{login_url}?{next_url}".format(
login_url = reverse( "testing_runtime.web.user.login_page" ),
next_url = urllib.urlencode( {"next" : request.path } )
) )
request.user = user
def use_db():
# session.execute('delete from user where true;')
session.execute('truncate table user;')
user = User(name='a')
session.add(user)
user = User(name='b')
session.add(user)
user = User(name='c')
session.add(user)
user = User()
session.add(user)
session.commit()
query = session.query(User)
print(query) #显示SQL语句
print(query.statement) #同上
# for user in query: #遍历时查询
# print(user.name)
print(query.all()) #返回的是一个类似列表的对象
print(query.first().name) #记录不存在时，first()会返回None
# print(query.one().name) #不存在，或有多行记录时会抛出异常
print(query.filter(User.id == 2).first().name)
print(query.get(2).name) #以主键获取，等效于上句
print(query.filter('id=2').first().name) #支持字符串
query2 = session.query(User.name)
print(query2.all()) #每行是个元组
print(query2.limit(1).all()) #最多返回1条记录
print(query2.offset(1).all()) #从第二条记录开始返回
print(query2.order_by(User.name).all()) #空排在最前
print(query2.order_by('name').all())
print(query2.order_by(User.name.desc()).all()) #倒序
print(query2.order_by('name desc').all())
#SELECT "user".id AS user_id FROM "user" ORDER BY "user".name DESC, "user".id
print(session.query(User.id).order_by(User.name.desc(), User.id).all())
print(query2.filter(User.id == 1).scalar()) #如果有记录，返回第一条记录的第一个元素
print(session.query('id').select_from(User).filter('id=1').scalar())
print(query2.filter(User.id > 1, User.name != 'a').first()) #and
# print(query2.filter(User.id > 1, User.name != 'a').scalar()) #and
query3 = query2.filter(User.id > 1) #多次拼接filter也是and
query3 = query3.filter(User.name != 'a')
print(query3.first())
print(query2.filter(or_(User.id == 1, User.id == 2)).all()) #or
print(query2.filter(User.id.in_((1, 2))).all()) #in
query4 = session.query(User.id)
print(query4.filter(User.name == None).scalar())
print(query4.filter('name is null').scalar())
print(query4.filter(not_(User.name == None)).all()) #not
print(query4.filter(User.name != None).all())
print(query4.count())
print(session.query(func.count('*')).select_from(User).scalar())
print(session.query(func.count('1')).select_from(User).scalar()) #结果为4
print(session.query(func.count(User.id)).scalar())
#filter()中包含User，因此不需要指定表
print(session.query(func.count('*')).filter(User.id > 0).scalar())
print(session.query(func.count('*')).filter(User.name == 'a').limit(1).\
scalar() == 1) #可以用limit()限制count()的返回数
print(session.query(func.sum(User.id)).scalar())
print(session.query(func.now()).scalar()) #func后可以跟任意函数名，只要该数据库支持
print(session.query(func.current_timestamp()).scalar())
print(session.query(func.md5(User.name)).filter(User.id == 1).scalar())
query.filter(User.id == 1).update({User.name: 'c'})
user = query.get(1)
print(user.name)
user.name = 'd'
session.flush() #写数据库，但并不提交
print(query.get(1).name)
session.delete(user)
session.flush()
print(query.get(1))
session.rollback()
print(query.get(1).name)
query.filter(User.id == 1).delete()
session.commit()
print(query.get(1))
print query4.filter('tag_name is null').scalar()
print query4.filter(not_(Tags.tag_name == None)).all() # not
print query4.filter(Tags.tag_name != None).all()
print query4.count()
print session.query(func.count('*')).select_from(Tags).scalar()
print session.query(func.count('1')).select_from(Tags).scalar()
print session.query(func.count(Tags.id)).scalar()
print session.query(func.count('*')).filter(
Tags.id > 0).scalar() # filter() 中包含 Tags，因此不需要指定表
print session.query(func.count('*')).filter(Tags.tag_name == 'a').limit(
1).scalar() == 1 # 可以用 limit() 限制 count() 的返回数
print session.query(func.sum(Tags.id)).scalar()
print session.query(func.now()).scalar() # func 后可以跟任意函数名，只要该数据库支持
print session.query(func.current_timestamp()).scalar()
print session.query(func.md5(Tags.tag_name)).filter(Tags.id == 1).scalar()
'''like 查询'''
session.query(Tags).filter(Tags.tag_name.like('%hello%'))
'''in 查询'''
session.query(Tags).filter(Tags.name.in_(['hello', 'hello', 'hello']))
'''update'''
query.filter(Tags.id == 1).update({Tags.tag_name: 'c'})
query.filter(Tags.id == 1).update({Tags.count: Tags.count + 1},
synchronize_session=False)
Tags = query.get(1)
print Tags.tag_name
'''事物 '''
Tags.tag_name = 'd'
session.flush() # 写数据库，但并不提交
print query.get(1).tag_name
print session.query(func.count(User.id)).scalar()
# Because there is User.id, so do not need to identify table User
# select count(*) from user where id > 0
print session.query(func.count('*')).filter(User.id > 0).scalar()
# select count(*) from user where name='a'
print session.query(
func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1
# select sum(id) from user
print session.query(func.sum(User.id)).scalar()
# func can invoke any functions if the database support
# select now() from user
print session.query(func.now()).scalar()
# select current_timestamp() from user
print session.query(func.current_timestamp()).scalar()
# select md5(name) from user where id=1
print session.query(func.md5(User.name)).filter(User.id == 1).scalar()
# update user set name=c where id=1
query.filter(User.id == 1).update({User.name: 'c'})
user = query.get(1)
# the print value is c, not in database still a
print user.name
user.name = 'd'
# write, but do not commit
session.flush()
# the print value is d, but in database still a
print query.get(1).name
# do not delete in database
session.delete(user)
session.flush()
# the value is None, not a, but in database still a
def check_credentials(self, username, password):
res = User.query.filter_by(username=username,
password=func.md5(password)).count()
return res
def use_db():
# session.execute('delete from user where true;')
session.execute('truncate table user;')
user = User(name = 'a')
session.add(user)
user = User(name = 'b')
session.add(user)
user = User(name = 'c')
session.add(user)
user = User()
session.add(user)
session.commit()
query = session.query(User)
print(query) #显示SQL语句
print(query.statement) #同上
# for user in query: #遍历时查询
# print(user.name)
print(query.all()) #返回的是一个类似列表的对象
print(query.first().name) #记录不存在时，first()会返回None
# print(query.one().name) #不存在，或有多行记录时会抛出异常
print(query.filter(User.id == 2).first().name)
print(query.get(2).name) #以主键获取，等效于上句
print(query.filter('id=2').first().name) #支持字符串
query2 = session.query(User.name)
print(query2.all()) #每行是个元组
print(query2.limit(1).all()) #最多返回1条记录
print(query2.offset(1).all()) #从第二条记录开始返回
print(query2.order_by(User.name).all()) #空排在最前
print(query2.order_by('name').all())
print(query2.order_by(User.name.desc()).all()) #倒序
print(query2.order_by('name desc').all())
#SELECT "user".id AS user_id FROM "user" ORDER BY "user".name DESC, "user".id
print(session.query(User.id).order_by(User.name.desc(), User.id).all())
print(query2.filter(User.id == 1).scalar()) #如果有记录，返回第一条记录的第一个元素
print(session.query('id').select_from(User).filter('id=1').scalar())
print(query2.filter(User.id > 1, User.name != 'a').first()) #and
# print(query2.filter(User.id > 1, User.name != 'a').scalar()) #and
query3 = query2.filter(User.id > 1) #多次拼接filter也是and
query3 = query3.filter(User.name != 'a')
print(query3.first())
print(query2.filter(or_(User.id == 1, User.id == 2)).all()) #or
print(query2.filter(User.id.in_((1, 2))).all()) #in
query4 = session.query(User.id)
print(query4.filter(User.name == None).scalar())
print(query4.filter('name is null').scalar())
print(query4.filter(not_(User.name == None)).all()) #not
print(query4.filter(User.name != None).all())
print(query4.count())
print(session.query(func.count('*')).select_from(User).scalar())
print(session.query(func.count('1')).select_from(User).scalar()) #结果为4
print(session.query(func.count(User.id)).scalar())
#filter()中包含User，因此不需要指定表
print(session.query(func.count('*')).filter(User.id > 0).scalar())
print(session.query(func.count('*')).filter(User.name == 'a').limit(1).\
scalar() == 1) #可以用limit()限制count()的返回数
print(session.query(func.sum(User.id)).scalar())
print(session.query(func.now()).scalar()) #func后可以跟任意函数名，只要该数据库支持
print(session.query(func.current_timestamp()).scalar())
print(session.query(func.md5(User.name)).filter(User.id == 1).scalar())
query.filter(User.id == 1).update({User.name : 'c'})
user = query.get(1)
print(user.name)
user.name = 'd'
session.flush() #写数据库，但并不提交
print(query.get(1).name)
session.delete(user)
session.flush()
print(query.get(1))
session.rollback()
print(query.get(1).name)
query.filter(User.id == 1).delete()
session.commit()
print(query.get(1))
def get_by_log(self, name, password):
user = self.session.query(User).\
filter(and_(User.name == name, User.password == func.md5(password))).\
first()
return user
print query2.filter(User.id.in_((1, 2))).all() # in
query4 = session.query(User.id)
print query4.filter(User.name == None).scalar()
print query4.filter('name is null').scalar()
print query4.filter(not_(User.name == None)).all() # not
print query4.filter(User.name != None).all()
print query4.count()
print session.query(func.count('*')).select_from(User).scalar()
print session.query(func.count('1')).select_from(User).scalar()
print session.query(func.count(User.id)).scalar()
print session.query(func.count('*')).filter(User.id > 0).scalar() # filter() 中包含 User，因此不需要指定表
print session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1 # 可以用 limit() 限制 count() 的返回数
print session.query(func.sum(User.id)).scalar()
print session.query(func.now()).scalar() # func 后可以跟任意函数名，只要该数据库支持
print session.query(func.current_timestamp()).scalar()
print session.query(func.md5(User.name)).filter(User.id == 1).scalar()
query.filter(User.id == 1).update({User.name: 'c'})
user = query.get(1)
print user.name
user.name = 'd'
session.flush() # 写数据库，但并不提交
print query.get(1).name
session.delete(user)
session.flush()
print query.get(1)
session.rollback()
print query.get(1).name
query.filter(User.id == 1).delete()
session.commit()
print query.get(1)
def __init__(self, names, password, role):
self.names = names
self.password = func.md5(password)
self.role = role
query4 = Session.query(Author.id)
print query4.filter(Author.name == None).scalar()
print query4.filter('name is null').scalar()
print query4.filter(not_(Author.name == None)).all() # not
print query4.filter(Author.name != None).all()
print query4.count(), '666666'
print Session.query(func.count('*')).select_from(Author).scalar()
print Session.query(func.count('1')).select_from(Author).scalar()
print Session.query(func.count(Author.id)).scalar()
print Session.query(func.count('*')).filter(Author.id > 0).scalar() # filter() 中包含 User，因此不需要指定表
print Session.query(func.count('*')).filter(Author.name == 'a').limit(1).scalar() == 1 # 可以用 limit() 限制 count() 的返回数
print Session.query(func.sum(Author.id)).scalar()
print Session.query(func.now()).scalar() # func 后可以跟任意函数名，只要该数据库支持
print Session.query(func.current_timestamp()).scalar()
print Session.query(func.md5(Author.name)).filter(Author.id == 2).scalar(), '777777'
sql_tmp.filter(Author.id == 2).update({Author.name: 'c'})
user1 = sql_tmp.get(3)
print user1.name
user1.name = 'd'
Session.flush() # 写数据库，但并不提交
print sql_tmp.get(3).name, '8888888'
Session.delete(author_obj)
Session.flush()
print sql_tmp.get(3)
Session.rollback()
print sql_tmp.get(3).name
sql_tmp.filter(Author.id == 3).delete()
Session.commit()
