Python執(zhí)行PostgreSQL數(shù)據(jù)庫的SQL腳本詳解

更新時間：2025年05月19日 10:26:58 作者：Tzq@2018

Psycopg2是一個用于Python編程語言的第三方庫,用于訪問PostgreSQL數(shù)據(jù)庫系統(tǒng),可以輕松地在Python程序中進行數(shù)據(jù)庫操作,下面小編就來和大家詳細(xì)介紹一下它吧

1、Python更新pip插件
2、Python安裝psycopg2插件
3、信任該安裝源
1.4、Python安裝chardet插件
5、查看已安裝的插件pip list
6、創(chuàng)建Python文件
7、創(chuàng)建Python文件
8、創(chuàng)建測試SQL腳本
9、執(zhí)行execute_db_script.py
10、查看數(shù)據(jù)庫

使用psycopg2插件

psycopg2插件簡介

psycopg2庫介紹: Psycopg2是一個用于Python編程語言的第三方庫，用于訪問PostgreSQL數(shù)據(jù)庫系統(tǒng)。它提供了一組工具和方法，可以輕松地在Python程序中進行數(shù)據(jù)庫操作，包括查詢、插入、更新、刪除等操作。

以下是Psycopg2庫的一些主要特點：

1.簡單易用：Psycopg2提供了簡單的API，易于學(xué)習(xí)和使用。

2.高性能：Psycopg2是基于C語言實現(xiàn)的，能夠提供高效的數(shù)據(jù)庫操作。

3.完全兼容：Psycopg2與PostgreSQL數(shù)據(jù)庫完全兼容，并支持大多數(shù)PostgreSQL特性。

4.安全性：Psycopg2具有內(nèi)置的防止SQL注入攻擊的功能，能夠保證數(shù)據(jù)安全。

5.使用Psycopg2庫進行數(shù)據(jù)庫操作通常需要以下步驟：

安裝psycopg2庫：可以使用pip install psycopg2來安裝該庫。
建立數(shù)據(jù)庫連接：使用psycopg2庫提供的connect()方法建立與數(shù)據(jù)庫的連接。
執(zhí)行SQL語句：使用psycopg2提供的方法執(zhí)行SQL語句，如查詢、插入、更新等操作。
處理查詢結(jié)果：如果執(zhí)行的是查詢操作，需要使用fetchone()或fetchall()方法來處理查詢結(jié)果。
關(guān)閉連接：最后需要使用close()方法關(guān)閉數(shù)據(jù)庫連接。

psycopg2插件使用

1、Python更新pip插件

以管理員權(quán)限打開命令行窗口，執(zhí)行下面的命令：

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

2、Python安裝psycopg2插件

以管理員權(quán)限打開命令行窗口，執(zhí)行下面的命令：

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、信任該安裝源

以管理員權(quán)限打開命令行窗口，執(zhí)行下面的命令：

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

從上面的清單，發(fā)現(xiàn)插件chardet及psycopg2都安裝成功了，下面開始來創(chuàng)建Python文件來對PostgreSQL數(shù)據(jù)庫進行操作。

6、創(chuàng)建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):
        # 獲取文件編碼類型
        # 二進制方式讀取，獲取字節(jié)數(shù)據(jù)，檢測類型
        with open(file, 'rb') as f:
            return chardet.detect(f.read())['encoding']

    # 輸出文件所有內(nèi)容
    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

    # 按行輸出文件內(nèi)容
    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循環(huán)讀取文件內(nèi)容
    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")
    # 連接數(shù)據(jù)庫執(zhí)行腳本
    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)
        # 連接到數(shù)據(jù)庫
        conn = psycopg2.connect(connect_string)
        # 建立游標(biāo)，用來執(zhí)行數(shù)據(jù)庫操作
        # 這?創(chuàng)建的是?個字典Cursor, 這樣返回的數(shù)據(jù), 都是字典的形式, ?便使?
        # cursor = conn.cursor()
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        # 讀取文本文件中的內(nèi)容
        file_content = self.get_file_content1(sql_script)
        # 執(zhí)行SQL命令
        cursor.execute(file_content)
        # 提交SQL命令
        conn.commit()
        # 執(zhí)行SQL SELECT命令
        # command = 'SELECT * FROM test_conn '
        # cursor.execute(command)
        ## 獲取SELECT返回的元組
        # rows = cursor.fetchall()
        # 關(guān)閉數(shù)據(jù)庫連接
        conn.close()
        # print(rows)

    """TZQLOG"""
    # 連接數(shù)據(jù)庫執(zhí)行腳本 - TZQLOG PRO 環(huán)境
    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()
#     # 執(zhí)行的腳本文件全路徑
#     sql_file_path = "D:/20220706-1736-tzqlog_XXX.sql"
#     # 在 TZQLOG PRO 執(zhí)行腳本
#     postgreSQLExecuteSql.exec_sql__TZQLOG_DB_PRO(sql_file_path)

7、創(chuàng)建Python文件

execute_db_script.py

上代碼：

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import PostgreSQLExecuteSql
# from PostgreSQLExecuteSql import PostgreSQLExecuteSql
# import sys

''' TZQ 環(huán)境 匯總 一套執(zhí)行 '''

# 執(zhí)行的腳本文件全路徑
# 腳本目錄名
DIR_STRING = "D:/oracle_database/sql/"
# 腳本文件名
# FILE_NAME = "20220706-1736-tzq-xxxxx.sql"
# FILE_NAME = "20220715-1522-張三-xxxx更新.sql"
FILE_NAME = "20230822-2312-log創(chuàng)建表-log_test_t.sql"

''' TZQ 環(huán)境 匯總 一套執(zhí)行 '''
# 腳本全路徑
sql_file_path = DIR_STRING + FILE_NAME
print(sql_file_path)
# 定義類的實例
postgreSQLExecuteSql = PostgreSQLExecuteSql.PostgreSQLExecuteSql()

# ################################各個環(huán)境下執(zhí)行腳本################################

# 在 “TZQ DEV 環(huán)境” 執(zhí)行腳本
# postgreSQLExecuteSql.exec_sql__TZQLOG_DB_DEV(sql_file_path)

# 在 “TZQ PRO 環(huán)境” 執(zhí)行腳本
postgreSQLExecuteSql.exec_sql__TZQLOG_DB_PRO(sql_file_path)

8、創(chuàng)建測試SQL腳本

20230822-2312-log創(chuàng)建表-log_test_t.sql

在 D:/oracle_database/sql/ 目錄下創(chuàng)建腳本文件：20230822-2312-log創(chuàng)建表-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;

如下圖：