学习python多种方式连接pg数据库

学习多种方式连接pg数据库

第一种

创建pg数据库连接配置文件

db_pgconfig = {
    "host":'localhost',
    "port":"8888",
    "user":"admin",
    "password":"admin",
    "database":"zhejiang"
}

其他py脚本使用配置文件

import psycopg2
import pandas as pd
from db_pgconfig import db_pgconfig

conn = psycopg2.connect(**db_pgconfig)
print("连接成功")
cur = conn.cursor()
cur.execute("SELECT column_name FROM information_schema.columns  where LOWER(TABLE_NAME) ='dm_autoalarm_eshop_order_stat_test'")
fields = [row[0] for row in cur.fetchall()]
print(fields)
full_fields = ','.join(fields)
print('select ' + full_fields +  ' from zhejiang.dm_autoalarm_eshop_order_stat_test')
sql ='select ' + full_fields +  ' from zhejiang.dm_autoalarm_eshop_order_stat_test order by month_id '
cur.execute(sql)
#导出文件
df = pd.read_sql(sql,conn)
df.to_csv(r"C:\Users\Administrator\Desktop\test.csv",index=False)
cur.close()
conn.close()

第二种、直接使用psycopg2的连接信息

import psycopg2
import pandas as pd
import time
conn = psycopg2.connect(
    database = "zhejiang",
    user= "admin" ,
    password="admin",
    host='localhost',
    port='8888')
cur = conn.cursor()
#自动获取入库表结构
cur.execute("SELECT column_name FROM information_schema.columns  where LOWER(TABLE_NAME) ='dm_autoalarm_eshop_order_stat_test'")
rows = cur.fetchall()
#print(rows)
fields = [row[0] for row in rows]
print(fields)
cur.close()
time.sleep(3)
full_fields = ','.join(fields)
print(full_fields)
time.sleep(3)
sql = 'select '+ full_fields + ' from zhejiang.dm_autoalarm_eshop_order_stat_test ;'
print(sql)
df= pd.read_sql(sql,conn)
df.to_csv(r"C:\Users\Administrator\Desktop\test.csv",index=False)
conn.close()

第三种方式、创建pgdatabase类

import psycopg2
class pgdatabase:
    def __init__(self, database, user, password, host, port):
        self.database = database
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.connection = None
        self.cursor = None

    def connect(self):
        try:
            self.connection = psycopg2.connect(
                database=self.database,
                user=self.user,
                password=self.password,
                host=self.host,
                port=self.port
            )
            self.cursor = self.connection.cursor()
        except psycopg2.Error as e:
            print("Error while connecting to PostgreSQL", e)
            # handle connection error here

    def close(self):
        if self.cursor is not None:
            self.cursor.close()
        if self.connection is not None:
            self.connection.close()

    def execute_query(self, query):
        if self.cursor is None or self.connection is None:
            print("Cursor or connection not initialized.")
            return None  # or raise an exception or return an error message
        else:
            self.cursor.execute(query)
            self.connection.commit()

其他脚本引用

from pg_database import pgdatabase
db = pgdatabase('zhejiang','admin','admin','loaclhost','8888',)
db.connect()
print(db.execute_query("SELECT column_name FROM information_schema.columns  where LOWER(TABLE_NAME) ='dm_autoalarm_eshop_order_stat_test'"))
db.close()