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()
