第三节 PyMySQL的使用

　　　　执行SQL语句：

import pymysql

# 创建连接
conn = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    passwd="",
    db="test",
    charset="utf8"
)

# 创建游标
cursor = conn.cursor()
# 执行sql语句,并返回影响的行数
# effect_row = cursor.execute("select * from tb1")
# fetchall()获取所有的数据 fetchmany(n)指定数据个数 fetchone()只拿一个数据
# print(cursor.fetchall())

# 插入操作
# effect_row = cursor.execute("insert into tb1(name,email,extra) values(%s,%s,%s)",("bbb","bbb@136.com","2",))
# print(effect_row)
# 插入多条数据
effect_row = cursor.executemany("insert into tb1(name,email,extra) values(%s,%s,%s)",[("ccc","ccc@136.com","2"),("ddd","ddd@136.com","3"),("eee","eee@136.com","4")])
print(effect_row)
# 提交,不然无法保存新建或者修改的数据
conn.commit()

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

# 获取自增id
new_id = cursor.lastrowid
print(new_id)

　　　　移动游标：在fetch数据时按照顺序进行，可以使用cursor.scroll（num，mode）来移动游标位置，如：

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

 

　　　　fetch数据：我们在获取数据的时候，默认是元组类型的，可以将其转为字典类型

# !/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    passwd="",
    db="test",
    charset="utf8"
)
# cursor=pymysql.cursors.DictCursor 将游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
effect_rows = cursor.execute("select * from tb")

print(cursor.fetchall())
conn.commit()
cursor.close()
conn.close()

 　　　　调用存储过程:

import pymysql
# 创建连接
conn = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    passwd="",
    db="test",
    charset="utf8"
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 无参存储过程
# cursor.callproc("p1")  # 等价于cursor.execute("call p1()")
# 有参存储过程
cursor.callproc("p2",args=(1,22,3,4))
# 这次获取的查询的数据
print(cursor.fetchall())
# 获取执行完存储的参数,参数@开头
cursor.execute("select @p2,@_p2_1,@_p2_2,@_p2_3")
# 这次获取的是返回值
print(cursor.fetchall())

conn.commit()
cursor.close()
conn.close()

　　　　使用MySQL存储过程自动提供防注入,动态传入SQL到存储过程执行语句:

-- 存储过程
delimiter //
DROP PROCEDURE IF EXISTS proc_sql//
CREATE PROCEDURE proc_sql(
    in nid1 INT,
    in nid2 INT,
    in callsql VARCHAR(255)
)

BEGIN
    set @nid1 = nid1;
    set @nid2 = nid2;
    set @callsql = callsql;
        prepare myprod from @callsql;
-- prepare prod from "select * from tb where nid > ? and nid < ?" 传入值为字符串,?为占位符
-- 用@p1和@p2填充占位符
        EXECUTE myprod USING @nid1,@nid2;
    deallocate PREPARE myprod;
END //

delimiter ;
set @nid1 = 2;
set @nid2 = 6;
set @callsql = "select * from tb where nid > ? and nid < ?";

CALL proc_sql(@nid1,@nid2,@callsql)

tb表数据:

-- ----------------------------
-- Table structure for tb
-- ----------------------------
DROP TABLE IF EXISTS `tb`;
CREATE TABLE `tb` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tb
-- ----------------------------
INSERT INTO `tb` VALUES ('1', 'aaa', 'aaa@qq.com');
INSERT INTO `tb` VALUES ('2', 'bbb', 'bbb@qq.com');
INSERT INTO `tb` VALUES ('3', 'ccc', 'ccc@qq.com');
INSERT INTO `tb` VALUES ('4', 'ddd', 'ddd@qq.com');
INSERT INTO `tb` VALUES ('5', 'eee', 'eee@qq.com');
INSERT INTO `tb` VALUES ('6', 'fff', 'fff@qq.com');
INSERT INTO `tb` VALUES ('7', 'ggg', 'ggg@qq.com');

通过pymysql来调用:

