Python开发【管理第十一篇】:Python操作MySQL

MySQL Fabric安装

本篇对于Python操作MySQL首要选用两种办法:

 

1、原生模块pymsql。

MySQL Fabric是Oracle提供的用于援助进行ha\sharding的工具,它的宗旨架构:

2、ORM框架SQLAchemy。

 管理 1

pymsql

pymsql是Python中操作MySQL的模块,其选用方法和MySQLdb差不多一模一样。

 

windows下载安装

  1. pip3 install pymysql

 

应用操作

执行SQL

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4. import pymysql

  5.  

  6. # 创立连接

  7. conn =
    pymysql.connect(host=’192.168.31.134′,port=3306,user=’root’,passwd=’123456′,db=’information_schema’)

  1. # 创制游标

  2. cursor = conn.cursor()

  3. # 执行SQL,并赶回受影响行数

  4. effect_row = cursor.execute(“select * from tables”)

  5. print(effect_row)

  6. # 取出一条数据

  7. print(cursor.fetchone())

  8. # 取出前n条数据

  9. print(“*********************************************************”)

  1. print(cursor.fetchmany(5))

  2. # 取出所有数据

  3. print(“*********************************************************”)

  1. print(cursor.fetchall())

  2. # 提交,不然不能保存新建或改动的数量

  1. conn.commit()

  2. # 关闭游标

  3. cursor.close()

  4. # 关闭连接

  5. conn.close()

想必报错:pymysql.err.InternalError: (1130, “Host ‘192.168.31.1’ is not
allowed to connect to this MySQL server”)

唯恐是因为你的账号不容许从远程登录,只好在localhost登录。只必要在localhost的那台计算机登录mysql,更改对应数据库里的”user”表里的”host”项,把”localhost”改为”%”。

解决办法:

1、改表法

  1. [root@lnmp ~]# mysql -uroot -p123456
  1. mysql> use test;

  2. Database changed

  3. mysql> update user set host = ‘%’ where user = ‘root’;

  1. mysql> select host,user from user;
  1. mysql> flush rivileges;

2、授权法

同意用户user使用密码password从其他主机连接到mysql服务器。

  1. mysql> grant all privileges on *.*
    to ‘user’@’%’ identified by ‘password’ with grant option;

  2. mysql> flush privileges;

允许用户user从ip192.168.1.122的主机连接到mysql服务器,并选用password作为密码。

  1. mysql> grant all privileges on *.*
    to ‘user’@’192.168.1.122’ identified by ‘password’ with grant
    option;

  2. mysql> flush privileges;

同意用户user从ip192.168.1.122的主机连接到mysql服务器的test_db数据库,并运用password作为密码。

  1. mysql> grant all privileges on
    test_db.* to ‘user’@’192.168.1.122’ identified by ‘password’ with
    grant option;

  2. mysql> flush privileges;

插入数据

  1. # 插入数据

  2. effect_row = cursor.executemany(“inster into
    table_name(field_name1,field_name2)
    values(%s,%s)”,[(“value_1″,”value_2”),(“value_3″,”value_4”)])

从地点看出,借助于Fabric, 可以搭建 HA 集群、Sharing 、HA+Sharding。

SQLAlchemy ORM

运用从fabric中查出各类数据库、表、分区等的炫耀关系,然后访问相应的mysql
实例。

ORM介绍

对象关联映射(Object Relational
Mapping,简称ORM),是一种程序技术,用于落到实处面向对象编程语言里不一样档次系统的数量里面的变换。从效益上说,它实际是创制了一个可在编程语言里使用的——”虚拟对象数据库”。

面向对象是从软件工程中央原则(如耦合、聚合、封装)的根底上更上一层楼兴起的,而关全面据库则是从数学理论发展而来的,两套理论存在鲜明的区分。为驾驭决这一个不匹配的现象,对象关联映射技术出现。

目的关系映射(Object Relational
Mapping)提供了概念性的、易于精晓的模型化数据的艺术。ORM方法论基于多个为主标准。不难,以最中央的形式建模数据。传达性,数据库结构被任何人都能明白的语言文档化。精确性,基于数据模型创设正确原则的布局。典型地,建模者通过募集来自那一个熟稔应用程序但不在行数据建模者开发新闻模型。建模者必须可以用非技术公司专家可以领会的术语在概念层次上与数据结构进行电视公布。建模者也亟须能以简练的单元分析信息,对样本数量开展处理。ORM专门被规划为改进那种关系。

