SQLAlchemy连接MySQL及记录的查询、更新、删除、多表关联查询

已于 2024-04-02 10:31:48 修改
阅读量3.7k 收藏 11
点赞数 4
分类专栏： python 文章标签： mysql 数据库 python
于 2022-12-24 20:22:34 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lilongsy/article/details/128430737
版权

SQLAlchemy是Python的ORM库，支持多种数据库。

建立连接

连接MySQL要用到Engine，Engine集成了连接池pool和方言Dialect（支持不通数据库的SQL语法），最后都统一成标准DBAPI。

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://user:pass@localhost/db?charset=utf8mb4',
                       echo=True, pool_recycle=7200, pool_size=5, max_overflow=10, pool_timeout=30)

create_engine第一个参数是database url，用到了pymysql驱动。
参数echo用于打印执行中的关键日志，包括SQL执行语句。
pool_recycle表示connection空闲7200秒就会重新获取。
pool_size指定连接池大小。
max_overflow指定超过连接池大小的最大数。
pool_timeout获取连接的超时阈值。
poolclass设置为NullPool，禁用连接池，session.Close()之后会立即断开数据库连接。

报错：Lost connection to MySQL server during query

2013, 'Lost connection to MySQL server during query 
([Errno 104] Connection reset by peer)

设置pool_pre_ping=True和pool_recycle可以每次执行都查看连接是否可用。
pool_recycle的值 应该比mysql中设置的 interactive_timeout 和 wait_timeout 值小才有效。
pool_timeout的值(默认10s) 应该比 mysql的 connect_timeout值 小或者等于。

SHOW GLOBAL VARIABLES LIKE '%timeout%';

@等特殊字符的处理

用urllib.parse.quote_plus编码@特殊字符。

from urllib import parse
from sqlalchemy import create_engine
 
user = "root"
password = "******@108"
host = "127.0.0.1"
db = "db_name"
 
pwd = parse.quote_plus(password)
 
engine = create_engine(f"mysql+pymysql://{user}:{pwd}@{host}:3306/{db}?charset=utf8")

建立映射

sqlalchemy把表table映射成model class，把字段field映射成column，把记录record映射成实例instance。

手动创建映射

from sqlalchemy.sql.schema import Column
from sqlalchemy.types import BigInteger, String, Integer

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(BigInteger, primary_key=True)
    name = Column(String(255))
    age = Column(Integer)
    gender = Column(String(255))

另外可以使用sqlacodegen（pip install sqlacodegen）自动生成model：

sqlacodegen mysql+pymysql://user:password@localhost/test --outfile=models.py --tables users

# coding: utf-8
from sqlalchemy import Column, String
from sqlalchemy.dialects.mysql import BIGINT, INTEGER
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class User(Base):
    __tablename__ = 'users'

    id = Column(BIGINT(20), primary_key=True)
    name = Column(String(255, 'utf8_unicode_ci'))
    age = Column(INTEGER(11))
    gender = Column(String(255, 'utf8_unicode_ci'))

参考类型：

类型介绍
Integer/BigInteger/SmallInteger整型
Boolean布尔类型. Python 中表现为 True/False , 数据库根据支持情况, 表现为 BOOLEAN 或 SMALLINT . 实例化时可以指定是否创建约束(默认创建).
Date/DateTime/Time (timezone=False)日期类型, Time 和 DateTime 实例化时可以指定是否带时区信息
Interval时间偏差类型. 在 Python 中表现为 datetime.timedelta() , 数据库不支持此类型则存为日期
Enum (*enums, **kw)枚举类型, 根据数据库支持情况, SQLAlchemy 会使用原生支持或者使用 VARCHAR 类型附加约束的方式实现. 原生支持中涉及新类型创建, 细节在实例化时控制
Float浮点小数
Numeric (precision=None, scale=None, decimal_return_scale=None, …)定点小数, Python 中表现为 Decimal
LargeBinary (length=None)字节数据. 根据数据库实现, 在实例化时可能需要指定大小
PickleTypePython 对象的序列化类型
String (length=None, collation=None, …)字符串类型, Python 中表现为 Unicode , 数据库表现为 VARCHAR , 通常都需要指定长度
Unicode类似与字符串类型, 在某些数据库实现下, 会明确表示支持非 ASCII 字符. 同时输入输出也强制是 Unicode 类型
Text长文本类型, Python 表现为 Unicode , 数据库表现为 TEXT
UnicodeText参考 Unicode

