Part11 – MySQL和ORM之sqlalchemy

本节内容

1.MySQL

1.MySQL基础

2.Python连接MySQL

2.ORM之sqlalchemy

一、MySQL

MySQL是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System,即关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL使用标准的SQL数据语言形式。
• Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
• Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
• Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

1、MySQL基础

1.MySQL操作

MySQL、ORM：http://www.cnblogs.com/alex3714/articles/5950372.html

MySQL练习题：http://www.cnblogs.com/wupeiqi/articles/5729934.html

MySQL更多：http://www.cnblogs.com/wupeiqi/articles/5713323.html

2.MySQL数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

• 数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

• 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。

• 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。TEXT有4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，分别对应4种BLOB类型，有相同的最大长度和存储需求。

2、Python连接MySQL

pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

import pymysql  
    
# 创建连接  
conn = pymysql.connect(host='192.168.3.121', port=3306, user='root', passwd='123456', db='oldboydb')  
# 创建游标  
cursor = conn.cursor()  
    
# 执行SQL，并返回收影响行数  
effect_row = cursor.execute("select * from student")  
print(cursor.fetchone())  
print(cursor.fetchone())  
print(cursor.fetchall())  
    
# 提交，不然无法保存新建或者修改的数据  
# conn.commit()  
    
# 关闭游标  
cursor.close()  
# 关闭连接  
conn.close()  

其他具体操作参考：

mysqldb: http://www.cnblogs.com/wupeiqi/articles/5095821.html

pymysql、SQLAlchemy: http://www.cnblogs.com/wupeiqi/articles/5713330.html

二、ORM之sqlalchemy

ORM英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

更详细ORM介绍：http://www.cnblogs.com/alex3714/articles/5978329.html

1、基础知识

engine = create_engine("mysql+pymysql://root:123456@192.168.3.121/oldboydb?charset=utf8",
                encoding='utf-8', echo=False)  
#写入中文时候要加 ?charset=utf8 ,echo=False 不显示sql执行过程  
    
常用SQLALCHEMY列数据类型   
类型名称        Python数据类型      描述  
Integer         int                 常规整形，通常为32位  
SmallInteger    int                 短整形，通常为16位  
BigInteger      int或long            精度不受限整形  
Float           float               浮点数  
Numeric         decimal.Decimal     定点数  
String          str                 可变长度字符串  
Text            str                 可变长度字符串，适合大量文本  
Unicode         unicode             可变长度Unicode字符串  
Boolean         bool                布尔型  
Date            datetime.date       日期类型  
Time            datetime.time       时间类型  
Interval        datetime.timedelta  时间间隔  
Enum            str                 字符列表  
PickleType      任意Python对象      自动Pickle序列化  
LargeBinary     str                 二进制  
    
常见SQLALCHEMY列配置参数  
可选参数        描述  
primary_key     如果此参数为True，该列为列表的主键，该列不允许有空值(null)  
                注意：数据库中查询结果显示空白是0长度字符串，不是空值(null)  
unique          唯一属性，如果此参数为True，该列不允许有相同值，但允许有一个空值  
index           索引，如果参数为True，为该列创建索引，查询效率会更高，但会增加修改表的时间  
nullable        如果此参数为True，该列允许为空。如果参数为False，该列不允许空值  
default         定义该列的默认值     

2、增删改查

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey  
from sqlalchemy.orm import mapper  
    
#对于不用程序经常维护的表，可以通过如下方式创建  
metadata = MetaData()  
    
user = Table('user', metadata,  
             Column('id', Integer, primary_key=True),  
             Column('name', String(50)),  
             Column('fullname', String(50)),  
             Column('password', String(12))  
             )  
    
class User(object):  
    def __init__(self, name, fullname, password):  
        self.name = name  
        self.fullname = fullname  
        self.password = password  
    
mapper(User,user)    
# the table metadata is created separately with the Table construct,   
# then associated with the User class via the mapper() function  

from sqlalchemy import create_engine  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy import Column, Integer, String, DATE,Enum  
from sqlalchemy.orm import sessionmaker  
    
