操作 MySQL 数据库的几种方法

Question

本文主要总结我实际使用 python 操作 MySQL 数据库用到的几种方法，希望能对你有所帮助。在学习后面实例代码前。首先对数据库需要有一定的了解，而且本地需要安装 MySQL 数据库，并设置好 MySQL 账户和密码。

数据库主要分为关系型和非关系型数据库。MySQL 属于关系型数据库库。

PyMySQL 模块

首先总结 pymysql 模块所常用到的一些方法，它主要用来操作 MySQL 数据库。如需查看官方文档，请点击 这里 。

安装

推荐使用 pip 方式安装，最新版本为 0.9.2。本文方法基于此版本总结。

pip3 install pymysql

要求

python2 版本要求 2.7 及以上，python3 版本要求 3.5 及以上。

MySQL 版本>=5.5，MariaDB 版本>=5.5。

基础用法

我们对数据的操作基本可以概括为增、删、改、查这几个方面。所以 pymysql 用法也主要是以这 4 个方面为主。

下面的实例代码为使用 pymysql 库初始连接对象，定义游标和执行 sql 命令调用的相关方法。

注意：每次执行数据库操作时，最好先初始化数据库连接，同时数据库操作完毕之后，关闭连接。因为数据库连接如果长时间不操作的话，会自动终止连接。

import pymysql
CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'passwd': '',
    'db': 'test',
    'charset': 'utf8',
}
def init_connect(**kargs):
    # 初始化数据库连接，依次传入数据地址，数据库账户，密码，数据库名
	db=pymysql.connect(**kargs)
	# 定义游标
	cursor=db.cursor()
    # 返回元祖
	return db,cursor

创建表

创建一个 classes 表

sql="""CREATE TABLE classes (
           id INT(10) NOT NULL AUTO_INCREMENT,
           name CHAR(20) NOT NULL,
           PRIMARY KEY(id)
	    ) DEFAULT CHARSET=utf8"""
try:
    cursor.execute(sql)
except:
    print('create table failed')

创建一个 students 表

sql="""CREATE TABLE students (
		 id INT(10) NOT NULL AUTO_INCREMENT,
         name  CHAR(20) NOT NULL,
         age INT(4),
         gender CHAR(1),
         class_id  INT(10),
         FOREIGN KEY(class_id) REFERENCES classes(id),
         PRIMARY KEY (id)
        ) DEFAULT CHARSET=utf8"""
try:
    cursor.execute(sql)
except:
    print('create table failed')

增

增，增加 MySQL 数据库中的数据表的记录，也就是添加数据。对应的 SQL 命令是 INSERT

# 初始化数据库连接
function=init_connect(**CONFIG)
db=function[0]
cursor=function[1]
sql = """INSERT INTO STUDENTS(NAME, AGE, SEX, CLASS) VALUES ('%s', %s, '%s', '%s')" % ('xiaoming', 20, 'M', 'class1')"""
try:
   # 执行 sql 语句
   cursor.execute(sql)
   # 提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

删

删，删除数据表中的记录，也就是删除数据。对应 SQL 命令是 DELETE

# 初始化数据库连接
function=init_connect(**CONFIG)
db=function[0]
cursor=function[1]
# SQL 删除语句
sql = "DELETE FROM STUDENTS WHERE AGE > %s" % (20)
try:
   # 执行 SQL 语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭连接
db.close()

改

改，修改数据表中的记录，也就是更新数据。对应的 SQL 命令是 UPDATE

# 初始化数据库连接
function=init_connect(**CONFIG)
db=function[0]
cursor=function[1]
sql="UPDATE students SET AGE = 19 WHERE NAME='xiaoming'"
try:
    # 执行 sql
    cursor.execute(sql)
    # 提交
    db.commit()
except:
    db.rollback()
db.close()

查

查，查询数据表中的记录。对应的 SQL 命令是 SELECT。如果使用默认的 cursorclass 查询数据表记录，默认返回的数据类型为元祖类型。

• fetchall() 返回所有查询的所有记录
• fetchone() 返回单条记录
• rawcount() 返回执行 execute() 方法影响的命令行

