55 lines
1.6 KiB
Python
55 lines
1.6 KiB
Python
import os
|
|
import psycopg2
|
|
from psycopg2 import sql
|
|
from dotenv import load_dotenv
|
|
|
|
load_dotenv()
|
|
|
|
def get_db_connection():
|
|
try:
|
|
conn = psycopg2.connect(
|
|
host=os.getenv("DB_HOST"),
|
|
port=os.getenv("DB_PORT"),
|
|
database=os.getenv("DB_NAME"),
|
|
user=os.getenv("DB_USER"),
|
|
password=os.getenv("DB_PASSWORD")
|
|
)
|
|
return conn
|
|
except Exception as e:
|
|
print(f"Error connecting to database: {e}")
|
|
return None
|
|
|
|
def create_table():
|
|
conn = get_db_connection()
|
|
if not conn:
|
|
return
|
|
|
|
try:
|
|
with conn.cursor() as cur:
|
|
# Create table with requested fields
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS google_review (
|
|
id SERIAL PRIMARY KEY,
|
|
review_id TEXT UNIQUE,
|
|
place_id TEXT,
|
|
original_text TEXT,
|
|
author_display_name TEXT,
|
|
publish_time TIMESTAMP,
|
|
rating INTEGER,
|
|
outlet_code VARCHAR(255),
|
|
language VARCHAR(10),
|
|
phone VARCHAR(50),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_outlet_code FOREIGN KEY (outlet_code) REFERENCES master_outlet(popcorn_code) ON DELETE SET NULL
|
|
);
|
|
""")
|
|
conn.commit()
|
|
print("Table 'google_review' verified/created successfully.")
|
|
except Exception as e:
|
|
print(f"Error creating table: {e}")
|
|
finally:
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
create_table()
|