Python之MySQL数据库增删改查操作

一.插入操作

1.插入1

import pymysql

def get_conn():
    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1')    # db:表示数据库名称
    return conn

def insert(sql):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql)
    print(result)
    conn.commit()
    cur.close()
    conn.close()

if __name__ == '__main__':
    sql = "INSERT INTO student VALUES(1,'zhang',12);"
    insert(sql)

2.插入2

import pymysql

def get_conn():
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')
    return conn

def insert(sql, args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql, args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()



if __name__ == '__main__':
    sql = 'INSERT INTO student VALUES(%s,%s,%s);'
    insert(sql, (2, 'wang', 13))

3.插入3

import pymysql


def get_conn():
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')
    return conn

def insert_many(sql, args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.executemany(query=sql, args=args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()


if __name__ == '__main__':
    sql = 'insert into student VALUES (%s,%s,%s)'
    args = [(3, 'li', 11), (4, 'sun', 12), (5, 'zhao', 13)]
    insert_many(sql=sql, args=args)

二.查询操作

import pymysql

def get_conn():
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')
    return conn

def query(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(sql,args)
    results = cur.fetchall()
    print(type(results))  # 返回<class 'tuple'> tuple元组类型

    for row in results:
        print(row)
        id = row[0]
        name = row[1]
        age = row[2]
        print('id: ' + str(id) + '  name: ' + name + '  age: ' + str(age))
        pass

    conn.commit()
    cur.close()
    conn.close()


if __name__ == '__main__':
    sql = 'SELECT  * FROM student;'
    query(sql,None)

三.更新update

import pymysql

def get_conn():
    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1')    # db:表示数据库名称
    return conn

def update(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql,args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()


if __name__ == '__main__':
    sql = 'UPDATE student SET NAME=%s WHERE id = %s;'
    args = ('zhangsan', 1)
    update(sql, args)

四.删除delete

import pymysql

def get_conn():
    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1')    # db:表示数据库名称
    return conn

def delete(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql,args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()


if __name__ == '__main__':
    sql = 'DELETE FROM student WHERE id = %s;'
    args = (1,) # 单个元素的tuple写法
    delete(sql,args)