python 操作MySQL数据库

CHERISHGF

已于 2023-07-25 18:04:02 修改

阅读量92

点赞数

分类专栏： python 学习笔记文章标签：数据库 python mysql

于 2023-07-25 18:02:24 首次发布

本文链接：https://blog.csdn.net/CHERISHGF/article/details/131921908

版权

python 学习笔记专栏收录该内容

51 篇文章 1 订阅

订阅专栏

本文展示了如何使用SQL创建数据库和表格，插入、查询、更新与删除数据。同时，它演示了使用Python的pymysql库进行数据库连接、CRUD操作以及事务处理。还提到了对PostgreSQL数据库的连接池管理。

摘要由CSDN通过智能技术生成

创建数据库及测试表

CREATE DATABASE if not EXISTS books DEFAULT charset utf8;


CREATE TABLE books.t_book (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `title` varchar(100) NOT NULL COMMENT '图书名称',
  `pub_date` date NOT NULL COMMENT '发布日期',
  `read` int NOT NULL DEFAULT '0' COMMENT '阅读量',
  `comment` varchar(100) NOT NULL DEFAULT '0' COMMENT '评论量',
  `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='图书表';

INSERT into books.t_book VALUES (1,'射雕英雄传','1980-05-01','12','34',0);
INSERT into books.t_book VALUES (2,'天龙八部','1986-07-24','36','40',0);
INSERT into books.t_book VALUES (3,'笑傲江湖','1995-12-24','20','80',0);

CREATE TABLE books.t_hero(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(100) NOT NULL COMMENT '姓名',
`gender` SMALLINT(6) NOT NULL COMMENT '性别',
`description` VARCHAR(200) default NULL comment '描述',
`is_delete` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
`book_id` bigint NOT null comment '所属图书ID',
PRIMARY KEY(`id`),
key `t_hero_book_id`(`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='英雄人物表';



INSERT into books.t_hero VALUES (1,'郭靖',1,'降龙十八掌',0,1);
INSERT into books.t_hero VALUES (2,'黄蓉',0,'打狗棍法',0,1);
INSERT into books.t_hero VALUES (3,'乔峰',1,'降龙十八掌',0,2);
INSERT into books.t_hero VALUES (4,'令狐冲',1,'独孤九剑',0,3);
INSERT into books.t_hero VALUES (5,'任盈盈',0,'弹琴',0,3);

安装pymysql

 pip install PyMySQL

数据库基本操作

创建连接
获取游标
执行sql
1. 查询操作（select）
2. 非查询操作（insert/update/delete）
  1. 事务提交（连接对象.commit()）
  2. 事务回滚（连接对象.rollback()）
关闭游标
关闭连接

连接数据库

# -*- coding: utf-8 -*-
# 导包
import pymysql

# 创建连接
conn = pymysql.connect(host="*.*.*.*",
                       port=3306,
                       user="******",
                       password="******",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
cursor.execute("select version()")
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库查询操作

'''
1).连接到数据库（host:localhost user:root password:root database:books）
2).查询图书表的数据（包括：图书id、图书名称、阅读量、评论量）
3).获取查询结果的总记录数
4).获取查询结果的第一条数据
5).获取全部的查询结果
'''

# 导包
import pymysql

# 创建连接
# 1).连接到数据库（host:localhost user:root password:root database:books）
conn = pymysql.connect(host="*.*.*.*",
                       port=3306,
                       user="******",
                       password="******",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
# 2).查询图书表的数据（包括：图书id、图书名称、阅读量、评论量）
sql = "select id, title, `read`, `comment` from t_book;"
cursor.execute(sql)
# 3).获取查询结果的总记录数
print("获取的查询结果记录行数为：", cursor.rowcount)
# # 4).获取查询结果的第一条数据
# print(cursor.fetchone())
# 5).获取全部的查询结果
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库插入操作

'''
1).连接到数据库（host:localhost user:root password:root database:books）
2).查询图书表的数据（包括：图书id、图书名称、阅读量、评论量）
3).获取查询结果的总记录数
4).获取查询结果的第一条数据
5).获取全部的查询结果
'''
# 导包
import pymysql

# 创建连接
# 1).连接到数据库（host:localhost user:root password:root database:books）
conn = pymysql.connect(host="*.*.*.*",
                       port=3306,
                       user="******",
                       password="******",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
# 新增一条图书数据（id:4 title:西游记 pub_date:1986-01-01 ）
sql = "insert into t_book(id, title, pub_date) values(4, '西游记', '1986-01-01');"
cursor.execute(sql)
# 3).获取受影响的结果记录数
print("影响的结果记录数为：", cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库更新操作

"""
1).连接到数据库（host:localhost user:root password:root database:books
autocommit:True）
2).更新[西游记]图书名称为（title:东游记）
"""
# 导包
import pymysql

# 创建连接
conn = pymysql.connect(host="*.*.*.*",
                       port=3306,
                       user="******",
                       password="******",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "update t_book set title='东游记' where title = '西游记';"
cursor.execute(sql)
print(cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库删除操作

"""
1).连接到数据库（host:localhost user:root password:root database:books
autocommit:True）
2).删除图书（title:东游记）
"""
# 导包
import pymysql

# 创建连接
conn = pymysql.connect(host="*.*.*.*",
                       port=3306,
                       user="******",
                       password="******",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "delete from t_book where title = '东游记';"
cursor.execute(sql)
print(cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库事务操作

"""
1).连接到数据库（host:localhost user:root password:root database:books），
并开启自动提交事务
2).新增一条图书数据（id:4 title:西游记 pub_date:1986-01-01 ）
3).故意抛出一个异常（模拟代码出现异常）
4).新增一条英雄人物数据（name:孙悟空 gender:1 book_id:4）
"""
# 导包
import pymysql

# 初始化
conn = None
cursor = None
# 业务处理
try:
    # 创建连接
    conn = pymysql.connect(host="*.*.*.*",
                           port=3306,
                           user="******",
                           password="******",
                           database="books",
                           autocommit=False)
    # 获取游标
    cursor = conn.cursor()
    # 执行sql
    sql = "insert into t_book(id, title, pub_date) values(4, '西游记', '1986-01-01');"
    cursor.execute(sql)
    print(cursor.rowcount)
    print("-" * 200)

    # 主动抛出异常
    raise Exception("程序出错啦。。。。。。")

    # 4).新增一条英雄人物数据（name:孙悟空 gender:1 book_id:4）
    sql = "insert into t_hero(name,gender,book_id) values('孙悟空', 1, 4)"
    cursor.execute(sql)
    print(cursor.rowcount)

    # 提交事务
    conn.commit()
except Exception as e:
    # 回滚数据
    conn.rollback()
    # 打印异常信息
    print(e)
finally:
    # 关闭游标
    if cursor:
        cursor.close()
    # 关闭连接
    if conn:
        conn.close()

数据库工具封装

# 导包
import pymysql


# 创建工具类
class DBUtil():
    # 初始化
    __conn = None
    __cursor = None

    # 创建连接
    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host="*.*.*.*",
                                         port=3306,
                                         user="******",
                                         password="******",
                                         database="books")

        return cls.__conn

    # 获取游标
    @classmethod
    def __get_cursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__get_conn().cursor()

        return cls.__cursor

    # 执行sql
    @classmethod
    def exe_sql(cls, sql):
        try:
            # 获取游标对象
            cursor = cls.__get_cursor()
            # 调用游标对象的execute方法，执行sql
            cursor.execute(sql)
            # 如果是查询
            if sql.split()[0].lower() == "select":
                # 返回所有数据
                return cursor.fetchall()
            # 否则：
            else:
                # 提交事务
                cls.__conn.commit()
            # 返回受影响的行数
            return cursor.rowcount
        except Exception as e:
            # 事务回滚
            cls.__conn.rollback()
            # 打印异常信息
            print(e)
        finally:
            # 关闭游标
            cls.__close_cursor()
            # 关闭连接
            cls.__close_conn()

    # 关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    # 关闭连接
    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None


if __name__ == '__main__':
    # sql = "select * from t_book"
    # sql = "insert into t_book(id, title, pub_date) values(4, '西游记', '1986-01-01');"
    # sql = "update t_book set title='东游记' where title = '西游记';"
    sql = "delete from t_book where title = '东游记';"
    result = DBUtil.exe_sql(sql)
    print(result)

python连接postgres数据库

都差不多

# -*- coding: utf-8 -*-

from psycopg2 import pool

pool = pool.SimpleConnectionPool(minconn=1, maxconn=5, dbname='****',
                                 user='****',
                                 password='****',
                                 host='*.*.*.*',
                                 port=5432,
                                 options='-c search_path=**,statement_timeout=300')
cnn = pool.getconn()
cursor = cnn.cursor()
# 执行sql
cursor.execute("select version()")
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
cnn.close()