管理 2

 

ORM的优点

1、隐藏了数码访问细节,”封闭”的通用数据库交互,ORM的主干。使得通用数据库的竞相变得简单易行,并且完全不用考虑SQL语句。

2、ORM使协会固化数据结构变得简单易行。

Fabric由多少个零部件组成:

ORM的缺点

1、自动化意味着映射和涉嫌管理,代价是捐躯品质。现在各类ORM框架都在品味选择各样艺术(如LazyLoad、Cache)来减轻质量的震慑,效果比较显明。

2、面向对象的查询语言(OQL)作为一种数据库与对象之间的交接,即使隐藏了数码层面的事务抽象,但并不可能完全的屏蔽掉数据库层的设计,并且确实将加码学习花费。

3、对于复杂查询,ORM照旧不可以。即便可以兑现,不过不值得。视图可以化解一大半calculated
column、case、group、having、order by、exists,不过查询条件a and b and
not c and (d or e)无法很好解决。

1)fabric 的管理工具(也就是 mysqlfabric)。

常用的ORM框架

1、Hibernate全自动需求hql语句。

2、iBatis半自动自己写sql语句,可操作性强,小巧。

3、EclipseLink一个可扩张的辅助JPA的ORM框架,提供强大的缓存效率,缓存扶助集群。

4、Apache OJB等等。

5、在Python中最显赫的ORM框架是SQLAlchemy。

2)fabric
nodes,可以启动1到多个node。每个node也是一个进程。用于拍卖fabric的田间管理请求、应用的mapping请求。

SQLAlchemy安装

SQLAlchemy的视角是,SQL数据库的量级和性质首要于对象集合,而目的集合的架空又主要与表和行。

管理 3

Dialect用于和数据API进行交换,按照陈设文件的分歧调用不一样的数据库API,从而落成对数据库的操作。

  1. MySQL-Python

  2.     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

  1.  

  2. pymysql

  3.     mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

  1.  

  2. MySQL-Connector

  3.     mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

  1.  

  2. cx_Oracle

  3.     oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value…]

  1.  

  2. 越来越多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html

安装sqlalchemy

  1. pip3 install sqlalchemy

3)backstore,它也是一个mysql server,维护了HA Group、Sharding Group。

SQLAlchemy基本选择

开创一个表。

SQL实现:

  1. CREATE TABLE USER(

  2.    ID INTEGER NOT NULL AUTO_INCREMENT,

  1.    NAME VARCHAR(32),

  2.    PASSWORD VARCHAR(64),

  3.    PRIMARY KET (ID)

  4. )

ORM实现:

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6.  

  7. from sqlalchemy import create_engine

  1. from sqlalchemy.ext.declarative import
    declarative_base

  2. from sqlalchemy import
    Column,Integer,String

  3.  

  4. # echo=True打印新闻

  5. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1.  

  2. # 生成orm基类

  3. Base = declarative_base()

  4.  

  5. class
    User(Base):

  6.     # 表名

  7.     __tablename__ = ‘USER’

  8.     # 定义字段ID,并安装为主键

  9.     ID =
    Column(Integer,primary_key=True)

  10.     NAME = Column(String(32))

  11.     PASSWORD = Column(String(64))

  1. # 创设表结构

  2. Base.metadata.create_all(engine)

除了下面的创造表之外,还有一种创造表的方法。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6.  

  7. from sqlalchemy import
    create_engine,Table,MetaData,Column,Integer,String,ForeignKey

  1. from sqlalchemy.orm import mapper
  1.  

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1. metadata = MetaData()

  2.  

  3. user = Table(‘USER_TABLE’,metadata,

  1.              Column(‘ID’,Integer,primary_key=True),
  1.              Column(‘NAME’,String(50)),
  1.              Column(‘FULLNAME’,String(50)),
  1.              Column(‘PASSWORD’,String(12))
  1.              )

  2.  

  3.  

  4. class
    User(object):

  5.     def
    __init__(self,name,fullname,password):

  6.         self.name = name

  7.         self.fullname = fullname

  1.         self.password = password
  1.  

  2. mapper(User,user)

  3.  

  4. # 创制表结构

  5. metadata.create_all(engine)

率先种办法开创的表是基于第三种艺术的再封装。

