16 数据库 pymysql SQLAlchemy

SQL与数据库操作
本文介绍SQL语言的基础概念,包括主键、视图、外键等,并详细解释了SQL语句的分类及其应用,如聚合函数、子查询、连接操作等。此外还介绍了事务的ACID特性、游标的应用及Python通过pymysql库与数据库交互的方法。
主键 PRIMARY KEY
  • 表中一列或多列组成的唯一key
  • 主键往往为长整型 且非空自增
视图
  • 由查询语句生成的虚表,依然可以进行CRUD操作
  • 将复杂SQL语句定义为视图,可简化操作
  • 视图可以只显示真实表的部分列或计算后结果,可实现数据隐藏

外键

  • 子表加外键引用主表
  • 一般与主键配合使用

SQL语句

  • 全称Structured Query Language 结构化查询语言
  • 所有主流关系型数据库以及大部分NoSQL都支持SQL
  • SQL语句分类 :
名称含义SQL语句
DDL数据定义语言,负责数据库定义 数据库对象定义CREATE ALTER DROP
DML数据操作语言,负责数据库对象的操作CRUD(增删改查)
DCL数据控制语言,负责数据库访问权限控制GRANT REVOKE
TCL事务控制语言,负责处理ACID事务COMMIT ROLLBACK

- DCL GRANT授权 REVOKE撤销
- *为通配符,代表任意库或任意表
- %为通配符,代表任意host或ip地址段

GRANT ALL 
ON database.* 
TO 'login_name'@'192.168.%'
IDENTIFIED BY 'password';
REVOKE ALL ON *.* FROM login_name
  • DML
  • 聚合函数
聚合函数含义
COUNT(expr)返回记录数目,若指定列,则返回非NULL值的行数
COUNT(DISTINCT expr)返回不重复的非NULL记录数目
SUM() AVG()求和 平均,可使用DISTINCT
MAX() MIN()最大 最小
-- 指定范围 并按要求分类
SELECT * FROM employees 
WHERE emp_no BETWEEN 10010 AND 10020 
ORDER BY birth_date DESC;

-- 去重显示
SELECT DISTINCT dept_no FROM dept_emp;

-- 分组 使用聚合函数
SELECT emp_no, SUM(salary), AVG(salary) as avg_sal, COUNT(emp_no) 
from salaries 
GROUP BY emp_no 
HAVING avg_sal > 70000;

-- 子查询
SELECT * FROM employees 
WHERE emp_no IN (SELECT emp_no FROM employees WHERE emp_no > 10015)
ORDER BY emp_no DESC;

SELECT emp.emp_no,emp.first_name,gender 
FROM (SELECT * FROM employees WHERE emp_no > 10015) AS emp 
WHERE emp.emp_no < 10019 
ORDER BY emp_no DESC;
(CROSS/INNER)JOIN 内(交叉)连接
select * from salaries cross join employees;
-- select * from a cross join b;
-- 设a表有a行,b表有b行
-- a cross join b有a * b行
-- 一共分为a部分 每一部分有b行,左侧b行内容全相同,右侧为b表的b行完整内容
-- 第一部分内容如下,左侧为a表的第一行
emp_nosalaryfrom_dateto_dateNULLemp_nobirth_datefirst_namelast_namegenderhire_date
10001601171986-06-261987-06-26100011953-09-02GeorgiFacelloM1986-06-26
10001601171986-06-261987-06-26100021964-06-02BezalelSimmelF1985-11-21
10001601171986-06-261987-06-26100031959-12-03PartoBamfordM1986-08-28
10001601171986-06-261987-06-26100041954-05-01ChirstianKoblickM1986-12-01
10001601171986-06-261987-06-26100051955-01-21KyoichiMaliniakM1989-09-12
10001601171986-06-261987-06-26100061953-04-20AnnekePreusigF1989-06-02
10001601171986-06-261987-06-26100071957-05-23TzvetanZielinskiF1989-02-10
10001601171986-06-261987-06-26100081958-02-19SaniyaKalloufiM1994-09-15
10001601171986-06-261987-06-26100091952-04-19SumantPeacF1985-02-18
10001601171986-06-261987-06-26100101963-06-01DuangkaewPiveteauF1989-08-24
10001601171986-06-261987-06-26100111953-11-07MarySluisF1990-01-22
10001601171986-06-261987-06-26100121960-10-04PatricioBridglandM1992-12-18
10001601171986-06-261987-06-26100131963-06-07EberhardtTerkkiM1985-10-20
10001601171986-06-261987-06-26100141956-02-12BerniGeninM1987-03-11
10001601171986-06-261987-06-26100151959-08-19GuoxiangNooteboomM1987-07-02
10001601171986-06-261987-06-26100161961-05-02KazuhitoCappellettiM1995-01-27
10001601171986-06-261987-06-26100171958-07-06CristinelBouloucosF1993-08-03
10001601171986-06-261987-06-26100181954-06-19KazuhidePehaF1987-04-03
10001601171986-06-261987-06-26100191953-01-23LillianHaddadiM1999-04-30
10001601171986-06-261987-06-26100201952-12-24MayukoWarwickM1991-01-26
事务 Transaction
  • 事务,简而言之就是一系列操作
  • 关系型数据库中支持事务,必须具备ACID四大属性
