Python WITH AS连接postgresql示例

一、技术说明

用python connect Postgresql是程序开发中经常遇到的需求，但是，连接数据库需要考虑创建（Create)和关闭（Close）的连接与游标的问题。为此，我们采用python编程模式中的年with as数据结构，实现数据库的创建与自动关闭。

本示例涉及如下技术点：

• Python连接postgresql的连接包，该包的下载、安装与使用相见：psycopg2 · PyPI
• Python with as数据结构，详细技术说明参考：PEP 343 – The “with” Statement | peps.python.org
• postgresql安装与数据库创建，请参考：PostgreSQL: Documentation

二、编码示例

1. 创建数据库连接配置文件：pg_config.ini

[postgresql]
host = 127.0.0.1
database = mydb
user = steven
password = 11111111
port = 5432

2.数据库连接对象：PostgresqlConnect

'''
PostgresConnect
-------------
POSTGRESQL数据库连接服务，根据数据库连接信息（主机IP、端口号、数据库名称、用户名、密码 或者 数据库连接配置文件ini），创建数据库连接
Author: steven
Date : 2024-3-21
版本： v1.0.0
'''

import configparser as cp
import curses
import sys
from contextlib import contextmanager

import psycopg2 as pg2


class PostgresqlConnect:
    """this is to initialize the object of postgresqlconnect, configfile and section are one group and
    others are another group for inputting parameters."""

    def __init__(
            self,
            configfile: str | None = None,
            section: str = 'postgresql',
            *,
            host: str | None = None,
            port: str | None = None,
            database: str | None = None,
            user: str | None = None,
            password: str | None = None,
    ):
        self.params = {}
        if configfile is not None:
            parser = cp.ConfigParser()
            parser.read(configfile)
            if parser.has_section(section):
                self.params = dict(parser.items(section))
            else:
                raise Exception('Section {0} not found in the {1} file'.format(
                    section, configfile))
        else:
            self.params['host'] = host
            self.params['port'] = port
            self.params['database'] = database
            self.params['user'] = user
            self.params['password'] = password
        # print('The database\'s configuration is:', self.params)

    """this is to create a connnection to database when entered and 
    close it when left"""
    @contextmanager
    def connected(self):
        print('Connecting to:', self.params)
        conn = pg2.connect(**self.params)
        # create a cursor
        cur = conn.cursor()
        try:
            yield cur
        finally:
            cur.close()
            conn.close()
            print('Database connection is closed')

3.测试验证代码：main

# This is a sample Python script.
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.

def print_hi(name, python_learing=None):

    from postgresql_connection.postgresql_connection import PostgresqlConnect as pc
    pc_mydb = pc('postgresql_connection/pg_config.ini')

    with pc_mydb.connected() as cur:
        # execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')

        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')