在写python接口自动化测试脚本时,需要连接数据库去验证数据。这个时候需要python链接数据库去编写sql语句去查验数据。那么python链接数据库的脚本文件如何编写呢?
import psycopg2
# python链接数据库操作:
# 1.建立连接获得 connect 对象
# 2.获得游标对象,一个游标对象可以对数据库进行执行操作,非线程安全,多个应用会在同一个连接种创建多个光标;
# 3.书写sql语句
# 4.调用execute()方法执行sql
# 5.抓取数据(可选操作)
# 6.提交事物
# 7.关闭连接
# -*- coding: utf-8 -*-
class PostGreHelper(object):
def __init__(self, database="python", user="xxx", password="xxxxxxx", host="xxxx", port="*******"):
self._cursor = None
self._conn = None
self._database = database
self._user = user
self._password = password
self._host = host
self._port = port
self._result = None
def get_connection(self):
self._conn = psycopg2.connect(database=self._database, user=self._user, password=self._password,
host=self._host, port=self._port)
# 关闭数据库连接
def close_connection(self):
# 事务提交
self._conn.commit()
# 关闭数据库连接
self._cursor.close()
self._conn.close()
# 执行一条sql 带参数
def execute_sql_params(self, sql, params):
self._cursor = self._conn.cursor()
try:
print(f"当前执行sql:{sql},参数:{params}")
# 执行语句
self._cursor.execute(sql, params)
except psycopg2.Error as e:
print(f"执行sql:{sql},出错,错误原因:{e}")
# 通用执行方法
def execute_method(self, sql, params=None, method_name=None):
# 获取连接
self.get_connection()
# 执行sql
self.execute_sql_params(sql, params)
if method_name is not None:
# 查询单条
if "find_one" == method_name:
self._result = self._cursor.fetchone()
# 查询全部
elif "find_all" == method_name:
self._result = self._cursor.fetchall()
# 关闭数据库连接
self.close_connection()
# 查询单条
def find_one(self, sql, params=None):
self.execute_method(sql, params=params, method_name="find_one")
return self._result
# 查询所有
def find_all(self, sql, params=None):
self.execute_method(sql, params=params, method_name="find_all")
return self._result
# 插入
def insert(self, sql, params=None):
self.execute_method(sql, params=params)
# 更新
def update(self, sql, params=None):
self.execute_method(sql, params=params)
# 删除
def delete(self, sql, params=None):
self.execute_method(sql, params=params)