import pymysql
# 创建连接
conn = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    passwd="",
    db="test",
    charset="utf8"
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
mysql = "select * from tb where nid > ? and nid < ?"
cursor.callproc("proc_sql",args=(2,6,mysql))

print(cursor.fetchall())

conn.commit()
cursor.close()
conn.close()

　　　　使用with简化连接过程：我们在每次连接MySQL数据库的时候，都需要手动关闭，这样就显得很麻烦，可以使用上下问管理，简化连接过程

import pymysql,contextlib

# 定义上下文管理器,连接后自动关闭
@contextlib.contextmanager
def mysql(host="localhost",port=3306,user="root",passwd="",db="test",charset="utf8"):
    conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db,charset=charset)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    try:
        yield cursor
    finally:
        conn.commit()
        cursor.close()
        conn.close()

# 执行sql
with mysql() as cursor:
    cursor.execute("select * from tb")
    print(cursor.fetchall())

　　　　SQLAchemy：是一款ORM框架，那么什么是ORM框架呢？一种对象映射关系程序，简单来说类似Python这种面向对象程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过ORM将将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不直接使用sql语言

　　　　ORM相当于把数据库也给实例化了，在代码操作MySQL中又加了ORM这一层

　　　　ORM优点：

　　　　　　a：隐藏了数据访问细节，“封闭”的通用数据库交互，快速开发

　　　　　　b：使我们构造固化数据结构变得简单易行

　　　　缺点：无可避免的，自动化意味这映射和管理管理，代价是牺牲性能

　　　　SQLAchemy本身无法操作数据库，其必须以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

 

　　　　①基本使用

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

# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:@localhost/test",
                       encoding="utf-8",echo=True)
# 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))

# 创建表结构(这里是父类调用子类)
Base.metadata.create_all(engine)

使用实例：

# !/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import Table,MetaData,Column,Integer,String
from sqlalchemy.orm import mapper,sessionmaker
from sqlalchemy import create_engine
from sqlalchemy import func
# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:@localhost/test",
                       encoding="utf-8",echo=True)
metadata = MetaData()
user = Table("user",metadata,
             Column("id",Integer,primary_key=True),
             Column("name",String(50)),
             Column("password",String(12)))

class User:
    def __init__(self,id,name,password):
        self.name = name
        self.id = id
        self.password = password

    def __repr__(self):
        '''
        重写这个方法,方便查询
        :return:
        '''
        return "<User(name='%s',password='%s')>"%(self.name,self.password)

# 将User类和user表关联起来
# the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function
mapper(User,user)

# 创建于数据库会话的session class,注意,这里返回给session的是个class,不是实例
Session_class = sessionmaker(bind=engine)   # 实例和engine绑定
# 生成session实例,相当于游标
Session = Session_class()

'''插入开始'''
# 生成要创建的数据对象
# user_obj = User(id=27,name="Jasonhy",password="123")
# user_obj = User(id=28,name="Jason",password="123")

# 把要创建的数据对象添加到这个session里,一会统一创建
# Session.add(user_obj)   # 这个时候还没创建

# 现在统一提交,创建数据
# Session.commit()    # 这个时候将会将要添加的数据添加到表中
'''插入结束'''

'''查询开始'''
my_user = Session.query(User).filter_by(name="Jasonhy").first()
print(my_user)
# 查询所有
my_user = Session.query(User).filter_by().all()
print(my_user)
# filter 和 filter_by
my_user1 = Session.query(User).filter(User.id == 27).all()
my_user2 = Session.query(User).filter_by(id=27).all()
print(my_user1,my_user2)
# 多条件查询
my_user3 = Session.query(User).filter(User.id == 28).filter(User.name == "Jason").all()     # 相当于and
print(my_user3)

'''查询结束'''

'''修改开始'''
# my_user = Session.query(User).filter_by(id=27).first()
# 查询出来直接赋值修改
# my_user.name = "lhy"
# my_user.password = "456"
# Session.commit()
'''修改结束'''

