python学习之-- mysql模块和sqlalchemy模块

简单介绍python下操作mysql数据库模块有2个：pyhton-mysqldb  和 pymysql

说明：在python3中支持mysql 的模块已经使用pymysql替代了MysqlDB（这个模块用在python2中）

mysql api操作

举例如下：

import pymysql
# 创建socket连接,
conn = pymysql.Connect(host='localhost',port=3306,user='root',passwd='ls3du8',db='dbname')
# 创建游标，实例
cur = conn.cursor()
# 执行sql并返回响应行数
result_row = cur.execute('select * from student')
# result_row = cur.execute('select * from student where id=%s',(2,))
print(result_row) # 打印结果有多少条
print(cur.fetchone()) # 打印一条
print(cur.fetchmany(2)) # 打印前2行数就
print(cur.fetchall()) #　打印所有
###########插入代码如下##################
#  批量执行，如插入，默认开启了 事务（这样的话可以rollback回滚哦）
# data = [
#     ('name1','20170606','M'),
#     ('name2','20170606','M'),
#     ('name3','20170606','M'),
# ]
# result_row = cur.executemany('insert into student (name,register_data,sex) values (%s,%s,%s)',data)
#
# # 提交，用于增/删/改操作，查不需要
# conn.commit()
###########################################
# 关闭连接
conn.close()

以上对mysql的模块进行了简单的说明。

以下主要讲解ORM框架sqlalchemy

ORM（object relational mapping）就是对象映射关系程序，通过ORM将编程语言的对象模型和数据库的关系模型建立映射关系，这样
在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作即可，不用直接使用sql语言。
参考：http://www.cnblogs.com/alex3714/articles/5978329.html
python 中最有名的ORM框架是：sqlalchemy

安装：SQLAlchemy
优点：
1：隐藏了访问细节，‘封闭’的通用数据库交互，ORM的核心。使交互变得简单，方便快速开发。
2：ORM使我们构造固化数据结构变得简单易行。
缺点：
自动化意味着映射和关联管理，代价是牺牲性能。
结构简图：

Sqlalchemy 结构图

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

基本使用：
举例：创建表第一种写法

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,INTEGER,String
# 建立数据库SOCKET连接，这里的echo=True 会在运行时候打印所有输出信息
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/dbname',encoding='utf-8',echo=True)
# 生成ORM基类
Base = declarative_base()

class User(Base):
    __tablename__ = 'user'  # 表名
    id = Column(INTEGER,primary_key=True)  # 列字段
    name = Column(String(32))
    password = Column(String(64))
# 创建表结构，运行开始将engine传入，然后通过父类Base运行子类User
Base.metadata.create_all(engine)

数据的增/删/改/查举例

前提条件：建立session

from sqlalchemy.orm import sessionmaker # 相当于游标
# 第一步：创建与数据库的会话session class 注意这里返回的是一个session类，不是实例
Session_class = sessionmaker(bind=engine)
# 第二步：生成session实例，这个就相当于游标
Session = Session_class()

---》 创建数据
# 第三步：往类里传参数，生成要创建的数据对象
user_obj = User(name='jack',password='123')
user_obj2 = User(name='jack2',password='123')
# 上面只是声明要创建数据，但还没有实际生成
print(user_obj.name,user_obj.password)
# 第四步：把要创建的数据对象添加到这个session里，仍然未实际创建，最后统一创建
Session.add(user_obj)
Session.add(user_obj2)
# 第五步：这里进行提交统一创建数据
Session.commit()

