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 !

Alexander R. · Accepted Answer · 2015-03-22 17:38:21Z

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:

Try to Mysql's REPLACE instead of INSERT and UPDATE
Try to use ON DUPLICATE
Disable/Remove constraints, update tables, restore constraints
Use PL/SQL to write complicated and long script for updating database
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...

edited Mar 22, 2015 at 17:38

answered Mar 22, 2015 at 17:29

Alexander R.

1,75612 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Omkar Over a year ago

Sorry for late reply, I have tried REPLACE and ON DUPLICATE but it doesn't work. Disable / Remove constraints, update tables, restore constraints I cannot do that as data is live and peoples are using it. I don't have any idea about PL/SQL but I will check it out. I am still working over Python Script to sort my local file accordingly. I have applied logic you have said. But still there are some more complications.

Omkar Over a year ago

As I have edited my question, in .sql file to compare both we can consider PRIMARY KEY as uniqueness. But I have to apply logic in such way, when it start comparing online and local files, it should compare only PRIMARY KEY in both files. Which are in first few letters of each line. If that line doesn't match, write it in Insert File and if it match write it in a temp file. After that finishes, again new comparison should run. In this, temp file should again compare with online file and in that if line is not similar should write in new Update File.

Omkar Over a year ago

Thanks to you, with your logic, now I am able to sort out and create update file, where I need to just compare whole line. But I am getting trouble with comparing two files with uniqueness things. I am confused and don't know how to compare elements in two, 2D arrays (I am considering both files as two lists of multiple tuples). Please check above for my file data sample.

Omkar Over a year ago

new code I used for sorting updated file is :

for element in set(online):         if element in local:                 continue         else:             update.write(element)

Omkar Over a year ago

If I used REPLACE it gives me this error, ERROR 1451 (23000) at line 196: cannot delete or update a parent row: a foreign key constraint fails....) and if I use command line, as per your suggestion, it gives same error mentioned above,

#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

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest