#!/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 obj1
def 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 True
return False
def 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 True
return False
def 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()
通过sqlalchemy进行基本的增删改查
最新推荐文章于 2024-07-29 10:51:17 发布