Python学习打call第四十八天：SQLALchemy操作MySQL关系型数据库

1.SQLALchemy使用

安装

pip install sqlalchemy；

SQLAlchemy是Python编程语言下的一款开源软件，是PythonSQL工具包和对象关系映射器，它为应用程序开发人员提供了SQL的全部功能和灵活性

2.什么是ORM

ORM（Object Relational Mapper）就是把数据库表的行与相应的对象建立关联，互相转换；
目前，最知名的Python ORM是SQLAlchemy和SQLobject；

3.数据库操作

创建表使用create_all()方法，删除表使用drop_all()方法，我们一起来看一下如何创建和删除表

# 导入引擎模块
from sqlalchemy import create_engine
# 导入基类模块
from sqlalchemy.ext.declarative import declarative_base
# 导入字段类
from sqlalchemy import Column, Integer, String
# 导入会话模块
from sqlalchemy.orm import sessionmaker

# 实体类的基类
Base = declarative_base()

# 实体类
class Teacher(Base):
    '''
    实体类的创建有两个方面的用处：
    1：如果数据库中没有表，那么可以使用实体类创建
    2：如果数据库中有表，实体类可以映射表的结构，对表的CRUD操作
    '''
    # 表名必须指定
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255))
    age = Column(Integer)
    # 打印实例的使用可以发现，数据也是保存在实体类实例的__dict__中
    def __repr__(self):
        # print(self.__dict__)
        return "id='%s', name='%s', age='%s" % (self.id, self.name, self.age)
    __str__ = __repr__

# 创建连接引擎
host = 'localhost'
port = 3306
username = 'zengzeng'
password = '123456'
db = 'XKD_Python_Course'
connect_str = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(username, password, host, port,db)
engine = create_engine(connect_str, echo=True)

# 创建表
Base.metadata.create_all(engine)

# 删除表
# Base.metadata.drop_all(engine)

我们现在命令工具中查看一下数据库中有没有我们想要的teacher表，上一篇文章中讲过，先登录mysql：mysql -uzengzeng -p123456，然后进入我们要使用的数据库：use XKD_Python_Course，我们先通过：show tables;查看一下数据库中的表，发现没有teacher表
那现在就可以执行代码，创建数据库了，创建好后我们可以在命令行查看一下：show tables;，发现teacher表已经存在了，创建表成功yes!

查看表

想要删除刚刚创建的表，可以使用drop_all()方法，执行代码，再查看数据库表，就可以看teacher表已经被删除了

Base.metadata.drop_all(engine)

插入数据使用session.add_all()方法；

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255))
    age = Column(Integer)
    def __repr__(self):
        # print(self.__dict__)
        return "id='%s', name='%s', age='%s" % (self.id, self.name, self.age)
    __str__ = __repr__

host = 'localhost'
port = 3306
username = 'zengzeng'
password = '123456'
db = 'XKD_Python_Course'
connect_str = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(username, password, host, port,db)
engine = create_engine(connect_str, echo=True)

# 创建会话，用于提交数据
Session = sessionmaker(bind=engine)
session = Session()

# 创建多行行实例，给表添加数据
try:
    lst = []
    for i in range(10):
        teacher = Teacher()
        teacher.name = 'zengzeng' + str(i)
        teacher.age = 20 + i
        lst.append(teacher)
        print(teacher)

    # session.add(student) 可以添加一行记录，也可以添加多行记录
    # 注意：这里将行记录实例添加到session，不会提交，需要手动提交
    session.add_all(lst)

except Exception as e:
    print('~~~~~~~~~~~'*200)
    session.rollback()
    print(e)
finally:
    session.commit()

我们执行代码，然后去命令工具查看一下表是否插入数据：select * from teacher;

插入数据

查询操作使用session.query()方法，迭代查询；

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255))
    age = Column(Integer)
    def __repr__(self):
        # print(self.__dict__)
        return "id='%s', name='%s', age='%s" % (self.id, self.name, self.age)
    __str__ = __repr__

host = 'localhost'
port = 3306
username = 'zengzeng'
password = '123456'
db = 'XKD_Python_Course'
conn_str = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(username, password, host, port,db)
engine = create_engine(conn_str, echo=False)

Session = sessionmaker(bind=engine)
session = Session()

teacher_obj = session.query(Teacher)
for teacher in  teacher_obj:
    print(teacher) # 返回结果：{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1063125f8>, 'age': 20, 'name': 'nihao0', 'id': 1}
print('*'*300)

# 直接返回实例对象
teacher = session.query(Teacher).get(4)
print(teacher) # 返回结果：{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1075fd400>, 'age': 23, 'name': 'nihao3', 'id': 4}
print(teacher.id)  # 返回 aobama
print(teacher.name)
print(teacher.age)
print('*'*300)

# 返回的是可迭代对象
teacher_results = session.query(Teacher).filter(Teacher.id == 5)
for teacher in teacher_results:  # 拿到student实例对象
    print(teacher)

