94 lines
3.3 KiB
Python
94 lines
3.3 KiB
Python
import mysql.connector
|
|
import sys
|
|
from mysql.connector import Error
|
|
|
|
def copy_tomorrow_data():
|
|
source_conn = None
|
|
dest_conn = None
|
|
|
|
# ---- Gegevens voor de BRON (alfen) ----
|
|
source_db_config = {
|
|
'host': '127.0.0.1',
|
|
'database': 'alfen',
|
|
'user': 'alfen_user',
|
|
'password': '5uVgr%f%s2P5GR@3q!',
|
|
'port': 3307
|
|
}
|
|
|
|
# ---- Gegevens voor de BESTEMMING (energy_prices) ----
|
|
dest_db_config = {
|
|
'host': '127.0.0.1',
|
|
'database': 'energy_prices',
|
|
'user': 'energy_prices_user',
|
|
'password': 'kS9R*xp17ZwCD@CV&E^N',
|
|
'port': 3307
|
|
}
|
|
|
|
try:
|
|
source_conn = mysql.connector.connect(**source_db_config)
|
|
dest_conn = mysql.connector.connect(**dest_db_config)
|
|
|
|
if source_conn.is_connected() and dest_conn.is_connected():
|
|
source_cursor = source_conn.cursor(dictionary=True)
|
|
dest_cursor = dest_conn.cursor()
|
|
|
|
# 1. Maak de doeltabel aan als deze niet bestaat
|
|
# Let op: UNIQUE KEY op datetime is nodig voor ON DUPLICATE KEY UPDATE
|
|
create_table_query = """
|
|
CREATE TABLE IF NOT EXISTS dynamic_price_data_tommorow (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
datetime DATETIME NOT NULL,
|
|
price DECIMAL(10, 4) NOT NULL,
|
|
UNIQUE KEY unique_datetime (datetime)
|
|
) ENGINE=InnoDB;
|
|
"""
|
|
dest_cursor.execute(create_table_query)
|
|
|
|
# 2. Data ophalen uit de bron (alfen)
|
|
print("Data selecteren uit bron...")
|
|
select_query = """
|
|
SELECT datetime, price
|
|
FROM dynamic_price_data_tommorow
|
|
WHERE DATE(datetime) = CURDATE() + INTERVAL 1 DAY
|
|
"""
|
|
source_cursor.execute(select_query)
|
|
rows_to_copy = source_cursor.fetchall()
|
|
|
|
if not rows_to_copy:
|
|
print("Geen data voor morgen gevonden in de bron.")
|
|
return
|
|
|
|
# --- NIEUW: Eerst de doeltabel leegmaken ---
|
|
print("Oude data verwijderen uit bestemming...")
|
|
dest_cursor.execute("DELETE FROM dynamic_price_data_tommorow")
|
|
# ------------------------------------------
|
|
|
|
# 3. Data invoeren in de bestemming
|
|
insert_query = """
|
|
INSERT INTO dynamic_price_data_tommorow (datetime, price)
|
|
VALUES (%s, %s)
|
|
"""
|
|
|
|
# We hebben ON DUPLICATE KEY UPDATE niet meer strikt nodig als we net DELETE hebben gedaan,
|
|
# maar het kan geen kwaad om het simpel te houden.
|
|
|
|
rows_data = [(row['datetime'], row['price']) for row in rows_to_copy]
|
|
dest_cursor.executemany(insert_query, rows_data)
|
|
|
|
dest_conn.commit()
|
|
print(f"Succes! {len(rows_data)} rijen vers gekopieerd.")
|
|
except Error as e:
|
|
print(f"Fout tijdens proces: {e}", file=sys.stderr)
|
|
sys.exit(1)
|
|
|
|
finally:
|
|
if source_conn and source_conn.is_connected():
|
|
source_cursor.close()
|
|
source_conn.close()
|
|
if dest_conn and dest_conn.is_connected():
|
|
dest_cursor.close()
|
|
dest_conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
copy_tomorrow_data()
|