一文彻底搞懂Python操作MySQL数据库（附详细代码）

学渣的数据分析日常 2021-11-15

1490

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，本文的重点就是详细的讲解如何使用python操作MySQL数据库。

安装PyMySQL

pip install PyMySQL

基本操作

# 1. 导入相关库
import pymysql


# 2. 连接MySQL服务器
db = pymysql.connect(host='localhost', user='root', password='***', port=3306, db='dbtest', charset='utf8')


# 3. 创建一个cursor游标对象
cursor = db.cursor()


# 4. sql语句
sql = 'select version()'


# 5. 执行sql语句
cursor.execute(sql)


# 6. 断开连接
db.close()

可以看出，整个过程只有第四部的sql语句是需要根据实际情况进行编写的，其余步骤都是已经写死的，后续可以封装为一个类，方便重复调用。

六个常用的连接参数

db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='', charset='utf8')

host：数据库服务器所在的主机；
user：登录的用户名；
password：要使用的密码；
port：要使用的MySQL端口，默认通常都可以。（默认值：3306）；
db：要使用的数据库，None不使用特定的数据库；
charset：通信采用的编码方式，默认是'gb2312'，要求与数据库创建时指定的编码一致，否则中文会乱码；

cursor游标

游标对象：执行查询和获取结果，用于与数据库进行交互，

支持以下几种方法：

execute（query，args = None ） # 执行查询语句
executemany（query，args）：执行单条sql语句，但是重复执行参数列表里的参数，返回被引用的行数；
fetchone() # 返回一行结果
fetchall() # 返回所有查询结果
fetchmany(size=None) # 接收大小条返回结果行。如果size的值大于返回的结果行的数量，则返回cursor.arraysize条数据

Python增删改查操作

创建表

import pymysql
db = pymysql.connect(host='localhost', user='root', password='root123',
                     port=3306, db='dbtest', charset='utf8')


# 创建一个游标对象；
cursor = db.cursor()


# 建表语句；
sql = """create table person(
            id int auto_increment primary key not null,
            name varchar(10) not null,
            age int not null) charset=utf8"""


# 执行sql语句；
cursor.execute(sql)


# 断开数据库的连接；
db.close()

插入数据

注意：插入多条数据使用的是executemany()

import pymysql


db = pymysql.connect(host='localhost', user='root', password='root123',
                     port=3306, db='dbtest', charset='utf8')


cursor = db.cursor()


# 插入数据
sql = """insert into person(name, age) values (%s, %s)"""
datas = [('刘备', 62), ('诸葛亮', 53), ('关羽', 56), ('张飞', 53)]
try:
    cursor.executemany(sql, datas)
    db.commit()
    print("插入成功")
except:
    print("插入失败")
    db.rollback()
db.close()

更新数据

import pymysql


db = pymysql.connect(host='localhost', user='root', password='root123',
                     port=3306, db='dbtest', charset='utf8')
cursor = db.cursor()
sql = """update person set name = %s where age > %s"""
try:
    cursor.execute(sql, ('如来佛祖', 100))
    db.commit()
    print("更新成功")
except:
    print("更新失败")
    db.rollback()
db.close()

删除数据

importpymysql


db=pymysql.connect(host='localhost', user='root', password='root123',
                     port=3306, db='dbtest', charset='utf8')
cursor=db.cursor()
sql="""delete from person where age = 10000"""
try:
cursor.execute(sql)
db.commit()
print("删除成功")
except:
print("删除失败")
db.rollback()
db.close()

查询操作：

fetchone()：获取下一个查询结果
fetchall()：接收全部返回结果

import pymysql
  ...: 
  ...: db = pymysql.connect(host='localhost', user='root', password='root123',
  ...:                      port=3306, db='dbtest', charset='utf8')
  ...: cursor = db.cursor()
  ...: sql = """select name,age from person"""
  ...: cursor.execute(sql)
  ...: res = cursor.fetchall()
  ...: for n, a in res:
  ...:     c = '我的名字教{}，今年{}岁'.format(n, a)
  ...:     print(c)
  ...: db.close()
  ...: 
