?- 使用前請先安裝sqlalchemy

?

?- 創建數據表

# 導入官宣基礎模型 from sqlalchemy.ext.declarative import declarative_base # 實例化官宣模型 - Base 就是 ORM 模型 Base = declarative_base() # 當前的這個Object繼承了Base也就是代表了Object繼承了ORM的模型 class User(Base): # 相當于 Django Models中的 Model# 為Table創建名稱__tablename__ = "user"# 創建ID數據字段 , 創建ID字段 == 創建ID數據列from sqlalchemy import Column,Integer,String# id = Column(數據類型,索引,主鍵,外鍵,等等)# int == Integerid = Column(Integer,primary_key=True,autoincrement=True)# str == char(長度) == String(長度)name = Column(String(32),index=True) # 去連接數據庫 創建數據引擎 from sqlalchemy import create_engine # 創建的數據庫引擎 engine = create_engine("mysql+pymysql://root:這里是sql密碼沒有可以不寫@127.0.0.1:3306/dragon?charset=utf8")# Base 自動檢索所有繼承Base的ORM 對象 并且創建所有的數據表 Base.metadata.create_all(engine)

?

?- 增刪改查 (單表)

# 導入創建好的User和engine from create_table import engine,User # 導入 sqlalchemy.orm 中的 sessionmaker 就是創建一個操縱數據庫的窗口 from sqlalchemy.orm import sessionmaker # 創建 sessionmaker 會話對象,將數據庫引擎 engine 交給 sessionmaker Session = sessionmaker(engine) # 打開會話對象 Session db_session = Session()# 方法一 # 創建數據 user_obj = User(name="jamlee") # 在db_session會話中添加一條 UserORM模型創建的數據 db_session.add(user_obj) # 使用 db_session 會話提交 , 這里的提交是指將db_session中的所有指令一次性提交 db_session.commit() db_session.close()# 方法二 提交多條數據 db_session.add_all([User(name="123"),User(name="wqz"),User(name="ywb"), ])db_session.commit() db_session.close() 添加數據 from create_table import User,engine from sqlalchemy.orm import sessionmaker Session = sessionmaker(engine) db_session = Session()res = db_session.query(User).filter(User.name=="ywb").update({"name":"豬"}) print(res) # res就是我們當前這句更新語句所更新的行數 db_session.commit() db_session.close()# 修改多條 res = db_session.query(User).filter(User.id <= 20).update({"name":"豬頭"}) print(res) db_session.commit() db_session.close() 修改數據 from create_table import User,engine from sqlalchemy.orm import sessionmaker Session = sessionmaker(engine) db_session = Session()# 查詢所有 user_list = db_session.query(User).all() for row in user_list:print(row.id,row.name)# 查詢第一條 user = db_session.query(User).first() print(user.name)# 查詢符合條件的所有 user = db_session.query(User).filter(User.name == "alex").all() print(user[0].name)# 查詢符合條件的第一條 user = db_session.query(User).filter_by(id=4).first() print(user.id,user.name)# 查詢sql原始語句 user = db_session.query(User) print(user)最后記得都要關閉哦 db_session.close() 查看數據 from create_table import engine,User from sqlalchemy.orm import sessionmakerSession = sessionmaker(engine) db_session = Session()db_session.query(User).filter(User.id==4).delete() db_session.commit() db_session.close() 刪除數據 from my_create_table import User,engine from sqlalchemy.orm import sessionmakerSession = sessionmaker(engine) db_session = Session()# 查詢數據表操作 # and or from sqlalchemy.sql import and_ , or_ ret = db_session.query(User).filter(and_(User.id > 3, User.name == 'jamlee')).all() ret = db_session.query(User).filter(or_(User.id < 2, User.name == 'jamlee')).all()# 查詢所有數據 r1 = db_session.query(User).all()# 查詢數據 指定查詢數據列 加入別名 r2 = db_session.query(User.name.label('username'), User.id).first() print(r2.id,r2.username) # 表達式篩選條件 r3 = db_session.query(User).filter(User.name == "jamlee").all()# 原生SQL篩選條件 r4 = db_session.query(User).filter_by(name='jamlee').all() r5 = db_session.query(User).filter_by(name='jamlee').first()# 字符串匹配方式篩選條件 并使用 order_by進行排序 r6 = db_session.query(User).filter(text("id<:value and name=:name")).params(value=224, name='jamlee').order_by(User.id).all()#原生SQL查詢 r7 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='jamlee').all()# 篩選查詢列 # query的時候我們不在使用User ORM對象,而是使用User.name來對內容進行選取 user_list = db_session.query(User.name).all() print(user_list) for row in user_list:print(row.name)# 別名映射 name as nick user_list = db_session.query(User.name.label("nick")).all() print(user_list) for row in user_list:print(row.nick) # 這里要寫別名了# 篩選條件格式 user_list = db_session.query(User).filter(User.name == "jamlee").all() user_list = db_session.query(User).filter(User.name == "jamlee").first() user_list = db_session.query(User).filter_by(name="jamlee").first() for row in user_list:print(row.nick)# 復雜查詢 from sqlalchemy.sql import text user_list = db_session.query(User).filter(text("id<:value and name=:name")).params(value=3,name="jamlee")# 查詢語句 from sqlalchemy.sql import text user_list = db_session.query(User).filter(text("select * from User id<:value and name=:name")).params(value=3,name="jamlee")# 排序 : user_list = db_session.query(User).order_by(User.id).all() user_list = db_session.query(User).order_by(User.id.desc()).all() for row in user_list:print(row.name,row.id)#其他查詢條件 """ ret = session.query(User).filter_by(name='jamlee').all() ret = session.query(User).filter(User.id > 1, User.name == 'jamlee').all() ret = session.query(User).filter(User.id.between(1, 3), User.name == 'jamlee').all() # between 大于1小于3的 ret = session.query(User).filter(User.id.in_([1,3,4])).all() # in_([1,3,4]) 只查詢id等于1,3,4的 ret = session.query(User).filter(~User.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查詢不等于1,3,4的 ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='jamlee'))).all() 子查詢 from sqlalchemy import and_, or_ ret = session.query(User).filter(and_(User.id > 3, User.name == 'jamlee')).all() ret = session.query(User).filter(or_(User.id < 2, User.name == 'jamlee')).all() ret = session.query(User).filter(or_(User.id < 2,and_(User.name == 'eric', User.id > 3),User.extra != "")).all() # select * from User where id<2 or (name="eric" and id>3) or extra != "" # 通配符 ret = db_session.query(User).filter(User.name.like('e%')).all() ret = db_session.query(User).filter(~User.name.like('e%')).all()# 限制 ret = db_session.query(User)[1:2]# 排序 ret = db_session.query(User).order_by(User.name.desc()).all() ret = db_session.query(User).order_by(User.name.desc(), User.id.asc()).all()# 分組 from sqlalchemy.sql import funcret = db_session.query(User).group_by(User.extra).all() ret = db_session.query(func.max(User.id),func.sum(User.id),func.min(User.id)).group_by(User.name).all()ret = db_session.query(func.max(User.id),func.sum(User.id),func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all() """# 關閉連接 db_session.close() 更多查詢方法 #高級版更新操作 from my_create_table import User,engine from sqlalchemy.orm import sessionmakerSession = sessionmaker(engine) db_session = Session()#直接修改 db_session.query(User).filter(User.id > 0).update({"name" : "099"})#在原有值基礎上添加 - 1 db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)#在原有值基礎上添加 - 2 db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate") db_session.commit() 更多更新方法