运用ORM制造一条数据。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String

  7. from sqlalchemy.orm import sessionmaker

  1. from sqlalchemy.ext.declarative import
    declarative_base

  2.  

  3. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1. Base = declarative_base()

  2.  

  3. class
    User(Base):

  4.     __tablename__ = ‘USER’

  5.     ID =
    Column(Integer,primary_key=True)

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2. Base.metadata.create_all(engine)

  1. # 创立与数据库的会话session class,注意那里重回给session的是一个class,不是实例
  1. Session_class =
    sessionmaker(bind=engine)

  2. # 生成session实例

  3. Session = Session_class()

  4. # 生成要创制的数据对象

  5. user_obj = User(NAME=”Golden”,PASSWORD=”123456″)

  6. # 此时还尚未创制对象

  7. print(user_obj.NAME,user_obj.ID)

  1. # 把须求创制的数额对象添加到session
  1. Session.add(user_obj)

  2. # 此时还没有成立对象

  3. print(user_obj.NAME,user_obj.ID)

  1. # 创立数量,统一提交

  2. Session.commit()

 

查询、修改

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,
  1.                        encoding=’utf-8′,echo=False)
  1. Base = declarative_base()

  2. class
    User(Base):

  3.     __tablename__ = ‘USER’

  4.     ID =
    Column(Integer,primary_key=True)

  5.     NAME = Column(String(32))

  6.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<NAME : %s | PASSWORD : %s>”
    %(self.NAME,self.PASSWORD)

  4.  

  5. Session_class =
    sessionmaker(bind=engine)

  6. Session = Session_class()

  7. # first 查出NAME=’高尔德en’的首先个

  1. my_user =
    Session.query(User).filter_by(NAME=’Golden’).first()

  2. print(my_user.NAME,my_user.PASSWORD)

  1. # NAME=’Golden’

  2. data =
    Session.query(User).filter_by(NAME=’Golden’).all()

  3. print(data[0].PASSWORD)

  4. #all 查出所有

  5. data_2 =
    Session.query(User).filter_by().all()

  6. print(data_2)

  7. data_3 =
    Session.query(User).filter(User.ID == 1).all()

  8. print(data_3)

  9. data_4 =
    Session.query(User).filter_by(ID=1).all()

  10. print(data_4)

  11. # 多尺度查询

  12. data_5 =
    Session.query(User).filter(User.ID > 1).filter(User.ID <
    4).all()

  13. print(data_5)

  14. data_6 =
    Session.query(User).filter().first()

  15. print(data_6)

  16. # 修改

  17. data_6.NAME = ‘zhangsan’

  18. data_6.PASSWORD = ‘110’

  19. # 提交

  20. Session.commit()

 

回滚

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1.  

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1. Base = declarative_base()

  2.  

  3. class
    User(Base):

  4.     __tablename__ = ‘USER’

  5.     ID =
    Column(Integer,primary_key=True)

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<ID : %s | NAME : %s | PASSWORD :
    %s>” %(self.ID,self.NAME,self.PASSWORD)

  4.  

  5. Session_class =
    sessionmaker(bind=engine)

  6. Session = Session_class()

  7.  

  8. my_user =
    Session.query(User).filter_by(ID=1).first()

  9. my_user.NAME = ‘Mark’

  10.  

  11. fake_user =
    User(NAME=’Merry’,PASSWORD=’999999′)

  12. Session.add(fake_user)

  13. # 查看刚刚添加和修改的数据

  14. print(Session.query(User).filter(User.NAME.in_([‘Merry’,’Mark’])).all())

  1. # rollback

  2. Session.rollback()

  3. # 再度查询

  4. print(Session.query(User).filter(User.NAME.in_([‘Merry’,’Mark’])).all())

 

计算和分组

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String,func

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1.  

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1.  

  2. Base = declarative_base()

  3.  

  4.  

  5. class
    User(Base):

  6.     __tablename__ = ‘USER’

  7.     ID =
    Column(Integer,primary_key=True)

  8.     NAME = Column(String(32))

  9.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<ID : %s | NAME : %s | PASSWORD :
    %s>” %(self.ID,self.NAME,self.PASSWORD)

  4.  

  5. Session_class =
    sessionmaker(bind=engine)

  6. Session = Session_class()

  7. # 统计ha开头的NAME个数

  8. print(Session.query(User).filter(User.NAME.like(“ha%”)).count())

  9. # 分组

  10. print(Session.query(User.NAME,func.count(User.NAME)).group_by(User.NAME).all())

