sql python 教程_Python SQLAlchemy快速入门教程

0、模块的安装

pip install SQLAlchemy

一、单表的操作

1、单表的创建

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/5

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, Stringfrom sqlalchemy.engine importcreate_engine#声明所有的ORM类对象继承的基类

BaseModel =declarative_base()#Column 定义列的数据#Integer、String数据类型

classUser(BaseModel):__tablename__ = "t_user"id= Column(Integer, primary_key=True, autoincrement=True)

name= Column(String(32), nullable=False, index=True, unique=True)if __name__ == '__main__':#创建数据库引擎

engine = create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建数据库表

BaseModel.metadata.create_all(engine)

2、单表的增删改查

2.1、单表插入数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/5

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, Stringfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker#声明所有的ORM类对象继承的基类

BaseModel =declarative_base()#Column 定义列的数据#Integer、String数据类型

classUser(BaseModel):__tablename__ = "t_user"id= Column(Integer, primary_key=True, autoincrement=True)

name= Column(String(32), nullable=False, index=True, unique=True)if __name__ == '__main__':#创建数据库引擎

engine = create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建sql会话

db_sessoin =sessionmaker(engine)()#插入数据

user = User(name='单条插入，你好1')

user_list=[

User(name='批量插入，你好_list_1'),

User(name='批量插入，你好_list_2'),

User(name='批量插入，你好_list_3')

]

db_sessoin.add(user)#单条插入

db_sessoin.add_all(user_list) #批量插入

db_sessoin.commit()#提交数据

db_sessoin.close() #关闭会话

插入数据代码

2.2、单表查询数据

2.2.1、单表无条件查询数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/5

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, Stringfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker#声明所有的ORM类对象继承的基类

BaseModel =declarative_base()#Column 定义列的数据#Integer、String数据类型

classUser(BaseModel):__tablename__ = "t_user"id= Column(Integer, primary_key=True, autoincrement=True)

name= Column(String(32), nullable=False, index=True, unique=True)if __name__ == '__main__':#创建数据库引擎

engine = create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建sql会话

db_sessoin =sessionmaker(engine)()#查询所有数据

user_list =db_sessoin.query(User).all()print('查询所有的数据')for user_obj inuser_list:print(user_obj.id, user_obj.name)#查询第一条数据

first_user =db_sessoin.query(User).first()print('查询第一条数据')print(first_user.id, first_user.name)

db_sessoin.close()#关闭会话

单表无条件查询数据代码

2.2.2、单表有条件查询数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/5

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, Stringfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker#声明所有的ORM类对象继承的基类

BaseModel =declarative_base()#Column 定义列的数据#Integer、String数据类型

classUser(BaseModel):__tablename__ = "t_user"id= Column(Integer, primary_key=True, autoincrement=True)

name= Column(String(32), nullable=False, index=True, unique=True)if __name__ == '__main__':#创建数据库引擎

engine = create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建sql会话

db_sessoin =sessionmaker(engine)()#查询指定的数据

user_list1 = db_sessoin.query(User).filter(User.id == 3).all()

user_list2= db_sessoin.query(User).filter_by(id=3).all()#and

ret_list = db_sessoin.query(User).filter(User.id == 2, User.name == '批量插入，你好_list_2').all()#使用and链接起来，其实是or的关系

#ret_list = db_sessoin.query(User).filter(User.id == 2 and User.name == '批量插入，你好_list_2').all()

db_sessoin.close()#关闭会话

单表有条件查询数据的代码

2.3、单表修改数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/5#@Author : suk#@File : first_orm.py#@Software: PyCharm

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, Stringfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker#声明所有的ORM类对象继承的基类

BaseModel =declarative_base()#Column 定义列的数据#Integer、String数据类型

classUser(BaseModel):__tablename__ = "t_user"id= Column(Integer, primary_key=True, autoincrement=True)

