操作MySQL数据库的几种方法

前言

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

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

PyMySQL模块

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

安装

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

1	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。本文所有方法基于此版本总结。

1	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+mysqlconnector://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()

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

总结

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

前言

PyMySQL模块

安装

要求

基础用法

创建表

增

删

改

查

SQLAlchemy模块

介绍

原理

安装

用法

表映射

外键

增

删

改

查

总结

FEATURED TAGS

FRIENDS