grekodev · February 3, 2021 13:18 · Feb 3, 2021
diff --git a/migrate_data_mysql_to_local_hosting.py b/migrate_data_mysql_to_local_hosting.py
@@ -0,0 +1,50 @@
+import pymysql
+import pandas as pd
+import math
+import logging
+
+LOG_FILE = "logging.log"
+logging.basicConfig(filename=LOG_FILE,level=logging.DEBUG,format='%(asctime)s %(message)s', datefmt='%d/%m/%Y %H:%M:%S')
+
+try:
+    def set_log(e, block):
+        error_str = 'ERROR: '+ str(e) + ' ' + block
+        logging.error(error_str)
+        print(error_str)          
+except Exception as e:
+    set_log(e, '::FUNCTIONS')
+
+# CREATE CSV
+try:
+    conn = pymysql.connect(host='localhost',
+                             user='root',
+                             password='',
+                             database='db_name', local_infile=True)
+    cursor = conn.cursor()
+    query = 'CALL sp_select_user();'
+    results = pd.read_sql_query(query, conn)
+    results.to_csv("output.csv",header=False, index=False, sep='[')
+
+    conn.commit()
+    cursor.close()
+except Exception as e:
+    set_log(e, '::CONECTION LOCAL')
+
+#=============================================================================
+# READ CSV AND SET DATA IN HOSTING
+try:
+    conn_hosting = pymysql.connect(host='server',
+                                user='user',
+                                password='pass',
+                                database='db_server', local_infile=True)   
+
+    cursor_hosting = conn_hosting.cursor()
+    query_hosting = 'TRUNCATE TABLE db_table;'
+    cursor_hosting.execute(query_hosting)
+
+    cursor_hosting.execute("LOAD DATA LOCAL INFILE 'D:/DEV/python/output.csv' INTO TABLE db_table fields terminated by '[' lines terminated by '\n' (name_column,name_column,name_column);")
+
+    conn_hosting.commit()
+    cursor_hosting.close()
+except Exception as e:
+    set_log(e, '::CONECTION HOSTING')