下载

连表

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String,DATE

  7. from sqlalchemy.orm import sessionmaker

  1. from sqlalchemy.ext.declarative import
    declarative_base

  2.  

  3. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1. Base = declarative_base()

  2.  

  3. class
    User(Base):

  4.     __tablename__ = ‘USER’

  5.     ID =
    Column(Integer,primary_key=True)

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<ID :%s | NAME : %s | PASSWORD :
    %s>” %(self.ID,self.NAME,self.PASSWORD)

  4.  

  5.  

  6. class
    Student(Base):

  7.     __tablename__ = ‘STUDENT’

  1.     ID =
    Column(Integer,primary_key=True)

  2.     NAME =
    Column(String(32),nullable=False)

  3.     REGISTER_DATE =
    Column(DATE,nullable=False)

  4.     GENDER =
    Column(String(32),nullable=False)

  5.  

  6.     def __repr__(self):

  7.         return “<NAME : %s | PASSWORD : %s>”
    %(self.NAME,self.REGISTER_DATE)

  8.  

  9. Base.metadata.create_all(engine)

  1. Session_class =
    sessionmaker(bind=engine)

  2. Session = Session_class()

  3.  

  4. # 外联

  5. print(Session.query(User,Student).filter(User.ID
    == Student.ID).all())

  6. # 三个表必须有外键关联才能动用 Can’t
    find any foreign key relationships between ‘A’ and ‘B’

  7. print(Session.query(User).join(Student).all())

  1. print(Session.query(User).join(Student,isouter=True).all())
  1. Session.commit()

 

已毕多少个表的外键关联

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4. import sqlalchemy

  5. from sqlalchemy import
    create_engine,Column,Integer,String,DATE,ForeignKey

  6. from sqlalchemy.orm import
    sessionmaker,relationship

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8.  

  9. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1. Base = declarative_base()

  2.  

  3. class
    Student(Base):

  4.     __tablename__ = ‘student’

  1.     id =
    Column(Integer,primary_key=True)

  2.     name =
    Column(String(32),nullable=False)

  3.     register_date =
    Column(DATE,nullable=False)

  4.  

  5.     def __repr__(self):

  6.         return “<name : %s | register_date :
    %s>”%(self.name,self.register_date)

  7.  

  8.  

  9. class
    StudyRecord(Base):

  10.     __tablename__ = “study_record”

  11.     id =
    Column(Integer,primary_key=True)

  12.     day =
    Column(Integer,nullable=False)

  13.     status =
    Column(String(32),nullable=False)

  14.     # 创立外键

  15.     stu_id =
    Column(Integer,ForeignKey(“student.id”))

  16.     #

  17.     student = relationship(“Student”,backref=”my_study_record”)

  18.  

  19.     def __repr__(self):

  20.        return “<%s day :%s | status :
    %s>”%(self.student.name,self.day,self.status)

  21. # 创立表结构

  22. Base.metadata.create_all(engine)

  1. # 成立与数据库的会话session

  2. Session_class =
    sessionmaker(bind=engine)

  3. # 生成session实例

  4. session = Session_class()

  5.  

  6. “””添加数据

  7. s1 = Student(name=”Golden”,register_date=”2017-12-24″)

  8. s2 = Student(name=”Jack”,register_date=”2017-11-13″)

  9. s3 = Student(name=”Rain”,register_date=”2017-10-11″)

  10. s4 = Student(name=”Eric”,register_date=”2017-07-07″)

  11.  

  12. study_obj1 =
    StudyRecord(day=1,status=”Yes”,stu_id=1)

  13. study_obj2 =
    StudyRecord(day=2,status=”No”,stu_id=1)

  14. study_obj3 =
    StudyRecord(day=3,status=”Yes”,stu_id=1)

  15. study_obj4 =
    StudyRecord(day=1,status=”Yes”,stu_id=2)

  16.  

  17. # 四回性全体创建

  18. session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4]管理,)

  1. “””

  2. stu_obj =
    session.query(Student).filter(Student.name == “Golden”).first()

  3. print(stu_obj)

  4. print(stu_obj.my_study_record)

  1. # 提交

  2. session.commit()

在mysql
5.6过后的版本,可以运用fabric的附和版本是1.5,在mysql官网上可以直接下载。由于fabric是由python语言编写的,并且其中通讯用会用到
mysql-connector- python,所以也要下载对应的python驱动。

多外键关联

