Python操作MySQL之原生pymysql模块和orm框架SQLAchemy

1、原生模块pymysql

1.下载安装

pip3 install pymysql

2.使用操作

执行sql

# Author: 73

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='seth', passwd='123456', db='test')
# 创建游标
cursor = conn.cursor()

# # 执行SQL，并返回收影响行数
# effect_row = cursor.execute("select * from stu")

#effect_row = cursor.execute("update study_record set status='YES' where id=%s", (1,))

# # 执行SQL，并返回收影响行数
#effect_row = cursor.execute("insert into study_record(day, status, stu_id) values(%s, %s, %s)", ((2,"YES",1,)))

#一次插入多条数据
data = [
    (2,"NO",2),
    (3,"YES",3),
    (4,"NO",1),

]
effect_row = cursor.executemany("insert into study_record(day, status, stu_id) values(%s, %s, %s)", data)

# 提交，不然无法保存新建或者修改的数据
conn.commit()

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

获取查询数据

# Author: 73

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='seth', passwd='123456', db='test')
# 创建游标
cursor = conn.cursor()

# # 执行SQL，并返回收影响行数
effect_row = cursor.execute("select * from stu")

# row1 = cursor.fetchone() # 取一条结果
# row2 = cursor.fetchmany(3) # 取多条数据
# row3 = cursor.fetchall() # 取所有结果，如果执行这条语句之前，执行了fetchone语句，那么只会得到剩余的结果

# 提交，不然无法保存新建或者修改的数据
conn.commit()

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

注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:

• cursor.scroll(1,mode=‘relative’) # 相对当前位置移动, 光标往后移一个
• cursor.scroll(2,mode=‘absolute’) # 相对绝对位置移动, 光标往前移两个

fetch数据类型
默认获取的数据是元祖类型，如果想要获取字典类型的数据，需要在创建游标时设置为字典类型

# Author: 73

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='seth', passwd='123456', db='test')
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# # 执行SQL，并返回收影响行数
effect_row = cursor.execute("select * from stu")

row1 = cursor.fetchone() # 取一条结果
print(row1)
# 提交，不然无法保存新建或者修改的数据
conn.commit()

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

2、ORM框架SQLAchemy

1.ORM介绍

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

优点：

• 隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句.
• ORM使我们构造固化数据结构变得简单易行.

缺点：

• 自动化意味着映射和关联管理，代价是牺牲性能。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。

2.sqlalchemy安装

pip3 install SQLAlchemy

3.sqlalchemy基本使用

根据配置文件的不同调用不同的数据库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

创建表

# Author: 73

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

engine = create_engine("mysql+pymysql://seth:123456@localhost/test", encoding="utf-8", echo=True) # echo是否打印运行过程

Base = declarative_base() # 生成orm基类

class User(Base):
    __tablename__ = 'user' # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(32))

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

创建一条数据

from sqlalchemy.orm import sessionmaker

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

user_obj = User(name="bob", password="123") #生成你要创建的数据对象
print(user_obj.name, user_obj.id) #此时还没创建对象呢，不信你打印一下id发现还是None

Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建
Session.commit() #现此才统一提交，创建数据

创建多条数据

Session.add_all([user1,user2...])

查询

data = Session.query(User).filter_by(name="bob").first()
print(data.name)

#data = Session.query(User).filter_by(name="bob").all()
#print(data[0].name)

#data = Session.query(User).filter(User.id>1).all()
#print(data.name)

返回的data是一个对象，通过data.name就可以看到数据了

如果想打印data时看到数据信息，可以在定义表的类下面加上这样的代码：

def __repr__(self):
    return "<%s name: %s>" % (self.id, self.name)

常用查询语法

equals:
     query.filter(User.name == 'ed')

not equals:
     query.filter(User.name != 'ed')

LIKE:
	query.filter(User.name.like('%ed%'))

IN:
NOT IN:
	query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

多条件查询

# 下面2个filter的关系相当于 user.id >1 AND user.id <7 的效果
data = Session.query(User).filter(User.id>2).filter(User.id<4).all()
print(data)

修改

data = Session.query(User).filter(User.id>2).filter(User.id<4).first()
print(data)
data.name = "RAIN"

删除

data = Session.query(User).filter_by(id=6).first()
print(data)
Session.delete(data)

Session.query(User).filter_by(id=6).delete()

回滚

user = User(name="fish", password="123")
Session.add(user)

print(Session.query(User).filter(User.name.in_(['jack','fish'])).all()) # 这时看session有你刚刚添加和修改的数据
Session.rollback()
print(Session.query(User).filter(User.name.in_(['jack', 'fish'])).all()) # 再查就发现刚才添加的数据没了

获取所有数据

print(Session.query(User.name,User.password).all())

统计分组

统计

print(Session.query(User.name,User.password).count())

print(Session.query(User).filter(User.name.like("Ra%")).count())

