ROM SQL Alchemy使用代码教程

ROMSQLAlchemy使用代码教程

# -- coding: utf-8 --
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, DateTime, Boolean


# 获取链接 Connecting
# engine = create_engine('mysql://root:admin123@localhost:3306/news_test')
# 插入中文出现错误解决
engine = create_engine('mysql://root:admin123@localhost:3306/news_test?charset=utf8')

# Declare a Mapping 
# 获取基类
Base = declarative_base()

# 获取session对象
Session = sessionmaker(bind = engine)

#模型声明 
class News(Base):   # Base 是 ORM的一个基类 ，也就是说News类继承自Base
    """ 新闻类 """
    __tablename__ = 'news'
    id = Column(Integer, primary_key = True)    # 若数据为news_id id = Column(Interger, primaty_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)
"""
使用命令行创建表
lijuncheng@lijunchengdeMacBook-Pro ~/Code/MySQL数据库 代码/实战网易新闻 $ python
Python 2.7.13 |Anaconda custom (x86_64)| (default, Sep 21 2017, 17:38:20)
[GCC 4.2.1 Compatible Clang 4.0.1 (tags/RELEASE_401/final)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from test_mysql_orm import News
>>> from test_mysql_orm import engine
>>> News.metadata.create_all(engine)      # 创建news表
/Users/lijuncheng/anaconda2/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:1569: Warning: (1287L, "'@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead")
  cursor.execute('SELECT @@tx_isolation')
>>>

"""
class MySQLOrmTest(object):

    def __init__(self):
        self.session = Session()

    def add_one(self):
        """ 新增一条记录 """
        new_obj = News(
            title = '标题',
            content = '内容',
            types = '百家',
        )

        new_obj2 = News(
            title = 'title ',
            content = 'content ',
            types = '1',
        )

        self.session.add(new_obj)
        self.session.add(new_obj2)
        self.session.commit()
        return new_obj

    def get_one(self):
        """  获取一条数据 """
        return self.session.query(News).get(13) # 查询id为1的数据

    def get_more(self):
        """  获取多条数据  """
        return self.session.query(News).filter_by(is_valid = True) # 查询没有删除的 即 is_valid =1

    def update_data(self, pk):
        """ 修改单条数据 """
        new_obj = self.session.query(News).get(pk)
        if new_obj:
            new_obj.is_valid = 0
            self.session.add(new_obj)
            self.session.commit()
            return True
        return False  # 如果有数据就修改数据返回True，没有数据就直接返回False

    def update_data_more(self):
        """ 修改多条数据 """
        #data_list = self.session.query(News).filter_by(is_valid = False)
        data_list = self.session.query(News).filter(News.id >= 5)
        for item in data_list:
            item.is_valid = 1
            self.session.add(item)
        self.session.commit()
        pass

    def delete_data(self, pk):
        """ 删除单条数据  """
        # 获取删除的数据
        new_obj = self.session.query(News).get(pk)
        self.session.delete(new_obj)
        self.session.commit()

    def delete_data_more(self):
        """ 删除多条数据  """
        data_list = self.session.query(News).filter(News.id >= 5)
        for item in data_list:
            self.session.delete(item)
        self.session.commit()
        pass






def main():
    obj = MySQLOrmTest()

    # 测试插入一条数据
    # rest = obj.add_one()
    # print rest.id

    # 测试获取一条数据
    # rest = obj.get_one()
    # if rest:
    #     print 'ID:{0} => {1}'.format(rest.id, rest.title) # 采用format的方式格式化
    # else:
    #     print "No exist."

    # 测试获取多条数据
    # rest = obj.get_more()
    # print rest.count() # 答应查询的数据条数

    # for new_obj in rest:
    #      print 'ID:{0} => {1}'.format(new_obj.id, new_obj.title)
    # pass

    # 测试修改数据代码
    # print obj.update_data(10)

    # # 测试多条数据修改代码
    # obj.update_data_more()

    # 测试删除数据代码
    # obj.delete_data(1)

    # 测试删除多条数据代码
    obj.delete_data_more()



if __name__ == '__main__':
    main()
"""
将每一条记录看成一个对象，ORM就是把数据库的记录转换为对象。
ORM的实现：
    1.SqlObject
    2.peewee
    3.Django‘s ORM Django框架
    4.SQLAlchemy


"""