engine = create_engine("mysql+pymysql://root:123456@192.168.3.121/oldboydb",  
                       encoding='utf-8', echo=False) #echo=True,即打印过程  
    
Base = declarative_base()  # 生成orm基类  
    
class User(Base):  
    __tablename__ = 'user'  # 表名  
    id = Column(Integer, primary_key=True)  
    name = Column(String(32))  
    password = Column(String(64))  
    
    def __repr__(self):  
        '''''使查询结果中显示具体信息，不是内存地址'''  
        return "<%s name:%s>"%(self.id,self.name)  
    
class Student(Base):  
    __tablename__ = "student"  
    id = Column(Integer,primary_key=True)  
    name = Column(String,nullable=False)  
    register_date = Column(DATE,nullable=False)  
    gender = Column(Enum('M','F'),nullable=False)  
    
    def __repr__(self):  
        '''''使查询结果中显示具体信息，不是内存地址'''  
        return "<%s name:%s>" % (self.id, self.name)  
    
    
###########创建表结构  
# Base.metadata.create_all(engine)  
    
    
###########创建数据  
Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例  
Session = Session_class()  # 生成session实例  
# user_obj = User(name="alex", password="alex3714")  # 生成你要创建的数据对象  
# print(user_obj.name, user_obj.id)  # 此时还没创建对象呢，不信你打印一下id发现还是None  
# Session.add(user_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建  
# print(user_obj.name, user_obj.id)  # 此时也依然还没创建  
# Session.commit()  # 现此才统一提交，创建数据  
    
 
# ###########删除  
# data = Session.query(User).filter_by(name="alex").first()  
# Session.delete(data)  
# Session.commit()  
    
###########单个修改  
# my_user = Session.query(User).filter_by(name="alex").first()  
# my_user.name = "Alex Li"  
# Session.commit()  
    
    
###########查询数据  
#单个查询条件  
# data = Session.query(User).filter_by(name="alex").all()     #把所有符合条件的数据取成一个列表  
# data = Session.query(User).filter_by(name="alex").first()     #取出符合条件的第一个  
# data = Session.query(User).filter_by(id = 1).all()  
# data = Session.query(User).filter(User.id == 2).all()  
    
#多个查询条件组合查询  
# data = Session.query(User).filter(User.id > 1).filter(User.name == 'alex').all()  
    
#获取所有数据  
# print(Session.query(User.name,User.id).all() )  
    
#统计  
# data = Session.query(User).filter(User.name.like("Ra%")).count()  
    
#分组  
# from sqlalchemy import func  
# data = Session.query(func.count(User.name),User.name).group_by(User.name).all()  
    
#联表（join）  
# data = Session.query(User, Student).filter(User.id == Student.id).all()  
# data = Session.query(User).join(Student).all()  #如果两个表已经有了外键关联关系  
# data = Session.query(User).join(Student, isouter=True).all()  
    
#print(data)  
# print(data.id,data.name,data.password)  
    
    
###########数据会滚  
# my_user = Session.query(User).filter_by(id=1).first()  
# my_user.name = "Jack"  
# fake_user = User(name='Rain', password='12345')  
# Session.add(fake_user)  
# print(Session.query(User).filter(User.name.in_(['Jack', 'rain'])).all())  # 这时看session里有你刚添加和修改的数据(脏数据)  
# Session.rollback()  # 此时你rollback一下  
# print(Session.query(User).filter(User.name.in_(['Jack', 'rain'])).all())  # 再查就发现刚才添加的数据没有了。  

3、外键关联

from sqlalchemy import create_engine  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy import Column, Integer, String, DATE,Enum,ForeignKey  
from sqlalchemy.orm import sessionmaker,relationship  
    
engine = create_engine("mysql+pymysql://root:123456@192.168.3.121/oldboydb",  
                       encoding='utf-8', echo=False) #echo=True,即打印过程  
    
Base = declarative_base()  # 生成orm基类  
    
