Schreiben Sie mit Python in ein anderes Schema in PostgreSQLPython

Python-Programme
Guest
 Schreiben Sie mit Python in ein anderes Schema in PostgreSQL

Post by Guest »

Hallo, ich habe einen Python -Code, in dem ich einige Daten aus der PostgreSQL -Tabelle ( Schema der Tabellen in einem db ) lese und einige Transformationen durchführen und dann einige Daten zurück in die postgresql -Tabelle schreiben, aber in einem anderen Schema So gleiche Datenbank, aber unter einem anderen Schema. Mein Code schreibt jedoch die Daten oder das Erstellen von Tabellen und das Schreiben von Daten in das gleiche Schema. < /p>
unten ist mein Code. Im folgenden Code gebe ich den Namen und den Tabellennamen des Schemas in der Variablen 'table_name', aber es hilft mir nicht. < /p>

Code: Select all

import pandas as pd
import psycopg2
from sqlalchemy import create_engine, JSON
import json

# Function to convert PostgreSQL data types to Snowflake data types
def convert_data_type(pg_data_type):
conversion_dict = {
'integer': 'NUMBER',
'bigint': 'NUMBER',
'smallint': 'NUMBER',
'serial': 'NUMBER',
'bigserial': 'NUMBER',
'decimal': 'NUMBER',
'numeric': 'NUMBER',
'real': 'FLOAT',
'double precision': 'FLOAT',
'money': 'FLOAT',
'character varying': 'VARCHAR',
'varchar': 'VARCHAR',
'character': 'CHAR',
'char': 'CHAR',
'text': 'STRING',
'bytea': 'BINARY',
'timestamp without time zone': 'TIMESTAMP_NTZ',
'timestamp with time zone': 'TIMESTAMP_TZ',
'date': 'DATE',
'time without time zone': 'TIME_NTZ',
'time with time zone': 'TIME_TZ',
'boolean': 'BOOLEAN'
}
return conversion_dict.get(pg_data_type, pg_data_type)

pg_dbname = "my_db_name"
pg_user = "my_user"
pg_password = "My_pw"
pg_host = "My_host"
pg_port = "5432"

# Connect to PostgreSQL database
conn = psycopg2.connect(
dbname=pg_dbname,
user=pg_user,
password=pg_password,
host=pg_host,
port=pg_port
)

# Create a cursor object
cur = conn.cursor()

# Query to get table schemas
cur.execute("""
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name
""")

# Fetch all results
results = cur.fetchall()

# Close the cursor and connection
cur.close()
conn.close()

# Process the results and create a DataFrame
data = []
for row in results:
table_schema, table_name, column_name, data_type = row
converted_data_type = convert_data_type(data_type)
data.append([table_schema, table_name, column_name, data_type, converted_data_type])

df = pd.DataFrame(data, columns=['table_schema', 'table_name', 'column_name', 'original_data_type', 'converted_data_type'])

# Grouping data and making dictionary
result = (
df.groupby(['table_schema', 'table_name'])
.apply(lambda x: pd.Series({
'columns': dict(zip(x['column_name'], x['converted_data_type'])),
'original_columns': dict(zip(x['column_name'], x['original_data_type']))
}))
.reset_index()
)

# Create SQLAlchemy engine
engine = create_engine(f'postgresql+psycopg2://{pg_user}:{pg_password}@{pg_host}:{pg_port}/{pg_dbname}')

# Define the table name in the new schema
table_name = 'project_demo.mapping_table'

# Insert the DataFrame into the PostgreSQL table in the new schema
result.to_sql(table_name, engine, if_exists='replace', index=False, dtype={'columns': JSON, 'original_columns': JSON})

print("Data inserted successfully!")

Ich habe versucht, den Schema -Namen in der Variablen

Quick Reply

Change Text Case: 
   
  • Similar Topics
    Replies
    Views
    Last post