ORM操作数据库示例

下面的示例，直接拷贝到sublime，保存为****.py文件，然后运行即可！

 

备注：用ORM生成数据库没有写在文件内部，在python环境下的命令行工具中输入：

from ****** import #### (from test_mysql_orm import News)

from ****** import #### (from test_mysql_orm import engine)

News.metadata.create_all(engine)

上述三行代码可以生成一个数据表；注意：用navicat生成数据库是上面操作的前提；

 

 

'''运用SQLalchemy操作mysql数据库'''
#运用SQLalchemy是一款很常用的ORM工具，SQLalchemy本身不能直接操作mysql数据库，而是通过MySQLdb这样的插件提供的相应数据库的API来操作数据库；
#SQLalchemy为代表的ORM可以操作几乎所有的数据库，用户不用熟悉数据库语句，写SQL语句，而是通过ORM的API来生成和运维数据库；
#如果你想更换数据库的话，也很简单，只要改变SQLalchemy的数据库设置就好了，程序不用做任何改变；

# import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,DateTime,Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql://hiadmin:1q2w3e$R@rm-wz9lp7a734h99y0s5yo.mysql.rds.aliyuncs.com:3306/newsdata?charset=utf8')
Base = declarative_base()
Session = sessionmaker(bind=engine)

class News(Base):
__tablename__='newsdata'
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(String(20),)
created_at = Column(DateTime)
is_valid = Column(Boolean)

'''from test_mysql_orm import News'''
'''from test_mysql_orm import engine'''
'''News.metadata.create_all(engine)'''

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

def add_one(self):
new_obj = News(
title = '标题',
content = '内容',
types = '百家'
)
self.session.add(new_obj)
self.session.commit()
return new_obj

def add_all(self):
self.session.add_all([
News(
title = '标题0',
content = '内容0',
types = '百家0',
),
News(
title = '标题1',
content = '内容1',
types = '百家1',
),
News(
title = '标题2',
content = '内容2',
types = '百家2',
),
News(
title = '标题3',
content = '内容3',
types = '百家3',
),
News(
title = '标题4',
content = '内容4',
types = '百家4',
),
News(
title = '标题5',
content = '内容5',
types = '百家5',
),
News(
title = '标题6',
content = '内容6',
types = '百家6',
),
])
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()
# rest = obj.add_one()
# print(rest.id)
# rest = obj.get_one()
# print('ID:{0}=>{1}=>{2}'.format(rest.id,rest.title,rest.content))
# rest = obj.get_more()
# print(rest.count())
obj.add_all()

if __name__ == '__main__':
main()