Python执行PostgreSQL数据库的SQL脚本详解_Python

使用psycopg2插件

psycopg2插件简介

psycopg2库介绍: psycopg2是一个用于python编程语言的第三方库，用于访问postgresql数据库系统。它提供了一组工具和方法，可以轻松地在python程序中进行数据库操作，包括查询、插入、更新、删除等操作。

以下是psycopg2库的一些主要特点：

1.简单易用：psycopg2提供了简单的api，易于学习和使用。

2.高性能：psycopg2是基于c语言实现的，能够提供高效的数据库操作。

3.完全兼容：psycopg2与postgresql数据库完全兼容，并支持大多数postgresql特性。

4.安全性：psycopg2具有内置的防止sql注入攻击的功能，能够保证数据安全。

5.使用psycopg2库进行数据库操作通常需要以下步骤：

安装psycopg2库：可以使用pip install psycopg2来安装该库。
建立数据库连接：使用psycopg2库提供的connect()方法建立与数据库的连接。
执行sql语句：使用psycopg2提供的方法执行sql语句，如查询、插入、更新等操作。
处理查询结果：如果执行的是查询操作，需要使用fetchone()或fetchall()方法来处理查询结果。
关闭连接：最后需要使用close()方法关闭数据库连接。

psycopg2插件使用

1、python更新pip插件

以管理员权限打开命令行窗口，执行下面的命令：

python -m pip install --upgrade pip -i https://pypi.tuna.tsinghua.edu.cn/simple

2、python安装psycopg2插件

以管理员权限打开命令行窗口，执行下面的命令：

pip install psycopg2 -i https://pypi.tuna.tsinghua.edu.cn/simple

报错：error: cannot determine archive format of c:\users\tttzz\appdata\local\temp\pip-req-build-rrzp7n41

3、信任该安装源

以管理员权限打开命令行窗口，执行下面的命令：

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --trusted-host pypi.tuna.tsinghua.edu.cn psycopg2

1.4、python安装chardet插件

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --trusted-host pypi.tuna.tsinghua.edu.cn chardet

pycharm下安装chardet插件，安装完毕。

5、查看已安装的插件pip list

pip list

从上面的清单，发现插件chardet及psycopg2都安装成功了，下面开始来创建python文件来对postgresql数据库进行操作。

6、创建python文件

postgresqlexecutesql.py

上代码：

#!/usr/bin/python
# -*- coding: utf-8 -*-
import chardet
import psycopg2
import psycopg2.extras

class postgresqlexecutesql:
    """
    python test library for tzq
    """

    def __init__(self):
        pass

    def get_encoding(self, file):
        # 获取文件编码类型
        # 二进制方式读取，获取字节数据，检测类型
        with open(file, 'rb') as f:
            return chardet.detect(f.read())['encoding']

    # 输出文件所有内容
    def get_file_content1(self, file_path):
        # filepath="d:/20220711-1657.sql"
        file_encoding = self.get_encoding(file_path)
        # print(get_encoding(filepath))
        file = open(file_path, "r", encoding=file_encoding)
        # print(file.read())
        str = file.read()
        file.close()
        return str

    # 按行输出文件内容
    def get_file_content2(self, filepath):
        # filepath="d:/20220711-1657.sql"
        file_encoding = self.get_encoding(filepath)
        print(self.get_encoding(filepath))
        file = open(filepath, "r", encoding=file_encoding)
        # print(file.readline())
        str = file.readline()
        file.close()
        return str

    # for循环读取文件内容
    def get_file_content3(self, filepath):
        with open(filepath, "r", encoding=self.get_encoding(filepath)) as file:
            for item in file:
                print("file_content:" + item)

    # conn = psycopg2.connect("dbname=tzqlog_pro user=tzq password=tzq@123456 host=127.0.0.1 port=5432")
    # 连接数据库执行脚本
    def conn_db_exec_sql(self, sql_script, dbname, user, password, host, port):
        connect_string = "dbname=" + dbname + " user=" + user + " password=" + password + " host=" + host + " port=" + port
        print(connect_string)
        # 连接到数据库
        conn = psycopg2.connect(connect_string)
        # 建立游标，用来执行数据库操作
        # 这⾥创建的是⼀个字典cursor, 这样返回的数据, 都是字典的形式, ⽅便使⽤
        # cursor = conn.cursor()
        cursor = conn.cursor(cursor_factory=psycopg2.extras.dictcursor)
        # 读取文本文件中的内容
        file_content = self.get_file_content1(sql_script)
        # 执行sql命令
        cursor.execute(file_content)
        # 提交sql命令
        conn.commit()
        # 执行sql select命令
        # command = 'select * from test_conn '
        # cursor.execute(command)
        ## 获取select返回的元组
        # rows = cursor.fetchall()
        # 关闭数据库连接
        conn.close()
        # print(rows)

    """tzqlog"""
    # 连接数据库执行脚本 - tzqlog pro 环境
    def exec_sql__tzqlog_db_pro(self, sql_script):
        # 配置项
        dbname = "tzqlog_db_pro"
        user = "plan"
        password = "tzq@123456"
        host = "127.0.0.1"
        port = "5432"
        self.conn_db_exec_sql(sql_script, dbname, user, password, host, port)

# if __name__ == '__main__':
#     postgresqlexecutesql = postgresqlexecutesql()
#     # 执行的脚本文件全路径
#     sql_file_path = "d:/20220706-1736-tzqlog_xxx.sql"
#     # 在 tzqlog pro 执行脚本
#     postgresqlexecutesql.exec_sql__tzqlog_db_pro(sql_file_path)

7、创建python文件

execute_db_script.py

上代码：

#!/usr/bin/python
# -*- coding: utf-8 -*-
import postgresqlexecutesql
# from postgresqlexecutesql import postgresqlexecutesql
# import sys

''' tzq 环境 汇总 一套执行 '''

# 执行的脚本文件全路径
# 脚本目录名
dir_string = "d:/oracle_database/sql/"
# 脚本文件名
# file_name = "20220706-1736-tzq-xxxxx.sql"
# file_name = "20220715-1522-张三-xxxx更新.sql"
file_name = "20230822-2312-log创建表-log_test_t.sql"

''' tzq 环境 汇总 一套执行 '''
# 脚本全路径
sql_file_path = dir_string + file_name
print(sql_file_path)
# 定义类的实例
postgresqlexecutesql = postgresqlexecutesql.postgresqlexecutesql()

# ################################各个环境下执行脚本################################

# 在 “tzq dev 环境” 执行脚本
# postgresqlexecutesql.exec_sql__tzqlog_db_dev(sql_file_path)

# 在 “tzq pro 环境” 执行脚本
postgresqlexecutesql.exec_sql__tzqlog_db_pro(sql_file_path)

8、创建测试sql脚本

20230822-2312-log创建表-log_test_t.sql

在 d:/oracle_database/sql/ 目录下创建脚本文件：20230822-2312-log创建表-log_test_t.sql：

create table log_info_t
(
  info_id            int8 not null,
  info_type_id       int8,
  title              varchar(200),
  content            text,
  article_view_count int8 default 1 not null,
  created_by         int8 default -1 not null,
  creation_date      timestamp(0) default current_timestamp not null,
  last_updated_by    int8 default -1 not null,
  last_update_date   timestamp(0) default current_timestamp not null,
  delete_flag        varchar(1) default 'n' not null,
  deleted_by         int8,
  delete_date        timestamp(0),
  content_no_html    text,
  info_right         varchar(50) default 'all'
);
create sequence log_info_s;

如下图：