Python Mysql和SQLalchemy

Python操作mysql主要有两种使用方式：

• 原生模块pymysql
• ORM框架SQLAlchemy

pymysql模块

pymysql的操作方式和mysql 的操作方式差不多，简单的介绍一下它的增删改查

import pymysql

# 创建连接
conn = pymysql.connect(host='localhost', port=3306, user='root', 
                       passwd='123456',db='testdb' )
# 创建游标，游标相当于进入数据库后的命令行
cursor = conn.cursor()

# 查询student表中的所有数据，执行sql，并返回影响行数，
effect_row = cursor.execute("select * from student")
print(cursor.fetchall())  # 获取所有的数据
#print(cursor.fetchmany(3))  # 获取前3行数据
#print(cursor.fetchone())  # 获取第一行数据
# 添加数据
data = [
    ("N1", "16", "2019-05-30"),
    ("N2", '28', "2019-03-30"),
]
# 输入的data全部都要是字符窜，不用管原来的数据库中是什么格式
effect_row = cursor.executemany("insert into student (name,age,register_data) values (%s,%s,%s)", data )

# 删除数据
effect_row = cursor.execute("delete from student where id>7")

# 修改数据
effect_row = cursor.execute("update student set name='TFboy',age=3 where id=5")

 # 默认开启了事物，所以必须要提交，否则数据不上传
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

依次执行上面的命令，通过cmd进去数据库就可以查看到相应的结果，下面显示修改前后的运行结果：

更多关于mysql的操作，可以猛戳这里

SQLAlchemy模块

ORM

在介绍SQLAlchemy之前，先介绍一下ORM。

ORM英文全称object relational mapping，即对象关系映射，是一种程序技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。看下面这张图

由于Python这种面向对象的程序来说一切皆对象，所有的数据都可以看做对象型，而数据库的数据却是关系型。如果用Python直接对数据进行操作，要用很多繁琐的SQL语言操作，而且很多都是重复性的操作，就很麻烦。。。那有没有其他的办法呢，还真有。

ORM将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

ORM的优点：

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

缺点：

        无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架    都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。

SQLAlchemy的基本用法

在数据库testbd中新建一个user表，再向user表中插入数据，查询、统计分组等操作。

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:123456@localhost/testdb", encoding="utf-8", echo=True) # echo=True显示所有信息
engine = create_engine("mysql+pymysql://root:123456@localhost/testdb", encoding="utf-8")
Base = declarative_base() # 生成orm基类


class User(Base):

    __tablename__ = "user" # 表名
    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(String(32))
    password = Column(String(64))

    # 编译显示的name和password
    def __repr__(self):
        return "name='%s', password='%s'" % (self.name, self.password)

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

Session_class = sessionmaker(bind=engine)  # 创建于数据库之间的会话
Session = Session_class() # 生成Session实例
# 写入数据
# user_obj = User(name="jianghui", password="jh123456")
# user_obj2 = User(name="jack",password="123")
# print(user_obj.name,user_obj.password)
# Session.add(user_obj)
# Session.add(user_obj2)
# print(user_obj2.name,user_obj2.password)
# Session.commit()

# 查询
# my_user = Session.query(User).filter_by(name="jack").all()  # 查询里面所有name=jack的信息
# print(my_user)

# # 多条件查询
# my_user = Session.query(User).filter(User.id>3).filter(User.id<5).all()
# print(my_user)

# 统计和分组
num = Session.query(User).filter(User.name.like("ja%")).count()  # User.name.like("ja%") 模糊查询
print(num)
from sqlalchemy import func
print(Session.query(func.count(User.name),User.name).group_by(User.name).all())

运行结果：

多外键关联

# 多对多外键关联
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
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:123456@localhost/test?charset=utf8")  # 数据库实现中文的写入
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')

    def __repr__(self):
        return "name:%s, pub_data:%s" %(self.name, self.pub_date)


class Author(Base):
    __tablename__ = 'authors'
    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 class ,注意,这里返回给session的是个class,不是实例
session = Session_class()  #生成session实例
# # 插入数据
# b1 = Book(name="跟Jack学Python")
# b2 = Book(name="跟"Mike学把妹")
# b3 = Book(name="跟Jack学装逼")
# b4 = Book(name="跟Rain学开车")
#
# a1 = Author(name="Mike")
# a2 = Author(name="Jack")
# a3 = Author(name="Rain")
#
# b1.authors = [a1, a2]  # 自动关联到Author中作者相应的id
# b2.authors = [a1, a2, a3]
#
# session.add_all([b1, b2, b3, b4, a1, a2, a3])  # 写入所有数据


print('--------通过书表查关联的作者---------')
book_obj = session.query(Book).filter_by(name="跟Jack学Python").first()
print(book_obj.name, book_obj.authors)  # 通过关联键authors返回Author类中repr所返回的数据

print('--------通过作者表查关联的书---------')
author_obj = session.query(Author).filter_by(name="Mike").first()
print(author_obj.name, author_obj.books)   # 通过关联键books返回Book类中repr所返回的数据

session.commit()

运行结果：

--------通过书表查关联的作者---------
跟Alex学Python [Mike, Jack]
--------通过作者表查关联的书---------
Mike [name:跟Jack学Python, pub_data:None, name:跟Mike学把妹, pub_data:None]

返回的pub_data为空，是因为没有写入数据。