16 数据库 pymysql SQLAlchemy

主键 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_no	salary	from_date	to_date	NULL	emp_no	birth_date	first_name	last_name	gender	hire_date
10001	60117	1986-06-26	1987-06-26		10001	1953-09-02	Georgi	Facello	M	1986-06-26
10001	60117	1986-06-26	1987-06-26		10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10001	60117	1986-06-26	1987-06-26		10003	1959-12-03	Parto	Bamford	M	1986-08-28
10001	60117	1986-06-26	1987-06-26		10004	1954-05-01	Chirstian	Koblick	M	1986-12-01
10001	60117	1986-06-26	1987-06-26		10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12
10001	60117	1986-06-26	1987-06-26		10006	1953-04-20	Anneke	Preusig	F	1989-06-02
10001	60117	1986-06-26	1987-06-26		10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10001	60117	1986-06-26	1987-06-26		10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15
10001	60117	1986-06-26	1987-06-26		10009	1952-04-19	Sumant	Peac	F	1985-02-18
10001	60117	1986-06-26	1987-06-26		10010	1963-06-01	Duangkaew	Piveteau	F	1989-08-24
10001	60117	1986-06-26	1987-06-26		10011	1953-11-07	Mary	Sluis	F	1990-01-22
10001	60117	1986-06-26	1987-06-26		10012	1960-10-04	Patricio	Bridgland	M	1992-12-18
10001	60117	1986-06-26	1987-06-26		10013	1963-06-07	Eberhardt	Terkki	M	1985-10-20
10001	60117	1986-06-26	1987-06-26		10014	1956-02-12	Berni	Genin	M	1987-03-11
10001	60117	1986-06-26	1987-06-26		10015	1959-08-19	Guoxiang	Nooteboom	M	1987-07-02
10001	60117	1986-06-26	1987-06-26		10016	1961-05-02	Kazuhito	Cappelletti	M	1995-01-27
10001	60117	1986-06-26	1987-06-26		10017	1958-07-06	Cristinel	Bouloucos	F	1993-08-03
10001	60117	1986-06-26	1987-06-26		10018	1954-06-19	Kazuhide	Peha	F	1987-04-03
10001	60117	1986-06-26	1987-06-26		10019	1953-01-23	Lillian	Haddadi	M	1999-04-30
10001	60117	1986-06-26	1987-06-26		10020	1952-12-24	Mayuko	Warwick	M	1991-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)