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()

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值