Python MySQL Comparing two tables

Question

I want to learn how to compare two tables in order to find unmatched records.

I´m a total beginner to MySQL and therefore struggling currently in order to find out which query to use for comparing two tables.

Suppose, I have two tables that are identical in structure.

Table 1 (Old)

+------+--------+---------------+--------+-----------+
| "id" | "name" | "price" | "description" | "country" |
+------+--------+---------------+--------+-----------+
| "1"  | "a"    | "2"     | "Lord "       | "US"      |
| "2"  | "b"    | "3"     | "Taker"       | "UK"      |
+------+--------+---------------+--------+-----------+

Table 2 (New)

+------+--------+---------------+--------+-----------+
| "id" | "name" | "price" | "description" | "country" |
+------+--------+---------------+--------+-----------+
| "1"  | "a"    | "5"     | "Lord"        | "DE"      |
| "2"  | "b"    | "6"     | "Taker"      | "JP"      |
+------+--------+---------------+--------+-----------+

Table1 holds the outdated data and table2 the newest one.

I want to check if name, price, description fields from table 2 have any match to name, price, description fields in table 1. If not, it should update table 1 with data from table 2.

I checked out the folling site in order to find out to use which Mysql query: http://www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx

But I cannot figure out how to write it down in Python.

I am new to using SQL statements in python and I have attempted it as,

 import sys, mysql.connector
 import mysql

 try:
     connection = mysql.connector.connect\
         (host = "localhost", user = "root", passwd ="", db = "local")
 except:
     print("No connection")
     sys.exit(0)

 cursor = connection.cursor()
 cursor.execute("SELECT name, price, description FROM table2_new WHERE

I´m do not really have a clue how to continue. I searched for similar tasks but do not fully understand how to do it in Python.

Thats the desired output for table 1, the prices should be updated:

    +------+--------+---------------+--------+-----------+
    | "id" | "name" | "price" | "description" | "country" |
    +------+--------+---------------+--------+-----------+
    | "1"  | "a"    | "5"     | "Lord "       | "US"      |
    | "2"  | "b"    | "6"     | "Taker"       | "UK"      |
    +------+--------+---------------+--------+-----------+

Could you guys help me out? Any feedback is appreciated.

Since you are a beginner you have automated tasks that can compare and sync the data that are available in Workbench,SQLyog,Navicat,Toad. [here](http://stackoverflow.com/questions/225772/compare-two-mysql-databases) — marklong, Dec 10 '15 at 14:10
Please provide the expected output!! i.e. `table_1` after `update` — Praveen, Dec 10 '15 at 14:11
If it's not too late I would suggest to change your DB structure. Merge the two tables and add a column that indicates if the item is outdated or not. — Gonzalo, Dec 10 '15 at 14:13
You are trying to tackle several problems at once. Go one by one. I would start with learning to deal with databases directly, without python (e.g. Toad or Navicat). Next question, since you are a beginner, are you sure you want to have two tables with the same structure? That seems like it might be useful only in some very specific advanced situations. Next, python: I would suggest that you avoid learning the low-level DB API an try an ORM - sqlalchemy is great. — zvone, Dec 10 '15 at 14:14

score 1 · Answer 1 · answered Dec 10 '15 at 14:27

1

Try;

update table_1 a
join table_2 b
on a.`name` = b.`name` and a.`description` = b.`description`
set a.`price` = b.`price`

Demo sqlfiddle

This query will update price of table_1 with price of table_2, if description and name match. (beware of more than one match which wont give the result that you expect)

answered Dec 10 '15 at 14:27

Praveen

8,945
4
31
49

Thanks for your feedback. What do you exactly mean with "(beware of more than one match which wont give the result that you expect)" I dont get it fully – Serious Ruffy Dec 10 '15 at 20:02

score 1 · Answer 2 · answered Dec 10 '15 at 14:35

1

your SQL Query will be something along the lines of:

UPDATE t1
SET t1.name = t2.name
from table1 as t1
inner join table2 as t2
on t1.id = t2.id
AND t1.name != t2.name

Then repeat for each field.

answered Dec 10 '15 at 14:35

ctrl-alt-delete

3,696
2
24
37

score 1 · Accepted Answer · answered Dec 10 '15 at 14:44

At first, by which field(-s) your tables binds? Let's name it bind_field. If there are more than one field, then it will be bind_field1, bind_field2 and other. Your query:

UPDATE table1 t1
INNER JOIN table2 t2
ON t1.bind_field = t2.bind_field [AND t1.bind_field1 = t2.bind_field2 ...]
SET t1.name = t2.name, t1.price = t2.price, t1.description = t2.description

The query updates fields name, price, description of table1 with values from table2 only for rows, which have same bind_field (-s, if there are more than one bind field).

ON statement allows you how your tables bind. SET statement allows you to control updating values.

And it seems to me that you use text (VARCHAR or same) type field with id. If this is it, than your should change it to number-type field like INTEGER;

P.S. Sorry for my English :)

Thanks for your feedback. Appreciated it. Stupid question: I have to write it like that in python, right? cursor.execute("UPDATE table1 t1") cursor.execute("INNER JOIN table2 t2") Thanks for your feedback:) — Serious Ruffy, Dec 10 '15 at 15:50
no, that is one fully query: cursor.execute('update table1 t1 inner join table2 t2....') — antonio_antuan, Dec 11 '15 at 16:43

Python MySQL Comparing two tables

3 Answers3