windows下的python对mysql进行增删改查

import pymysql

def add_one():
    #向表中加入一条数据
    try:
        conn = pymysql.connect(
                    host='',
                    port=3306,
                    user='root',
                    passwd='root',
                    db='demo01',
                    charset='utf8'
                )
        cs1 = conn.cursor()
        count = cs1.execute("INSERT into student (age,name,url) VALUES(100,'斯巴达_old','http://www.spartan.com')")
        print("数据插入完成！")
        #查看插入新数据后的表
        print("查看插入数据后的表")
        sql = 'select * from student'
        count = cs1.execute(sql)
        dataAll = cs1.fetchall()
        for temp in dataAll:
            print(temp)
        #关闭
        conn.commit()
        cs1.close()
        conn.close()
    except Exception as error:
      print(error)


def Insert_custom():
    try:
        conn = pymysql.connect(
                host = '',
                port = 3306,
                user = 'root',
                passwd = 'root',
                db = 'demo01',
                charset = 'utf8'
                )
        cs1 = conn.cursor()
        sql = 'insert into student(name,age,url) values(%s,%s,%s)'
        #参数列表
        name = input('输入姓名：')
        age = input('输入年龄：')
        url = input('输入url：')
        params = [name,age,url]
        #执行
        count = cs1.execute(sql,params)
        print('受影响的行数：%s'%count)
        print('查看添加数据后的表：')
        sql2 = 'select * from student'
        count = cs1.execute(sql2)
        dataAll = cs1.fetchall()
        for temp in dataAll:
            print(temp)
        #提交
        conn.commit()
        #关闭
        cs1.close()
        conn.close()
    except Exception as error:
        print(error)

    
def update_one():
    #修改表中的一条数据
    try:
        conn = pymysql.connect(
                host = '',
                port = 3306,
                user = 'root',
                passwd = 'root',
                db = 'demo01',
                charset = 'utf8'
                )
        cs1 = conn.cursor()
        sql = 'update student set age = 100 where id = 3'
        count = cs1.execute(sql)
        print("数据修改完成！")
        print("查看修改完数据的表！")
        sql = 'select * from student'
        count = cs1.execute(sql)
        dataAll = cs1.fetchall()
        for temp in dataAll:
            print(temp)
        #关闭
        conn.commit()
        cs1.close()
        conn.close()
    except Exception as error:
        print(error)

def del_one():
    #删除一条数据
    try:
        conn = pymysql.connect(
                host = '',
                port = 3306,
                user = 'root',
                passwd = 'root',
                db = 'demo01',
                charset = 'utf8'
                )
        cs1 = conn.cursor()
        sql = 'delete from student where id = 4'
        count = cs1.execute(sql)
        print("数据删除完成！")
        print("查看删除了数据的表！")
        sql = 'select * from student'
        count = cs1.execute(sql)
        dataAll = cs1.fetchall()
        for temp in dataAll:
            print(temp)
        #关闭
        conn.commit()
        cs1.close()
        conn.close()
    except Exception as error:
        print(error)


def select_one():    
    #查一条数据
    try:
        conn = pymysql.connect(
                host = '',
                port = 3306,
                user = 'root',
                passwd = 'root',
                db = 'demo01',
                charset = 'utf8'
                )
        cs1 = conn.cursor()
        count = cs1.execute("select * from student where id = 1")
        result = cs1.fetchone()
        print(result)
        cs1.close()
        conn.close()
    except Exception as error:
        print(error)


def select_all():
    #查询一个表的全部数据
    try:
        conn = pymysql.connect(
                host = '',
                port = 3306,
                user = 'root',
                passwd = 'root',
                db = 'demo01',
                charset = 'utf8'
                )
        cs1 = conn.cursor()
        sql = 'select * from student'
        count = cs1.execute(sql)
        dataAll = cs1.fetchall()
        for temp in dataAll:
            print(temp)
        conn.commit()
        #关闭
        cs1.close()
        conn.close()
    except Exception as error:
        print(error)


def close():
    print("结束！")

host是mysql所在系统的ip，linux可通过ifconfig查看，windows通过ipconfig查看。要连接linux的mysql要先关闭防火墙：service iptables stop；用完记得开启防火墙：service iptables start。

#########

调用上面定义好对mysql增删改查的函数：

import MySQL

number = 0
num = -1
print("_______________________")
while num != number:
    num = int(input("请输入您想进行的操作:\n"
                    "1、查看所有数据\n"
                    "2、增加一条数据\n"
                    "3、自定义添加数据\n"
                    "4、查看一条数据\n"
                    "5、删除一条数据\n"
                    "6、更新一条数据\n"
                    "7、查看最终的表\n"
                    "-------------------------------\n"))
 
    if num == number:
        MySQL.close()
        print("结束！")
        print("_____________________________")
    elif num ==1:
        print("查看所有数据")
        print("select * from student")
        print("--------------------------加载中--------------------------")
        MySQL.select_all()
    elif num == 2:
        print("增加一条数据")
        print("INSERT into student (age,name,url) VALUES(100,'斯巴达_old','http://www.spartan.com')")
        print("--------------------------加载中--------------------------")
        MySQL.add_one()
    elif num == 3:
        print("自定义添加数据")
        print("insert into student(name,age,url) values(%s,%s,%s)")
        print("--------------------------加载中--------------------------")
        MySQL.Insert_custom()
    elif num == 4:
        print("查看一条数据")
        print("select * from student where id = 1")
        print("--------------------------加载中--------------------------")
        MySQL.select_one()
    elif num == 5:
        print("删除一条数据")
        print("delete from student where id = 4")
        print("--------------------------加载中--------------------------")
        MySQL.del_one()
    elif num == 6:
        print("更新一条数据")
        print("update student set age = 100 where id = 3")
        print("--------------------------加载中--------------------------")
        MySQL.update_one()
    elif num == 7:
        print("查看最终的表！")
        print("select * from student")
        print("--------------------------加载中--------------------------")
        MySQL.select_all()