python连接MySQL增删改查

首先安装pymysql

pip install pymysql

连接及增删改查代码如下

import pymysql

#连接数据库返回connect
def connect_DB():
    host = "localhost"
    user = ""
    password = ""
    database = "user" # 数据库名字不是表名
    try:
        connect = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
        )
        print("数据库连接成功")
        return  connect
    except:
        print("数据库连接失败")
        return False

def insert_DB():
    connect = connect_DB()
    if connect != False:
        cursor = connect.cursor()
        sql = "insert into student(id,name,age,sex) values (%s,%s,%s,%s)"
        # 该方式支持单个元组插入
        data = [
            (12, "test1", 19, "男"),
            (13, "test2", 20, "女"),
        ]
        try:
           cursor.executemany(sql, data)
           connect.commit()
           return True
        except:
           return False

def retrieve_DB():
    connect = connect_DB()
    if connect != False:
        cursor = connect.cursor()
        sql = "select * from student"
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            cursor.close()
            connect.close()
            return result
        except:
            return False

def update_Db():
    connect = connect_DB()
    if connect != False:
        cursor = connect.cursor()
        sql = "update student set age=19 where id=1"
        try:
            cursor.execute(sql)
            connect.commit()
            cursor.close()
            connect.close()
            return True
        except:
            return False

def delete_Db():
    connect = connect_DB()
    if connect != False:
        cursor = connect.cursor()
        sql = "delete from student where id=19"
        try:
            cursor.execute(sql)
            connect.commit()
            cursor.close()
            connect.close()
            return True
        except:
            return False

# 删改可合并传入sql即可
def delete_and_update_Db(sql):
    connect = connect_DB()
    if connect != False:
        cursor = connect.cursor()
        try:
            cursor.execute(sql)
            connect.commit()
            cursor.close()
            connect.close()
            return True
        except:
            return False

# sql = "delete from student where id=13"
# res = delete_and_update_Db(sql)
# print(res)

# res = retrieve_DB()
# print(res)

# res = insert_DB()
# print(res)