修改操作：session.update()；

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255))
    age = Column(Integer)
    def __repr__(self):
        # print(self.__dict__)
        return "id='%s', name='%s', age='%s" % (self.id, self.name, self.age)
    __str__ = __repr__

# 创建连接引擎
host = 'localhost'
port = 3306
username = 'zengzeng'
password = '123456'
db = 'XKD_Python_Course'
conn_str = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(username, password, host, port,db)
engine = create_engine(conn_str, echo=False)

# 创建会话，用于提交数据
Session = sessionmaker(bind=engine)
session = Session()

teacher = session.query(Teacher).get(4)
teacher.name = 'Robby'
teacher.age = '99'
session.commit()

where条件查询

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE, Enum, ForeignKey
import enum
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class My_Enum(enum.Enum):
    M = 'M'
    F = 'F'


# 实体类
class Emploee(Base):
    '''
    +------------+---------------+------+-----+---------+-------+
    | Field      | Type          | Null | Key | Default | Extra |
    +------------+---------------+------+-----+---------+-------+
    | emp_no     | int(11)       | NO   | PRI | NULL    |       |
    | birth_date | date          | NO   |     | NULL    |       |
    | first_name | varchar(14)   | NO   |     | NULL    |       |
    | last_name  | varchar(16)   | NO   |     | NULL    |       |
    | gender     | enum('M','F') | NO   |     | NULL    |       |
    | hire_date  | date          | NO   |     | NULL    |       |
    +------------+---------------+------+-----+---------+-------+
    '''
    __tablename__ = 'employees'
    emp_no = Column(Integer, primary_key=True, nullable=False)
    birth_date = Column(DATE, nullable=False)
    first_name = Column(String(14), nullable=False)
    last_name = Column(String(16),nullable=False)
    gender = Column(Enum(My_Enum), nullable=False)
    hire_date = Column(DATE, nullable=False)
    def __repr__(self):
        return "emp_no='%s', birth_date='%s', first_name='%s', last_name='%s', gender='%s', hire_date='%s'" % (self.emp_no, self.birth_date, self.first_name, self.last_name, self.gender, self.hire_date)
    __str__ = __repr__

# 创建连接引擎
host = 'localhost'
port = 3306
username = 'zengzeng'
password = '123456'
db = 'XKD_Python_Course'
conn_str = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(username, password, host, port,db)
engine = create_engine(conn_str, echo=False)

# 创建表
Base.metadata.create_all(engine)

# 创建会话，用于提交数据
Session = sessionmaker(bind=engine)
session = Session()


# # 简单的where条件查询过滤， 返回可迭代对象, AND 取与
emploees = session.query(Emploee).filter(Emploee.emp_no < 10010).filter(Emploee.gender == 'M')
emploees = session.query(Emploee).filter((Emploee.emp_no > 10010) & (Emploee.gender == 'F'))

# OR 取并
emploees = session.query(Emploee).filter((Emploee.emp_no > 10010) | (Emploee.gender == 'F'))

# NOT 取反
emploees = session.query(Emploee).filter(~(Emploee.emp_no > 10010))

# in
emploees = session.query(Emploee).filter(Emploee.emp_no.in_([10010, 10011, 10012]))

# not in
emploees = session.query(Emploee).filter(~Emploee.emp_no.in_([10010, 10011, 10012]))

# like ，like可以忽略大小写进行模式匹配
emploees = session.query(Emploee).filter(Emploee.last_name.like('B%'))

for emploee in emploees:
    print(emploee)

order排序

asc()：升序；

例如：
emploees = session.query(Emploee).filter(Emploee.last_name.like('B%')).order_by(Emploee.emp_no.asc())

desc()：降序；

例如：
emploees=session.query(Emploee).filter(Emploee.last_name.like('B%')).order_by(Emploee.emp_no.desc())

多字段排序：在语句里面进行多次排序；

聚合与分组

list()：转化为列表；
count()：聚合count(*)查询；
all()：转化为列表；
limit().one()：查询首行；

emploees = session.query(Emploee)
print(list(emploees))         # 转化为列表
print(emploees.count())     # 聚合count(*)查询
print(emploees.all())        # 转化为列表
print(emploees.limit(1).one())    # 查询首行

max()：返回最大值；
min()：返回最小值；
avg()：返回平均值；

emploees = session.query(func.max(Emploee.emp_no))
emploees = session.query(func.min(Emploee.emp_no))
emploees = session.query(func.avg(Emploee.emp_no))

group_by()：分组查询；

emploees = session.query(func.count(Emploee.emp_no)).group_by(Emploee.gender)
print(emploees)
for emploee in emploees:
    print(emploee)

参考：https://www.9xkd.com/user/plan-view.html?id=2415909403

最后编辑于：2019.04.18 14:09:59

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,100评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,308评论 3赞 388
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 159,718评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,275评论 1赞 287
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,376评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,454评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,464评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,248评论 0赞 269
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,686评论 1赞 306
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,974评论 2赞 328
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,150评论 1赞 342
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,817评论 4赞 337
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,484评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,140评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,374评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,012评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,041评论 2赞 351