sqlalchemy的基本操作大全

SQLAlchemy是一个数据库的ORM框架，安装命令为`pip install sqlalchemy==1.4.46

ORM是什么

ORM:Object Relationship Mapping，既对象关系映射，通过ORM我们可以通过类的方式去操作数据库，而不用再写原生的SQL语句。

通过把表映射成类，把行作为实例对象，把字段作为类属性，ORM在执行对象操作的时候最终还是会把对应的操作转化为数据库原生语句。

其主要优点有：
1.性能损耗小
2. 设计灵活
3. 可移植性强
4. 易用

1.基本使用

1.1创建表

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker
# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class Person(Base):# 表名__tablename__ = 'person'# 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射，这些属性必须是sqlalchemy提供好的数据类型# 设定id为Int，主键， 自增长id = Column(Integer, primary_key=True, autoincrement=True)# String类型需要指定长度name = Column(String(50))age = Column(Integer)
#删除表
#Base.metadata.drop_all(engine)
#创建表
Base.metadata.create_all(engine)

1.2增

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker
# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class Person(Base):# 表名__tablename__ = 'person'# 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射，这些属性必须是sqlalchemy提供好的数据类型# 设定id为Int，主键， 自增长id = Column(Integer, primary_key=True, autoincrement=True)# String类型需要指定长度name = Column(String(50))age = Column(Integer)
session = sessionmaker(engine)()
#新增一条数据
p1 = Person(name='张三', age=18)
session.add(p1)
session.commit()
#新增多条数据
p2 = Person(name='李四', age=19)
p3 = Person(name='王五', age=20)
session.add_all([p1,p2])
session.commit()

1.3删

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker
# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class Person(Base):# 表名__tablename__ = 'person'# 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射，这些属性必须是sqlalchemy提供好的数据类型# 设定id为Int，主键， 自增长id = Column(Integer, primary_key=True, autoincrement=True)# String类型需要指定长度name = Column(String(50))age = Column(Integer)
session = sessionmaker(engine)()
person = session.query(Person).first()
session.delete(person)
session.commit()

1.4改

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker
# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class Person(Base):# 表名__tablename__ = 'person'# 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射，这些属性必须是sqlalchemy提供好的数据类型# 设定id为Int，主键， 自增长id = Column(Integer, primary_key=True, autoincrement=True)# String类型需要指定长度name = Column(String(50))age = Column(Integer)
session = sessionmaker(engine)()
person = session.query(Person).first()
person .name = '食铁兽'
session.commit()

1.5查

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker
# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class Person(Base):# 表名__tablename__ = 'person'# 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射，这些属性必须是sqlalchemy提供好的数据类型# 设定id为Int，主键， 自增长id = Column(Integer, primary_key=True, autoincrement=True)# String类型需要指定长度name = Column(String(50))age = Column(Integer)
session = sessionmaker(engine)()
query_person = session.query(Person)
#查找所有数据
all_person = query_person.all()
for person in all_person:print(person)
#查找第一条数据
all_person = query_person.first()
for person in all_person:print(person)
#查找和主键相同的数据，返回一条数据或None
person = query_person.get(2)
print(person)
#filter_by：通过参数中的值，来查找符合参数中的值的数据，返回一个查找对象
all_person = query_person.filter_by(name='食铁兽').all()
for person in all_person:print(person)
#filter：通过参数中的布尔表达式，来查找使布尔表达式为True的数据，返回一个查找对象，注意：参数必须是类.属性名，功能比filter_by更强
person = query_person.filter(Person.name == '王五').first()
print(person)

2.字段常用数据类型和属性设置

2.1字段常用数据类型

1. Integer：整形，映射到数据库中是int类型。
2. Float：浮点类型，映射到数据库中是float类型。他占据的32位。
3. Double：双精度浮点类型，映射到数据库中是double类型，占据64位。
4. String：可变字符类型，映射到数据库中是varchar类型.
5. Boolean：布尔类型，映射到数据库中的是tinyint类型。
6. DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位。
7. Enum：枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举
8. Date：存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用datetime.date来指定
9. DateTime：存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用datetime.datetime来指定
10. Time：存储时间，可以存储时分秒。映射到数据库中也是time类型。在Python代码中，可以使用datetime.time来至此那个
11. Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。
12. LONGTEXT：长文本类型，映射到数据库中是longtext类型

2.2字段常用的属性设置

1. primary_key：设置某个字段为主键。
2. autoincrement：设置这个字段为自动增长的。
3. default：设置某个字段的默认值。在发表时间这些字段上面经常用。
4. nullable：指定某个字段是否为空。默认值是True，就是可以为空。
5. unique：指定某个字段的值是否唯一。默认是False。
6. onupdate：在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是
   update_time（每次更新数据的时候都要更新的值）。
7. name：指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为参数。这个参数也可以当作位置参数，在第1个参数来指定。
8. comment：设置该字段的注释

3.ORM关系

3.1一对一

在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个uselist=False这个参数进去。就是告诉父模型，以后引用这个从模型的时候，不再是一个列表了，而是一个对象了

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker
# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class User(Base):__tablename__ = 'user'id = Column(Integer,primary_key=True,autoincrement=True)username = Column(String(50),nullable=False)extend = relationship("UserExtend",uselist=False)def __repr__(self):return "" % self.usernameclass UserExtend(Base):__tablename__ = 'user_extend'id = Column(Integer, primary_key=True, autoincrement=True)school = Column(String(50))uid = Column(Integer,ForeignKey("user.id"))user = relationship("User",backref="extend")Base.metadata.create_all(engine)

可以借助sqlalchemy.orm.backref来简化代码

3.1.2优化一对一关系

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker,backref
# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class User(Base):__tablename__ = 'user'id = Column(Integer,primary_key=True,autoincrement=True)username = Column(String(50),nullable=False)# extend = relationship("UserExtend",uselist=False)def __repr__(self):return "" % self.usernameclass UserExtend(Base):__tablename__ = 'user_extend'id = Column(Integer, primary_key=True, autoincrement=True)school = Column(String(50))uid = Column(Integer,ForeignKey("user.id"))user = relationship("User",backref=backref("extend",uselist=False))Base.metadata.create_all(engine)

3.2一对多

from sqlalchemy import create_engine, Column, Integer, String,Enum,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker, backref# 配置链接数据库信息
db_config = {'host': '127.0.0.1','port': '3306','database': 'mytest1','username': 'root','password': '123456'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
Base = declarative_base(engine)
class Author(Base):__tablename__ = 'author'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(10), nullable=False)# 关联，默认为一对多，有外键的是多，被引的是一，这个地方写的是类名books = relationship('Book', backref='author')def __repr__(self):return ''.format(self.id, self.name)class Book(Base):__tablename__ = 'book'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(20), nullable=False)# 外键，表名.字段名author_id = Column(Integer, ForeignKey('author.id'))# 关联，默认为一对多，有外键的是多，被引的是一，这个地方写的是类名# author = relationship("Author", backref="books")def __repr__(self):return ''.format(self.id, self.name, self.author_id)session = sessionmaker(engine)()
Base.metadata.create_all(engine)

3.3多对多

4.常用方法操作数据

4.1排序

4.2limit,offset和切片操作

4.3懒加载

4.4group_by

4.5having

4.6join

4.7subquery

3.ForeignKey和relationship