属性描述
atomicity 原子性事务的一系列操作不可分割,若完成一部分突然中断,之前的操作需要回滚
contistency 一致性一项事务完成后,数据库必须从一个一致性状态变至另一个一致性状态
isolation 隔离性并发执行的各事务间不能相互干扰
durability 持久性事务一旦提交,对数据库的影响就是永久性的.即数据不可丢失
游标 Cursor
  • 操作查询结果集的一种方法
  • 可将游标看做指针,指向结果集的某一行

pymysql

  • Python连接MySQL的库
  • MySQLdb : 最有名的库,但不支持Python3
  • pymysql : 语法兼容MySQL,支持Python3
  • pymysql安装 : pip install pymysql
  • 使用 :
import pymysql
from pymysql.cursors import DictCursor

conn=pymysql.connect('127.0.0.1','root','passwd','test')
cursor=conn.cursor(DictCursor)
try:
    SQL='SELECT * FROM tj1 where id=%(name)s'
    row=cursor.execute(SQL,{'name':11})
    print(cursor.fetchall())
    conn.commit()
except:
    conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()
#---------------------------------------------------
import pymysql
conn=pymysql.connect('127.0.0.1','root','passwd','test')
try:
    with conn.cursor() as cursor:
        for i in range(3):
            SQL="INSERT INTO tj1 (val) VALUES (%s)"
            rows=cursor.execute(SQL,i)
    print(cursor.fetchall())
    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()
finally:
    conn.close()
#---------------------------------------------------------
import pymysql
conn=pymysql.connect('127.0.0.1','root','passwd','test')
with conn as cursor:
    with cursor:
        for i in range(3):
            SQL="INSERT INTO tj1 (val) VALUES (%s)"
            rows=cursor.execute(SQL,i)
        print(cursor.fetchall())  
conn.close()

ORM

  • 对象关系映射(Object Relational Mapping,简称ORM)
  • 为了解决面向对象与关系数据库存在的互不匹配的现象的技术
  • ORM是通过使用描述对象和数据库之间映射的元数据,将程序中的对象自动持久化到关系数据库中
  • 一个持久化类和一个表对应,类的每个实例对应表中的一条记录,类的每个属性对应表的每个字段。
  • ORM对应关系表 :
数据库面向对象
数据表
字段类属性
实例一条记录

SQLAlchemy

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


print(1,sqlalchemy.__version__)
conn_url='mysql+pymysql://root:passwd@127.0.0.1:3306/test'
engine=create_engine(conn_url,echo=True) #
session=sessionmaker(bind=engine)()

Base=declarative_base()
class Tj1(Base):
    __tablename__ = 'tj1'
    id=Column(Integer,primary_key=True)
    val=Column(Integer)

    def __repr__(self):
        return "{} id={} val={}".format(self.__class__.__tablename__,self.id,self.val)
t=Tj1(val=79)
print(2,t)
session.add(t)
#-------------------------------------------------
res=session.query(Tj1).get(65)
print(3,res)
session.delete(res)
#-------------------------------------------------
t=session.query(Tj1).filter(Tj1.id > 63).order_by(Tj1.id)
print(4,t.first())
##-------------------------------------------------
t=session.query(func.sum(Tj1.id)).group_by()
print(5,t.scalar())
#-------------------------------------------------
try:
    session.commit()

except:
    session.rollback()
    raise
print(6,res)
### 结合使用 PyMySQLSQLAlchemy 进行数据库操作 #### 使用 PyMySQL 创建连接并执行查询 PyMySQL 是一个用于 Python 的 MySQL 客户端库,可以直接用来建立到 MySQL 数据库的连接,并执行 SQL 查询。 ```python import pymysql connection = pymysql.connect( host='localhost', user='root', password='password', database='test_db' ) try: with connection.cursor() as cursor: sql = 'SELECT version();' cursor.execute(sql) result = cursor.fetchone() print(f'Database version: {result}') finally: connection.close() ``` 这段代码展示了如何利用 PyMySQL 建立 MySQL 数据库之间的连接以及怎样发送一条简单的 SELECT 语句来获取当前使用的数据库版本[^1]。 #### 利用 SQLAlchemy 构建 ORM 映射关系 SQLAlchemy 提供了一个更高层次的对象关系映射(ORM),允许开发者定义类作为表结构表示形式。下面的例子说明了如何设置 SQLAlchemy 并将其 PyMySQL 配合工作: ```python from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) engine = create_engine('mysql+pymysql://root:password@localhost/test_db') Session = sessionmaker(bind=engine) session = Session() new_user = User(name="Alice") session.add(new_user) session.commit() for instance in session.query(User).order_by(User.id): print(instance.name) ``` 此部分代码首先设置了 SQLAlchemy 中的基础组件——`create_engine()` 函数指定了要使用的驱动程序 (`pymysql`) 及其对应的 URL;接着声明了一个名为 `User` 的模型类对应于数据库中的 users 表;最后通过创建会话实例来进行增删改查等操作[^2]. #### 综合应用两者优势 当希望充分利用两者的特性时,可以先借助 PyMySQL 执行一些低级别的命令或处理特殊情况下的事务管理,而日常的数据存取则交给更易于维护和扩展的 SQLAlchemy ORM 层面去完成。例如,在某些情况下可能需要直接调用存储过程或其他复杂逻辑,则可以通过 PyMySQL 来实现这些功能的同时保持大部分业务逻辑基于 SQLAlchemy 编写[^3].
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值