通过sqlalchemy进行基本的增删改查

#!/usr/bin/env python
# -*- coding: utf-8 -*-from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DateTime,Boolean
from sqlalchemy.orm import sessionmaker# 初始化数据库连接:
engine = create_engine('mysql+pymysql://root:123123@localhost/school?charset=utf8',encoding='utf-8')   # school 为数据库名称，为已经存在的
# 创建对象的基类:
Base = declarative_base()'''   mysql原生语句
CREATE TABLE `news`(`id` INT 	NOT NULL AUTO_INCREMENT,`title` VARCHAR(200) NOT NULL,`content` VARCHAR(2000) NOT NULL,`types` VARCHAR(10) NOT NULL,`image` VARCHAR(300) NULL,`author` VARCHAR(20) NULL,`view_count` INT DEFAULT 0,`create_at` DATETIME NULL,`is_valid` SMALLINT DEFAULT 1,PRIMARY KEY(`id`)
)DEFAULT CHARSET 'UTF8';
'''class News(Base):# 表的名字:__tablename__ = 'news_alchemy'    # 需要新建的表名字# 表的结构: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,)create_at = Column(DateTime,)is_valid = Column(Boolean,)# 创建表
#News.metadata.create_all(engine)# 添加数据
Session_class = sessionmaker(bind=engine)   # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例class OrmAdd(object):def __init__(self):self.session = Session_class()  # 生成session实例def add(self):"""插入数据"""obj1 = News(title = 'D1',content = '我是D1',types = '链家',is_valid = 0,)obj2 = News(title = 'D2',content = '我是D2',types = '链家',is_valid=0,)obj3 = News(title = 'D2',content = '我是D2',types = '链家',is_valid=1,)self.session.add(obj1)self.session.add(obj2)self.session.add(obj3)self.session.commit()return obj1def get_one(self):"""查询一条数据"""return self.session.query(News).get(13)def get_more(self):"""查询多条数据"""return self.session.query(News).filter_by(is_valid=0)def update_one(self,nid):"""修改一条数据"""ud_obj = self.session.query(News).get(nid)if ud_obj:ud_obj.author = '赵云'self.session.add(ud_obj)self.session.commit()return Truereturn Falsedef update_more(self,qk):"""修改多条数据"""data_list = self.session.query(News).filter_by(is_valid=qk)for ud_obj in data_list:ud_obj.author = '小乔'self.session.add(ud_obj)self.session.commit()def del_one(self):"""删除一条数据"""del_obj = self.session.query(News).filter_by(id=15).first()if del_obj:self.session.delete(del_obj)self.session.commit()return Truereturn Falsedef main():orm = OrmAdd()#obj = orm.add()# 查询数据#obj = orm.get_one()obj = orm.get_more()if obj:   # 处理查询的数据# 获取一条# print(obj.types)# 获取多条for ele in obj:print(ele.types)else:print("查询的对象不存在")# 更改数据# orm.update_one(110)# orm.update_more(False)# 删除数据orm.del_one()if __name__ == '__main__':main()