python网络篇【第十二篇】MySQL、ORM框架

Mysql相关

DML:Date Manapulate Language:数据操作语言：

INSERT插入，REPLACE替换，UPDATE修改，DELETE删除

DDL：Date Defination Language:数据定义语言

CREATE创建数据库创建表,ALTER修改数据库修改表，DROP删除数据库删除表

DCL:Data Control Language:数据控制语言

GRANT设定权限，REVORE收回恢复权限

SELECT:查询

RDBMS：分类

egreSQL 第一款关系型数据库

Oracle,Sybase,Infomix, SQL Server,MySQL----->后期开源MariaDB

------>PerconeSQl mysql优化版

关于mysql的用户管理，

　　1、创建新用户

　　通过root用户登录之后创建

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" ;　　//　　创建新用户，用户名为testuser，密码为123456 ；

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，可以在本地访问mysql

　　>> grant all privileges on *.* to testuser@"%" identified by "123456" ;　　　//　　设置用户testuser，可以在远程访问mysql

　　>> flush privileges ;　　//　　mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效

　　2、设置用户访问数据库权限

　　>> grant all privileges on test_db.* to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，只能访问数据库test_db，其他数据库均不能访问；

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，可以访问mysql上的所有数据库；

　　>> grant all privileges on test_db.user_infor to testuser@localhost identified by "123456" ;　　//　　设置用户testuser，只能访问数据库test_db的表user_infor，数据库中的其他表均不能访问；

　　3、设置用户操作权限

　　>> grant all privileges on *.* to testuser@localhost identified by "123456" WITH GRANT OPTION ;　　//设置用户testuser，拥有所有的操作权限，也就是管理员；

　　>> grant select on *.* to testuser@localhost identified by "123456" WITH GRANT OPTION ;　　//设置用户testuser，只拥有【查询】操作权限；

　　>> grant select,insert on *.* to testuser@localhost identified by "123456" ;　　//设置用户testuser，只拥有【查询插入】操作权限；

　　>> grant select,insert,update,delete on *.* to testuser@localhost identified by "123456" ;　　//设置用户testuser，只拥有【查询插入】操作权限；

　　>> REVOKE select,insert ON what FROM testuser　　//取消用户testuser的【查询插入】操作权限；

　　4、设置用户远程访问权限

　　>> grant all privileges on *.* to testuser@“192.168.1.100” identified by "123456" ;　　//设置用户testuser，只能在客户端IP为192.168.1.100上才能远程访问mysql ；

　　5、关于root用户的访问设置

　　设置所有用户可以远程访问mysql，修改my.cnf配置文件，将bind-address = 127.0.0.1前面加“#”注释掉，这样就可以允许其他机器远程访问本机mysql了；

　　>> grant all privileges on *.* to root@"%" identified by "123456" ;　　　//　　设置用户root，可以在远程访问mysql

　　>> select host,user from user; 　　//查询mysql中所有用户权限

　　关闭root用户远程访问权限

　　>> delete from user where user="root" and host="%" ;　　//禁止root用户在远程机器上访问mysql

　　>> flush privileges ;　　//修改权限之后，刷新MySQL的系统权限相关表方可生效　　

mysql备份

mysqldump -uroot -pxxxxxx dabataname >/home/backup-mysqldb/name`date +%Y-%m-%d`.sql

pymysql

pymysql是Python中操作MySQL的模块，使用方法和MySQLDB几乎一样。

一. 安装

pip3 install pymysql   


#源码安装
https://github.com/PyMySQL/PyMySQL

python3 setup.py install

二. 使用

import pymysql

#创建连接
conn = pymysql.connect(
    host="192.168.1.129",     #主机地址
    port=3306,      #mysql端口
    user='root',    #连接用户名
    password="123456",   #密码
    db="db1",     #库名，
)
#注意：所要操作的库名和表明事先要存在
cursor=conn.cursor()     #创建游标

#执行SQL，并返回受影响的行数
# effect_row=cursor.execute("UPDATE tb1 SET name='tom'")     #把tb1表中的name 全部替换成tom

# effect_row=cursor.execute("UPDATE tb1 SET name='tom' WHERE age > %s",(18,))    #把age大于18的name改成tom

effect_row=cursor.executemany("INSERT INTO tb1 (name) VALUES(%s),(%s)",[('xi','jj'),('hi',"ll")]) #插入数据，后面列表括号内须传递两个参数

effect_row=cursor.execute("INSERT INTO tb1 (name,age) VALUES(%(id)s,%(age)s)",{'id':"tom","age":18}) #插入数据，name:tom,age:18