# 初始化数据库连接，cursorclass 设置后将返回字典格式的数据。
# 如果 cursorclass 使用默认设置，查询返回的数据类型为元组类型
CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': '',
    'db': 'test',
    'charset': 'utf8',
    'cursorclass': pymysql.cursors.DictCursor
}
db=pymysql.connect(**CONFIG)
cursor=db.cursor()
sql="SELECT * FROM students WHERE CLASS="class1"
try:
    # 执行 sql 命令
    cursor.execute(sql)
    # 获取所有记录列表，字典类型的数据
    results=cursor.fetchall()
    # 根据需要处理数据，这里只是打印
    for i in results：
    	print(i)
except:
    print('Error')

SQLAlchemy 模块

介绍

SQLAlchemy 库是 python 中著名的 ORM(Object Relationship Mapping) 框架。在学习这个库之前，先要学习并理解什么是 ORM 框架。ORM 全称对象关系映射，把关系型数据库的表结构映射到对象上。谁来做映射这个动作呢？没错，就是 ORM 框架，也就是本文中的 SQLAlchemy 模块。

简而言之，ORM 就是把数据库表的行与相应的对象建立关联，互相转换。

该模块支持 PostgreSQL | MySQL | SQLite | Oracle | Microsoft SQL Server 等数据库，一般我们常用的是 MySQL 数据库。

原理

主要应用了 metaclass 元类这一强大的功能，对于我们来说基本上很难用上元类这个功能，所以以下这些理解即可。什么叫元类？我们知道实例对象根据定义的类生成，而类则可以根据元类生成。你也理解成类是根据 metaclass 创建的”实例对象”。如果你想自己写一个简单 ORM 框架，那么元类可以去深入学习。

安装

sqlalchemy 版本为: 1.2.14。本文所有方法基于此版本总结。

pip3 install sqlalchemy

用法

同样的 SQLAlchemy 用于操作数据库能够实现增、删、改、查等几个动作。它相比 pymysql 更加抽象，但也更加易用。常用于 web 应用的后端开发。

舒适化

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

# 生成基类
Base=declarative_base()
# 创建数据库连接引擎
engine=create_engine("mysql+mysql  connector://root:@localhost:3306/test", encoding='utf-8', echo=True)
# 创建会话类
DBSession=sessionmaker(bind=engine)

表映射

首先我们需要在数据库 test 中创建两张表，以下使用 sql 语言创建了 2 张表，并插入了一些数据记录。这里解释一下主键，主键一般命名为 id，它唯一标识了一条数据记录。