?

?-?ForeignKey (一對多)

　　 - 創建數據表+relationship

from sqlalchemy.ext.declarative import declarative_base # 關系映射 from sqlalchemy.orm import relationship from sqlalchemy import Column, Integer, String, ForeignKeyBase = declarative_base()class Student(Base):__tablename__ = "student"id = Column(Integer,primary_key=True)name = Column(String(32))# 關聯字段 主外鍵關系(這里的ForeignKey一定要是表名.id不是對象名)school_id = Column(Integer,ForeignKey("school.id"))# 將student 和school創建關系 這個不是字段,只是關系,backref是反向關聯的關鍵字stu2sch = relationship("School",backref = "sch2stu")class School(Base):__tablename__ = "school"id = Column(Integer, primary_key=True)name = Column(String(32))from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/userinfo?charset=utf8") Base.metadata.create_all(engine)

　　 - 增刪改查

from sqlalchemy.orm import sessionmaker from create_table import engine, Student, SchoolSession = sessionmaker(engine) db_session = Session()# 先演示一下笨方法 sch = db_session.query(School).filter(School.name == "清華").first() stu_obj = Student(name="jamlee",school_id=sch.id) db_session.add(stu_obj) db_session.commit() db_session.close()# 開始表演 正向插入 stu_obj = Student(name="wqz",stu2sch=School(name = "北大")) db_session.add(stu_obj) db_session.commit() db_session.close()# 反向插入 sch_obj = School(name="復旦") sch_obj.sch2stu = [Student(name="123"),Student(name="ywb")] db_session.add(sch_obj) db_session.commit() db_session.close() 基于relationship添加數據 from sqlalchemy.orm import sessionmaker from create_table import engine,Student,SchoolSession = sessionmaker(engine) db_session = Session()# 正向查詢 stu = db_session.query(Student).all() for row in stu:print(row.id,row.name,row.school_id,row.stu2sch.name)# 反向查詢 sch = db_session.query(School).all() for school in sch:for student in school.sch2stu:print(school.id,school.name,student.name) 基于relationship查詢數據 from sqlalchemy.orm import sessionmaker from create_table import engine,Student,SchoolSession = sessionmaker(engine) db_session = Session()sch = db_session.query(School).filter(School.name=="清華").first() db_session.query(Student).filter(Student.name=="wqz").update({"school_id":sch.id}) db_session.commit() db_session.close() 基于relationship修改數據 from sqlalchemy.orm import sessionmaker from create_table import engine,Student,SchoolSession = sessionmaker(engine) db_session = Session()sch = db_session.query(School).filter(School.name=="北大").first() db_session.query(Student).filter(Student.school_id==sch.id).delete() db_session.commit() db_session.close() 基于relationship刪除數據

