Python_sqlalchemy——创建、查询、删除、更新一对多的表
生活随笔
收集整理的這篇文章主要介紹了
Python_sqlalchemy——创建、查询、删除、更新一对多的表
小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
目錄
一、創(chuàng)建
第一種方法：
第二種寫(xiě)法：
二、查詢
三、刪除
四、更新數(shù)據(jù)
一、創(chuàng)建
第一種方法：
from sqlalchemy import create_engine from sqlalchemy import String,Integer,Column,select,ForeignKey from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy.ext.declarative import declarative_baseBase=declarative_base() engine=create_engine("mysql+pymysql://root:root@127.0.0.1:3306/pytest",pool_size=10,max_overflow=5,echo=True )class User(Base):__tablename__='user'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)gender=Column(String(10),nullable=True,default='保密')town=Column(String(125))language=relationship('Language',backref='user')class Language(Base):__tablename__='language'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)advantage=Column(String(125),nullable=True)disadvantage=Column(String(125),nullable=True)user_id=Column(Integer(),ForeignKey('user.id'))# Base.metadata.create_all(engine) # 創(chuàng)建表 if __name__ == '__main__':Session=sessionmaker(engine)session=Session() # 實(shí)例化# 添加用戶u1=User(name='張三',gender='男',town='北京')u2 = User(name='李四', gender='男', town='上海')u3 = User(name='小劉', gender='女', town='鄭州')session.add_all([u1,u2,u3])session.commit()# 添加語(yǔ)言l1=Language(name='Python',advantage='開(kāi)發(fā)快',disadvantage='運(yùn)行慢')l1.user=u1 # 關(guān)聯(lián)用戶u1session.add(l1)session.commit()因?yàn)槲野製ser表中原來(lái)的三行數(shù)據(jù)刪除了，所以id從4開(kāi)始
第二種寫(xiě)法：
from sqlalchemy import create_engine from sqlalchemy import String,Integer,Column,select,ForeignKey from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy.ext.declarative import declarative_baseBase=declarative_base() engine=create_engine("mysql+pymysql://root:root@127.0.0.1:3306/pytest",pool_size=10,max_overflow=5,echo=True )class User(Base):__tablename__='user'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)gender=Column(String(10),nullable=True,default='保密')town=Column(String(125))language=relationship('Language',backref='user')class Language(Base):__tablename__='language'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)advantage=Column(String(125),nullable=True)disadvantage=Column(String(125),nullable=True)user_id=Column(Integer(),ForeignKey('user.id')) if __name__ == '__main__':Session=sessionmaker(engine)session=Session() # 實(shí)例化# 添加用戶u4 = User(name='王五', gender='男', town='河北')u4.language = [Language(name='C++', advantage='穩(wěn)定', disadvantage='難理解'),Language(name='Python3', advantage='上手簡(jiǎn)單', disadvantage='太高級(jí)')]session.add(u4)session.commit()二、查詢
from sqlalchemy import create_engine from sqlalchemy import String,Integer,Column,select,ForeignKey from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy.ext.declarative import declarative_baseBase=declarative_base() engine=create_engine("mysql+pymysql://root:root@127.0.0.1:3306/pytest",pool_size=10,max_overflow=5,echo=True )class User(Base):__tablename__='user'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)gender=Column(String(10),nullable=True,default='保密')town=Column(String(125))language=relationship('Language',backref='user')class Language(Base):__tablename__='language'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)advantage=Column(String(125),nullable=True)disadvantage=Column(String(125),nullable=True)user_id=Column(Integer(),ForeignKey('user.id')) if __name__ == '__main__':Session=sessionmaker(engine)session=Session() # 實(shí)例化res=session.query(User).filter_by(id=7).first()print("用戶名：",res.name)lan=session.query(Language).filter_by(user_id=res.id)# 因?yàn)閕d=7的用戶喜歡兩門(mén)語(yǔ)言，所以循環(huán)輸出for i in lan:print(i.name,i.advantage)三、刪除
# 刪除u=session.query(User).filter_by(id=4).first()session.delete(u)session.commit()可以看到，Language表中的數(shù)據(jù)并沒(méi)有被刪除
解決方法是將class User(Base)修改為如下內(nèi)容
class User(Base):__tablename__='user'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)gender=Column(String(10),nullable=True,default='保密')town=Column(String(125))language=relationship('Language',backref='user', # 反向引用聲明# 這樣刪除user用戶時(shí)，Language中對(duì)應(yīng)的內(nèi)容才會(huì)被刪除cascade='all,delete')但是修改之后，要重新創(chuàng)建表，也就是原來(lái)的表不能適用了！把原來(lái)的表刪除，再次用修改后的代碼創(chuàng)建表并刪除吧
重新建表
from sqlalchemy import create_engine from sqlalchemy import String,Integer,Column,select,ForeignKey from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy.ext.declarative import declarative_baseBase=declarative_base() engine=create_engine("mysql+pymysql://root:root@127.0.0.1:3306/pytest",pool_size=10,max_overflow=5,echo=True )class User(Base):__tablename__='user'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)gender=Column(String(10),nullable=True,default='保密')town=Column(String(125))language=relationship('Language',backref='user', # 反向引用聲明# 這樣刪除user用戶時(shí)，Language中對(duì)應(yīng)的內(nèi)容才會(huì)被刪除cascade='all,delete')class Language(Base):__tablename__='language'id=Column(Integer(),primary_key=True,autoincrement=True)name=Column(String(125),nullable=True)advantage=Column(String(125),nullable=True)disadvantage=Column(String(125),nullable=True)user_id=Column(Integer(),ForeignKey('user.id'))Base.metadata.create_all(engine) # 創(chuàng)建表 if __name__ == '__main__':Session=sessionmaker(engine)session=Session() # 實(shí)例化# 添加用戶u1=User(name='張三',gender='男',town='北京')u2 = User(name='李四', gender='男', town='上海')u3 = User(name='小劉', gender='女', town='鄭州')session.add_all([u1,u2,u3])session.commit()# 添加語(yǔ)言l1=Language(name='Python',advantage='開(kāi)發(fā)快',disadvantage='運(yùn)行慢')# 關(guān)聯(lián)用戶u1，把u1的id賦值給ll的user_idl1.user=u1l2=l1=Language(name='Python3',advantage='開(kāi)發(fā)快',disadvantage='運(yùn)行慢')l2.user=u2session.add_all([l1,l2])session.commit()# 另一種寫(xiě)法u4 = User(name='王五', gender='男', town='河北')u4.language = [Language(name='H5', advantage='簡(jiǎn)單', disadvantage='運(yùn)行慢'),Language(name='C++', advantage='運(yùn)行快', disadvantage='上手難')]session.add(u4)session.commit()刪除
if __name__ == '__main__':Session=sessionmaker(engine)session=Session() # 實(shí)例化# 刪除u=session.query(User).filter_by(id=4).first()session.delete(u)session.commit()可以看到，用戶王五刪除后，在Language表內(nèi)其對(duì)應(yīng)的語(yǔ)言也被一起刪除
四、更新數(shù)據(jù)
# 更改用戶名u=session.query(User).filter(User.id==1).first()u.name='大王'session.commit()# 更改關(guān)聯(lián)表的語(yǔ)言名lan=u.language[0].name='JS'session.commit()?
總結(jié)
以上是生活随笔為你收集整理的Python_sqlalchemy——创建、查询、删除、更新一对多的表的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： Mysql之外连接_OUTER JOIN
- 下一篇： Python_sqlalchemy之多对