Python-操作MySQL

最新推荐文章于 2024-11-11 09:34:14 发布

「已注销」

最新推荐文章于 2024-11-11 09:34:14 发布

阅读量257

点赞数

文章标签： mysql python 数据库

BuGu

本文链接：https://blog.csdn.net/weixin_51800059/article/details/122420140

版权

这篇博客详细介绍了如何使用Python的pymysql库操作MySQL数据库，包括下载pymysql库、导入库、连接数据库、创建表、插入数据、修改数据、删除数据以及查询和删除表的操作步骤和示例。

摘要由CSDN通过智能技术生成

1. 下载pymysql

pip install pymysql

2. 导入库

import pymysql

3. 方法封装

(1) 连接数据库

def getConnect(HOST, USER, DB_PASSWD, DB_NAME):
    try:
        connect = pymysql.connect(host=HOST, user=USER, password=DB_PASSWD, database=DB_NAME)
        print('连接成功！')
        return connect
    except pymysql.Error as e:
        print("连接失败：" + str(e))

(2) 创建一个表

def createTable(cur, sql1, sql2):
    try:
        cur.execute(sql1
        cur.execute(sql2) 
        print('表创建成功！')
    except pymysql.Error as e:
        print("表创建失败：" + str(e))

(3) 插入数据

def insertData(cur, connect, sql, value):
    try:
        cur.execute(sql, value)
        connect.commit()
        print('数据插入成功！')
    except pymysql.Error as e:
        print("数据插入失败：" + str(e))
        connect.rollback()

(4) 修改数据

def updateData(cur, connect, sql, value):
    try:
        cur.execute(sql, value)
        connect.commit()
        print('数据更新成功！')
    except pymysql.Error as e:
        print("数据更新失败：" + str(e))
        connect.rollback()

(5) 删除表中的数据

def deleteData(cur, connect, sql, value):
    try:
        cur.execute(sql, value)
        connect.commit()
        print('数据删除成功！')
    except pymysql.Error as e:
        print("数据删除失败："+ str(e))
        connect.rollback()

(6)查询数据【仅针对我举例的’Stu’表】

def queryData(cur, sql):
    try:
        cur.execute(sql)
        results = cur.fetchall()
        for row in results:
            name = row[0]
            num = row[1]
            sex = row[2]
            print('Name:%s \n num:%s \n SEX:%s' % (name, num, sex))
    except pymysql.Error as e:
        print("数据查询失败：" + str(e))

(7) 删除一张表

def deleteTable(cur, sql):
    try:
        cur.execute(sql)
        print('表删除成功!')
    except pymysql.Error as e:
        print("表删除失败：" + str(e))

4. 使用举例

import pymysql

# 连接数据库
def getConnect(HOST, USER, DB_PASSWD, DB_NAME):
    try:
        connect = pymysql.connect(host=HOST, user=USER, password=DB_PASSWD, database=DB_NAME)
        print('连接成功！')
        return connect
    except pymysql.Error as e:
        print("连接失败：" + str(e))


# 创建一个表
def createTable(cur, sql1, sql2):
    try:
        cur.execute(sql1)
        cur.execute(sql2)
        print('表创建成功！')
    except pymysql.Error as e:
        print("表创建失败：" + str(e))


# 插入数据
def insertData(cur, connect, sql, value):
    try:
        cur.execute(sql, value)
        connect.commit()
        print('数据插入成功！')
    except pymysql.Error as e:
        print("数据插入失败：" + str(e))
        connect.rollback()


# 删除表中的数据
def deleteData(cur, connect, sql, value):
    try:
        cur.execute(sql, value)
        connect.commit()
        print('数据删除成功！')
    except pymysql.Error as e:
        print("数据删除失败："+ str(e))
        connect.rollback()


# 更新表中的数据
def updateData(cur, connect, sql, value):
    try:
        cur.execute(sql, value)
        connect.commit()
        print('数据更新成功！')
    except pymysql.Error as e:
        print("数据更新失败：" + str(e))
        connect.rollback()


# 查询表中的数据
def queryData(cur, sql):
    try:
        cur.execute(sql)
        results = cur.fetchall()
        for row in results:
            name = row[0]
            num = row[1]
            sex = row[2]
            print('Name:%s  num:%s  SEX:%s' % (name, num, sex))
    except pymysql.Error as e:
        print("数据查询失败：" + str(e))


# 删除一张表
def deleteTable(cur, sql):
    try:
        cur.execute(sql)
        print('表删除成功!')
    except pymysql.Error as e:
        print("表删除失败：" + str(e))


def main():
    connect = getConnect('localhost', 'root', 'root', 'pdsu')
    cur = connect.cursor()
    createTable(cur, 'DROP TABLE IF EXISTS Stu', 'CREATE TABLE Stu(Name VARCHAR(20), Num VARCHAR(20), SEX VARCHAR(20))')
    insertData(cur, connect, 'INSERT INTO Stu(Name, Num, Sex) VALUES(%s, %s, %s)', ('张三', '001', '男'))
    updateData(cur, connect, 'UPDATE Stu SET NUM=%s WHERE NAME=%s', ('999', '张三'))
    queryData(cur, 'SELECT * FROM Stu')
    deleteData(cur, connect, 'DELETE FROM Stu WHERE NAME=%s', ('张三'))
    deleteTable(cur, 'DROP TABLE IF EXISTS Stu')


if __name__ == '__main__':
    main()
    
    
# 结果：
'''
连接成功！
表创建成功！
数据插入成功！
数据更新成功！
Name:张三  num:999  SEX:男
数据删除成功！
表删除成功!
'''