?

?

?- ManyToMany(多對多)

　　 - 創建表

from sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()from sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.orm import relationshipclass Girls(Base):__tablename__ = "girl"id = Column(Integer,primary_key=True)name = Column(String(32))girl2boy = relationship("Boys",secondary="hotel",backref="boy2girl")class Boys(Base):__tablename__ = "boy"id = Column(Integer, primary_key=True)name = Column(String(32))class Hotel(Base):__tablename__ = "hotel"id = Column(Integer, primary_key=True)girl_id = Column(Integer, ForeignKey("girl.id"))boy_id = Column(Integer, ForeignKey("boy.id"))from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/userinfo?charset=utf8")Base.metadata.create_all(engine) from sqlalchemy.orm import sessionmaker from create_table import engine,Boys,GirlsSession = sessionmaker(engine) db_session = Session()# 正向 girl_obj = Girls(name="熱巴",girl2boy=[Boys(name="jamlee"),Boys(name="wqz")]) db_session.add(girl_obj) db_session.commit() db_session.close()# 反向 boy = Boys(name="GD") boy.boy2girl = [Girls(name="IU"),Girls(name="YWB")] db_session.add(boy) db_session.commit() db_session.close() 多對多添加 from sqlalchemy.orm import sessionmaker from create_table import engine,Boys,GirlsSession = sessionmaker(engine) db_session = Session()# 正向 g_list =db_session.query(Girls).all()for girl in g_list:for boy in girl.girl2boy:print(girl.name,boy.name)# 反向 b_list = db_session.query(Boys).all() for boy in b_list:for girl in boy.boy2girl:print(boy.name,girl.name) 多對對查詢

?

轉載于:https://www.cnblogs.com/lzmdbk/p/10384778.html

總結

以上是生活随笔為你收集整理的sqlalchemy 基操,勿6的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。