pymysql 操作mysql

1. 连接数据库

import pymysql


def connect():
    """
    数据库连接
    :return:
    """
    conn = pymysql.connect(
				        host = '127.0.0.1',
				        port = 3306,
				        user = 'root',
				        db = 'spider',
				        password = 'qwe123',
				        charset = 'utf8mb4'
				        					)
    return conn

2. 操作数据库

from connect_db import connect


def insert_one(sql, data):
    """
    单条插入
    :param sql:
    :return:
    """
    conn = connect()
    cursor = conn.cursor()

    try:
        cursor.execute(sql, data)
        conn.commit()

    except Exception as e:
        conn.rollback()
        print('执行SQL发生异常： {}'.format(e))

    finally:
        cursor.close()
        conn.close()

def insert_many(sql, data):
    """
    批量插入
    :param sql:
    :param data:
    :return:
    """
    conn = connect()
    cursor = conn.cursor()
    try:
        cursor.executemany(sql, data)
        conn.commit()

    except Exception as e:
        conn.rollback()
        print('执行SQL发生异常： {}'.format(e))

    finally:
        cursor.close()
        conn.close()


def update_data(sql):
    conn = connect()
    cursor = conn.cursor()

    try:
        print(sql)
        cursor.execute(sql)
        conn.commit()

    except Exception as e:
        conn.rollback()
        print("更新发生错误：{}".format(e))

    finally:
        cursor.close()
        conn.close()

def select_data(sql):
    """
    获取数据
    :param sql:
    :return:
    """
    conn = connect()
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result
    except Exception as e:
        print("获取数据失败：{}".format(e))

3. 案例解析

from operate_db import *


def insert_one_call():
    """
    单条插入例子
    :return:
    """
    name = 'Asia'
    print(name)
    age = 15
    sex = 'male'
    sql = 'insert into students(name, age, sex) values(%s, %s, %s)'
    insert_one(sql, (name, age, sex))


def insert_many_call():
    """
    批量插入例子
    :return:
    """
    data = [
        ['pink', 16, 'male'],
        ['green', 17, 'female'],
        ['blue', 18, 'male']
    ]
    sql = 'insert into students(name, age, sex) values(%s, %s, %s)'
    insert_many(sql, data)


def update_data_call():
    """
    更新例子
    :return:
    """
    name1 = 'red'
    name = 'python'
    sql = 'update students set name = "%s" where name = "%s"' % (name, name1)

    update_data(sql)


def get_data():
    """
    获取数据例子
    :return:
    """
    sql = 'select name, age from students where age = 15'
    result = select_data(sql)
    print(result)

get_data()