print(effect_row)
conn.commit()    #关闭游标
conn.close()     #关闭连接

获取查询数据：

import pymysql   #导入模块

#创建连接
conn = pymysql.connect(
    host='192.168.1.129',   #主机IP
    port=3306,              #端口
    user='root',             #连接数据库用户
    password='123456',      #连接密码
    db='db1'                #连接的数据库名称
)

coursor=conn.cursor()
coursor.execute("select * from tb1")   #查询tb1表

#获取第一行数据
row_1=coursor.fetchone()

#获取前N行数据
row_2=coursor.fetchmany(3)

#获取所有数据
row_3=coursor.fetchall()

conn.commit()
coursor.close()
conn.close()

print(row_1)   #游标的位置会变，获取第一行之后，游标指针就会指到第二行了
print(row_2)   #因此打印前三行，是从第二行开始，2、3、4
print(row_3)   #同上，当前游标到最后的位置

PS: 在fetch数据时按照顺序进行，可以使用cursor.scoll(num,mode) 来移动游标位置，如下：

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

实际用法：

#获取第一行数据
row_1 = cursor.fetchone()

#获取前N行数据
row_2 = cursor.fetchmany(3)

#获取所有数据
#先移动游标
cursor.scroll(0,mode='absolute')   #绝对位置移动
row_3 = cursor.fetchall()

print(row_1)
print(row_2)
print(row_3)

fetch数据类型

import pymysql   #导入模块

#创建连接
conn = pymysql.connect(
    host='192.168.1.129',   #主机IP
    port=3306,              #端口
    user='root',             #连接数据库用户
    password='123456',      #连接密码
    db='db1'                #连接的数据库名称
)

coursor=conn.cursor(cursor=pymysql.cursors.DictCursor)     #创建游标，并设置显示类型为字典形式
coursor.execute("select * from tb1")   #查询tb1表

#获取第一行数据
row_1=coursor.fetchone()

#获取前N行数据
row_2=coursor.fetchmany(3)

#获取所有数据
row_3=coursor.fetchall()

conn.commit()
coursor.close()
conn.close()

print(row_1)   #游标的位置会变，获取第一行之后，游标指针就会指到第二行了
print(row_2)   #因此打印前三行，是从第二行开始，2、3、4
print(row_3)   #同上，当前游标到最后的位置

SQLAlchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

SQLAlchemy本身无法操作数据库，其必须以pymysql等第三方插件。

Dialect用于和数据API交互，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如下代码：

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
  
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
  
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
  
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
  
更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123456@192.168.1.129:3306/db1",max_overflow=5)

#执行SQL
cur = engine.execute(
    "INSERT INTO tb1(name) VALUES ('192.168.1.129')"    #插入一条数据
)

print('ID,',cur.lastrowid)    #打印新插入行的自增ID

#执行SQL
cur1 = engine.execute(
    "INSERT INTO tb1(name) VALUES(%s),(%s)",[('172.16.0.1'),('172.16.0.2')]  #一下插入多条记录, 使用%s方式
)
print('ID,',cur1.lastrowid)
#执行SQL方式3
cur2 = engine.execute(
    "INSERT INTO tb1(name) VALUES(%(host)s),(%(host1)s)",host='172.16.1.1',host1='172.16.1.2'  #插入多条记录,使用占位符的形式
)

print('ID,',cur2.lastrowid)

#执行SQL
cur_select = engine.execute("SELECT * FROM tb1")

#获取第一行数据
res = cur_select.fetchone()    #也存在指针的问题
print(res)
#前三行
res1 = cur_select.fetchmany(3)
print(res1)
#获取所有
res2 = cur_select.fetchall()
print(res2)

ORM框架

使用ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect所有的组件对数据进行操作。根据类创建对象，将对象转换成SQL，而后执行SQL。

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123456@192.168.1.129:3306/db1", max_overflow=5)

Base = declarative_base()

# 创建单表
class Users(Base):
    '''
    一定要继承Base
    '''
    __tablename__ = 'users'     #users为表名，下面是表结构
    id = Column(Integer, primary_key=True)    #id队列，整数数据类型，主键
    name = Column(String(32))     #name列，字符串类型，长度32
    extra = Column(String(16))    #extra列，字符串类型，长度20

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),        #表结构插入
        Index('ix_id_name', 'name', 'extra'),
    )

# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)