分组

print(Session.query(func.count(User.name),User.name).group_by(User.name).all())
'''
相当于原生sql
SELECT count(name) AS count_1, name AS user_name FROM user GROUP BY name;
'''

外键关联

创建一张study_record表和stu关联

import sqlalchemy
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
from sqlalchemy import func

engine = create_engine("mysql+pymysql://seth:123456@localhost/test", encoding="utf-8", echo=False) # echo是否打印运行过程

Base = declarative_base() # 生成orm基类

class StudyRecord(Base):
    __tablename__ = 'study_record' # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    day = Column(Integer, nullable=False)
    status = Column(String(32), nullable=False)
    stu_id = Column(Integer, ForeignKey("stu.id"),nullable=False) # 外键

    # 这个nb，允许你在stu表里通过backref字段反向查出所有它在study_record表里的关联项
    # 同时，study_record表可以通过syudent这个字段查询stu表的关联项
    student = relationship("Stu", backref="my_study_record")

    def __repr__(self):
        return "<%s day: %s status:%s>" % (self.student.name, self.day, self.status)

class Stu(Base):
    __tablename__ = 'stu'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(32), nullable=False)
    age = Column(Integer, nullable=False)
    register_date = Column(DATE,nullable=True)
    gender = Column(Enum('M','F'), default='M', nullable=True)

    def __repr__(self):
        return "<%s name: %s>" % (self.id, self.name)

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

通过stu表反查study_record表

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

ret = Session.query(StudyRecord).filter(Stu).all()
print(ret.my_study_record)

多外键关联

Customer表有2个字段都关联了Address表

# Author: 73

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 relationship

engine = create_engine("mysql+pymysql://seth:123456@localhost/test", encoding="utf-8", echo=False) # echo是否打印运行过程

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


    billing_address = relationship("Address")
    shipping_address = relationship("Address")


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))

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

创建表结构是没有问题的，但向Address表中插入数据时会报错，原因是没有说明哪个外键对应哪个字段，修改如下即可：

billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

测试代码：

# Author: 73

from day12 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实例

# 插入数据
# addr1 = orm_many_fk.Address(street="yinzhou", city="ningbo", state="ZJ")
# addr2 = orm_many_fk.Address(street="zhenghai", city="ningbo", state="ZJ")
# addr3 = orm_many_fk.Address(street="yuhuan", city="taizhou", state="ZJ")
#
# Session.add_all([addr1, addr2, addr3])
#
# costomer1 = orm_many_fk.Customer(name='seth', billing_address_id=1, shipping_address_id=2)
# costomer2 = orm_many_fk.Customer(name='jack', billing_address_id=3, shipping_address_id=3)
#
# Session.add_all([costomer1, costomer2])

# 查询
data = Session.query(orm_many_fk.Customer).filter(orm_many_fk.Customer.id==1).first()
print(data.name, data.billing_address,data.shipping_address)


Session.commit()

多对多关系

前面的例子都是一对多或者一对一的关系，那么如果是多对多，那么该怎么存储呢？

比如说一本书可以有很多个作者，一个作者也可以有很多本书。

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") # 通过第三张表book_m2m_author实现多对多的关联

    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

创建数据

b1 = orm_m2m_fk.Book(name="python basic", pub_date="2020-01-11")
b2 = orm_m2m_fk.Book(name="linux basic", pub_date="2010-04-12")
b3 = orm_m2m_fk.Book(name="java basic", pub_date="2018-03-21")

author1 = orm_m2m_fk.Author(name="seth")
author2 = orm_m2m_fk.Author(name="jack")
author3 = orm_m2m_fk.Author(name="tom")

b1.authors = [author1, author2]
b2.authors = [author1, author3]
b3.authors = [author1, author2, author3]

Session.add_all([b1,b2,b3,author1,author2,author3])

插入结果

多对多查询

print("通过书查作者")
book_obj = Session.query(orm_m2m_fk.Book).filter(orm_m2m_fk.Book.name=="python basic").first()
print(book_obj.name, book_obj.authors)

print("通过作者查书")
author_obj = Session.query(orm_m2m_fk.Author).filter(orm_m2m_fk.Author.name=="seth").first()
print(author_obj.name, author_obj.books)

result

多对多删除
删除数据时不用管book_m2m_author, sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

book_obj = Session.query(orm_m2m_fk.Book).filter(orm_m2m_fk.Book.name=="python basic").first()
author_obj = Session.query(orm_m2m_fk.Author).filter(orm_m2m_fk.Author.name=="seth").first()
book_obj.authors.remove(author_obj) # #从一本书里删除一个作者

直接删除作者

删除作者时，会把这个作者跟所有书的关联关系数据也自动删除

author_obj = Session.query(orm_m2m_fk.Author).filter(orm_m2m_fk.Author.name=="seth").first()
Session.delete(author_obj)