---》查询数据
语法 data = Session.query(查询的表名).filter_by(字段数据).all()
解释：query为查询方法，filter_by为查询字段（空将查询所有）， (all 是返回查询的结果以列表返回，first：返回第一个数据）
# 第三步 各种方式查询
data = Session.query(User).filter_by().all() # 查询所有
data = Session.query(User).filter_by(name='jack').all() # 查询匹配
data = Session.query(User).filter(User.id>2).all() # 查询范围
data = Session.query(User).filter(User.id>2).filter(User.id<4).all() # 多条件查询
filter_by 和 filter 的区别用法
filter 可以像写 sql 的 where 条件那样写 > < 等条件，但引用列名时，需要通过 类名.属性名 的方式。不支持组合查询
filter_by 可以使用 python 的正常参数传递方法传递条件，指定列名时，不需要额外指定类名，参数名对应各类中的属性名，
但不能使用 > < 等条件。支持组合查询
print(data)
# 另说明 连表查询方法
data = Session.query(表1，表2).filter(表1.id == 表2.id).all() # 因为强行指定了关系，所以不需要外键关联，相当于 inner join
data = Session.query(表１).join(表２).all() #　这２个表必须有外键关联才可以
data = Session.query(表1).join(表2,isouter=True).all() # 也必须有外键关联

---》修改数据
# 第三步
data = Session.query(User).filter(User.id>2).filter(User.id<6).first() # 提取一条数据
data.name = 'vivi' # 修改数据
data.password = '654321' # 修改数据
Session.commit() # 修改后必须提交

---》删除数据
# 第三步
data = Session.query(User).filter(User.id == 1).first() # 必须提取一行进行删除
Session.delete(data)
Session.commit()

---》回滚
fake_user = User(name='Rain',password='123')
Session.add(fake_user)
print(Session.query(User).filter(User.name.in_(['jack','Rain'])).all()) # 这个sessin里可以看到新添加的数据
Session.rollback() # 回滚
print(Session.query(User).filter(User.name.in_(['jack','Rain'])).all()) # 刚添加的数据已没有了
注意 回滚必须在commit之前 才有效

---》分组和统计

# 统计
print(Session.query(User).filter(User.id > 2).count())
输出：统计数字
# 分组
from sqlalchemy import func
# func.count(User.name) 计算name出现的次数，group_by(User.name)：分组要求
print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )
输出：[(2, 'jack2'), (1, 'vivi')]

参考举例：创建表第二种写法，一般用于不需修改的表使用此方法，如多对多的关联表

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper

metadata = MetaData()

user = Table('user1', 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) # 将User类和user创建的表建立关联关系。

外键关联： 使用类的形式映射外键

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,INTEGER,String,DATE,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

engine = create_engine('mysql+pymysql://root:123@127.0.0.1/dbname',encoding='utf-8')

Base = declarative_base()

class Student(Base):
    __tablename__ = 'student'
    id = Column(INTEGER,primary_key=True)
    name = Column(String(8),nullable=False)
    register_date = Column(DATE,nullable=False)

    def __repr__(self):
        return '<ID:%s name:%s regiter_data: %s>' % (self.id,self.name,self.register_date)
class StudyRecord(Base):
    __tablename__ = 'study_record'
    id = Column(INTEGER,primary_key=True)
    day = Column(INTEGER,nullable=False)
    status = Column(String(8),nullable=False)
    stu_id = Column(INTEGER,ForeignKey('student.id'))

    # 外键表里加这个关系，这样可以通过stu_user字段查询Student表，也可以在Student表通过my_study_record进行反向查询StudyRecord表，实现双向查询功能，
    # backref功能就是可以在Student表里通过my_study_record字段查询StudyRecord表
    # relationship解释：存储于内存中，就是将Student和StudyRecord俩个类进行了关联
    #       Student表 查询 StudyRecord表数据，通过my_study_record字段进行查询
    #       StudyRecord表 查询Student表数据，通过字段stu_user来进行查询
    # 这里stu_user 相当于： stu_user = Student()
    stu_user = relationship('Student',backref='my_study_record')
    def __repr__(self):
        # 注意，这里调用Student表数据是通过stu_user关系映射对象
        return '<ID:%s name:%s day:%s status:%s' % (self.id,self.stu_user.name,self.day,self.status)

Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)
Session = Session_class()
# 添加数据
# s1 = Student(name='jack',register_date='2016-02-12')
# s2 = Student(name='vivi',register_date='2016-02-12')
# s3 = Student(name='bard',register_date='2016-02-12')
# s4 = Student(name='anne',register_date='2016-02-12')
# study_obj1 = StudyRecord(day=1,status='yes',stu_id=1)
# study_obj2 = StudyRecord(day=2,status='yes',stu_id=1)
# study_obj3 = StudyRecord(day=3,status='no',stu_id=1)
# study_obj4 = StudyRecord(day=1,status='yes',stu_id=2)
# study_obj5 = StudyRecord(day=2,status='yes',stu_id=2)
# Session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5])
# Session.commit()
# 查询
stu_obj = Session.query(Student).filter(Student.name == 'jack').first()
print(stu_obj)
# 当前在Student表里查询study_record表数据
print(stu_obj.my_study_record)
# 在study_record表查询student表数据
study_obj = Session.query(StudyRecord).filter(StudyRecord.day == 1).first()
print(study_obj.stu_user)