创建包罗多外键的表结构。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. from sqlalchemy.orm import relationship

  1. from sqlalchemy import
    create_engine,Column,Integer,String,DATE,ForeignKey

  2. from sqlalchemy.ext.declarative import
    declarative_base

  3.  

  4. Base = declarative_base()

  5.  

  6. class
    Customer(Base):

  7.     __tablename__ = ‘customer’

  1.     id =
    Column(Integer,primary_key=True)

  2.     name = Column(String(64))

  3.  

  4.     billing_address_id =
    Column(Integer,ForeignKey(“address.id”))

  5.     shipping_address_id =
    Column(Integer,ForeignKey(“address.id”))

  6.  

  7.     billing_address =
    relationship(“Address”,foreign_keys=[billing_address_id])

  1.     shipping_address =
    relationship(“Address”,foreign_keys=[shipping_address_id])
  1.  

  2. class
    Address(Base):

  3.     __tablename__ = ‘address’

  1.     id =
    Column(Integer,primary_key=True)

  2.     street = Column(String(64))

  3.     city = Column(String(64))

  4.     state = Column(String(64))

  5.  

  6.     def __repr__(self):

  7.         return self.street

  8.  

  9. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1. # 成立表结构

  2. # Base.metadata.create_all(engine)

布置数据和询问。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import os,sys

  6. path =
    os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

  1. sys.path.append(path)

  2. from day11 import orm_many_fk

  3. from sqlalchemy.orm import sessionmaker

  1.  

  2. Session_class =
    sessionmaker(bind=orm_many_fk.engine)

  3. session = Session_class()

  4.  

  5. # 创设数据

  6. # addr1 =
    orm_many_fk.Address(street=”zhongshanroad”,city=”qiaokou”,state=’hankou’)

  7. # addr2 =
    orm_many_fk.Address(street=”dongyiroad”,city=”hongshan”,state=”wuchang”)

  8. # addr3 =
    orm_many_fk.Address(street=”guangshanroad”,city=”gaoxin”,state=”guanggu”)

  9. #

  10. #
    session.add_all([addr1,addr2,addr3])

  11. # c1 =
    orm_many_fk.Customer(name=”Golden”,billing_address=addr1,shipping_address=addr3)

  1. # c2 =
    orm_many_fk.Customer(name=”Jack”,billing_address=addr2,shipping_address=addr2)
  1. #

  2. # session.add_all([c1,c2])

  3.  

  4. # 查询数据

  5. obj =
    session.query(orm_many_fk.Customer).filter(orm_many_fk.Customer.name==”Golden”).first()

  6. print(obj.name,obj.billing_address,obj.shipping_address)

  1.  

  2. session.commit()

         我下载的版本是:

多对多关系

创造多对多关联表

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. from sqlalchemy import
    Table,Column,Integer,String,DATE,ForeignKey,create_engine

  6. from sqlalchemy.orm import
    relationship,sessionmaker

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8.  

  9. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1.  

  2. Base = declarative_base()

  3.  

  4. book_m2m_author =
    Table(‘book_m2m_author’,Base.metadata,

  5.                         Column(‘book_id’,Integer,ForeignKey(‘books.id’)),

  1.                         Column(‘author_id’,Integer,ForeignKey(‘authors.id’)))
  1.  

  2. class
    Book(Base):

  3.     __tablename__ = ‘books’

  4.     id =
    Column(Integer,primary_key=True)

  5.     name = Column(String(64))

  6.     pub_date = Column(DATE)

  7.     authors =
    relationship(‘Author’,secondary=book_m2m_author,backref=’books’)

  1.  

  2.     def __repr__(self):

  3.         return self.name

  4.  

  5.  

  6. class
    Author(Base):

  7.     __tablename__ = ‘authors’

  1.     id =
    Column(Integer,primary_key=True)

  2.     name = Column(String(32))

  3.  

  4.     def __repr__(self):

  5.         return self.name

  6.  

  7. Base.metadata.create_all(engine)

  1. Session_class =
    sessionmaker(bind=engine)

  2. session = Session_class()

 

