ORM的主要實(shí)現(xiàn)方式
- SqlObject
- peewee
- Django's ORM
- SQLAlchemy
SQLAlchemy原理圖
SQLAlchemy原理圖.png
安裝SQLAlchemy
#安裝
pip install SQLAlchemy
#測試
>>> import sqlalchemy
>>> sqlalchemy.__version__
1.1.9
常見類型
Integer
Float
Boolean
ForeignKey
Date/DateTime
String
連接數(shù)據(jù)庫
from sqlalchemy import Column, Integer, String, DateTime, Boolean
engine = create_engine("mysql://root:@127.0.0.1:3306/news?charset=utf8")
Declare a Mapping
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String, DateTime, Boolean
engine = create_engine("mysql://root:@127.0.0.1:3308/news?charset=utf8")
Session = sessionmaker(bind=engine)
Base = declarative_base()
class News(Base):
''' 新聞?lì)愋?'''
__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)
def __repr__(self):
return '<News %r>' % self.title
創(chuàng)建數(shù)據(jù)庫
#在終端輸入以下命令
>>> from test_mysql_orm import engine
>>> News.metadata.create_all(engine)
增加session
#The ORM’s “handle” to the database is the [`Session`]
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine) #綁定engine
新增數(shù)據(jù)
def add_one(self):
''' 添加數(shù)據(jù) '''
#實(shí)例化對(duì)象
new_obj = News(
title='ORM標(biāo)題',
content='content',
types="百家"
)
self.session.add(new_obj)
self.session.commit()
return new_obj
查詢數(shù)據(jù)
def get_one(self):
''' 獲取一條數(shù)據(jù) '''
return self.session.query(News).get(1)
def get_more(self):
''' 獲取多條數(shù)據(jù) '''
return self.session.query(News).filter_by(is_valid=1)
修改數(shù)據(jù)
def update_data(self):
''' 修改數(shù)據(jù) '''
obj = self.session.query(News).get(38)
obj.is_valid = 0
self.session.add(obj)
self.session.commit()
return obj
刪除數(shù)據(jù)
def delete_data(self):
''' 刪除數(shù)據(jù) '''
# 獲取要?jiǎng)h除的數(shù)據(jù)
data = self.session.query(News).get(39)
self.session.delete(data)
self.session.commit()
匯總操作數(shù)據(jù)庫
class MysqlOrmTest(object):
def __init__(self):
self.session = Session()
def add_one(self):
''' 添加數(shù)據(jù) '''
new_obj = News(
title='ORM標(biāo)題',
content='content',
types="百家"
)
self.session.add(new_obj)
self.session.commit()
return new_obj
def get_one(self):
''' 獲取一條數(shù)據(jù) '''
return self.session.query(News).get(1)
def get_more(self):
''' 獲取多條數(shù)據(jù) '''
return self.session.query(News).filter_by(is_valid=1)
def update_data(self):
''' 修改數(shù)據(jù) '''
obj = self.session.query(News).get(38)
obj.is_valid = 0
self.session.add(obj)
self.session.commit()
return obj
def delete_data(self):
''' 刪除數(shù)據(jù) '''
# 獲取要?jiǎng)h除的數(shù)據(jù)
data = self.session.query(News).get(39)
self.session.delete(data)
self.session.commit()
def main():
obj = MysqlOrmTest()
# rest = obj.add_one()
# print(dir(rest))
# print(obj.get_one().title)
# print(obj.get_more().count())
# for row in obj.get_more():
# print(row.title)
# print(obj.update_data())
obj.delete_data()
if __name__ == '__main__':
main()
