50 SQLAlchemy對數據的增刪改查操作 · Flask notebook

# 50 SQLAlchemy對數據的增刪改查操作 1.構建session對象，所有和數據庫的ORM操作必須通過一個叫做"session"的會話對象來實現，通過以下代碼來獲取會話對象: ```text from sqlalchemy.orm import sessionmaker # Session = sessionmaker(engine) # session = Session() # 上面兩行等價于下面一行 session = sessionmaker(engine)() ``` 2.添加對象 * 創建對象，也即創建一條數據 ```text p = Person(name="angle",age=16,country="china") ``` * 將對象添加到'session'會話對象中 ```text session.add(p) ``` * 將session中的對象做commit操作\(提交\) ```text session.commit() ``` * 一次性添加多條數據 ```text p1 = Person(name="angle1",age=16,country="china") p2 = Person(name='angle2',age=20,country='china') session.add_all([p1,p2]) ``` 3.查改對象 ```text #查找某個模型對應的那個表中所有的數據: all_person = session.query(Person).all() # 使用filter_by來做條件查詢 all_person = session.query(Person).filter_by(name='angle1').all() # 使用filter來做條件查詢 all_person = session.query(Person).filter(Person.name.contains('angle')) #使用get方法查找數據，get方法是根據id來查詢的，只會返回一條數據或者None person = session.query(Person).get(primary_key) #使用first方法獲取結果集中的第一條數據 person = session.query(Person).first() ``` 4.修改對象，首先在數據庫查找數據，然后將數據進行修改，最后做commit操作 ```text person = session.query(Person).first() person.name = 'angle_update' session.commit() ``` 5.刪除對象，首先在數據庫查找數據，然后將查找到的數據進行刪除，最后執行commit操作 ```text person = session.query(Person).first() session.delete(person) session.commit() ``` ```text from sqlalchemy import create_engine,Column,String,Integer from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker HOSTNAME = '127.0.0.1' PORT = "3306" USERNAME = "root" PASSWORD = "123456" DATABASE = "xt_flask" DB_URI = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE) engine = create_engine(DB_URI) Base = declarative_base(engine) # Session = sessionmaker(engine) # session = Session() # 上面兩行等價于下面一行 session = sessionmaker(engine)() class Person(Base): __tablename__ = "person" id = Column(Integer,primary_key=True,autoincrement=True) # String(50)==>varchar(50) name = Column(String(50)) age = Column(Integer) country = Column(String(50)) # 返回字符串 def __str__(self): return "<Person(name:%s,age:%s,country:%s)>" % (self.name,self.age,self.country) # session會話 # 增 def add_data(): p = Person(name="angle", age=16, country="china") p1 = Person(name="angle1",age=16,country="china") p2 = Person(name='angle2',age=20,country='china') session.add_all([p1,p2]) # session.add(p) session.commit() # 查 def serach_data(): # # 指定表，all()方法返回所有數據 # all_person = session.query(Person).all() # # print(all_person) # for p in all_person: # print(p) # 指定篩選 # all_person = session.query(Person).filter_by(name='angle1').all() # for p in all_person: # print(p) # # filter(條件) # all_person = session.query(Person).filter(Person.name.contains('angle')) # for p in all_person: # print(p) # # 獲取id為1的數據 # person = session.query(Person).get(1) # print(person) #使用first方法獲取結果集中的第一條數據 person = session.query(Person).first() print(person) # 改 def update_data(): person = session.query(Person).first() person.name = 'angle_update' session.commit() # 刪 def delete_data(): person = session.query(Person).first() session.delete(person) session.commit() if __name__ == "__main__": # Base.metadata.create_all() # add_data() # serach_data() # update_data() delete_data() ```