插入数据和查询

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import os,sys

  6. path =
    os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

  1. sys.path.append(path)

  2. from day11 import orm_m2m

  3. from sqlalchemy.orm import sessionmaker

  1.  

  2. Session_class =
    sessionmaker(bind=orm_m2m.engine)

  3. session = Session_class()

  4.  

  5. # 创制数据

  6. # b1 = orm_m2m.Book(name=”Python”)

  7. # b2 = orm_m2m.Book(name=”JAVA”)

  8. # b3 = orm_m2m.Book(name=”C++”)

  9. # b4 = orm_m2m.Book(name=”c#”)

  10. #

  11. # a1 = orm_m2m.Author(name=”Golden”)

  12. # a2 = orm_m2m.Author(name=”Jack”)

  13. # a3 = orm_m2m.Author(name=”Rain”)

  14. #

  15. # b1.authors = [a1,a2]

  16. # b2.authors = [a1,a2,a3]

  17. #

  18. #
    session.add_all([b1,b2,b3,b4,a1,a2,a3])

  19. # session.commit()

  20.  

  21. print(“通过书表查询关联的小编”.center(50,”*”))

  22. book_obj =
    session.query(orm_m2m.Book).filter_by(name=”JAVA”).first()

  23. print(book_obj.name,book_obj.authors)

  1. print(“通过小编表查询关联的书”.center(50,”*”))

  2. author_obj =
    session.query(orm_m2m.Author).filter_by(name=”Golden”).first()

  3. print(author_obj.name,author_obj.books)

  1.  

  2. #
    多对多删除,删除数据时绝不管book_m2m_author,sqlalchemy会自动删除相应的数码

  1. # 通过书删除小编

  2. book_obj.authors.remove(author_obj)

  1.  

  2. # 直接删除小编

  3. author_obj2 =
    session.query(orm_m2m.Author).filter_by(name=”Jack”).first()

  4. session.delete(author_obj2)

  5. session.commit()

 

mysql-utiltities-1.5.6-1.el6.noarch.rpm
mysql-connector-ptyhon-2.0.4-1.el6.noarch.rpm

支撑普通话

sqlalchemy设置编码字符集一定要在数据库访问的URL上平添charset=utf8,否则数据库的连日就不是utf8的编码格式。

  1. # 帮忙中文

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db?charset=utf8”,echo=True)

 

  

安装

1、安装介质

采取操作系统root用户来设置。

先安装mysql-connector-ptyhon,再安装mysql-utilities:

rpm –i mysql-connector-ptyhon-2.0.4-1.el6.noarch.rpm
rpm –i mysql-utiltities-1.5.6-1.el6.noarch.rpm

 安装完成后,会有一个mysqlfabric命令可以选择。所以可以通过该命令来判定是或不是安装成功。

2、配置文件

在设置好fabric之后,就会有一个默认的配置文件:fabric.cfg。

默许的安顿文件的地点:

Platform

Package

Location

Microsoft Windows

mysql-utilities-1.5.6-win32.msi

UTILITIES_INSTALLDIR/etc/mysql/fabric.cfg

Ubuntu Linux 14.04

mysql-utilities_1.5.6-1ubuntu14.04_all.deb

/etc/mysql/fabric.cfg

Debian Linux 6.0

mysql-utilities_1.5.6-1debian6.0_all.deb

/etc/mysql/fabric.cfg

Red Hat Enterprise Linux 6 / Oracle Linux 6

mysql-utilities-1.5.6-1.el6.noarch.rpm

/etc/mysql/fabric.cfg

OS X

mysql-utilities-1.5.6-osx10.9.dmg

/etc/mysql/fabric.cfg

 

默许的布局文件预览:

[DEFAULT]
prefix = /usr/local
sysconfdir = /usr/local/etc
logdir = /var/log

[storage]
address = localhost:3306
user = fabric_store
password = secret
database = mysql_fabric
auth_plugin = mysql_native_password
connection_timeout = 6
connection_attempts = 6
connection_delay = 1

[servers]
user = fabric_server
password = secret
backup_user = fabric_backup
backup_password = secret
restore_user = fabric_restore
restore_password = secret
unreachable_timeout = 5

[protocol.xmlrpc]
address = localhost:32274
threads = 5
user = admin
password = secret
disable_authentication = no
realm = MySQL Fabric
ssl_ca =
ssl_cert =
ssl_key =

[protocol.mysql]
address = localhost:32275
user = admin
password = secret
disable_authentication = no
ssl_ca =
ssl_cert =
ssl_key =

[executor]
executors = 5

[logging]
level = INFO
url = file:///var/log/fabric.log

[sharding]
mysqldump_program = /usr/bin/mysqldump
mysqlclient_program = /usr/bin/mysql

