关于字符集

修改 /etc/my.cnf文件添加如下内容：

show variables like 'collation_%';

show variables like 'character_set_%';

查看数据库 db_1803 的字符集、字符序

 use mydb     
 alter database mydb_name character set utf-8;

创建数据库指定数据库的字符集

shell> pip3 install sqlalchemy

SQLAlchemy本身无法操作数据库，其必须使用 pymsql 等第三方插件，从而实现对数据库的操作，如：

pymysql 使用如下方式连接：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
 
engine = create_engine(
  "mysql+pymysql://root:123@172.16.153.160:3306/dbname?charset=utf8mb4", 
  echo=True, 
  max_overflow=5)

echo 标志是设置SQLAlchemy日志记录的快捷方式。 启用它后，我们将看到所有生成的SQL。

Max_overflow 指定了连接池的最大连接数。

create_engine() 的返回值是一个实例引擎,它代表了一个数据库的核心接口。

此时的连接是惰性的，当create_engine()第一次返回的引擎，其实并没有试图连接到数据库之中; 只有在第一次要求它对数据库执行任务时才会发生这种情况。比如使用了以下的任一方法:

ret = engine.execute("select * from t1;")
# print(dir(engine))
# print(ret.fetchone())
print(ret.fetchall())

五、ORM 的基本操作

ORM 流程

ORM 操作的流程是，一个 Engine 使用 Schema Type 创建一个特定的结构对象，之后通过 SQL Expression Language 将该对象转换成 SQL 语句， 接着使用 ConnectionPling 连接数据库，最后通过 Dialect 执行 SQL 语句，并获取结果。

1. 声明一个映射类

官方解释：

使用ORM时，配置过程首先描述我们将要处理的数据库表，然后定义我们自己的类，这些类将映射到这些表。在现代SQLAlchemy中，这两个任务通常使用称为Declarative的系统一起执行，这允许我们创建包含指令的类，以描述它们将映射到的实际数据库表。

大白话：

创建一个类，一个类对应了一个数据库中的一张表，类的数据属性对应了表中的字段名，这个类称为映射类。

根据映射类创建出一个一个的对象，每个对象对应了表中的一条实际的数据。

  千锋云计算

使用Declarative系统映射的类是根据基类定义的，换句话说每个映射类需要继承这个基类。我们使用declarative_base() 函数可以创建这个基类，如下所示：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True)
    name = Column(String(12))
    age = Column(String(2))
    city = Column(String(16))
    
    def __repr__(self):
        tpl = "Teacher(id={}, name={}, age={}, city={})"
        return tpl.format(self.id, self.name,
                          self.age, self.city)

__repr__ 方法定义了一个对象的比较易读的显式方式

使用 Declarative 的类至少需要一个__tablename__属性，并且至少有一个 Column属于主键。

2. 创建表到数据库中

我们可以使用MetaData 为所有数据库中尚不存在的表向数据库发出CREATE TABLE语句。