自动创建映射

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import Table

Base = declarative_base()
metadata = Base.metadata
metadata.bind = engine


# class User(Base):
#     __table__ = Table('users', metadata, autoload=True)
class User(Base):
    __table__ = Table('users', metadata, autoload_with=engine)

设置autoload或autoload_with就可以自动生成映射，另外Base.metadata需要提前绑定engine。

查询

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()
query = session.query(User).filter(User.age >= 19).filter(User.name.like("王%"))
for user in query.all():
    print(user.age)

查询用到了Session对象，类似一个容器，容器里是identity map 的结构对象。
用filter过滤数据。

等值过滤器（==）

session.query(Account).filter(Account.user_name=='Mark') #判断字符串类型
session.query(Account).filter(Account.salary==2000) #判断数值类型

不等过滤器（!=、<、>、<=、>=）

session.query(Account).filter(Account.user_name !="mark" ) #不等于字符串类型
session.query(Account).filter(Account.salary !=2000) #不等于数值类型
session.query(Account).filter(Account.salary >2000) #大于过滤器
session.query(Account).filter(Account.salary <2000) #小于过滤器
session.query(Account).filter(Account.salary <=2000) #小于等于过滤器
session.query(Account).filter(Account.salary >=2000) #大于等于过滤器

模糊查询（like）

#查询所有名字包含字母i的用户，结果包括id为1、2、3、4的4条记录
session.query(Account).filter(Account.user_name.like('%i%'))

#查询所有title中以Manager结尾的用户，结果包括id为1、5的两条记录
session.query(Account).filter(Account.title.like('%Manager'))

#查询所有名字中以Da开头的用户，结果包括id为1、3的两条记录
session.query(Account).filter(Account.user_name.like('Da%'))

包括过滤器（in_）

#查询id不为1，3，5的记录，结果包含id为2，4的两条记录
session.query(Account).filter(~Account.id.in_([1,3,5]))
#查询工资不为2000、3000、4000的记录，结果包含id为5的1条记录
session.query(Account).filter(~Account.id.in_([2000,3000,4000]))
#查询所有title不为Engineer和Accountant记录，结果包括id为1、5的两条记录
session.query(Account).filter(~Account.title.in_(['Accountant','Engineer']))

判断是否为空（is NULL、is not NULL）

#查询salary为空值的记录，结果包含id为5的记录
#下面两方式效果相同
session.query(Account).filter(Account.salary==None)
session.query(Account).filter(Account,salary.is_(None))

#查询salary不为空值的记录，结果包含id为1、2、3、4的记录
#下面两方式效果相同
session.query(Account).filter(Account.salary!=None)
session.query(Account).filter(Account.salary.isnot(None))

非逻辑（~）

#查询id不为1、3、5的记录，结果包含id为2、4的两条记录
session.query(Account).filter(~Account.id.in_([1,3,5]))

#查询工资不为2000、3000、4000的记录，结果包含id为5的1条记录
session.query(Account).filter(~Account.id.in_([2000,3000,4000]))

#查询所有title不为Engineer和Accountant的记录，结果包括id为1、5的2条记录。
session.query(Account).filter(~Account.title.in(['Accountant','Engineer']))

与逻辑（and_）

#直接在filter中添加多个条件即表示与逻辑
session.query(Account).filter(Account.title=='Engineer',Account.salary=3000)