class Student(Base):  
    __tablename__ = "student"  
    id = Column(Integer, primary_key=True)  
    name = Column(String(32), nullable=False)  
    register_date = Column(DATE, nullable=False)  
    
    def __repr__(self):  
        '''''使查询结果中显示具体信息，不是内存地址'''  
        return "<%s name:%s>" % (self.id, self.name)  
    
class StudyRecord(Base):  
    __tablename__ = 'study_record'  # 表名  
    id = Column(Integer, primary_key=True)  
    day = Column(Integer,nullable=False)  
    status = Column(String(32), nullable=False)  
    stu_id = Column(Integer,ForeignKey('student.id'))  
    
    student = relationship("Student", backref="my_study_record")  # 这个nb，允许你在student表里通过backref字段反向查出所有它在study_record表里的关联项,建立在内存中  
    
    def __repr__(self):  
        '''''使查询结果中显示具体信息，不是内存地址'''  
        return "<%s day:%s status:%s>"%(self.student.name,self.day,self.status)  
    
###########创建表结构(外键)  
#Base.metadata.create_all(engine)  
    
    
###########创建数据  
Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例  
session = Session_class()  # 生成session实例  
    
# s1 = Student(name="Alex",register_date='2017-01-23')  
# s2 = Student(name="Jack",register_date='2017-02-23')  
# s3 = Student(name="Rain",register_date='2017-03-23')  
# s4 = Student(name="Eric",register_date='2017-04-23')  
#  
# study_obj1 = StudyRecord(day=1,status="YES",stu_id=1)  
# study_obj2 = StudyRecord(day=2,status="NO",stu_id=1)  
# study_obj3 = StudyRecord(day=3,status="YES",stu_id=1)  
# study_obj4 = StudyRecord(day=1,status="YES",stu_id=2)  
#  
# #session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])  
# session.add_all([s1,s2,s3,s4])  
# session.add_all([study_obj1,study_obj2,study_obj3,study_obj4])  
# session.commit()  
    
    
###########查询  
# stu_obj = session.query(Student,StudyRecord).filter(Student.name=='alex')\  
#     .filter(Student.id==StudyRecord.stu_id).all()     #返回的是列表  
stu_obj = session.query(Student).filter(Student.name=='alex').first()  
    
print(stu_obj.my_study_record)  

4、多外键关联

from sqlalchemy import Integer, ForeignKey, String, Column  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy.orm import relationship  
from sqlalchemy import create_engine  
    
engine = create_engine("mysql+pymysql://root:123456@192.168.3.121/oldboydb",  
                       encoding='utf-8', echo=False) #echo=True,即打印过程  
    
Base = declarative_base()  # 生成orm基类  
    
class Customer(Base):  
    __tablename__ = 'customer'  
    id = Column(Integer, primary_key=True)  
    name = Column(String(64))  
    
    billing_address_id = Column(Integer, ForeignKey("address.id"))  
    shipping_address_id = Column(Integer, ForeignKey("address.id"))  
    
    # foreign_keys=[billing_address_id]如果不写添加数据时程序会分不清两个relationship  
    billing_address = relationship("Address", foreign_keys=[billing_address_id])  
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])  
    
class Address(Base):  
    __tablename__ = 'address'  
    id = Column(Integer, primary_key=True)  
    street = Column(String(64))  
    city = Column(String(64))  
    state = Column(String(64))  
    
    def __repr__(self):  
        return self.street  
    
if __name__ == '__main__':  
    ###########创建表结构(多外键)  
    Base.metadata.create_all(engine) 

from orm_many_fk import Customer,Address,engine  
from sqlalchemy.orm import sessionmaker  
    
###########创建数据  
Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例  
session = Session_class()  # 生成session实例  
    
