在写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)