name= Column(String(32), nullable=False, index=True, unique=True)if __name__ == '__main__':#创建数据库引擎

engine = create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建sql会话

db_sessoin =sessionmaker(engine)()

ret= db_sessoin.query(User).filter(User.id == 1).update(

{'name': '张三'}

)

db_sessoin.commit()

db_sessoin.close()#关闭会话

单表修改数据代码

2.4、单表删除数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/5

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, Stringfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker#声明所有的ORM类对象继承的基类

BaseModel =declarative_base()#Column 定义列的数据#Integer、String数据类型

classUser(BaseModel):__tablename__ = "t_user"id= Column(Integer, primary_key=True, autoincrement=True)

name= Column(String(32), nullable=False, index=True, unique=True)if __name__ == '__main__':#创建数据库引擎

engine = create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建sql会话

db_sessoin =sessionmaker(engine)()

ret= db_sessoin.query(User).filter(User.id == 2).delete()

db_sessoin.commit()

db_sessoin.close()#关闭会话

单表删除数据代码

二、一对一的操作

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy.orm importrelationship, sessionmakerfrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_engine

Base_Model=declarative_base()classWife(Base_Model):__tablename__ = 'wife'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

husband= relationship("Husband", uselist=False, back_populates="wife") #uselist=False,不能使用列表批量增加数据

classHusband(Base_Model):__tablename__ = 'husband'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

wife_id= Column(Integer, ForeignKey('wife.id'))

wife= relationship("Wife", back_populates="husband")if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建数据表

Base_Model.metadata.create_all(engine)

db_session=sessionmaker(engine)()#增加数据

wife_obj = Wife(name='女1', husband=Husband(name='男1'))

db_session.add(wife_obj)#删除数据

db_session.query(Husband).filter(Husband.id == '1').delete()#修改数据

ret = db_session.query(Wife).filter(Wife.name == '女1').one()

ret.husband= db_session.query(Husband).filter(Husband.name == '男3').one()#查询数据

#正向查询

ret = db_session.query(Husband).filter(Husband.name == '男3').one()print(ret.name, ret.wife.name)#反向查询

ret = db_session.query(Wife).filter(Wife.name == '女1').one()print(ret.name, ret.husband.name)

db_session.commit()

db_session.close()

一对一创建表和CRUD代码

三、一对多的操作

1、一对多表的创建

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.orm importrelationshipfrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_engine

BaseModel=declarative_base()

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')classSchool(BaseModel):__tablename__ = 'school'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classStudent(BaseModel):__tablename__ = 'student'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

sch_id= Column(Integer, ForeignKey('school.id')) #这个是数据库层面关联

#ORM层面关联

stu2sch = relationship('School', backref='sch2stu')if __name__ == '__main__':#创建表

BaseModel.metadata.create_all(engine)

2、一对多的增删改查

2.1、一对多插入数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.orm importrelationshipfrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker

BaseModel=declarative_base()classSchool(BaseModel):__tablename__ = 'school'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classStudent(BaseModel):__tablename__ = 'student'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

sch_id= Column(Integer, ForeignKey('school.id')) #这个是数据库层面关联

#ORM层面关联

stu2sch = relationship('School', backref='sch2stu')if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')

db_session=sessionmaker(engine)()#正向插入

stu = Student(name='张三', stu2sch=School(name='清华大学'))

db_session.add(stu)#反向插入

sch = School(name='北京大学')

sch.sch2stu=[

Student(name='王五'),

Student(name='赵龙')

]

db_session.add(sch)

db_session.commit()

db_session.close()

一对多插入数据

2.2、一对多查询数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6#@Author : suk#@File : fk.py#@Software: PyCharm

from sqlalchemy.orm importrelationshipfrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker

BaseModel=declarative_base()classSchool(BaseModel):__tablename__ = 'school'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classStudent(BaseModel):__tablename__ = 'student'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

