0

I would like to compare the *.sql files in python and capture the difference in a new file (.sql file)

Are there any packages in python which helps in performing the below tasks.

For example:

file_1.sql

 CREATE TABLE Persons
 (
 PersonID int,
 LastName varchar(255),
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 );

file_2.sql

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
Salary int,
JobDetail int
);

Expected Output file ->

diff.sql

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
**Salary int,
JobDetail int**
);
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
sekhar Reddy
  • 49
  • 1
  • 5
  • Inside python I'm not too certain, but when using a database there is a union command used to merge data like this. See: http://stackoverflow.com/questions/1227835/sql-combine-two-tables-for-one-output – Xander Luciano Jan 12 '16 at 08:59
  • Are the attributes to the `create table` the only difference you want to merge? What about `select` statements, `join` statements etc. – sushant Jan 12 '16 at 09:00
  • How will the differences actually be highlighted in the output .sql file? – sptrks Jan 12 '16 at 09:01
  • It's not a python module (but you could call it via subprocess), but you should look into `diff`. Running `diff file_1.sql file_2.sql` from your terminal will highlight the differences between the files. – Bailey Parker Jan 12 '16 at 09:02
  • @sekhar-reddy I second Bailey. A unix command will be better than a long program – Andy K Jan 12 '16 at 09:03
  • This is one scenario which i represented above, if there is a scenario like:file_1.sql CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); file_2.sql City is deleted CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), Salary int, JobDetail int ); Expected Output file -> diff.sql CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), Salary int, JobDetail int ); – sekhar Reddy Jan 12 '16 at 09:07
  • Why and how on earth is this an off-topic question, I don't understand. Maybe the OP doesn't clearly know how to ask which is very common for newbies. But downvoting and closing only demotivates askers. And you can see one of those who voted to close is named SuperBiasedMan :3 – Sнаđошƒаӽ Jan 20 '16 at 08:27

1 Answers1

2

You can use difflib python module for this:

from difflib import Differ
from pprint import pprint    

d = Differ()
result = list(d.compare(open('1.sql', 'r').readlines(), open('2.sql', 'r').readlines()))
pprint(result)
Dima Kudosh
  • 7,126
  • 4
  • 36
  • 46
  • Thanks for your answer – sekhar Reddy Jan 12 '16 at 09:10
  • Hi, can i get the output with out symbols of '+' '-' '/n'. The following is the output:[' CREATE TABLE Persons\n', ' (\n', ' PersonID int,\n', ' LastName varchar(255),\n', ' FirstName varchar(255),\n', ' Address varchar(255),\n', '+ SALARY int,\n', '+ JobDetails int\n', ' ); \n'] – sekhar Reddy Jan 12 '16 at 09:21
  • '+', '-' shows difference in files. Look at this table https://docs.python.org/2/library/difflib.html#difflib.Differ . So you can write some function that analyzes first character on each string and do something with similar or different strings. For removing this symbols you can write map(lambda x: x[2:], result) – Dima Kudosh Jan 12 '16 at 09:36
  • thanks for your reply, i will check the same. – sekhar Reddy Jan 12 '16 at 09:39