# addr1 = Address(street="Tiantongyuan",city="ChangPing",state="Beijing")  
# addr2 = Address(street="Wudaokou",city="Haidian",state="Beijing")  
# addr3 = Address(street="Yanjiao",city="LangFang",state="Hebei")  
# session.add_all([addr1,addr2,addr3])  
#  
# c1 = Customer(name="Alex",billing_address=addr1,shipping_address=addr2)  
# c2 = Customer(name="Jack",billing_address=addr3,shipping_address=addr3)  
# session.add_all([c1,c2])  
#  
# session.commit()  
    
    
###########查询  
obj = session.query(Customer).filter(Customer.name=="alex").first()  
print(obj.name,obj.billing_address,obj.shipping_address)  

5、多对多关系

#一本书可以有多个作者，一个作者又可以出版多本书  
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey  
from sqlalchemy.orm import relationship  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy import create_engine  
    
engine = create_engine("mysql+pymysql://root:123456@192.168.3.121/oldboydb?charset=utf8",   #?charset=utf8支持中文  
                       encoding='utf-8', echo=False) #echo=True,即打印过程  
    
Base = declarative_base()  
    
book_m2m_author = Table('book_m2m_author', Base.metadata,  
                        Column('book_id',Integer,ForeignKey('books.id')),  
                        Column('author_id',Integer,ForeignKey('authors.id')),  
                        )  
    
class Book(Base):  
    __tablename__ = 'books'  
    id = Column(Integer,primary_key=True)  
    name = Column(String(64))  
    pub_date = Column(DATE)  
    authors = relationship('Author',secondary=book_m2m_author,backref='books')  
    # secondary=book_m2m_author，与Author做关联，查询"authors"时去book_m2m_author中查  
    # backref='books'，通过Author反向查询Book时,使用"author表查询实例.books"  
    
    def __repr__(self):  
        return self.name  
    
class Author(Base):  
    __tablename__ = 'authors'  
    id = Column(Integer, primary_key=True)  
    name = Column(String(32))  
    
    def __repr__(self):  
        return self.name  
    
if __name__ == '__main__':  
    ###########创建表结构(多对多)  
    Base.metadata.create_all(engine)  

import orm_m2m  
from sqlalchemy.orm import sessionmaker  
    
###########创建数据  
Session_class = sessionmaker(bind=orm_m2m.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例  
session = Session_class()  # 生成session实例  
    
# b1 = orm_m2m.Book(name="learn python with Alex",pub_date="2014-05-22")  
# b2 = orm_m2m.Book(name="learn Zhuangbility with Alex",pub_date="2014-05-22")  
# b3 = orm_m2m.Book(name="learn hook up girls with Alex",pub_date="2014-05-22")  
b4 = orm_m2m.Book(name="跟Alex去泰国",pub_date="2014-05-22")  
#  
# a1 = orm_m2m.Author(name="Alex")  
# a2 = orm_m2m.Author(name="Jack")  
# a3 = orm_m2m.Author(name="Rain")  
#  
# 建立第三张表的关联关系  
# b1.authors = [a1,a3]  
# b3.authors = [a1,a2,a3]  
#  
# session.add_all([b1,b2,b3,a1,a2,a3])  
session.add(b4)  
session.commit()  
    
    
###########查询  
# author_obj = session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="alex").first()  
# print(author_obj.books)  
# print(author_obj.books[1].pub_date)  
# book_obj = session.query(orm_m2m.Book).filter(orm_m2m.Book.id==2).first()  
# print(book_obj.authors)  
    
    
###########删除,删除数据时不用管boo_m2m_authors，sqlalchemy会自动帮你把对应的数据删除  
#通过书删除作者  
# author_obj = session.query(orm_m2m.Author).filter_by(name="Jack").first()  
# book_obj = session.query(orm_m2m.Book).filter_by(name="跟Alex学把妹").first()  
# book_obj.authors.remove(author_obj)  # 从一本书里删除一个作者  
    
#直接删除作者。删除作者时，会把这个作者跟所有书的关联关系数据也自动删除  
# author_obj =session.query(orm_m2m.Author).filter_by(name="Alex").first()  
# session.delete(author_obj)  
#  
# session.commit()  

参考：

http://www.cnblogs.com/alex3714/articles/5248247.html