sch_id= Column(Integer, ForeignKey('school.id')) #这个是数据库层面关联

#ORM层面关联

stu2sch = relationship('School', backref='sch2stu')if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')

db_session=sessionmaker(engine)()#正向查询

students =db_session.query(Student).all()print('正向查询')for student instudents:print(student.name, student.stu2sch.name)#反向查询

schools =db_session.query(School).all()print('反向查询')for school inschools:for stu inschool.sch2stu:print(school.name, stu.name)

db_session.close()

一对多查询数据代码

2.3、一对多修改数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.orm importrelationshipfrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker

BaseModel=declarative_base()classSchool(BaseModel):__tablename__ = 'school'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classStudent(BaseModel):__tablename__ = 'student'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

sch_id= Column(Integer, ForeignKey('school.id')) #这个是数据库层面关联

#ORM层面关联

stu2sch = relationship('School', backref='sch2stu')if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')

db_session=sessionmaker(engine)()#正向修改，思路：先查询学生，再修改学校

school_obj = db_session.query(School).filter(School.name == '清华大学').one()

student_obj= db_session.query(Student).filter(Student.id == 1).one()

student_obj.stu2sch=school_obj#反向修改，思路：先查询学校，再将学校下面的学生转移到新的学校

school_obj = db_session.query(School).filter(School.name == '北京大学').one()

student_list=school_obj.sch2stu

school_obj= db_session.query(School).filter(School.name == '清华大学').one()

school_obj.sch2stu= school_obj.sch2stu +student_list

db_session.commit()

db_session.close()

一对多修改数据代码

2.4、一对多删除数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.orm importrelationshipfrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_enginefrom sqlalchemy.orm importsessionmaker

BaseModel=declarative_base()classSchool(BaseModel):__tablename__ = 'school'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classStudent(BaseModel):__tablename__ = 'student'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

sch_id= Column(Integer, ForeignKey('school.id')) #这个是数据库层面关联

#ORM层面关联

stu2sch = relationship('School', backref='sch2stu')if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')

db_session=sessionmaker(engine)()#反向修改，思路：先查询学校，再将学校下面的学生删除

school_obj = db_session.query(School).filter(School.name == '北京大学').one()#school_obj.sch2stu.clear() # 清除关联

for sch inschool_obj.sch2stu:

db_session.query(Student).filter(Student.id==sch.id).delete()

db_session.commit()

db_session.close()

一对多删除数据代码

四、多对多的操作

1、多对多表的创建

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy.orm importrelationshipfrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_engine

Base_Model=declarative_base()classGirl(Base_Model):__tablename__ = 'girl'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)#secondary="hotel",数据表中的数据才能证明两者关系

g2b = relationship('Boy', backref='b2g', secondary='hotel')classBoy(Base_Model):__tablename__ = 'boy'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classHotel(Base_Model):__tablename__ = 'hotel'id= Column(Integer, primary_key=True)

gid= Column(Integer, ForeignKey('girl.id'))

bid= Column(Integer, ForeignKey('boy.id'))if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')

Base_Model.metadata.create_all(engine)

2、多对多的增删改查

2.1、多对多插入数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy.orm importrelationship, sessionmakerfrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_engine

Base_Model=declarative_base()classGirl(Base_Model):__tablename__ = 'girl'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)#secondary="hotel",数据表中的数据才能证明两者关系

g2b = relationship('Boy', backref='b2g', secondary='hotel')classBoy(Base_Model):__tablename__ = 'boy'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classHotel(Base_Model):__tablename__ = 'hotel'id= Column(Integer, primary_key=True)

gid= Column(Integer, ForeignKey('girl.id'))

bid= Column(Integer, ForeignKey('boy.id'))if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建数据表

#Base_Model.metadata.create_all(engine)

db_session =sessionmaker(engine)()#正向增加

g = Girl(name='小红', g2b=[Boy(name='张三'), Boy(name='李四')])

db_session.add(g)#反向增加

