目录
一、基础环境
(一)建立虚拟环境
Python 3.10.7
(二)安装依赖
psycopg2==2.9.9 (在虚拟环境中执行安装)
二、连接数据库
import psycopg2
from psycopg2.extras import execute_values
class ExcuteDB:
# ————————————————————————————————————————————— 连接数据库
def __init__(self, host, dbname, user, port, password):
self.host = host
self.dbname = dbname
self.user = user
self.port = port
self.password = password
# self.conn = self.__postgres # 连接数据库(放在各种功能方法中,比如exc_query方法)
@property
def __postgres(self):
conn = psycopg2.connect(host=self.host, # 主机地址
dbname=self.dbname, # 数据库名称
user=self.user, # 用户名
password=self.password, # 密码
port=self.port) # 端口号
return conn
# ————————————————————————————————————————————— 查询
def exc_query(self, sql):
try:
conn = self.__postgres # 连接数据库
cur = conn.cursor() # 创建游标对象
cur.execute(sql) # 执行SQL语句
res_list = None
res_list = cur.fetchall() # 获取记录值,结果是列表包元组:[('1F002', '数据结构怎么学'), ('1F001', '数据库原理')]
cols_list = [row[0] for row in cur.description] # 获取栏位名称['card', 'name']
# data_list的第一种组成方法
# 1. 结合map、lambda函数
# 2. 必须先用一个变量存储cur.fetchall(),再执行下面的for循环
data_list = [dict(map(lambda key, value: [key, value], cols_list, val_list)) for val_list in res_list] # 组装列表包字典:data_list [{'card': '1F002', 'name': '数据结构怎么学'}, {'card': '1F001', 'name': '数据库原理'}]
# data_list的另一种组成方法
# 1. 结合zip函数
# 2. 可以直接在for循环中使用
# data_list = [dict(zip(cols_list, val_list)) for val_list in cur.fetchall()]
except Exception as e:
print(str(e))
finally:
cur.close() # 关闭游标
conn.close() # 关闭数据库连接
return {'栏位名+栏位值': data_list, '只有栏位值': res_list}
# ————————————————————————————————————————————— 单笔更新/插入
def exc_edit(self, sql):
try:
conn = self.__postgres # 连接数据库
cur = conn.cursor() # 创建游标对象
cur.execute(sql) # 执行SQL语句
conn.commit() # 提交事务
except Exception as e:
conn.rollback() # 回滚commit事务
print(str(e))
finally:
cur.close() # 关闭游标
conn.close() # 关闭数据库连接
# ————————————————————————————————————————————— 批量更新/插入
def exc_bulk_edit(self, sql_list):
try:
conn = self.__postgres # 连接数据库
cur = conn.cursor() # 创建游标对象
for l in sql_list: # 循环不同的sql
sql = l['sql'] # sql语句:'insert into auth_group (card, name, author, btype, price, num) values %s '
args_list = l['args_list'] # 批量插入的值:[('2F0001', '高等数学', '小星星', '教育', 35, 10), ('2F0002', '线性代数', '小星星', '教育', 40, 12)]
execute_values(cur, sql, args_list) # 批量执行
conn.commit() # 提交事务
except Exception as e:
conn.rollback() # 回滚commit事务
print(str(e))
finally:
cur.close() # 关闭游标
conn.close() # 关闭数据库连接
三、操作数据库
(一)查询
exc_db = ExcuteDB('XX.XX.XX.XX', 'bookDB', 'admin', '5432', '123') # 连接数据库
res = exc_db.exc_query('select card, name from "myApp_book" limit 10') # 执行查询
(二)单笔更新/插入
exc_db = ExcuteDB('XX.XX.XX.XX', 'bookDB', 'admin', '5432', '123') # 连接数据库
res2 = exc_db.exc_edit('update "myApp_book" set name=\'数据结构\' where card=\'1F002\' ') # 执行更新
(三)批量更新/插入
exc_db = ExcuteDB('XX.XX.XX.XX', 'bookDB', 'admin', '5432', '123') # 连接数据库
res2 = exc_db.exc_bulk_edit([{'sql': 'insert into auth_group (name) values %s ',
'args_list': [('2F0001',), ('2F0002',)]}, # 批量插入auth_group表
{'sql': 'insert into "myApp_book" (card, name, author, btype, price, num) values %s ',
'args_list': [('2F0001', '高等数学', '小星星', '教育', 35, 10),
('2F0002', '线性代数', '小星星', '教育', 40, 12)]} # 批量插入myApp_book表
])
(四)fetchall() 查询的结果若为None
1. 若没有使用group by ,则获取的空情况是[(None,)],判断是否为空时,需要用if res1 and res1[0][0]
2. 若使用了group by,则获取的空情况是[],判断是否为空时,直接用if res2
def db_query(self,sql):
conn = psycopg2.connect(dbname=self.dbname,
user=self.user,
password=self.password,
port=self.port,
host=self.host)
conn.autocommit = True
cur = conn.cursor()
cur.execute(sql)
res_list = cur.fetchall()
cur.close()
conn.close()
return res_list
sql1 = 'select max(id) from book where bookname=\'数据结构\''
sql2 = 'select max(id) from book where bookname=\'数据结构\' group by id'
res1 = db_query(sql1) # 结果是 [(None,)]
res2 = db_query(sql2) # 结果是 [ ]
(五)遇到的问题
1. SQL语句 - 栏位名报错
(1)具体问题
column "数据结构" does not exist
LINE 1: update "myApp_book" set name="数据结构" where card='1F002'
(2)解决
数据值不能用双引号括起来,而是用 \'。
错误写法:
exc_db.exc_query('update "myApp_book" set name="数据结构" where card=\'1F002\' ')
正确写法:
exc_db.exc_query('update "myApp_book" set name=\'数据结构\' where card=\'1F002\' ')
2. SQL语句 - 表不存在
(1)具体问题
relation "myapp_book" does not exist
(2)解决
表名要用双引号包裹。
正确写法:
insert into "myApp_book" …
错误写法:
insert into myApp_book …
3. SQL语句 - 插入的数据格式不正确
(1)具体问题
INSERT has more expressions than target columns
LINE 1: insert into auth_group (name) values ('2','F','0','0','0','1…
(2)解决
元组里只有一个数据时,得用逗号结尾。
正确写法:
[('2F0001',), ('2F0002',)] # 列表包元组
错误写法:
[('2F0001'), ('2F0002')] # 会自动转变为:['2F0001','2F0002']