多外键关联：

表结构创建（这里创建文件名为：orm_many_fk）
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer,ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('mysql+pymysql://root:123@127.0.0.1/dbname',encoding='utf-8')

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    # 定义2个地址，同时关联到一个地址表的同一个字段
    billing_address_id = Column(Integer,ForeignKey('address.id'))
    shipping_address_id = Column(Integer,ForeignKey('address.id'))
    # 由于这里的关联关系全部指定到address，导致反向查询无法定位，所以需要通过foreign_keys指定哪个外键对应哪个字段
    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

Base.metadata.create_all(engine)

数据增/删/改/查
import orm_many_fk
from sqlalchemy.orm import sessionmaker

# 这里统一实现增/删/改/查
# 新增数据
Session_class = sessionmaker(bind=orm_many_fk.engine)
Session = Session_class()

# addr1 = orm_many_fk.Address(street='changyang',city='fangshan',state='BJ')
# addr2 = orm_many_fk.Address(street='wudaokou',city='haidian',state='BJ')
# addr3 = orm_many_fk.Address(street='wangjing',city='chaoyang',state='BJ')
# Session.add_all([addr1,addr2,addr3])
# # 注意这里使用的是关联关系变量billing_address，并没有使用外键变量
# c1 = orm_many_fk.Customer(name='jack',billing_address=addr1,shipping_address=addr2)
# c2 = orm_many_fk.Customer(name='vivi',billing_address=addr3,shipping_address=addr3)
# Session.add_all([c1,c2])
# Session.commit()

# 查询
obj = Session.query(orm_many_fk.Customer).filter(orm_many_fk.Customer.name=='jack').first()
print(obj.name,obj.billing_address,obj.shipping_address)

多对多关联

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer,DATE,ForeignKey,Table
from sqlalchemy.orm import sessionmaker,relationship
# 注意这里使用charset=utf8 可以实现支持中文
engine = create_engine('mysql+pymysql://root:123@127.0.0.1/dbname?charset=utf8',encoding='utf-8')
Base = declarative_base()
# 这里使用Table创建的这个表，因为是由ORM自动管理，所以不需要使用类来做映射关系，
book_m2m_author = Table('book_m2m_author',Base.metadata,
    # book_id 关联Book下的id
    Column('book_id',Integer,ForeignKey('book.id')),
    # author_id 关联Author下的id
    Column('author_id',Integer,ForeignKey('author.id'))
)

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    # 连接Author表，book_m2m_author表实现book和author表的关联关系，在本表中通过authors字段提取Author表数据，反向author表通过books反向查询book表数据
    authors = relationship('Author',secondary=book_m2m_author,backref='books')
    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    def __repr__(self):
        return self.name
Base.metadata.create_all(engine) # 创建表结构


Session_class = sessionmaker(bind=engine)
Session = Session_class()
# 创建数据
# b1 = Book(name='python',pub_date='2017-06-29')
# b2 = Book(name='go',pub_date='2017-04-29')
# b3 = Book(name='java',pub_date='2017-02-23')
# a1 = Author(name='jack')
# a2 = Author(name='rain')
# a3 = Author(name='bard')
# b1.authors = [a1,a3]
# b3.authors = [a1,a2,a3]
# Session.add_all([b1,b2,b3,a1,a2,a3])
# Session.commit()

# 查询
# author_obj = Session.query(Author).filter(Author.name=='jack').first()
# print(author_obj)
# print(author_obj.books)
# book_obj = Session.query(Book).filter(Book.id == 2).first()
# print(book_obj)
# print(book_obj.authors)

# 删除book_obj的其中一个作者author_obj
# book_obj.authors.remove(author_obj)
# Session.commit()
# 直接删除作者，会把这个作者跟所有书的关联关系数据也自动删除
# author_obj = Session.query(Author).filter(Author.name == 'bard').first()
# print(author_obj)
# Session.delete(author_obj)
# Session.commit()
# 直接删除书，所有作者的关联关系也都删除了
# book_obj = Session.query(Book).filter(Book.name == 'python').first()
# print(book_obj)
# Session.delete(book_obj)
# Session.commit()

# 另小提示 --》支持中文
# 参数：charset=utf8
# 在数据库连接串末尾添加：charset=utf8
b1 = Book(name='python核心编程',pub_date='2017-05-05')
Session.add(b1)
Session.commit()