[statistics]
prune_time = 3600

[failure_tracking]
notifications = 300
notification_clients = 50
notification_interval = 60
failover_interval = 0
detections = 3
detection_interval = 6
detection_timeout = 1
prune_time = 3600

[connector]
ttl = 1

 

在那些布局文件中,会波及到很各个帐户:
·backstore

在[storage]区域,用来安插backstore mysql
server的音讯。所以那边配置的就是backstore mysql server的操作帐户。

该用户必需的权位:

ALTER              - alter some database objects
CREATE             - create most database objects
CREATE VIEW        - create views
DELETE             - delete rows
DROP               - drop most database objects
EVENT              - manage events
REFERENCES         - foreign keys
INDEX              - create indexes
INSERT             - insert rows
SELECT             - select rows
UPDATE             - update rows

  

·server

在[servers]区域布局的,是颇具的受管mysql server的帐户。

该用户必需的权能:

内部全局满围的权限:

DELETE             - prune_shard
PROCESS            - list sessions to kill
RELOAD             - RESET SLAVE
REPLICATION CLIENT - SHOW SLAVE STATUS
REPLICATION SLAVE  - SHOW SLAVE HOSTS

 

其中Fabric 数据库的权杖:

ALTER              - alter some database objects
CREATE             - create most database objects
DELETE             - delete rows
DROP               - drop most database objects
INSERT             - insert rows
SELECT             - select rows
UPDATE             - update rows

 

·backup

在[servers]区域布局的,它是具备的受管mysql
server上用以backup的帐户。例如执行mysqldump。

该用户的权杖:

EVENT              - show event information
EXECUTE            - show routine information inside views
REFERENCES         - foreign keys
SELECT             - read data
SHOW VIEW          - SHOW CREATE VIEW
TRIGGER            - show trigger information

 

·restore

在[servers]区域布局的,它是有着的受管mysql
server上用以restore的帐户。Restore 用户可以行使mysql
client执行restore操作。

该用户的权杖:

ALTER              - ALTER DATABASE
ALTER ROUTINE      - ALTER {PROCEDURE|FUNCTION}
CREATE             - CREATE TABLE
CREATE ROUTINE     - CREATE {PROCEDURE|FUNCTION}
CREATE TABLESPACE  - CREATE TABLESPACE
CREATE VIEW        - CREATE VIEW
DROP               - DROP TABLE (used before CREATE TABLE)
EVENT              - DROP/CREATE EVENT
INSERT             - write data
LOCK TABLES        - LOCK TABLES (--single-transaction)
REFERENCES         - Create tables with foreign keys
SELECT             - LOCK TABLES (--single-transaction)
SUPER              - SET @@SESSION.SQL_LOG_BIN = 0
TRIGGER            - CREATE TRIGGER

 

·admin 

在[protocol.mysql]、[protocol.xmlrpc]中布局的admin用户,是Fabric
的客户端(包罗mysqlfabric、使用jdbc驱动的拔取等) 与fabric
node交互使用的用户、密码。

 在拔取mysqlfabric时,可以选择默许的布局文件,也足以使用自定义的安插文件。不管选取哪个配置文件,相关的配备项都是要配的。

 

 

3、创建backstore 帐户

创立一个mysql 实例作为fabric 的backstore。然后创设一个fabric
backstore帐户。

CREATE USER 'fabric_store'@'localhost'
   IDENTIFIED BY 'secret';

GRANT ALTER, CREATE, CREATE VIEW, DELETE, DROP, EVENT,
   INDEX, INSERT, REFERENCES, SELECT, UPDATE ON mysql_fabric.*
   TO 'fabric_store'@'localhost';

创制的这么些用户名、密码要跟配置文件fabric.cfg中[storeage]区域中的user、password相同。 

此时fabric数据库还尚未开创,可是也不用自己去创建。

 

4、初始化fabric数据库、表

使用mysqlfabric初始化:

mysqlfabric manage setup

如果要采用自定义的fabric.cfg配置文件:

 

mysqlfabric --config=/your/fabric/cfg/path manage setup

 

5、mysqlfabric命令

1)启动fabric节点

mysqlfabric --config=/your/fabric/cfg/path manage start

2)停止fabric节点 

mysqlfabric --config=/your/fabric/cfg/path manage stop

有关其它命令,可以参照官方文档。 

 

Post Author: admin

发表评论

电子邮件地址不会被公开。 必填项已用*标注