1.初学mysql

安装pymysql (终端运行)

pip install pymysql

数据库

建立数据库连接

def query_records():
    db = connect(
        host="localhost", 
        user="root",
        password="123456",
        database="mydb",
        port=3306
    )

创建游标对象:

cursor = db.cursor() 

查询语句

cursor = db.cursor()  # 将 connect 改为 cursor
    s_name = "子龙"
    #
    sql="SELECT * FROM s WHERE s.name='{}'".format(s_name)


    try:
        cursor.execute(sql)
        print("查询成功")
        res=cursor.fetchall()
        print(res)



    except Exception as e:
        print(e)
    db.close()

运行

if __name__=="__main__":
    query_records()

插入语句

sql2 = "INSERT INTO biao (id, `name`, birthday, address) VALUES(55, '李明', '2024-06-01', '广西桂林市')"
    try:
        res = cursor.execute(sql2)
        print(f"插入成功{res}行数据")
    except Exception as e:
        print(f"插入失败，原因：{e}")

    finally:
        cursor.close()  # 关闭游标
        db.close()  # 关闭数据库连接

修改语句

    cursor = db.cursor()
    id = 1
    name = "李世民"
    birthday = "2000-11-11"
    address = "广西桂林市"



    # 更新记录
    sql = "UPDATE biao SET `name`=%s, birthday=%s, address=%s WHERE id=%s"

    try:
        # 执行更新语句
        res = cursor.execute(sql, (name, birthday, address, id))
        print("修改成功" if res > 0 else "未找到匹配记录进行修改")



    except Exception as e:
        print(f"修改失败，原因：{e}")

    finally:
        db.close()

if __name__ == "__main__":
    insert_records()

完整代码

from pymysql import connect


def query_records(db, cursor):
    try:
        # 查询记录
        id = int(input("输入查询的id："))
        sql = "SELECT * FROM biao WHERE id = %s"
        cursor.execute(sql, (id,))
        result = cursor.fetchone()
        if result:
            print(f"查询成功，记录：{result}")
        else:
            print("未找到匹配记录")
    except Exception as e:
        print(f"查询失败，原因：{e}")


def update_records(db, cursor):
    try:
        id = int(input("输入更新的id："))
        name = input("输入更新的姓名：")
        birthday = input("输入更新的生日：")
        address = input("输入更新的地址：")

        # 更新记录
        sql = "UPDATE biao SET `name`=%s, birthday=%s, address=%s WHERE id=%s"
        res = cursor.execute(sql, (name, birthday, address, id))
        db.commit()
        print("修改成功" if res > 0 else "未找到匹配记录进行修改")
    except Exception as e:
        db.rollback()
        print(f"修改失败，原因：{e}")


def insert_records(db, cursor):
    try:
        id = int(input("输入新增的id："))
        name = input("输入新增的姓名：")
        birthday = input("输入新增的生日：")
        address = input("输入新增的地址：")

        # 检查记录是否已经存在
        check_sql = "SELECT * FROM biao WHERE id = %s"
        cursor.execute(check_sql, (id,))
        result = cursor.fetchone()
        if result:
            print("该ID已经存在，请使用其他ID！")
            return

        # 插入记录
        sql = "INSERT INTO biao (id, `name`, birthday, address) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, (id, name, birthday, address))
        db.commit()
        print("插入成功")
    except Exception as e:
        db.rollback()
        print(f"插入失败，原因：{e}")

def query_all_records(db, cursor):
    try:
        # 查询全部记录以验证数据
        sql = "SELECT * FROM biao"
        cursor.execute(sql)
        result = cursor.fetchall()
        print(f"查询全部记录：{result}")
    except Exception as e:
        print(f"查询全部记录失败，原因：{e}")

def main():
    # 连接到数据库
    db = connect(
        host="localhost",
        user="root",
        password="123456",
        database="lm",
        port=3306,
        autocommit=True  # 手动提交
    )
    cursor = db.cursor()
    print("欢迎使用MySQL数据库！")
    print("1. 查询记录")
    print("2. 更新记录")
    print("3. 插入记录")
    print("0. 查询全部记录")
    choice = input("请输入操作编号：")

    if choice == "1":
        query_records(db, cursor)
    elif choice == "2":
        update_records(db, cursor)
    elif choice == "3":
        insert_records(db, cursor)
    elif choice == "0":
        query_all_records(db, cursor)
    else:
        print("输入错误，请重新输入！")

    # 查询全部记录以验证数据
    sql = "SELECT * FROM biao"
    cursor.execute(sql)
    result = cursor.fetchall()
    print(f"查询全部记录：{result}")

    # 关闭数据库连接
    db.close()


if __name__ == "__main__":
    while True:
         main()