#用关机子and_进行逻辑查询
from sqlalchemy import and_
session.query(Account).filter(and_(Account.title=='Engineer',Account.salary=3000))

#通过多个filter的链接表示与逻辑
session.query(Account).filter(Account.title=='Engineer').filter(Account.salary=3000)

或逻辑（or_）

#引入或逻辑关键字or_
from sqlalchemy import or_

#查询title是Engineer或者salary为3000的记录，返回结果为id为1、2、3、4的记录
session.query(Account).filter(or_(Account.title=='Engineer',Account.salary=3000))

新增

user1 = User()
user1.name = "赵六"
user1.age = 21
user1.gender = "女"
session.add(user1)
session.commit()

先建立一个User实例，然后调用session的add方法，最后commit。

批量插入新增

bulk_save_objects方法

session = Session()
objects = [
    User(name="u1"),
    User(name="u2"),
    User(name="u3")]
session.bulk_save_objects(objects)

add_all方法

session = Session()
objects = [User(name="u1"), User(name="u2"), User(name="u3")]
session.add_all(objects)
session.commit()

更新

user2 = session.query(User).filter_by(name='赵六').first()
user2.gender = "男"
session.commit()

先查找到User实例，然后修改实例属性，最后调用session的commit修改数据库。

删除

user3 = session.query(User).filter_by(name='赵六').first()
session.delete(user3)
session.commit()

多表查询

Session = sessionmaker(bind=engine)
session = Session()
query = session.query(User, Address).join(Address, User.id == Address.user_id)
for user in query.all():
    print(user[0].name)
    print(user[1].add)

这里用了join方法。

参考

http://sunnyingit.github.io/book/section_python/SQLalchemy-session.html

