from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
Base=declarative_base()
class Accout(Base):
 __tablename__=u'accout'
 id=Column(Integer,primary_key=True)
 user_namr=Column(String(50),nullable=False)
 password=Column(String(200),nullable=False)
 title=Column(String(50))
 salary=Column(Integer)

 def is_active(self):
 #假設所有
 return True

 def get_id(self):
 #返回賬號ID，用方法返回屬性值提高了表的封裝性。
 return self.id

 def is_authenticated(self):
 #假設已經通過驗證
 return True

 def is_anonymous(self):
 #具有登陸名和密碼的賬號不是匿名用戶
 return False

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session,sessionmaker
from contextlib import contextmanager

db_connect_string='mysql://v_user:v_pase@localhost:3306/test_database?charset=utf8'

ssl_args={
 'ssl':{
 'cert':'/home/ssl/client-cert.pem',
 'key':'/home/shouse/ssl/client-key.pem',
 'ca':'/home/shouse/ssl/ca-cert.pem'
 }
}
engine=create_engine(db_connect_string,connect_args=ssl_args)
SessionType=scoped_session(sessionmaker(bind=engine,expire_on_commit=False))
def GetSession():
 return SessionType()

@contextmanager
def session_scope():
 session=GetSession()
 try:
 yield session
 session.commit()
 except:
 session.rollback()
 raise
 finally:
 session.close()

from sqlalchemy import or_,orm
def InsertAccount(user,passwd,title,salary): #新增操作
 with session_scope() as session:
 account=orm.Account(user_name=user,passwd=passwd,title=title,salary=salary)
 session.add(account)

def GetAccount(id=None,user_name=None): #查詢操作
 with session_scope() as session:
 return session.query(orm.Account).filter(
 or_(orm.Account.id==id,orm.Account.user_name=user_name)
 ).first()

def DeleteAccount(user_name): #刪除操作
 with session_scope() as session:
 account=GetAccount(user_name=user_name)
 if account:
 session.delete(account)

def UpdateAccount(id,user_name,password,title,salary): #更新操作
 with session_scope() as session:
 account=session.query(orm.Account).filter(orm.Account.id==id).first()
 if not account:return
 account.user_name=user_name
 account.password=password
 account.salary=salary
 account.title=title


InsertAccount("Mark","123","Manager",3000) #調用新增操作
InsertAccount("帥哥","456","Boss",2000) #調用新增操作
GetAccount(2) #調用查詢操作
DeleteAccount("Mark")
UpdateAccount(1,"admin","none","System admin",2500)