UPDATE existing and INSERT new database using Python File I/O or MySQL query

Question

What I have or I am using:
I have two Database -
1. Online Database (table contain about 37k entries),
2. My local Database (table contain about 55k entries).

I am using phpMyAdmin on both, Server as well as Local.
Online phpMyAdmin and Local phpMyAdmin version - 5.5.42-cll - MySQL Community Server (GPL) (Both are same)

---

What I am trying to do:
I want to update existing data and insert new data in a table on my Online Database from My local Database.
Note: I cannot drop or replace table as it is interconnected. It gives me FOREIGN KEY constraint error and PRIMARY KEY constraint error.

---

What I tried to do:
I tried to export from my local database, and import into online database using phpMyAdmin but I got error this error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''CHEA B C13279', 'CHE', 'CHK', 'Reaction kinetics can be studied by', '0', '    ' at line 1 

I have tried exporting different types of file formats like SQL, CSV, XML, etc as well as customized SQL files with INSERT, UPDATE and REPLACE but I failed.

---

What I think to do:
Export my local and online database in SQL INSERT file format, then compare both file using Python File I/O and String formatting and create two new files to UPDATE existing data in database table and INSERT new data database table.

---

My Python code till now:

online = "questions(online).sql"
local = "questions(local).sql"

temp1 = "temp/questions(online)"
temp2 = "temp/questions(local)"

newFileName ="questions(insert).sql"
newFileName2 ="questions(update).sql"

open(newFileName, "a") as insert
open(newFileName2, "a") as update

with open(local, "r") as localfile:
    with open(online, "r") as onlinefile:
        for line in localfile:
            for line2 in onlinefile:
                if line == line2:
                    update.write(line)
                else:
                    insert.write(line)
insert.close()
update.close()

After running above script I get output as:
In update file only 1 line written.
In Insert file all entries -1 of online file written.

Note:
1. I personally edited more than 15k entries on my local machine, So it is not possible that only 1 entry is different while comparing both database.
2. In Insert file non duplicate entries should have been written, but it wrote all entries from online database except duplicate in update file.
3. Actually the uniqueness in line of both file or Primary Key of database is in first few letters. But I don't know how to compare line using only those first letters of each lines in both files.

---

EDITS:
Text in both SQL files is similar to this:

Online Database File Sample

('CHEA B C13279', 'CHE', 'CHK', 'Reaction kinetics can be studied by', 0),
('CHEA B C13281', 'CHE', 'CHK', 'Half - life is independent', 0),
('CHEA B C15590', 'CHE', 'Elo', 'A horizontal plank has a rectangular block', 0),
('PHYPTEST3217', 'PHY', 'PT', 'Two particles of same mass and charge', 0),
('PHYMP34780', 'PHY', 'MP', 'What id the momentum of X-rays', 0),
('MATCUS10678', 'MAT', 'DC', 'If $\\int x^{-3}.{5^{x^\\frac{1}{^2}}}dx{5^{x^\\frac{1}{^2}}} $  then $ k $  is', 0),

Local Database File Sample

('CHEA B C13279', 'CHE', 'CHK', 'Reaction kinetics can be studied by', '0'), #Unchanged content

('CHEA B C13281', 'CHE', 'CHK', 'This is edited and updated', '0'), #Updated content
('CHEA B C15590', 'CHE', 'Elo', 'This is edited and updated', '0'), #Updated content
('PHYPTEST3217', 'PHY', 'PT', 'This is edited and updated', 0), #Updated content

('PHYMP34780', 'PHY', 'MP', 'What id the momentum of X-rays', 0), #New contents
('MATCUS10678', 'MAT', 'DC', 'If $\\int x^{-3}.{5^{x^\\frac{1}{^2}}}dx{5^{x^\\frac{1}{^2}}} $  then $ k $  is', 0), #New contents
('CHEVHV14696', 'CHE', 'TCE', 'Variation of heat of reaction with temperature is given by Kirchhoff''s equation, which is :', '0'), #New contents
('MAT2D35456', 'MAT', '2D', 'The shortest distance between the parabolas $y^{2}=4x$ and $y^{2}=2x-6$ is', '0'), #New contents
('PHYHaT33353', 'PHY', 'TP', 'What is the unit $pV$ in the gas equation $pV = \\ce{RT}?$', '0', '$\\ce{Nm}$', '0'), #New contents
('PHYWEP4745', 'PHY', 'WEP', 'A rubber ball of mass m and radius r is submerged', '0'), #New contents

All can be considered as tuples. So that I have two lists (files), local with 55,263 tuples and online with 37548 tuples. Each tuple have equal number of elements i.e 21 elements, which are strings, some special characters (like $, ^, etc) and integers.

---

Please help me out solving this issue either way, using Python or if there any way using SQL query. I am a beginner in languages.

Thank you in advance !

Answer 1

Your solution is wrong. And you logic is wrong too. Explanation:

Local db file:

Line 1: aaa
Line 2: bbb
Line 3: ccc

Online db file:

Line 1: aaa
Line 2: bbb
Line 3: ccc

Now just run your code line by line inside loops:

Iteration 1:

line = aaa
line2 = aaa

**update**.write(line ) => **line == aaa**

Iteration 2:

line = aaa
line2 = bbb

**insert**.write(line ) => **line == aaa**

Iteration 3:

line = aaa
line2 = ccc

**insert**.write(line )  => **line == aaa**

What happened? You just wrote three times, the same line. Once to UPDATE, and two times to INSERT.

What exactly happens, is that you are doing 37k*55k comparisons. And each time your are writing line to some file.

This is wrong way to do.

Possible solutions:

1. Try to Mysql's REPLACE instead of INSERT and UPDATE
2. Try to use ON DUPLICATE
3. Disable/Remove constraints, update tables, restore constraints
4. Use PL/SQL to write complicated and long script for updating database
5. Put all local data as rows to Python's set data structure. then run only on online file, check if line exists in set, and write to update/insert file. But it will slow and require too much memory.

But, why not just to export new edited database by Mysql's one line command in few seconds? Upload your local .sql file to server. You even can use zipped file for quick upload. Extract it on server. An then run this command in MySQL's command line:

mysql -u mysql_username -p new_db_name < /path/to/your/edited/database_local.sql

Then check that all is working, and rename databases, or just force your app to use new db...