Python 操作数据库

PyMYSQL

安装 PyMySQL

pip install  pymysql

创建数据库表

import pymysql

host = "127.0.0.1"
username = "root"
password = "root"
database = "python_db"
port = 3306

# 连接数据库，获取连接对象
conn = pymysql.connect(host, username, password, database, port)

# 获取 cursor 游标对象，用于操作数据库
cur = conn.cursor()

# 创建 SQL 语句
sql = """   
    create table t_student(
        sno int(10) primary key auto_increment,
        sname varchar(20) not null,
        age int(3) not null,
        score float(3, 1)
    )
"""

try:
    # 执行 SQL 语句
    cur.execute(sql)
except Exception as e:
    print(e.args)
    print('创建表失败')
finally:
    print('创建表成功')
    conn.close()

添加数据

方法	作用
execute	插入单条数据
executemany	插入多条数据

import pymysql


def getCursor():
    host = "127.0.0.1"
    username = "root"
    password = "root"
    database = "python_db"
    port = 3306
    # 连接数据库，获取连接对象
    conn = pymysql.connect(host, username, password, database, port)
    # 获取 cursor 游标对象，用于操作数据库
    cur = conn.cursor()
    #  返回 cursor 对象
    return conn, cur


if __name__ == '__main__':
    conn, cur = getCursor()
    sql = "insert into t_student(sname, age, score) values(%s, %s, %s)"
    zhangsan = ('张三', 20, 89.1)
    list1 = [
        ('李四', 20, 49.1),
        ('王五', 20, 69.2),
        ('赵六', 20, 79.3),
        ('小七', 18, 99.9)
    ]
    try:
        # 执行插入单条语句,
        # cur.execute(sql, zhangsan)
        # 执行同时插入多条语句
        cur.executemany(sql, list1)
        # 提交事务
        conn.commit()
    except Exception as e:
        print(e.args)
        print('插入失败')
        conn.rollback()
    finally:
        conn.close()

查询数据

方法	作用
findone	获取单条返回结果
findall	获取所有返回结果

查询多条数据

import pymysql
from Student import  Student


def getCursor():
    host = "127.0.0.1"
    username = "root"
    password = "root"
    database = "python_db"
    port = 3306
    # 连接数据库，获取连接对象
    conn = pymysql.connect(host, username, password, database, port)
    # 获取 cursor 游标对象，用于操作数据库
    cur = conn.cursor()
    #  返回 cursor 对象
    return conn, cur


if __name__ == '__main__':
    conn, cur = getCursor()

    sql = "select * from t_student"

    try:
        # 执行 SQL 语句
        cur.execute(sql)
        # 获取结果集
        students = cur.fetchall()
        stuList = []
        for stu in students:
            print(stu)
            s = Student(stu[0], stu[1], stu[2], stu[3])
            stuList.append(s)
        print('----------------------------')
        for stu in stuList:
            print(stu)
    except Exception as  e:
        print(e.args)
    finally:
        conn.close()

查询单条数据

import pymysql
from Student import Student


def getCursor():
    host = "127.0.0.1"
    username = "root"
    password = "root"
    database = "python_db"
    port = 3306
    # 连接数据库，获取连接对象
    conn = pymysql.connect(host, username, password, database, port)
    # 获取 cursor 游标对象，用于操作数据库
    cur = conn.cursor()
    #  返回 cursor 对象
    return conn, cur


if __name__ == '__main__':
    conn, cur = getCursor()

    sql = "select * from t_student where sname = %s"

    try:
        # 执行 SQL 语句
        cur.execute(sql, '张三')
        # 获取结果集
        zhangsan = cur.fetchone()
        print(zhangsan)
    except Exception as  e:
        print(e.args)
    finally:
        conn.close()

更新数据

import pymysql
from Student import Student


def getCursor():
    host = "127.0.0.1"
    username = "root"
    password = "root"
    database = "python_db"
    port = 3306
    # 连接数据库，获取连接对象
    conn = pymysql.connect(host, username, password, database, port)
    # 获取 cursor 游标对象，用于操作数据库
    cur = conn.cursor()
    #  返回 cursor 对象
    return conn, cur


if __name__ == '__main__':
    conn, cur = getCursor()

    sql = "update t_student set score = %s where sno = %s"

    try:
        # 执行 SQL 语句
        cur.execute(sql, (99.9, 1))
        # 提交事务
        conn.commit()
    except Exception as e:
        print(e.args)
        # 回滚事务
        conn.rollback()
    finally:
        conn.close()

删除数据

import pymysql
from Student import Student


def getCursor():
    host = "127.0.0.1"
    username = "root"
    password = "root"
    database = "python_db"
    port = 3306
    # 连接数据库，获取连接对象
    conn = pymysql.connect(host, username, password, database, port)
    # 获取 cursor 游标对象，用于操作数据库
    cur = conn.cursor()
    #  返回 cursor 对象
    return conn, cur


if __name__ == '__main__':
    conn, cur = getCursor()

    sql = "delete from  t_student  where sno = %s"

    try:
        # 执行 SQL 语句
        cur.execute(sql, 2)
        # 提交事务
        conn.commit()
    except Exception as e:
        print(e.args)
        # 回滚事务
        conn.rollback()
    finally:
        conn.close()