'''回滚开始'''
# my_user = Session.query(User).filter_by(id=27).first()
# my_user.name = "Jack"
# fake_user = User(id=29,name="Rain",password="123")
# Session.add(fake_user)
# # 回滚
# Session.rollback()  # 我们发现数据并没有修改 也没有添加新的数据
# Session.commit()
'''回滚结束'''

'''count开始'''
my_user = Session.query(User).filter(User.name.like("J%")).count()
print(my_user)
'''count结束'''

'''分组group_by开始'''
print(Session.query(User.name,func.count(User.name)).group_by(User.name).all())
'''分组group_by结束'''

 

　　　　②关联

# 创建表
# !/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import Enum
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker

# 创建实例
engine = create_engine("mysql+pymysql://root:@localhost/test",
                       encoding="utf-8",
                       echo=True)
# 生成ORM基类
Base = declarative_base()

class Student(Base):
    __tablename__="student"
    id = Column(Integer,primary_key=True,autoincrement=True)
    stu_id = Column(Integer)
    age = Column(Integer)
    gender = Column(Enum("M","F"),nullable=False)

# 创建表结构
Base.metadata.create_all(engine)

# 创建数据库会话
Session_class = sessionmaker(bind=engine)
# 生成实例
Session = Session_class()

stu_obj = Student(stu_id=27,age=22,gender="M")
Session.add(stu_obj)
Session.commit()
连表:

# !/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Enum
from sqlalchemy.orm import sessionmaker
# 创建实例，并连接test库
engine = create_engine("mysql+pymysql://root:@localhost/test",
                                    encoding='utf-8')
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, autoincrement=True)
    stu_id = Column(Integer)
    age = Column(Integer)  
    gender = Column(Enum('M','F'),nullable=False)
    def __repr__(self):
        return "[%s stu_id:%s sex:%s]" %(self.stu_id, self.age, self.gender)

Session_class = sessionmaker(bind=engine)
Session = Session_class()  # 生成session实例

res = Session.query(User, Student).filter(User.id == Student.stu_id).all()
print(res)

外键关联

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

engine = create_engine("mysql+pymysql://root:@localhost/test",
                                    encoding='utf-8')
Base = declarative_base()  # 生成orm基类

class Stu2(Base):
    __tablename__ = "stu2"
    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("stu2.id"))  #------外键关联------
    #这个nb，允许你在user表里通过backref字段反向查出所有它在stu2表里的关联项数据
    stu2 = relationship("Stu2", backref="my_study_record")  # 添加关系，反查（在内存里）
    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.stu2.name, self.day,self.status)

Base.metadata.create_all(engine)  # 创建表结构

Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = Session_class()  # 生成session实例 #cursor

s1 = Stu2(name="A",register_date="2014-05-21")
s2 = Stu2(name="J",register_date="2014-03-21")
s3 = Stu2(name="R",register_date="2014-02-21")
s4 = Stu2(name="E",register_date="2013-01-21")

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

stu_obj = session.query(Stu2).filter(Stu2.name=="a").first()  # 查询
# 在stu2表，查到StudyRecord表的记录
print(stu_obj.my_study_record)  # 查询A一共上了几节课

多外键关联

# 文件名 orm_many_fk.py
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine

Base = declarative_base()

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

    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

engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8')
Base.metadata.create_all(engine)  # 创建表结构

正常写的时候，表结构单独写一个模块，添加数据:

import orm_many_fk
from sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=orm_many_fk.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = Session_class()  # 生成session实例 #cursor

addr1 = orm_many_fk.Address(street="Tiantongyuan", city="ChangPing", state="BJ")
addr2 = orm_many_fk.Address(street="Wudaokou", city="Haidian", state="BJ")
addr3 = orm_many_fk.Address(street="Yanjiao", city="LangFang", state="HB")

session.add_all([addr1,addr2,addr3])
c1 =  orm_many_fk.Customer(name="Fgf", billing_address= addr1,shipping_address=addr2)
c2 =  orm_many_fk.Customer(name="Jack", 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=="Fgf").first()
print(obj.name,obj.billing_address,obj.shipping_address)  # 查询

来自：http://blog.csdn.net/fgf00/article/details/52949973