下面，我们调用该MetaData.create_all()方法，将我们Engine 作为数据库连接源传递。我们将看到首先发出特殊命令以检查teacher表的存在，然后是实际的语句：CREATE TABLE
`

MariaDB [(none)]> use db_1803
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [db_1803]> show tables;
+-------------------+
| Tables_in_db_1803 |
+-------------------+
| teacher           |
+-------------------+
1 row in set (0.00 sec)

MariaDB [db_1803]>

3. 创建映射类的实例

from sqlalchemy.orm import sessionmaker

# 把当前的引擎绑定给这个会话
Session = sessionmaker(bind=engine) 

# 实例化
session = Session()

5. 通过 Session 实例的方法进行操作表中的数据

刚才我们已经创建了一条数据的实例了，要想把这条数据实例真正的增加到数据库中的teacher 表中，需要先把这条数据对象添加到会话实例中，再通过会话实例的 commit() 方法提交事务到数据库，此时，数据库的 teacher 表中才会有这条数据。

添加单条数据到 session 中

our_teacher = session.query(Teacher).filter_by(
    name='yangge').first() 
print(our_teacher)

注意：此时得到的结果是并不是 数据库表中的最终数据，而是映射类的一个对象

6. 提交

session.add_all([
    Teacher(name='qiangge', age='19',city='北京'),
    Teacher(name='shark', age='19',city='北京'),
])

一起提交

session.rollback()

示例:

假如目前向表中添加一条新的数据，同时查询出一条已有的数据，并且把已有的数据某个字段的值进行更改。

session.rollback()

之后再次进行查询操作会发现已恢复到修改之前

# 创建连接相关
from sqlalchemy import create_engine

# 和 sqlapi 交互，执行转换后的 sql 语句，用于创建基类
from sqlalchemy.ext.declarative import declarative_base

# 创建表中的字段(列)
from sqlalchemy import Column

# 表中字段的属性
from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy import UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship

2. 连接数据库

# 创建基类
Base = declarative_base()

# 创建单表
class Person(Base):
    __tablename__ = 'person'  # 表名
    id = Column(Integer, primary_key=True)
    # 必须指定长度，在PostgreSQL上不需要
    name = Column(String(32))
    age = Column(Integer)      # 整型

    __table_args__ = (
    # 设置联合唯一
    UniqueConstraint('id', 'name', name='uix_id_name'),
    
    # 建立索引   
    Index('uix_id_name', 'name'),
    )


def init_db():
    """创建所有定义的表到数据库中"""
    Base.metadata.create_all(engine)


def drop_db():
    """从数据库中删除所有定义的表"""
    Base.metadata.drop_all(engine)
    
# 执行创建表
#init_db()
    
# 创建会话实例对象
Session = sessionmaker(bind=engine)
session = Session()

4. 查询数据

a. 结果集内看到的是对象 、还是数据

? query(类名) 返回的就是对象

? query(类名.字段名) 返回的就是含有数据的元组对象

for name, age, in session.query(Teacher.name, Teacher.age):
    print(name, age)
    
# 输出结果
yangge 18
qiangge 19
shark 23

c. 给列起别名

可以使用 label() 给每个列名起别名

# filter_by()
ret = session.query(Teacher).filter_by(name='yangge').first()
# 结果 Teacher(id=2, name=yangge, age=18, city=BeiJing)

# filter()
ret = session.query(Teacher).filter(Teacher.age>'20').first()
# 结果 Teacher(id=4, name=shark, age=23, city='北京')

1. 关系运算符的查询

以下适用于 filter()

以下查询都是以这个查询对象为基础的过滤

query.filter(Teacher.name == 'shark').all()

• 不相等

query.filter(Teacher.name.like('%sha%')).all()

• ILIKE

  确保忽略大小写， 大部分数据库不支持 ilike

query.filter(Teacher.id.in_([2,3])).all()

• NOT IN

  使用波浪号~ 表示非

query.filter(Teacher.id.between(1, 3)).all()

• IS NULL

  数据库中的空字符串不是 NUll ， python 中的 None 存到数据库中是 NULL。

query.filter(Teacher.name != None).all()
# 或者
query.filter(Teacher.name.isnot(None)).all()

• AND

from sqlalchemy import or_
query.filter(or_(Teacher.name == 'shark', 
                 Teacher.name == 'xiguatian')).all()

• AND 和 OR 的综合使用

ret = session.query(Teacher).all()
# 结果 [obj1,obj2,obj3]

ret = session.query(Teacher).first()
# 结果 obj1

• one

? 提取结果集中的所有数据，假如没有或者数据多于一条则会报错

? 找到后返回的是一个元组

• one_or_none

  和 one() 一样，但是没找到返回 None

• scalar

? scalar() 调用 one() 方法，找不到，返回 None

? 找到后返回的是赤裸裸的数据

• limit
• 使用 python 的切片控制输出多少行

# 正序
session.query(Teacher).order_by(Teacher.name).all()

# 倒序
session.query(Teacher).order_by(Teacher.name.desc()).all()

# 先按名字排序，假如有相同的再安装 id 排序
session.query(Teacher).order_by(Teacher.name,       
                                Teacher.id.desc()).all()

• count 统计

# 嵌套，从最内层的查询结果中再查询想要的数据
session.query(Teacher).filter(
    Teacher.id.in_(
        session.query(Teacher.id
                      ).filter_by(
            name='yangge'))).all()

g. 分组统计查询

# 再创建一个表
class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True)
    name = Column(String(12))
    age = Column(String(2))
    city = Column(String(16))

# 组合  用一条数据将两个表中的要查询的数据组合在一张表里展示出来
q1 = session.query(Teacher.name).filter(Teacher.id > 2)
q2 = session.query(Student.name).filter(Student.id < 2)
## 去重
ret = q1.union(q2).all()
## 不去重 
q1 = session.query(Teacher.name).filter(Teacher.id > 2)
q2 = session.query(Student.name).filter(Student.id < 2)
ret = q1.union_all(q2).all()

七、更新数据

session.query(Teacher).filter(Teacher.id > 4).delete()
session.commit()

九、连表操作

INNER JOIN 内连接
LEFT JOIN 左连接
RIGHT JOIN 右连接
FULL JOIN 完全连接

• 内连接 显示的是两个表的两列数据匹配的相关数据，并且返回的是组合后的数据
• 外连接 分为左外连接、右外连接和全连接，mysql中不支持全连接，所以以左外连接为例来讨论区别：
  • 左外连接是以左表为主，返回的是只在join关键字前面这张表（即左表）中，所有符合 where 子句的数据，不管是否符合连接条件。即这张表的内容都要全部显示。而后面的那张表只显示匹配连接条件的数据。
  • 右外联接则刚好和左外连接相反。

1. 一对多

示例表：

groups

id	name	full_name	cn_name
1	Other		默认组
2	PM	Product Manager	产品经理
3	RD	Research and Development engineer	开发
4	QA	Qualtiy Assurance	测试
5	OP	Operator	运维
6	DBA	Database Administrator	数据库管理员

users

id	name	goup_id
1	Yangge	2
2	Tom	2
3	Rose	3
4	Shark	3
5	Xiguatian	5
6	Jack	6
7	new_user	1

创建表的原生语句

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

engine = create_engine("mysql+pymysql://root:123456@172.16.153.160:3306/db_1802", echo=True, max_overflow=5)
Base = declarative_base()
class Groups(Base):
    __tablename__= 'groups'
    id = Column(Integer, primary_key=True)
    name = Column(String(12), 
                  unique=True,     # 值必须唯一
                  nullable=False)  # 不允许为空
    full_name = Column(String(64), nullable=True)
    cn_name = Column(String(64))

class Users(Base):
    __tablename__= 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), 
                  unique=True,
                  index=True,  # 此列建立索引
                  nullable=False)
    group_id = Column(Integer, 
                      ForeignKey('groups.id'),  # 定义外键
                      default=1)  # 默认值
    
    # 下面此列与创建表无关，仅用于查询使用，group 用于正向查询，user 用于反向查询
    group = relationship('Groups',  # 字符串类型的映射类名称。 
                         backref='user')
    
# 创建所有的表结构
Base.metadata.create_all(engine)

2. 添加数据

print(session.query(Groups).join(Users).all())

3. 查询数据

问题一: 查询用户 Shark 是属于哪个组 ,打印出组名?

group_obj = session.query(Groups).filter_by(name='RD').one()
for u_obj in group_obj.user:
    print(u_obj.name)

4. 更新数据

现在把用户 new_user 加入到 测试组 QA

# 删用户
session.query(Users).filter_by(name='jack').delete()
session.commit()

# 多对多
# 一个服务器中可以有个用户
# 同名的用户可以出现在多台服务器中，也就是多台服务器可能存在相同的用户名
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey

# 需要安装 sqlalchemy-utils 和 passlib
# pip3 install sqlalchemy-utils passlib
from sqlalchemy_utils import IPAddressType, PasswordType

from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://root:123456@172.16.153.160:3306/db_1803",
                       echo=True,
                       max_overflow=5)
Base = declarative_base()

class ServersToSysUsers(Base):
    __tablename__ = 'servers_to_sys_users'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('servers.id'))
    group_id = Column(Integer, ForeignKey('sys_users.id'))
    
    # 方式一
    #servers = relationship("Servers", backref='ser_to_user')
    #sys_users = relationship("SysUsers", backref='ser_to_user')

class Servers(Base):
    __tablename__ = 'servers'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip = Column(IPAddressType)  # 输入字符串，输出时是对象
    
    # 方式二
    sys_users = relationship('SysUsers',
                           secondary=ServersToSysUsers.__table__,
                           backref='servers')


class SysUsers(Base):
    __tablename__ = 'sys_users'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    password = Column(PasswordType(
        schemes=[
            'pbkdf2_sha512',
            'md5_crypt'
        ],
        deprecated=['md5_crypt']))
    key = Column(String(128),nullable=True)


Base.metadata.create_all(engine)

2. 添加数据

# 方式一的查询
query = session.query(Servers).filter_by(hostname='web')
if query:
    for obj in query.scalar().ser_to_user:
        print(obj.sys_users.name)

# 方式二的查询
query = session.query(Servers).filter_by(hostname='web')
if query:
    for u_obj in query.scalar().sys_users:
        print(u_obj.name, u_obj.password)

问题二： 查询用户 root 存在于哪些服务器上

pip3 install sqlalchemy-utils

更多字段的数据类型

http://sqlalchemy-utils.readthedocs.io/en/latest/data_types.html

作者：运维开发_西瓜甜
链接：https://www.jianshu.com/p/65903a69d61d
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。