python操作mysql③python操作mysql的orm工具sqlaichemy安裝配置和使用

標籤：nbsp   bit   基類   stat   注意   sql   oca   null   date   

python操作mysql③python操作mysql的orm工具sqlaichemy安裝配置和使用手冊地址：http://docs.sqlalchemy.org/en/rel_1_1/orm/index.html安裝D:\software\source_tar>pip install SQLALchemy檢測是否安裝成功D:\software\source_tar>pythonPython 3.5.2 (v3.5.2:4def2a2901a5, Jun 25 2016, 22:18:55) [MSC v.1900 64 bit (AMD64)] on win32Type "help", "copyright", "credits" or "license" for more information.>>> import sqlalchemymysql的orm庫SQLALchemy的操作#coding:utf-8from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, DateTime, Booleanfrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker‘‘‘    id int primary key auto_increment,    title varchar(200) not null,    content varchar(2000) not null,    tpes varchar(10) not null,    image varchar(300) null,    author varchar(20) null,    view_count int default 0,    created_at datetime null,    is_valid smallint default 1‘‘‘# 建立對象的基類Base = declarative_base()# 初始化資料庫連接，注意要接上charset=utf8否則中文無法支援engine = create_engine("mysql://root:@localhost/news?charset=utf8")# 建立DBSession類型DBSession = sessionmaker(bind=engine)# 定義News對象class News(Base):    __tablename__ = ‘news‘    id = Column(Integer, primary_key=True)    title = Column(String(200), nullable=False)    content = Column(String(2000), nullable=False)    types = Column(String(10), nullable=False)    image = Column(String(300),)    author = Column(String(20),)    view_count = Column(Integer)    created_at = Column(DateTime)    is_valid = Column(Boolean)‘‘‘# 簡單測試# 如果表不存在就建立表Base.metadata.create_all(engine)# 建立session對象:session = DBSession()# 建立新User對象，新增一條測試資料news01 = News(title=‘標題1‘, content = ‘content01‘, types = ‘baidu‘,     image = ‘/static/img/01.jpg‘, author = ‘jack‘, view_count = 3)# 添加到session:session.add(news01)# 提交即儲存到資料庫:session.commit()# 關閉session:session.close()‘‘‘# orm的測試類別class OrmTest(object):    # 初始化建立session    def __init__(self):        self.session = DBSession()    # 添加資料    def add_one(self):        new_obj = News(            title = ‘標題20180202‘,            content = ‘內容20180202‘,            types = ‘百家‘            )        self.session.add(new_obj)        self.session.commit()        return new_obj    # 添加多條資料    def add_more(self):        add_list = []        for i in range(10):            new_obj = News(title=‘標題%s‘%str(i),content=‘內容%s‘%str(i),types=‘百家%s‘%str(i))            self.session.add(new_obj)            add_list.append(new_obj)        self.session.commit()        return add_list    # 刪除資料    def delete_data(self):        data = self.session.query(News).get(51)        self.session.delete(data)        self.session.commit()    # 修改單條資料    def update_one(self, _id):        obj = self.session.query(News).get(_id)        if obj:            obj.is_valid = 0            self.session.add(obj)            self.session.commit()            return True        return False    # 修改多條資料    def update_data(self):        # filter_by的使用方法        # data_list = self.session.query(News).filter_by(is_valid = 0)        # filter的使用方法        data_list = self.session.query(News).filter(News.id > 45)        for data in data_list:            print(data.title)            data.is_valid = 1            self.session.add(data)        self.session.commit()    # 擷取一條資料    def get_one(self):        return self.session.query(News).get(1)    # 擷取多條資料    def get_more(self):        return self.session.query(News).filter_by(is_valid = 1)def main():    obj = OrmTest()    # rst = obj.add_one()    # print(‘id:%s, title:%s,content:%s,types = %s‘ % (rst.id,rst.title,rst.content,rst.types))    # 添加多條資料    # rst = obj.add_more()    # for _new in rst:    #     print(‘id:{0},title{1},content:{2}‘.format(_new.id,_new.title,_new.content))    # 測試刪除    # obj.delete_data()    # 修改單條資料    # print(obj.update_one(50))    # 修改多條資料    obj.update_data()    # 測試擷取一條資料的函數    # rst = obj.get_one()    # print(rst.title)    # 擷取多條資料    # rst = obj.get_more()    # for _news in rst:    #     print(‘news id: %s, title:%s, content:%s‘ % (_news.id,_news.title,_news.content))if __name__ == "__main__":    main()

 

python操作mysql③python操作mysql的orm工具sqlaichemy安裝配置和使用