【Python数据库】MySQL（3673字）

个人主页：丷从心·

系列专栏：Python数据库

学习指南：Python学习指南

---

创建数据库

import pymysql


def create_database():
    db = pymysql.connect(host='localhost', user='root', password='root')
    cursor = db.cursor()

    sql = """
        create database if not exists python_database charset=utf8;
    """

    try:
        cursor.execute(sql)
    except Exception as e:
        print(f'数据库创建失败: {e}')
    else:
        print('数据库创建成功...')


if __name__ == '__main__':
    create_database()

• pymysql.connect(host='localhost', user='root', password='root')用于连接数据库
  • host用于指定数据库IP地址，localhost表示本地
  • user用于指定数据库用户名
  • password用于指定数据库密码
• db.cursor()返回一个游标对象，用于执行数据库操作
• cursor.execute(sql)用于执行一条SQL语句

---

创建数据表

import pymysql


def create_table():
    db = pymysql.connect(host='localhost', user='root', password='root', db='python_database')
    cursor = db.cursor()

    sql = """
        create table if not exists book_info
        (
            id int primary key auto_increment,
            name varchar(10) not null,
            pub_date datetime,
            read_count int,
            comment_count int,
            is_delete bit
        );
    """

    try:
        cursor.execute(sql)
    except Exception as e:
        print(f'数据表创建失败: {e}')
    else:
        print('数据表创建成功...')
    finally:
        cursor.close()
        db.close()


if __name__ == '__main__':
    create_table()

数据插入

import pymysql


def insert_data():
    with pymysql.connect(host='localhost', user='root', password='root', db='python_database') as db:
        cursor = db.cursor()

        sql = """
            insert into book_info (id, name, pub_date, read_count, comment_count, is_delete) values (%s, %s, %s, %s, %s, %s);
        """

        try:
            cursor.executemany(sql, [(0, '射雕英雄传', '1980-5-1', 12, 34, 0),
                                     (0, '天龙八部', '1986-7-24', 36, 40, 0),
                                     (0, '笑傲江湖', '1995-12-24', 20, 80, 0),
                                     (0, '雪山飞狐', '1987-11-11', 58, 24, 0)])
            db.commit()
        except Exception as e:
            print(f'数据插入失败: {e}')

            db.rollback()
        else:
            print('数据插入成功...')


if __name__ == '__main__':
    insert_data()

• db.commit()用于事务提交
• db.rollback()用于在数据插入失败时进行事务回滚

数据查询

import pymysql


def query_data():
    db = pymysql.connect(host='localhost', user='root', password='root', db='python_database')
    cursor = db.cursor()

    sql = """
        select * from book_info;
    """

    try:
        cursor.execute(sql)
    except Exception as e:
        print(f'数据查询失败: {e}')
    else:
        result = cursor.fetchall()
        # result = cursor.fetchone()
        # result = cursor.fetchmany(4)

        for book in result:
            book_info = dict()

            book_info['id'] = book[0]
            book_info['name'] = book[1]
            book_info['pub_date'] = book[2]
            book_info['read_count'] = book[3]
            book_info['comment_count'] = book[4]
            book_info['is_delete'] = book[5]

            print(book_info)
    finally:
        cursor.close()
        db.close()


if __name__ == '__main__':
    query_data()

{'id': 1, 'name': '射雕英雄传', 'pub_date': datetime.datetime(1980, 5, 1, 0, 0), 'read_count': 12, 'comment_count': 34, 'is_delete': b'\x00'}
{'id': 2, 'name': '天龙八部', 'pub_date': datetime.datetime(1986, 7, 24, 0, 0), 'read_count': 36, 'comment_count': 40, 'is_delete': b'\x00'}
{'id': 3, 'name': '笑傲江湖', 'pub_date': datetime.datetime(1995, 12, 24, 0, 0), 'read_count': 20, 'comment_count': 80, 'is_delete': b'\x00'}
{'id': 4, 'name': '雪山飞狐', 'pub_date': datetime.datetime(1987, 11, 11, 0, 0), 'read_count': 58, 'comment_count': 24, 'is_delete': b'\x00'}

• cursor.fetchall()用于获取所有查询结果
• cursor.fetchone()用于获取一条查询结果
• cursor.fetchmany(4)用于获取四条查询结果

数据更新

import pymysql


def update_data():
    db = pymysql.connect(host='localhost', user='root', password='root', db='python_database')
    cursor = db.cursor()

    sql = """
        update book_info set read_count = read_count + 1 where id = 1;
    """

    try:
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(f'数据更新失败: {e}')

        db.rollback()
    else:
        print('数据更新成功...')
    finally:
        cursor.close()
        db.close()


if __name__ == '__main__':
    update_data()

---