class Person(Base):
    '''
    通过外键关联favor表的nid实现一对多
    '''
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))     #外键，关联fazvor表的nid
# 多对多
class ServerToGroup(Base):
    '''
    这个表存放下面两个表的对应关系，可以多对多
    '''
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))    #外键关联server表中的id
    group_id = Column(Integer, ForeignKey('group.id'))       #外键关联group表中的id

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)

Base.metadata.create_all(engine)     #创建所有表
# Base.metadata.drop_all(engine)     #删除所有表

操作表

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

engine = create_engine("mysql+pymysql://root:123456@192.168.1.129:3306/db1", max_overflow=5)

Base = declarative_base()

# 创建单表
class Users(Base):
    '''
    一定要继承Base
    '''
    __tablename__ = 'users'     #users为表名，下面是表结构
    id = Column(Integer, primary_key=True)    #id队列，整数数据类型，主键
    name = Column(String(32))     #name列，字符串类型，长度32
    extra = Column(String(16))    #extra列，字符串类型，长度20

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),        #表结构插入
        Index('ix_id_name', 'name', 'extra'),
    )

# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)


class Person(Base):
    '''
    通过外键关联favor表的nid实现一对多
    '''
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))     #外键，关联fazvor表的nid
    # #与生成表结构无关, 仅用于查询方便
    favor=relationship("Favor",backref='pers')
# 多对多
class ServerToGroup(Base):
    '''
    这个表存放下面两个表的对应关系，可以多对多
    '''
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))    #外键关联server表中的id
    group_id = Column(Integer, ForeignKey('group.id'))       #外键关联group表中的id

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)
def init_db():
    Base.metadata.create_all(engine)     #创建所有表

def drop_db():
    Base.metadata.drop_all(engine)     #删除所有表

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

表结构+连接数据库

　　增：

obj = Users(name='Tom',extra='mouse')
session.add(obj)    #添加一条记录
session.add_all([    #添加多条
    Users(name='Jerry',extra='Cat'),
    Users(name='Sam',extra='Human')
])

session.commit()      #提交记录

　　删：

session.query(Users).filter(Users.id > 2).delete()    #users表id列大于2的删除
session.commit()

　　改：

session.query(Users).filter(Users.id > 2).update({"name":"db"})#先将id>3的用户名字改为sb
session.query(Users).filter(Users.id > 2).update({Users.name:Users.name + '123'},synchronize_session = False)#而后在db后拼接一个123
session.query(Users).filter(Users.id > 2).update({"id":Users.id + 1 },synchronize_session = "evaluate")  #最后把大于2的id号+1

　　查：

res = session.query(Users).all()  #返回一个列表
print(res[0].name)    #列表里是一个表结构对象，获取第一个索引位置的name
res1 = session.query(Users.name, Users.extra).all()  #返回一个列表,中包含元组
print(res1)
res2 = session.query(Users).filter_by(name='Jerry').all()  #返回一个列表
print(res2[0].name)    #列表里是一个Jerry列对象，获取第一个的name
res3 = session.query(Users).filter_by(name='Jerry').first()
print(res3)
session.commit()

#显示结果
Tom
[('Jerry', 'Cat'), ('Tom', 'mouse')]
Jerry
<__main__.Users object at 0x000000E00E61A7B8>

　　其他：

#条件
ret = session.query(Users).filter_by(name='Jerry').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'Tom').all()  #并
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'Sam').all()   #id在1-3之间,并且名字是Sam
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()    #id在1,3,4里的
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()   #取反
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='Sam'))).all()  #子查询

from sqlalchemy import and_, or_

ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'Tom')).all()  #并
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'Tom')).all()  #或
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'Sam', Users.id > 3),
        Users.extra != ""
    )).all()     #id<2 或者  用户名是Sam并大于3的


# 通配符
ret = session.query(Users).filter(Users.name.like('J%')).all()     #J开头后续任意字符
ret = session.query(Users).filter(~Users.name.like('J%')).all()    #取反


# 限制
ret = session.query(Users)[0:5]    #显示多少个值,个人感觉有点类似于列表的切片一样

# 排序
ret = session.query(Users).order_by(Users.id.desc()).all()   #降序
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()  #升序



# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()
#结果:[(2, Decimal('2'), 2), (3, Decimal('3'), 3), (5, Decimal('5'), 5), (1, Decimal('1'), 1)]

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()   #添加条件最小id大于2


# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()   #连表查询, 条件 usersid 等于 Favor.nid
ret = session.query(Person).join(Favor).all()    #左连接, 如果要用右连接,可以把两个表的位置换下
#
ret = session.query(Person).join(Favor, isouter=True).all()


# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()


print(ret)
session.commit()

View Code