Buckets:

download
raw
6.57 kB
import psycopg2
from psycopg2.extras import RealDictCursor
import pandas as pd
from typing import Optional, List, Dict, Any
from config import config
from sqlalchemy import create_engine
from sqlalchemy.engine import URL
class PostgresConnector:
"""
Enhanced PostgreSQL connector with environment config support
Supports both direct parameters and automatic config loading
"""
def __init__(
self,
host: Optional[str] = None,
port: Optional[int] = None,
dbname: Optional[str] = None,
user: Optional[str] = None,
password: Optional[str] = None,
use_config: bool = True
):
"""
Initialize PostgreSQL connector
If use_config=True and parameters are None, loads from environment config
"""
if use_config:
self.conn_params = {
"host": host or config.POSTGRES_HOST,
"port": port or config.POSTGRES_PORT,
"dbname": dbname or config.POSTGRES_DB,
"user": user or config.POSTGRES_USER,
"password": password or config.POSTGRES_PASSWORD,
}
else:
self.conn_params = {
"host": host,
"port": port,
"dbname": dbname,
"user": user,
"password": password,
}
self.conn = None
def connect(self):
"""Establish database connection"""
if self.conn is None or self.conn.closed:
self.conn = psycopg2.connect(**self.conn_params)
return self
def execute(self, query: str, params: Optional[tuple] = None, fetch: bool = False) -> Optional[List[Dict]]:
"""
Execute a SQL query
Returns list of dicts if fetch=True, None otherwise
"""
if not self.conn or self.conn.closed:
raise ConnectionError("Not connected to database. Call connect() first.")
with self.conn.cursor(cursor_factory=RealDictCursor) as cur:
cur.execute(query, params)
result = cur.fetchall() if fetch else None
# Always commit for INSERT/UPDATE/DELETE queries
# Handle both string queries and psycopg2.sql.Composed objects
query_str = query.as_string(self.conn) if hasattr(query, 'as_string') else str(query)
if not query_str.strip().upper().startswith('SELECT'):
self.conn.commit()
return result
def query_to_dataframe(self, query: str, params: Optional[tuple] = None) -> pd.DataFrame:
"""
Execute query and return results as pandas DataFrame
Perfect for data transformation pipelines
"""
if not self.conn or self.conn.closed:
raise ConnectionError("Not connected to database. Call connect() first.")
return pd.read_sql_query(query, self.conn, params=params)
def get_tables(self) -> List[str]:
"""Get list of all tables in current database"""
query = """
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;
"""
results = self.execute(query, fetch=True)
return [row['tablename'] for row in results]
def get_table_info(self, table_name: str) -> pd.DataFrame:
"""Get column information for a specific table"""
query = """
SELECT
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_name = %s
ORDER BY ordinal_position;
"""
return self.query_to_dataframe(query, (table_name,))
def insert_dataframe(self, df: pd.DataFrame, table_name: str, if_exists: str = 'append'):
"""
Insert pandas DataFrame into PostgreSQL table using SQLAlchemy
Args:
df: DataFrame to insert
table_name: Target table name (can include schema, e.g., 'raw.customers')
if_exists: 'fail', 'replace', or 'append' (default)
"""
# Create SQLAlchemy engine URL securely (password not exposed in string representation)
url = URL.create(
drivername="postgresql+psycopg2",
username=self.conn_params['user'],
password=self.conn_params['password'],
host=self.conn_params['host'],
port=self.conn_params['port'],
database=self.conn_params['dbname']
)
engine = create_engine(url)
# Parse table name and schema
if '.' in table_name:
schema, table = table_name.split('.')
else:
schema = None
table = table_name
# Insert using SQLAlchemy engine
df.to_sql(
table,
engine,
schema=schema,
if_exists=if_exists,
index=False,
method='multi'
)
engine.dispose()
def test_connection(self) -> bool:
"""Test if connection is working"""
try:
result = self.execute("SELECT 1 as test;", fetch=True)
return result[0]['test'] == 1
except Exception as e:
print(f"Connection test failed: {e}")
return False
def close(self):
"""Close database connection"""
if self.conn and not self.conn.closed:
self.conn.close()
def __enter__(self):
"""Context manager entry"""
return self.connect()
def __exit__(self, exc_type, exc_val, exc_tb):
"""Context manager exit"""
self.close()
# Usage example
if __name__ == "__main__":
print("Testing PostgreSQL Connector with Environment Config\n")
print("=" * 60)
# Option 1: Use environment config (recommended)
with PostgresConnector() as pg:
print("✓ Connected using environment config")
print(f" Database: {pg.conn_params['dbname']}")
print(f" User: {pg.conn_params['user']}")
print(f" Host: {pg.conn_params['host']}")
if pg.test_connection():
print("\n✓ Connection test passed!")
# Get list of tables
tables = pg.get_tables()
print(f"\n📋 Found {len(tables)} tables in database")
for i, table in enumerate(tables[:5], 1):
print(f" {i}. {table}")
if len(tables) > 5:
print(f" ... and {len(tables) - 5} more")
else:
print("\n✗ Connection test failed")

Xet Storage Details

Size:
6.57 kB
·
Xet hash:
7347f3b24892b79473331be06c7bb59887e84cec491ef32313abe18e120ca6e6

Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.