b = Boy(name='张某')

b.b2g=[

Girl(name='女某1'),

Girl(name='女某2'),

Girl(name='女某3'),

]

db_session.add(b)

db_session.commit()

db_session.close()

多对多插入数据

2.2、多对多查询数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy.orm importrelationship, sessionmakerfrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_engine

Base_Model=declarative_base()classGirl(Base_Model):__tablename__ = 'girl'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)#secondary="hotel",数据表中的数据才能证明两者关系

g2b = relationship('Boy', backref='b2g', secondary='hotel')classBoy(Base_Model):__tablename__ = 'boy'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classHotel(Base_Model):__tablename__ = 'hotel'id= Column(Integer, primary_key=True)

gid= Column(Integer, ForeignKey('girl.id'))

bid= Column(Integer, ForeignKey('boy.id'))if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建数据表

#Base_Model.metadata.create_all(engine)

db_session =sessionmaker(engine)()#正向查询

girl_list = db_session.query(Girl).filter(Girl.name == '小红').all()for girl ingirl_list:

boy_list=girl.g2bfor boy inboy_list:print(girl.name, boy.name)#反向查询

boy_list = db_session.query(Boy).filter(Boy.name == '张三').all()for boy inboy_list:

girl_list=boy.b2gfor girl ingirl_list:print(boy.name, girl.name)

db_session.close()

多对多查询数据代码

2.3、多对多修改数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy.orm importrelationship, sessionmakerfrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_engine

Base_Model=declarative_base()classGirl(Base_Model):__tablename__ = 'girl'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)#secondary="hotel",数据表中的数据才能证明两者关系

g2b = relationship('Boy', backref='b2g', secondary='hotel')classBoy(Base_Model):__tablename__ = 'boy'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classHotel(Base_Model):__tablename__ = 'hotel'id= Column(Integer, primary_key=True)

gid= Column(Integer, ForeignKey('girl.id'))

bid= Column(Integer, ForeignKey('boy.id'))if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建数据表

#Base_Model.metadata.create_all(engine)

db_session =sessionmaker(engine)()

girl_obj= db_session.query(Girl).filter(Girl.name == '女某1').first()

boy_obj= db_session.query(Boy).filter(Boy.name == '李四').first()#将关联清除，再重新绑定

girl_obj.g2b.clear()

girl_obj.g2b.append(boy_obj)

db_session.commit()

db_session.close()

多对多修改数据代码

2.4、多对多删除数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#@Time : 2020/11/6

from sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy.orm importrelationship, sessionmakerfrom sqlalchemy importColumn, Integer, String, ForeignKeyfrom sqlalchemy.engine importcreate_engine

Base_Model=declarative_base()classGirl(Base_Model):__tablename__ = 'girl'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)#secondary="hotel",数据表中的数据才能证明两者关系

g2b = relationship('Boy', backref='b2g', secondary='hotel')classBoy(Base_Model):__tablename__ = 'boy'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)classHotel(Base_Model):__tablename__ = 'hotel'id= Column(Integer, primary_key=True)

gid= Column(Integer, ForeignKey('girl.id'))

bid= Column(Integer, ForeignKey('boy.id'))if __name__ == '__main__':

engine= create_engine('mysql+pymysql://test:test@127.0.0.1:3306/celery_db?charset=utf8')#创建数据表

#Base_Model.metadata.create_all(engine)

db_session =sessionmaker(engine)()

girl_obj= db_session.query(Girl).filter(Girl.name == '小红').first()

boy_obj= db_session.query(Boy).filter(Boy.name == '张某').first()

girl_obj.g2b.clear()#删除所有的数据

girl_obj.g2b.remove(boy_obj)#删除指定的数据

db_session.add(girl_obj)

db_session.commit()

db_session.close()

多对多删除数据代码

五、总结

更新请参考官方文档：

https://docs.sqlalchemy.org/en/13/