小龙在山东
关注
  • 4
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
python使用SQLAlchemy操作MySQL
12-23
SQLAlchemy是Python编程语言下的一款开源软件，提供了SQL工具包及对象关系映射（ORM）工具，使用MIT许可证发行。SQLAlchemy首次发行于2006年2月，并迅速地在Python社区中最广泛使用的ORM工具之一，不亚于Django的ORM框架。 本文将介绍如何使用SQLAlchemy操作MySQL，完成基础的表创建，表格数据的新增、查询、修改、删除（CRUD）等操作。 首先我们需要确认当前的Python环境下已经安装sqlalchemy和pymysql模块。 新建表格 我们使用本地的MySQL数据库，数据库为orm_test。新建一张users表，字段为id,name,
基于sqlalchemy对mysql实现增删改查操作
01-19
需求场景： 老大让我利用爬虫爬取的数据写到或更新到mysql数据库中，百度了两种方法 1 是使用pymysql连接mysql,通过操作原生的sql语句进行增删改查数据； 2 是使用sqlalchemy连接mysql,通过ORM模型建表并操作数据库，不需要写原生的sql语句，相对简单些； 以下就是本次使用sqlalchemy的经验之谈。 实现流程：连接数据库》通过模型类创建表》建立会话》执行创建表语句》通过会话进行增删改查 from sqlalchemy import exists, Column, Integer, String, ForeignKey, exists from sqlal
如何使用SQLAlchemy连接数据库
最新发布
qq_42580553的博客
03-26 274
SQLAlchemy是Python语音下最著名的ORM框架。该框架简历在数据库API之上，使用关系对象映射进行数据库操作。SQLAlchemy本身不能操作数据库，需要依赖pymysql等第三方插件，从而实现对数据库的操作。操作前请先确认已完成数据库的安装（本文以mysql为例），依赖库sqlalchemy、pymysql等的安装。
sqlalchemy怎么连接MySQL_sqlalchemy如何连接mysql数据库？
weixin_36459720的博客
02-06 1520
sqlalchemy是python知名的数据库管理框架，sqlalchemy独立于Flask而存在的，这是它与Flask-sqlalchemy不同之处。更多关于sqlalchemy方面的介绍，大家可以查阅sqlalchemy相关文档。在使用sqlalchemy 之前、确保你电脑安装了以下几款软件、包：1、mysql数据库(个人比较推荐Navicat Premium)2、pymysql包(pytho...
sqlalchemy 使用mysql_使用SQLALchemy连接MySql数据库并进行操作
weixin_29877175的博客
01-30 699
“导语：这期主要学一下利用SQLALchemy连接MySql数据库以及对数据库的增删改查的操作，可能代码部分很多还不能理解，我觉得像一些固定写法先用着(比如引入的类等)，再慢慢体会缘由”01—连接数据库并进行操作[ORM概念]是Object-Relational Mapping即对象关系映射，就是将关系型数据库和对象之间做一个映射，这样就可以不用使用SQL语句，直接用python语句就可以处理数据...
SQLAlchemy连接MySQL数据库的方法
To_the_East的博客
12-29 1678
代码如下： 在create_engine中输入的是一个连接字符串。在使用python的SQLAlchemy时，连接字符串的格式如下： 数据库产品名+连接工具名：//用户名：密码@数据库IP地址：数据库端口号/数据库名称？charset=数据库数据编码 但是对于新手或者刚刚安装anaconda的使用者来说，在连接过程中经常会出现问题，比如： 这就是本人在第一次使用的时候遇到的问...
sqlalchemy 连接使用 MySQL
木下瞳的博客
04-05 8297
连接数据库 from sqlalchemy import create_engine # 连接 connwin = create_engine('mysql+pymysql://root:123456@localhost:3306/db?charset=utf8') 插入数据库 result 为 DataFrame 类型的数据，可通过 to_sql 方法直接插入数据库，不用写 insert 语句，前提是数据库中已经建立好了表，在插入种可能会遇见编码问题，主键重复chauffeur等，具体..
SQLAlchemy如何连接mysql数据库
qq_25647857的博客
01-06 669
SQLAlchemy如何连接mysql数据库
sqlalchemy 连接mysql数据库
Liubingzhe
02-07 1218
Sqlalchemy连接mysql数据库
sqlalchemy连接mysql数据库_史上超详细的flask_sqlalchemy连接mysql数据库
weixin_42310347的博客
02-11 579
Python+Flask安装：https://www.jianshu.com/p/cd1925e90edaFlask路径参数以及请求参数讲解：https://www.jianshu.com/p/54057b4f0437首先安装pymysql，命令如下：pip install pymysql安装过程中看到成功就可以。可以看到我的pymysql版本为0.9.3pymysql是安装flask_sqlal...
Sqlalchemy join连表查询
热门推荐
weixin_40123451的博客
05-25 1万+
摘要1：https://blog.csdn.net/weixin_33804582/article/details/92471702 摘要2：https://www.cnblogs.com/juandx/p/5442752.html 摘要3：https://www.cnblogs.com/wuheng-123/p/9719812.html model存在外键做join连接 首先创建数据库，在这里一个user对应多个address，因此需要在address上增加user_id这个外键（一对多）。 from
Python的SQLalchemy模块连接与操作MySQL的基础示例
09-10
SQLalchemy是Python世界中驱动MySQL的一款高人气模块,这里我们从入门开始来看一下Python的SQLalchemy模块连接与操作MySQL的基础示例:
mysql记录根据日期字段倒序输出
09-10
主要介绍了mysql记录根据日期字段倒序输出 的相关资料,非常不错，具有参考借鉴价值，需要的朋友可以参考下
SQLAlchemy 连接 MySQL 数据库（一）
云淡风轻
05-30 8470
一、ORM介绍ORM 全称 Object Relational Mapping, 翻译过来叫对象关系映射。简单的说，ORM 将数据库中的表与面向对象语言中的类建立了一种对应关系。这样，我们要操作数据库，数据库中的表或者表中的一条记录就可以直接通过操作类或者类实例来完成。ORM 相当于把数据库也给你实例化了，在代码操作MySQL中级又加了orm这一层。ORM的优点： 隐藏了数据访问细节，使得我们的通用
sqlalchemy连接mysql_利用sqlalchemy(ORM框架)连接操作mysql_mysql
weixin_30647423的博客
01-19 327
一、什么是ORM框架object relational mapping，也就是对象映射关系程序，主要作用是用面向对象的形式转换原生sql，避免直接写死sql语言在代码当中优点：隐藏了数据访问细节，通用数据库交互，ORM的核心，使得交互更贱简单异性，适用多种数据库类型。缺点：进行sql转换，意味着映射和关联管理，会影响性能，但是现在各种ORM矿化适用lazyload，cache等有效减轻这块，效果明...
使用sqlalchemy连接mysql
No1Brother_Jian的博客
10-24 273
SQL alchemy连接MySQL分三步曲 1.在头部导入模块函数 from sqlalchemy import create_engine 2.连接数据库： 一个MySQL信息如下： username = root password = cbj123 host = 127.0.0.1 port = 3306 database = bigdata 则连接数据库时应该这样写： db1...
SQL Alchemy连接数据库
zy010101博客
05-15 3869
SQL Alchemy连接数据库 使用 SQLAlchemy 连接数据库需要创建一个 Engine 的对象。此对象充当与特定数据库的连接的中心源，为这些数据库连接提供工厂和连接池。Engine通常是一个只为特定数据库（例如MySQL）创建一次的全局对象，并且使用 URL 字符串进行配置，该字符串将描述它应如何连接到数据库主机或后端。在本文中，我们使用MySQL作为SQL Alchemy的后端数据库。Engine是通过create_engine()创建的。 创建数据库 SQL Alchemy不能直接创建库，只
SQLAlchemy基础篇（连接mysql数据库）
果汁华的博客
08-06 1044
安装mysql和sqlalchemy就不说了，googol之。 简单介绍下最基本的使用： from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker DB_CONNECT_STRING = 'mysql+mysqldb://root:123@localhost/ooxx
sqlalchemy-连接数据库
小鱼儿叮咚
11-12 174
# -*- coding: UTF-8 -*-from sqlalchemy import *import sqlalchemy.util as utilimport string, sysfrom sqlalchemy.databases import mysql mysql_engine = create_engine('mysql://root:@localhost:3306/test?c...
SQLAlchemy 连接mysql 使用连接池， 要连接多个数据库
07-24
要使用SQLAlchemy连接多个数据库并使用连接池，你可以创建多个连接池对象并为每个数据库创建一个独立的引擎。以下是一个示例代码，展示了如何使用SQLAlchemy连接池连接多个MySQL数据库： ```python from sqlalchemy import create_engine from sqlalchemy.pool import QueuePool # 创建连接池1 pool1 = QueuePool( creator=lambda: mysql.connector.connect( host='localhost', database='database1', user='username1', password='password1' ), pool_size=5 ) # 创建连接池2 pool2 = QueuePool( creator=lambda: mysql.connector.connect( host='localhost', database='database2', user='username2', password='password2' ), pool_size=5 ) # 创建SQLAlchemy引擎1 engine1 = create_engine('mysql+mysqlconnector://', creator=pool1.get) # 创建SQLAlchemy引擎2 engine2 = create_engine('mysql+mysqlconnector://', creator=pool2.get) # 将DataFrame写入数据库1的表 df.to_sql(name='table1', con=engine1, if_exists='replace', index=False) # 将DataFrame写入数据库2的表 df.to_sql(name='table2', con=engine2, if_exists='replace', index=False) ``` 在上面的代码中，你需要将`database1`、`username1`、`password1`替换为第一个数据库的实际信息，将`database2`、`username2`、`password2`替换为第二个数据库的实际信息。 通过创建多个连接池对象和引擎对象，你可以连接多个MySQL数据库，并使用连接池管理数据库连接。 希望这可以满足你的需求！如果还有其他问题，请随时提问。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小龙在山东

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值