CREATE DATABASE IF NOT EXISTS test;
-- 切换 test 数据库
USE test;
-- 如果存在 phone 和 company 表，则删除
DROP TABLE phone IF EXISTS;
DROP TABLE company IF EXISTS;
-- 创建 company 表
CREATE TABLE company (
	id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    country VARCHAR(20) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 创建 phone 表
CREATE TABLE phone (
 	id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    price BIGINT NOT NULL,
    company_name VARCHAR(20) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 插入数据记录
INSERT INTO company(id, name, country) VALUES (1, 'Apple', 'America');
INSERT INTO company(id, name, country) VALUES (2, 'Samsung', 'Korea');
INSERT INTO company(id, name, country) VALUES (3, 'Huawei', 'China');
INSERT INTO company(id, name, country) VALUES (4, 'Xiaomi', 'China');
INSERT INTO company(id, name, country) VALUES (5, 'Vivo', 'China');

INSERT INTO phone(id, name, price, company_name) VALUES (1, 'CC9', 1999, 'Xiaomi')
INSERT INTO phone(id, name, price, company_name) VALUES (1, 'Xiaomi9', 2999, 'Xiaomi')
-- ok
SELECT 'OK' AS 'Result:';

将数据库中的表映射成类

class Company(Base):
    # 表的名字
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    country = Column(String(20))
    
class Phone(Base):
    # 表的名字
    __tablename__='phone'
	# 定义表的结构
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    price = Column(Integer)
    company_name = Column(String(20))

外键

关系数据库的多个表可以用外键实现一对多、多对多等关联。但使用外键会降低数据库的查询性能。

例如，如果一个 User 拥有多个 Book，就可以定义一对多关系如下：

class User(Base):
    __tablename__ = 'user'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # 一对多:
    books = relationship('Book')

class Book(Base):
    __tablename__ = 'book'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # “多”的一方的 book 表是通过外键关联到 user 表的:
    user_id = Column(String(20), ForeignKey('user.id'))

增

增，即添加数据。数据库表中添加一条记录，使用 ORM 框架后可以视为添加一个 phone 对象。

关键是获取 session，然后把对象添加到 session，最后提交并关闭。 DBSession 对象可视为当前数据库连接。

# 创建 session 对象
session=DBSession()
# 创建 phone 对象
iphoneX=Phone(id=2, name='iPhone X', price=8388, company_name='Apple')
P30Pro=Phone(id=3, name='P30 Pro', price=5488, company_name='Huawei')
S10=Phone(name='Galaxy S10', price=5999, company_name='Samsung')
Z5x=Phone(name='Vivo Z5x', price=1599, company_name='vivo')
Neo=Phone(name='IQOO Neo', price=2499,company_name='vivo')
# 逐条添加到 session
session.add(iphoneX)
# 通过 list 添加多个对象到 session 中,传入对象 list
session.add_all([P30Pro,S10,Z5x,Neo])
# 提交到数据库中
session.commit()
# 关闭
session.close()

删

删，即删除数据。在删除数据前，首先需要查找到该数据。例如：删除 Phone 表中，属于 Vivo 公司手机的数据。

# 创建 session 对象
session=DBSession()
# 条件查询数据
phone = session.query(Phone).filter_by(company_name='Vivo').all()
# 调用 delete 方法删除
for i in phone_list:
    session.delete(i)
# 关闭当前数据库连接
session.close()

改

改，即修改数据。同样的需要查找到该数据，然后再进行修改。

# 创建 session 对象
session=DBSession()
# 创建公司对象
sony=Company(name='suoni',country='Japan')
# 添加
session.add(sony)
session.commit()
# 查询数据,修改数据
company=session.query(Company).filter(name=='suoni').first()
company.name='sony'
# 提交修改
session.commit()

查

查，即查询数据。

查询数据过滤有两种方法，两者区别如下:

• filter_by() column=数据，过滤，使用起来比较方便
• filter() 稍微复杂，但能实现更多复杂的查询操作

# 创建 Query 查询，filter 是 where 条件，最后调用 one() 返回唯一行,Phone 对象:
phone1 = session.query(Phone).filter(Phone.id=='2').one()
print('type:',type(phone))
print('Phone Name:', phone.name)
# 调用 all() 返回所有行，此时返回数据为对象的 list。
phone_list = session.query(Phone).filter(Phone.company_name == 'Apple').all()
print('type',type(phone_list))
for i in phone_list:
    print('Apple 品牌:', i.name)
# 根据 id 查询，查询 id=2 的数据记录
phone2 = session.query(Phone).get(2)
print(phone2.name)
# 打印查询 sql 语句
phone_sql = session.query(Phone).filter(Phone.id=='1')
print(phone_sql)

filter() 过滤几种方法

# like 查询
phone_list1 = session.query(Phone).filter(Phone.name.like("%Phone%")).all()
# not equals 查询
phone_list2 = session.query(Phone).filter(Phone.company_name != 'Apple').all()
# in 查询
phone_list3 = session.query(Phone).filter(Phone.id.in_(['1', '2']))
# not in 查询
phone_list4 = session.query(Phone).filter(~Phone.id.in_(['1', '2']))
# and 查询
from sqlalchemy import and_
phone_list5 = session.query(Phone).filter(and_(Phone.company_name=='Xiaomi', Phone.price==2999)).all()
phone_list5 = session.query(Phone).filter(Phone.company_name=='Xiaomi', Phone.price==2999).all()
# or 查询
from sqlalchemy import or_
phone_list6 = session.query(Company).filter(or_(Company.country=='China', Company.country=='America')).all

计数方法:count()

num = session.query(Phone).filter(Phone.name.like('%ed')).count()

总结

以上大致总结了一些比较常用的方法。如果你需要使用的方法，这里没有总结。可以去查看官方文档和其他博客文章。