目录
一、创建
第一种方法:
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_base
Base=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) # 创建表
if __name__ == '__main__':
Session=sessionmaker(engine)
session=Session() # 实例化
# 添加用户
u1=User(name='张三',gender='男',town='北京')
u2 = User(name='李四', gender='男', town='上海')
u3 = User(name='小刘', gender='女', town='郑州')
session.add_all([u1,u2,u3])
session.commit()
# 添加语言
l1=Language(name='Python',advantage='开发快',disadvantage='运行慢')
l1.user=u1 # 关联用户u1
session.add(l1)
session.commit()
因为我把user表中原来的三行数据删除了,所以id从4开始
第二种写法:
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_base
Base=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() # 实例化
# 添加用户
u4 = User(name='王五', gender='男', town='河北')
u4.language = [Language(name='C++', advantage='稳定', disadvantage='难理解'),
Language(name='Python3', advantage='上手简单', disadvantage='太高级')]
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_base
Base=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() # 实例化
res=session.query(User).filter_by(id=7).first()
print("用户名:",res.name)
lan=session.query(Language).filter_by(user_id=res.id)
# 因为id=7的用户喜欢两门语言,所以循环输出
for i in lan:
print(i.name,i.advantage)
三、删除
# 删除
u=session.query(User).filter_by(id=4).first()
session.delete(u)
session.commit()
可以看到,Language表中的数据并没有被删除
解决方法是将class User(Base)修改为如下内容
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用户时,Language中对应的内容才会被删除
cascade='all,delete'
)
但是修改之后,要重新创建表,也就是原来的表不能适用了!把原来的表删除,再次用修改后的代码创建表并删除吧
重新建表
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_base
Base=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用户时,Language中对应的内容才会被删除
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) # 创建表
if __name__ == '__main__':
Session=sessionmaker(engine)
session=Session() # 实例化
# 添加用户
u1=User(name='张三',gender='男',town='北京')
u2 = User(name='李四', gender='男', town='上海')
u3 = User(name='小刘', gender='女', town='郑州')
session.add_all([u1,u2,u3])
session.commit()
# 添加语言
l1=Language(name='Python',advantage='开发快',disadvantage='运行慢')
# 关联用户u1,把u1的id赋值给ll的user_id
l1.user=u1
l2=l1=Language(name='Python3',advantage='开发快',disadvantage='运行慢')
l2.user=u2
session.add_all([l1,l2])
session.commit()
# 另一种写法
u4 = User(name='王五', gender='男', town='河北')
u4.language = [Language(name='H5', advantage='简单', disadvantage='运行慢'),
Language(name='C++', advantage='运行快', disadvantage='上手难')]
session.add(u4)
session.commit()
删除
if __name__ == '__main__':
Session=sessionmaker(engine)
session=Session() # 实例化
# 删除
u=session.query(User).filter_by(id=4).first()
session.delete(u)
session.commit()
可以看到,用户王五删除后,在Language表内其对应的语言也被一起删除
四、更新数据
# 更改用户名
u=session.query(User).filter(User.id==1).first()
u.name='大王'
session.commit()
# 更改关联表的语言名
lan=u.language[0].name='JS'
session.commit()