使用SQLAlchemy

安裝方法
pip install SQLAlchemy

更多內(nèi)容參考flask魚(yú)書(shū)項(xiàng)目

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy import Column, Integer, String ,DateTime, Boolean

# 連接數(shù)據(jù)庫(kù)
engine = create_engine('mysql://root:password@localhost:3306/school?charset=utf8')
## 編碼問(wèn)題

# # 獲取基類
Base = declarative_base()

class News(Base): # 繼承基類
    __tablename__ = 'students1'
    id = Column(Integer, primary_key = True)
    nickname = Column(String(20))
    name = Column(String(20), nullable = False)
    sex = Column(String(1))
    in_time = Column(DateTime)
    is_vaild = Column(Boolean)
    idcard = Column(Integer, unique = True)
        
News.metadata.create_all(engine)   # 創(chuàng)建表格

## 新增數(shù)據(jù)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

class OrmTest(object):
    def __init__(self):
        self.session = Session()

    def add_one(self):
        new_obj = News(
            nickname = '123',
            name = '321',
            sex = '男',
        )
        self.session.add(new_obj)
        self.session.commit()
        return new_obj

    def add_more(self):
        new_obj = News(
            nickname = '123',
            name = '321',
            sex = '男',
        )
        new_obj2 = News(
            nickname = 'wei',
            name = 'lai',
            sex = '女',
        )
        self.session.add_all([new_obj,
                        new_obj2])
        self.session.commit()
        return new_obj

## 查詢數(shù)據(jù)        
    def get_one(self):
        return self.session.query(News).get(10)  # get 是選id為2的

    def get_more(self):
        return self.session.query(News).filter_by(is_vaild=True)


## 修改數(shù)據(jù)
## 將一條當(dāng)作多條的一種情況
    def update_data(self):
        data_list = self.session.query(News).filter(News.id >= 5)
        for item in data_list:
            if item:
                item.is_vaild = 0 
                self.session.add(item)  # 加入
        self.session.commit()  # 提交
## filter 與 filter_by 的區(qū)別


## 刪除數(shù)據(jù)
    def delete_data(self):
        data = self.session.query(News).get(8)
        if data:
            self.session.delete(data)
            self.session.commit()
        else:
            return False

    def delete_data_more(self):
        delete_list = self.session.query(News).filter(News.id <= 5)
        for item in delete_list:
            if item:
                self.session.delete(item)       
            else:
                return False
        self.session.commit() 



def main():
    obj = OrmTest()
    obj.add_one()
    obj.add_more()

    data = obj.get_one() 

    ## 防止查詢失誤
    if data:  
        print('ID:{0}  {1}'.format(data.id,data.sex))
    else:
        print('Not exist')

    data_more = obj.get_more()
    print(data_more.count())  # 計(jì)數(shù)
    for new_obj in data_more:
        print('ID:{0}  {1} {2} {3}'.format(new_obj.id,new_obj.sex,new_obj.name,new_obj.nickname))

    obj.update_data()
    print('數(shù)據(jù)修改成功')
    
    obj.delete_data()
    print('數(shù)據(jù)刪除成功')

    obj.delete_data_more()


if __name__ == '__main__':
    main()