创建数据库及测试表
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
- 查询操作(select)
- 非查询操作(insert/update/delete)
- 事务提交(连接对象.commit())
- 事务回滚(连接对象.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()