前言

  之前用java写MySQL的增删改查，要写好多代码（原生的），然后就用了框架，例如：dbutils，hibernate，mybatis，现在用的是jpa和mybatis plus。但是在用了pymysql之后，感觉原生的也挺香！！！(代码就放这了，我就不抽取了，我自己想怎么用就怎么用了，如有需要请自取)

  1. 增

import pymysql


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


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


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 book VALUES(%s,%s,%s);'
    # insert(sql, (5, 'wang', 13))
    sql = 'insert into book VALUES (%s,%s,%s)'
    args = [(3, 'li', 11), (4, 'sun', 12), (5, 'zhao', 13)]
    insert_many(sql=sql, args=args)

  2. 删

import pymysql


def get_conn():
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='python-mysql')  # 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 book WHERE vId = %s;'
    args = (1,)  # 单个元素的tuple写法
    delete(sql, args)

  3. 改

import pymysql


def get_conn():
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='python-mysql')  # 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 book set vN=%s where vId = %s;'
    args = ('zhangsan', 1)
    update(sql, args)

  4. 查

import pymysql


def get_conn():
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='python-mysql')  # db:表示数据库名称
    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)
        vId = row[0]
        vN = row[1]
        cCnt = row[2]
        print('vId: ' + str(vId) + '  vN: ' + vN + '  cCnt: ' + str(cCnt))
        pass

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


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

  5. 表结构

CREATE TABLE `book` (
  `vId` int(10) NOT NULL,
  `vN` varchar(20) DEFAULT NULL,
  `cCnt` int(10) DEFAULT NULL,
  PRIMARY KEY (`vId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;