Detailed Notes
Focus for this episode: Read in tables list Finding missing tables for source or Destination DB.
This tutorial walks through the creation of a database synchronization tool in Python. It continues our Python and SQL Tutorials with a useful application that provides a deeper look at SQL structures and commands.
Repository For Code: git clone [email protected]:rbroadhead/dbsync.git
Transcript Text
[Music] well welcome back we are continuing we're building our little database sync script and learning a little bit about sql along the way uh this episode we're gonna keep on going and we're gonna start looking at some table comparisons so uh this time around i have changed some stuff up and um i've gone ahead and created a database on my local machine that has no tables in it so if you go look here um i've got the tutorial database tutorial but if i just show tables there's going to be none so uh i should see and what we do is we're going to do a comparison we're going to start with that so i'm going to connect to the source database and i'm going to have this second one that i'm going to call db2 and i'm going to call it cursor 2 and this is going to be connect to db2 and that's going to be the db2 [Music] cursor okay so let's make sure it connects it does not let's see target database uh so we have to go take a look at this okay i have a different connector i need to deal with um and this does sometimes happen that's one of the fun things about things like python where you have libraries so i had to install this connector and now if i do it then i am able to let's come back here connect to source database connect to the target database so i've got that um and now i'm going to come through and what i'm going to do is i'm going to do two queries so first and let's do this this is going to be um compare tables and so what i want to do my first one is i'm going to get my results i'm going to call it source results for show tables in my first database and i'm going to come in and i'm going to do the same thing query 2 equals show tables uh it's gonna be cursor two query two actually i can keep it the same query and then uh this is gonna be target results equals cursor two fetch all and i'm going to skip this and so what i want to do is i'm going to say when i say see i want to do this probably the easiest way is i'm going to pull this so first i'm going to do um i'm going to create a let's just do a uh list of names so this will be source and so what i do here is i'm going to just do let's call it tables and i'm just going to make it an empty array and i'm going to do four row in source results so each row and remember that column one is going to be the name so i'm just going to do tables dot append and it's gonna be uh what is that row zero and let me just do this i'm gonna do print uh tables so i'm gonna do the whole thing for right now i'm just gonna print out the result and we're gonna see here is all my tables so now i can take that and i'm gonna say we're going to call this missing i'm going to create a missing array and we're going to see these tables are missing and print missing and so now i'm going to go through the target results and i am going well i don't wanna do that and uh name is equal to row zero and i'm actually going to do missing dot append the name but i only want to do it if name not in tables then i'm going to add it to the missing so i should see here oh it's not doing it so oh i'm sorry i want to do that reversed i want to take um so this would be oops this should be not in source so we're going to call this new tables and it's going to be new tables so i've got a tables missing tables and new tables so if they exist in the target but not in the source that means that they are new guys new tables and so we don't have any but we want to do the reverse or the different comparisons so now we're going to take let's do this let's do source tables and i'm going to do target tables just to be safe just to keep these so i'm gonna do that and source tables but now i'm going to also do let's create target tables uh what did i call that i call that targ results let's go ahead and just do it this way just to keep it consistent and easy to read okay so now i want that to be targettables.pin okay so that gives me that but now for the missing this is going to be missing this is going to be missing and let's see four row in source results src results name is not in target tables then i'm gonna append it and let's see how this looks so now what we've got is between the two i connect out and then i have none of the tables exist in the target that are not in the source but i have all of these tables that are in the target database but not in the um not in the source so now what i want to do is i'm going to do a little search and i'm going to say my sequel [Music] scribe table columns let's say in detail and i want to get is probably what i want okay so i can do this show tables here we go so i want to describe oh that's why i want to do it so for each of these now what i want to do is uh we're gonna do so now um for each missing table get details and so for now we're just gonna work on that so we're gonna say for table because i don't think i'm gonna so yeah okay for table in missing then i'm going to do uh query uh let's say uh let's call him query let's do describe query i'll make that easy equals describe and then plus table and now this is going to be since this is missing i need to get it from the source so i'm going to do it from cursor 1. let's do that whoops we'll just copy and paste that to make it quick so we're going to execute the describe query the describe results and then what i'm going to do here is i'm just going to do for uh let's say column and oops column in description results print the column and i'm going to do let's do this um whoops so i want to print the table just so we can see everything so now what we're going to see is it's going to get pretty complicated pretty quick but here we go so here's each of our tables and so now what we're going to want to do is we're going to have to do a sort of complicated kind of walk through and create so we're going to have uh let's see we're going to have this create sql equals and it's going to start with create table plus table and then we can do this so let's start with um that and at the end i know we're going to create sql equals create sql plus we're going to close this thing out and put our little semicolon there i think that gives us a good stopping point here because the next time we're going to start walking through this and actually build a table create on the fly to be able to sync up our tables that being said i think it's a good time to wrap it up as i said so we will go out there and get started into our day but as for all of you go out there and have us a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well welcome back we are continuing
we're building our little database sync
script and learning a little bit about
sql along the way
uh this episode we're gonna keep on
going and we're gonna start looking at
some table comparisons so
uh this time around
i have changed some stuff up
and um
i've gone ahead and created a database
on my local
machine that has no tables in it so if
you go look here
um
i've got the tutorial database
tutorial
but if i just show tables
there's going to be none
so
uh i should see and what we do is we're
going to do a comparison we're going to
start with that so i'm going to connect
to the source database
and i'm going to have this second one
that i'm going to call db2
and i'm going to call it cursor 2
and this is going to be connect to db2
and that's going to be the db2
[Music]
cursor okay
so let's make sure it connects
it
does not
let's see
target database
uh
so we have to go take a look at this
okay i have a different connector i need
to deal with
um and this does sometimes happen that's
one of the fun things about things like
python where you have libraries so i had
to install this
connector and now if i do it
then i am able to let's come back here
connect to source database connect to
the target database
so i've got that um
and now i'm going to come through
and what i'm going to do is i'm going to
do two queries so first
and let's do this this is going to be um
compare tables
and so what i want to do my first one is
i'm going to
get my results i'm going to call it
source results
for show tables in my first database
and i'm going to come in and i'm going
to do the same thing
query 2 equals show tables uh it's gonna
be cursor two query two
actually i can keep it the same query
and then
uh this is gonna be target results
equals cursor two fetch all
and
i'm going to skip this
and so what i want to do is i'm going to
say
when i say see
i want to do this probably the easiest
way
is
i'm going to pull this so first i'm
going to do um
i'm going to create
a
let's just do a
uh list of
names
so
this will be source
and so what i do here is i'm going to
just do
let's call it tables
and i'm just going to make it an empty
array
and i'm going to do four row
in source results
so each row and remember that
column one is going to be the name so
i'm just going to do tables dot append
and it's gonna be uh what is that row
zero
and let me just do this i'm gonna do
uh tables so i'm gonna do the whole
thing for right now i'm just gonna print
out the result
and we're gonna see here is
all my tables
so now
i can take that
and i'm gonna say
we're going to call this missing
i'm going to create a missing array
and we're going to
see
these
tables are missing
and print missing
and so now i'm going to go through the
target results
and i am going well i don't wanna do
that
and uh name is equal to
row zero
and i'm actually going to do missing dot
append
the name but i only want to do it if
name
not in
tables
then i'm going to add it to the missing
so
i should see here oh it's not doing it
so
oh i'm sorry i want to do that reversed
i want to take
um so this would be oops this should be
not in source
so we're going to call this new tables
and it's going to be new tables
so i've got
a tables missing tables and new tables
so if they
exist in
the target but not in the source that
means that they are new guys new tables
and so we don't have any but we want to
do the reverse or the different
comparisons so now we're going to take
let's do this
let's do source tables
and i'm going to do target tables
just to be safe just to keep these so
i'm gonna do that and
source tables
but now i'm going to also do let's
create target tables
uh what did i call that i call that targ
results
let's go ahead and just
do it this way
just to keep it
consistent and easy to read
okay so now
i want that to be targettables.pin
okay so that gives me that but now for
the
missing
this is going to be missing
this is going to be missing
and let's see four row in
source results
src results
name is not in target tables
then i'm gonna append it
and let's see how this looks so now what
we've got is
between the two i connect out and then i
have none of the tables exist in the
target that are not in the
source but
i have all of these tables
that are in the target database but not
in
the
um not in the source so now what i want
to do
is i'm going to do a little search
and i'm going to say my sequel
[Music]
scribe table columns
let's say in detail
and i want to get
is probably what i want
okay so i can do this
show tables here we go
so i want to describe oh that's why i
want to do it so
for each of these
now
what i want to do is
uh we're gonna do so now um
for each
missing table
get details and so for now we're just
gonna work on that so we're gonna say
for
table because i don't think i'm gonna so
yeah okay for table in
missing
then i'm going to do uh
query uh let's say
uh let's call him query
let's do describe query i'll make that
easy
equals
describe and then plus
table
and now this is going to be since this
is missing i need to get it from the
source so i'm going to do it from cursor
1.
let's do that whoops
we'll just copy and paste that to make
it quick
so we're going to execute the describe
query
the
describe results
and then what i'm going to do here is
i'm just going to do for
uh
let's say column and oops
column
in
description results
the column
and i'm going to do let's do this um
whoops
so i want to print the table
just so we can see everything so now
what we're going to see is it's going to
get pretty complicated pretty quick but
here we go so here's each of our tables
and so now what we're going to want to
do is we're going to have to do a sort
of complicated kind of walk through
and create
so we're going to have
uh let's see
we're going to have this create sql
equals
and it's going to start with create
table
plus
table
and then we can
do this
so let's start with um
that
and at the end i know we're going to
create sql equals
create sql plus
we're going to close this thing out and
put our little semicolon there
i think that gives us a good stopping
point here because the next time we're
going to start walking through this and
actually build a table create
on the fly to be able to sync up our
tables
that being said i think it's a good time
to wrap it up as i said so we will go
out there
and get started into our day but as for
all of you go out there and have us a
great day a great week and we will talk
to you
next time
you