我的名字教刘备，今年62岁
我的名字教诸葛亮，今年53岁
我的名字教关羽，今年56岁
我的名字教张飞，今年53岁

封装类

注意：调用封装好的类：from classMySQL import DBHandler

import pymysql
from pymysql.cursors import DictCursor


class DBHandler(object):


# 实例化
def __init__(self,
                 host=None,      # 连接名
                 port=3306,      # 端口
                 user=None,      # 用户民
                 password=None,  # 密码
                 charset=None,
                 database=None,  # 数据库名
                 cursorclass=DictCursor,
                 **kwargs):
        self.connect = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            charset=charset,
            database=database,
            cursorclass=cursorclass,
            **kwargs
        )
# 创建游标对象
        self.cursor = self.connect.cursor()


def close(self):
"""
        关闭
        :return:
        """
        self.cursor.close()
        self.connect.close()


def get_one(self, query, args=None):
"""
        查询一条数据
        :param query: 执行sql语句
        :param args:  与查询语句一起传递的参数（给语句传参）包括：元组、列表、字典
        :return:      返回查询结果
        """
        self.cursor.execute(query, args)
        self.connect.commit()
return self.cursor.fetchone()


def get_all(self, query, args=None):
"""
        查询所有数据
        :param query: 执行sql语句
        :param args:  与查询语句一起传递的参数（给语句传参）包括：元组、列表、字典
        :return:      返回查询结果
        """
        self.cursor.execute(query, args)
        self.connect.commit()
return self.cursor.fetchall()


def query(self, query, args=None, one=True):
"""
        主体查询：判断查询一条数据还是查询所有数据
        :param query: 执行sql语句
        :param args:  与查询语句一起传递的参数（给语句传参）包括：元组、列表、字典
        :param one:   True：执行get_one，否则执行get_all
        :return:      返回查询结果
        """
if one:
return self.get_one(query, args)
return self.get_all(query, args)


def insert(self, sql, datas=None, one=True):
"""
        插入数据
        :param sql:   插入数据语句
        :param datas: 需要插入的数据（插入多条数据时使用）
        :param one:   True：插入一条数据(执行execute())，否则插入多条数据(执行executemany())
        :return:
        """
if one:
try:
                self.cursor.execute(sql)
                self.connect.commit()
                print('数据插入成功')
                self.close()


except:
                self.connect.rollback()
                print('数据插入失败')
                self.close()
else:
try:
                self.cursor.executemany(sql, datas)
                self.connect.commit()
                print('数据插入成功')
                self.close()
except:
                self.connect.rollback()
                print('数据插入失败')
                self.close()


def update(self, sql):
"""
        更新数据
        :param sql: 更新数据语句
        :return:
        """
        self.cursor.execute(sql)
        self.connect.commit()
        self.cursor.close()


def delet(self, sql):
"""
        删除数据
        :param sql: 删除数据语句
        :return:
        """
        self.cursor.execute(sql)
        self.connect.commit()
        self.cursor.close()

调用DBHandler 查询数据

from classMySQL import DBHandler
   ...: 
   ...: # 实例化DBHandler
   ...: db = DBHandler(
   ...:     host='localhost',
   ...:     port=3306,
   ...:     user='root',
   ...:     password='***',
   ...:     charset='utf8',
   ...:     database='DBsupermarket'
   ...: )
   ...: sql = 'SELECT * FROM singleBack LIMIT 3;'
   ...: res = db.query(sql, one=False)
   ...: 
res
Out[56]: 
[{'order_id': 'CN-2018-100602576', 'single_back': 1},
 {'order_id': 'CN-2018-102104829', 'single_back': 1},
 {'order_id': 'CN-2018-104201581', 'single_back': 1}]

mysql python

文章转载自学渣的数据分析日常，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

一文彻底搞懂Python操作MySQL数据库（附详细代码）

评论